面试:详细介绍PostgreSQL锁类型、锁机制

PostgreSQL 锁机制深度解析

PostgreSQL 的锁机制是其高并发性能的核心，采用多粒度锁设计，既保证数据一致性又最大化并发性能。以下从锁类型、机制、场景三方面全面解析：

---

一、PostgreSQL 锁分类全景图

---

二、核心锁类型详解

1. 表级锁（Table-Level Locks）

(1) ACCESS SHARE（访问共享锁）

• 机制：SELECT 自动获取
• 冲突：仅与 ACCESS EXCLUSIVE 冲突
• 场景：普通查询时允许并发读

-- 会话1
SELECT * FROM orders; -- 获取ACCESS SHARE

-- 会话2
TRUNCATE orders; -- ACCESS EXCLUSIVE 被阻塞

(2) ROW SHARE（行共享锁）

• 机制：SELECT FOR UPDATE/SHARE 自动获取
• 冲突：与 EXCLUSIVE 和 ACCESS EXCLUSIVE 冲突
• 场景：计划更新特定行

SELECT * FROM accounts 
WHERE id=100 FOR UPDATE; -- 获取ROW SHARE

(3) ROW EXCLUSIVE（行排他锁）

• 机制：INSERT/UPDATE/DELETE 自动获取
• 冲突：与 SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 冲突
• 场景：DML 操作时保护表结构

UPDATE accounts SET balance=0; -- 获取ROW EXCLUSIVE

(4) SHARE UPDATE EXCLUSIVE（共享更新排他锁）

• 机制：VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY
• 冲突：与自身及更强锁冲突
• 场景：避免并发的 schema 修改

CREATE INDEX CONCURRENTLY idx_name ON users(name);

(5) ACCESS EXCLUSIVE（访问排他锁）

• 机制：DROP TABLE, TRUNCATE, REINDEX, VACUUM FULL
• 冲突：与所有锁冲突
• 场景：DDL 操作时独占表

ALTER TABLE orders ADD COLUMN notes TEXT; -- 获取ACCESS EXCLUSIVE

2. 行级锁（Row-Level Locks）

(1) FOR UPDATE（排他锁）

• 机制：SELECT FOR UPDATE 显式获取
• 冲突：阻塞其他 FOR UPDATE 和 FOR NO KEY UPDATE
• 场景：更新前的行锁定

SELECT * FROM products 
WHERE id=123 FOR UPDATE; -- 准备更新库存

(2) FOR NO KEY UPDATE（非键排他锁）

• 机制：更新非唯一键列时自动获取
• 冲突：允许 FOR KEY SHARE 并发
• 场景：更新非关键字段

UPDATE employees SET salary=5000; -- 自动获取

(3) FOR SHARE（共享锁）

• 机制：SELECT FOR SHARE 显式获取
• 冲突：阻塞 FOR UPDATE 但允许多个共享锁
• 场景：确保读取期间行不被修改

SELECT * FROM flights 
WHERE id=789 FOR SHARE; -- 查看航班座位

(4) FOR KEY SHARE（键共享锁）

• 机制：外键引用时自动获取
• 冲突：仅阻塞 FOR UPDATE
• 场景：维护引用完整性

-- 删除主表行时，子表自动加FOR KEY SHARE
DELETE FROM departments WHERE id=10;

3. 咨询锁（Advisory Locks）

• 机制：应用层控制的逻辑锁
• 类型：
  • 会话级：pg_advisory_lock()
  • 事务级：pg_advisory_xact_lock()
• 场景：分布式任务调度

-- 获取全局任务锁
SELECT pg_advisory_lock(123);
-- 执行任务...
SELECT pg_advisory_unlock(123);

---

三、锁机制核心原理

1. 锁兼容性矩阵

请求锁\已存在锁	ACCESS SHARE	ROW SHARE	ROW EXCL	SHARE	EXCLUSIVE	ACCESS EXCL
ACCESS SHARE	✔️	✔️	✔️	✔️	❌	❌
ROW SHARE	✔️	✔️	✔️	✔️	❌	❌
ROW EXCLUSIVE	✔️	✔️	✔️	❌	❌	❌
SHARE	✔️	✔️	❌	✔️	❌	❌
EXCLUSIVE	❌	❌	❌	❌	❌	❌
ACCESS EXCLUSIVE	❌	❌	❌	❌	❌	❌

2. 死锁检测机制

3. 锁升级流程

---

四、通俗场景举例

案例1：银行柜台业务（行级锁）

-- 柜台1：转账操作
BEGIN;
SELECT * FROM accounts WHERE id=101 FOR UPDATE; -- 锁定账户101
UPDATE accounts SET balance=balance-100 WHERE id=101;
UPDATE accounts SET balance=balance+100 WHERE id=102;
COMMIT;

-- 柜台2：同时查询账户101
SELECT balance FROM accounts WHERE id=101; -- 正常读取旧版本（MVCC）

比喻：柜台1操作时拉上隔断帘（FOR UPDATE），其他人可查看账户历史（MVCC读取），但不能修改

案例2：机票预订系统（咨询锁）

-- 节点1：锁定航班CA123
SELECT pg_advisory_lock(flight_id('CA123'));

-- 节点2：尝试锁定同航班
SELECT pg_advisory_lock(flight_id('CA123')); -- 等待或超时

比喻：空中交通管制塔台独占指挥权（咨询锁），其他塔台需等待

案例3：数据库维护（表级锁）

-- 维护时段执行
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
VACUUM FULL orders;

比喻：商场夜间全面清场（ACCESS EXCLUSIVE），进行深度清洁

案例4：库存管理系统（行锁冲突）

-- 用户A：购买最后一件商品
SELECT stock FROM products WHERE id=5 FOR UPDATE; -- stock=1
UPDATE products SET stock=0 WHERE id=5;

-- 用户B：同时购买同商品
SELECT stock FROM products WHERE id=5 FOR UPDATE; -- 阻塞等待

比喻：超市收银台排队结账，最后一件商品只能卖给第一顺位顾客

---

五、关键监控与优化

1. 锁监控命令

-- 查看阻塞关系
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks 
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
WHERE NOT blocked_locks.granted;

2. 锁优化策略

1. 索引优化：确保WHERE条件走索引，避免全表扫描升级锁

   CREATE INDEX idx_account_id ON accounts(id);
   

2. 短事务原则：减少锁持有时间

   BEGIN;
   -- 快速操作
   COMMIT; 
   

3. 锁超时设置：防止长时间等待

   SET lock_timeout = '2s'; -- 2秒超时
   

4. 谨慎DDL：避免业务高峰执行

   -- 使用CONCURRENTLY选项
   CREATE INDEX CONCURRENTLY idx_name ON users(name);
   

血泪教训：某电商在 UPDATE orders SET status=2 WHERE status=1 未建索引，导致全表被锁30分钟！结论：WHERE条件必须索引化！

---

六、总结：PostgreSQL锁的三大特性

1. 多版本并发控制（MVCC）

   • SELECT操作不阻塞DML
   • DML操作不阻塞SELECT

2. 锁粒度智能升级

   • 默认行级锁，必要时升级到表级锁
   • 全表扫描自动升级为ACCESS EXCLUSIVE

3. 冲突最小化设计

   • 8种表锁模式精细控制
   • 4种行锁模式精准协调

终极忠告：

• OLTP系统：优先使用 SELECT ... FOR UPDATE NOWAIT
• 批量操作：分批次提交事务
• DDL操作：务必评估锁影响，使用 CONCURRENTLY 选项
• 紧急解锁：SELECT pg_terminate_backend(阻塞PID)

欢迎关注我的公众号《IT小Chen》