锁机制

1. 表级锁

1.1 锁模式

-- 手动获取表锁
LOCK TABLE employees IN ACCESS EXCLUSIVE MODE;

2. 行级锁

2.1 行锁类型

2.2 行锁兼容性

-- 行锁示例
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

3. Advisory 锁

-- 获取 advisory 锁
SELECT pg_advisory_lock(12345);          -- 阻塞式
SELECT pg_try_advisory_lock(12345);      -- 非阻塞式

-- 释放
SELECT pg_advisory_unlock(12345);

-- 会话级锁（连接断开自动释放）
SELECT pg_advisory_lock(1, 2);  -- 双int参数

-- 事务级锁（事务结束自动释放）
SELECT pg_advisory_xact_lock(12345);

4. 查看锁

SELECT locktype, relation::regclass, mode, pid, granted
FROM pg_locks
WHERE pid != pg_backend_pid();

-- 查看阻塞
SELECT blocked.pid, blocker.pid, blocked.query, blocker.query
FROM pg_locks blocked
JOIN pg_locks blocker ON blocked.locktype = blocker.locktype
    AND blocked.database IS NOT DISTINCT FROM blocker.database
    AND blocked.relation IS NOT DISTINCT FROM blocker.relation
    AND NOT blocked.granted AND blocker.granted
    AND blocked.pid != blocker.pid;