PostgreSQL 锁机制

锁的分类

PostgreSQL 有两套锁机制：

LWLock（轻量锁） - 保护共享内存数据结构
Lock（重量锁） - 保护表级、行级操作

┌─────────────────────────────────────────────────┐
│                  PostgreSQL                       │
│                                                  │
│  ┌─────────────┐        ┌─────────────┐         │
│  │ LWLock      │        │ Lock        │         │
│  │ (轻量锁)     │        │ (重量锁)    │         │
│  │             │        │             │         │
│  │ - 缓冲区锁   │        │ - 表级锁    │         │
│  │ - WAL 锁    │        │ - 行级锁    │         │
│  │ - 事务锁    │        │ - 咨询锁    │         │
│  └─────────────┘        └─────────────┘         │
│                                                  │
└─────────────────────────────────────────────────┘

LWLock（轻量锁）

用途

保护共享内存中的数据结构
锁时间很短（微秒级）
不可见给用户，不需要手动管理

常见 LWLock

sql

-- 查看当前持有的轻量锁
SELECT * FROM pg_locks WHERE locktype = 'lwlock';

-- 查看轻量锁统计
SELECT * FROM pg_lwlock_stats;

锁名	保护内容
BufFreelist	缓冲区空闲列表
BufMapping	缓冲区页表
LockMgr	锁管理器
WALInsert	WAL 写入
WALWrite	WAL 刷盘
TransactionAbort	事务中止
ParallelQuery	并行查询

BufFreelist 锁

sql

-- 缓冲区分配时需要
-- 争用严重 → shared_buffers 太大或分配不均

WAL 锁

sql

-- WALInsert: 写入 WAL 缓冲区
-- WALWrite: 刷 WAL 到磁盘

-- 瓶颈症状：
-- SELECT wait_event FROM pg_stat_activity WHERE state = 'active';
-- 结果：WalWrite

Lock（重量锁）

锁类型

sql

-- 查看锁类型
SELECT DISTINCT locktype FROM pg_locks;

locktype	说明
relation	表级锁
tuple	行级锁
transactionid	事务锁
virtualxid	虚拟事务 ID
object	对象锁
userlock	用户自定义锁
advisory	咨询锁

relation（表级锁）

sql

-- 锁的强度（从弱到强）：
ACCESS SHARE → ROW SHARE → ROW EXCLUSIVE → SHARE UPDATE EXCLUSIVE 
→ SHARE → SHARE ROW EXCLUSIVE → EXCLUSIVE → ACCESS EXCLUSIVE

模式	被谁阻塞	典型用途
ACCESS SHARE	ACCESS EXCLUSIVE	SELECT
ROW SHARE	EXCLUSIVE, ACCESS EXCLUSIVE	SELECT FOR UPDATE
ROW EXCLUSIVE	SHARE, SHARE ROW EXCLUSIVE, ACCESS EXCLUSIVE	INSERT, UPDATE, DELETE
SHARE UPDATE EXCLUSIVE	SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE	VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY
SHARE	ROW EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE	CREATE INDEX
SHARE ROW EXCLUSIVE	ROW EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
EXCLUSIVE	ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
ACCESS EXCLUSIVE	所有	ALTER TABLE, DROP TABLE, TRUNCATE

默认表级锁

sql

-- SELECT（默认加 ACCESS SHARE）
SELECT * FROM users;  -- 允许并发 SELECT，不阻塞

-- INSERT/UPDATE/DELETE（加 ROW EXCLUSIVE）
INSERT INTO users VALUES (1);  -- 阻塞 SHARE 锁

-- ALTER TABLE（加 ACCESS EXCLUSIVE）
ALTER TABLE users ADD COLUMN age INT;  -- 阻塞所有操作

行级锁（tuple）

sql

-- SELECT FOR UPDATE
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 锁定这一行，其他事务不能修改

-- FOR UPDATE 会同时锁行 + 锁表（ROW SHARE）

模式	说明
FOR UPDATE	排他锁，禁止其他事务修改
FOR NO KEY UPDATE	类似 FOR UPDATE，但允许 KEY SHARE
FOR SHARE	共享锁，其他事务可以读但不能修改
FOR KEY SHARE	允许 KEY SHARE，不允许 FOR NO KEY UPDATE

锁等待与超时

sql

-- 设置锁等待超时
SET lock_timeout = '3s';

-- 设置语句超时（全局）
SET statement_timeout = '30s';

-- 查看当前锁
SELECT 
    pid,
    locktype,
    relation::regclass,
    mode,
    granted,
    query
FROM pg_locks
WHERE NOT granted;

-- 杀掉阻塞进程
SELECT pg_terminate_backend(pid);

死锁检测

sql

-- PostgreSQL 自动检测死锁
-- 检测到后选择一个事务回滚

-- 查看死锁日志
-- 设置 log_lock_waits = on
-- 设置 deadlock_timeout = '1s'

事务锁

transactionid

sql

BEGIN;
SELECT * FROM users WHERE id = 1;  -- 分配虚拟事务 ID
INSERT INTO users VALUES (2);       -- 分配真实事务 ID
COMMIT;

锁类型	说明
Share	读已提交隔离级别下，读取时加
Exclusive	修改时加
SIUpdate	快照更新（可串行化）
ShmemXactBuffer	事务使用的共享内存

虚拟事务ID

用于只读事务
不分配真正的 transactionid
格式：virtualxid

咨询锁

用户自定义的锁，不与实际数据关联。

sql

-- 申请咨询锁
SELECT pg_advisory_lock(12345);  -- 阻塞等待

-- 尝试获取（不阻塞）
SELECT pg_try_advisory_lock(12345);  -- 返回 true/false

-- 释放锁
SELECT pg_advisory_unlock(12345);

-- 会话级锁（退出会话自动释放）
SELECT pg_advisory_lock_shared(12345);
SELECT pg_advisory_unlock_shared(12345);

使用场景

sql

-- 防止并发执行定时任务
CREATE OR REPLACE FUNCTION run_daily_job()
RETURNS VOID AS $$
BEGIN
    -- 尝试获取锁
    IF NOT pg_try_advisory_lock(1) THEN
        RAISE NOTICE '任务已在运行';
        RETURN;
    END IF;
    
    -- 执行任务
    PERFORM my_daily_task();
    
    -- 释放锁
    PERFORM pg_advisory_unlock(1);
END;
$$ LANGUAGE plpgsql;

锁的监控

查看当前所有锁

sql

SELECT 
    pid,
    usename,
    state,
    query,
    locktype,
    relation::regclass,
    mode,
    granted,
    fastpath
FROM pg_stat_activity a
JOIN pg_locks l ON a.pid = l.pid
WHERE a.pid != pg_backend_pid()
ORDER BY a.pid, l.relation;

查看锁等待关系

sql

SELECT 
    COALESCE(blocking.pid, blocked.pid) AS pid,
    blocked.query AS blocked_query,
    blocking.query AS blocking_query,
    blocked.locktype AS blocked_lock,
    blocked.mode AS blocked_mode
FROM pg_locks blocked
JOIN pg_stat_activity blocked_stat ON blocked.pid = blocked_stat.pid
LEFT JOIN pg_locks blocking ON 
    blocking.locktype = blocked.locktype
    AND blocking.relation = blocked.relation
    AND blocking.pid != blocked.pid
    AND NOT blocking.granted
LEFT JOIN pg_stat_activity blocking_stat ON blocking.pid = blocking_stat.pid
WHERE NOT blocked.granted;

查看长时间运行的查询

sql

SELECT 
    pid,
    now() - query_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < NOW() - INTERVAL '1 minute'
ORDER BY duration DESC;

自动监控配置

sql

-- postgresql.conf

-- 记录锁等待
log_lock_waits = on

-- 死锁检测时间
deadlock_timeout = '1s'

-- 锁超时
lock_timeout = '30s'

-- 语句超时（毫秒）
statement_timeout = '30000'

常见锁问题与解决

问题1：长事务持有锁

sql

-- 原因：事务未提交，持有锁
-- 解决：找到长事务并终止

SELECT 
    pid,
    now() - xact_start AS duration,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state = 'active'
ORDER BY duration DESC;

-- 终止
SELECT pg_terminate_backend(pid);
-- 或回滚
SELECT pg_cancel_backend(pid);

问题2：VACUUM 被阻塞

sql

-- 原因：VACUUM 需要 SHARE UPDATE EXCLUSIVE 锁
-- 被更强的锁阻塞

-- 解决：
-- 1. 确认谁在持有锁
-- 2. 让持有者尽快完成
-- 3. 或等待锁超时

问题3：表结构变更阻塞

sql

-- ALTER TABLE 需要 ACCESS EXCLUSIVE 锁
-- 会阻塞所有读写操作

-- 解决：使用在线索引构建
ALTER TABLE users ADD COLUMN email VARCHAR(100);  -- 阻塞
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);  -- 不阻塞

问题4：外键锁升级

sql

-- 外键检查会锁定父表
-- 插入子表记录 → 锁定父表对应行

-- 解决：
-- 1. 在 application 层面保证数据完整性
-- 2. 使用触发器代替外键
-- 3. 批量插入时使用 SET CONSTRAINTS DEFERRED

锁的底层实现

锁表（LockTable）

// 内存中的锁表结构（简化）
typedef struct {
    LOCKTAG    tag;        // 锁的唯一标识
    int        holdMask;   // 当前持有的锁
    int        waitMask;   // 等待的锁
    PROClocale *proc;      // 等待的进程
} LOCALLOCK;

等待队列

sql

-- 锁不满足时，进程加入等待队列
-- 锁释放时，唤醒等待队列中的进程

MVCC 与锁的关系

快照读 vs 当前读

sql

-- 快照读（不需要锁）
SELECT * FROM users;  -- 读的是快照

-- 当前读（需要锁）
SELECT * FROM users FOR UPDATE;  -- 读取最新版本
UPDATE users SET name = 'new';   -- 当前读
DELETE FROM users WHERE id = 1;   -- 当前读

可见性判断中的锁

事务 A (id=100)
UPDATE users SET name='B' WHERE id=1;

事务 B (id=101)
SELECT * FROM users WHERE id=1;  -- 看到 'A' 还是 'B'？

判断：
1. 事务 100 是否提交？
2. 事务 101 的快照包含事务 100 吗？

最佳实践

sql

-- 1. 保持事务简短
BEGIN;
    -- 快进快出
COMMIT;

-- 2. 及时提交
-- 不要在事务中做耗时操作

-- 3. 使用适当的事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 默认

-- 4. 避免长时间持有锁
-- 批量操作分批提交

-- 5. 使用游标分批处理
DECLARE cursor CURSOR FOR SELECT * FROM big_table;
FETCH 1000 FROM cursor;

-- 6. 监控锁等待
-- 设置 log_lock_waits = on
-- 定期检查 pg_locks

面试要点总结

1. 锁分类
   - LWLock: 保护共享内存，透明
   - Lock: 表级锁、行级锁、事务锁、咨询锁

2. 表级锁模式（8种）
   - ACCESS SHARE → ACCESS EXCLUSIVE
   - SELECT 用 ACCESS SHARE
   - INSERT/UPDATE/DELETE 用 ROW EXCLUSIVE
   - DDL 用 ACCESS EXCLUSIVE

3. 行级锁
   - FOR UPDATE / FOR SHARE
   - 会升级为表级锁

4. 死锁
   - 自动检测
   - 回滚其中一个事务

5. 锁等待
   - pg_locks 视图
   - pg_stat_activity 结合查看
   - lock_timeout 设置超时

6. MVCC 与锁的关系
   - 快照读不需要锁
   - 当前读需要锁
   - MVCC 减少锁冲突

Spring基础

MySQL数据库

PostgreSQL数据库

核心原理

PostgreSQL 锁机制 ​

锁的分类 ​

LWLock（轻量锁） ​

用途 ​

常见 LWLock ​

BufFreelist 锁 ​

WAL 锁 ​

Lock（重量锁） ​

锁类型 ​

relation（表级锁） ​

默认表级锁 ​

行级锁（tuple） ​

锁等待与超时 ​

死锁检测 ​

事务锁 ​

transactionid ​

虚拟事务ID ​

咨询锁 ​

使用场景 ​

锁的监控 ​

查看当前所有锁 ​

查看锁等待关系 ​

查看长时间运行的查询 ​

自动监控配置 ​

常见锁问题与解决 ​

问题1：长事务持有锁 ​

问题2：VACUUM 被阻塞 ​

问题3：表结构变更阻塞 ​

问题4：外键锁升级 ​

锁的底层实现 ​

锁表（LockTable） ​

等待队列 ​

MVCC 与锁的关系 ​

快照读 vs 当前读 ​

可见性判断中的锁 ​

最佳实践 ​

面试要点总结 ​

PostgreSQL 锁机制

锁的分类

LWLock（轻量锁）

用途

常见 LWLock

BufFreelist 锁

WAL 锁

Lock（重量锁）

锁类型

relation（表级锁）

默认表级锁

行级锁（tuple）

锁等待与超时

死锁检测

事务锁

transactionid

虚拟事务ID

咨询锁

使用场景

锁的监控

查看当前所有锁

查看锁等待关系

查看长时间运行的查询

自动监控配置

常见锁问题与解决

问题1：长事务持有锁

问题2：VACUUM 被阻塞

问题3：表结构变更阻塞

问题4：外键锁升级

锁的底层实现

锁表（LockTable）

等待队列

MVCC 与锁的关系

快照读 vs 当前读

可见性判断中的锁

最佳实践

面试要点总结