PostgreSQL 锁机制

              AS  RS  RE  SUE  S  SRE  E  AE
ACCESS SHARE   -   -   -   -   -   -   -  ✗
ROW SHARE      -   -   -   -   -   -   ✗  ✗
ROW EXCLUSIVE  -   -   -   -   ✗   ✗   ✗  ✗
SHARE UPD EXC  -   -   -   ✗   ✗   ✗   ✗  ✗
SHARE          -   -   ✗   ✗   -   ✗   ✗  ✗
SHARE ROW EXC  -   -   ✗   ✗   ✗   ✗   ✗  ✗
EXCLUSIVE      -   ✗   ✗   ✗   ✗   ✗   ✗  ✗
ACCESS EXCL    ✗   ✗   ✗   ✗   ✗   ✗   ✗  ✗

-- 显式加表锁，默认 ACCESS EXCLUSIVE
LOCK TABLE orders;

-- 指定模式
LOCK TABLE orders IN SHARE MODE;
LOCK TABLE orders IN ROW EXCLUSIVE MODE;

-- 非阻塞，获取不到立即失败
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE NOWAIT;

-- 默认：阻塞等待，持有锁的事务提交或回滚后才能继续
-- 如果对方事务长时间不结束，当前查询会一直挂起
-- 可通过 SET lock_timeout = '5s' 设置最长等待时间，超时后报错
SELECT * FROM orders WHERE id = 1 FOR UPDATE;

-- NOWAIT：获取不到立即报错（ERROR: could not obtain lock on row in relation "orders"）
SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;

-- SKIP LOCKED：跳过已被锁定的行（任务队列场景常用）
SELECT * FROM tasks WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 10;

SET lock_timeout = '3s';
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- 等待超过 3 秒仍未获取到锁，抛出 ERROR: canceling statement due to lock timeout

-- Worker 抢任务：跳过其他 worker 已锁定的行
BEGIN;
SELECT id, payload
FROM tasks
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 1;

-- 处理完成后更新状态
UPDATE tasks SET status = 'done' WHERE id = $1;
COMMIT;

-- 获取排他锁 (阻塞, 直到获取成功)
SELECT pg_advisory_lock(key bigint);

-- 尝试获取排他锁 (非阻塞, 成功返回 true, 失败返回 false)
SELECT pg_try_advisory_lock(key bigint);

-- 获取共享锁 (多个会话可同时持有共享锁)
SELECT pg_advisory_lock_shared(key bigint);

-- 尝试获取共享锁 (非阻塞)
SELECT pg_try_advisory_lock_shared(key bigint);

-- 释放排他锁
SELECT pg_advisory_unlock(key bigint);

-- 释放共享锁
SELECT pg_advisory_unlock_shared(key bigint);

-- 释放当前会话所有 Advisory Lock
SELECT pg_advisory_unlock_all();

-- 获取事务级排他锁 (事务结束时自动释放, 无法手动解锁)
SELECT pg_advisory_xact_lock(key bigint);

-- 尝试获取事务级排他锁
SELECT pg_try_advisory_xact_lock(key bigint);

-- 等效于 pg_advisory_lock((int8(class) << 32) | int8(id))
SELECT pg_advisory_lock(class int4, id int4);
SELECT pg_try_advisory_lock(class int4, id int4);

SELECT pg_advisory_lock(100);   -- count = 1
SELECT pg_advisory_lock(100);   -- count = 2
SELECT pg_advisory_unlock(100); -- count = 1, 锁仍持有
SELECT pg_advisory_unlock(100); -- count = 0, 锁释放

应用 A: 从连接池借到连接 conn-1
应用 A: pg_advisory_lock(100) → 加锁成功
应用 A: 完成业务, 将 conn-1 归还连接池  ← 忘记 unlock!

应用 B: 从连接池借到 conn-1
应用 B: 以为是干净连接, 实际上 conn-1 仍持有 key=100 的锁
应用 B: 对其他 key 加锁, 操作共享资源 → 产生意外的锁竞争

-- 在应用代码中 (伪代码)
connection.execute("SELECT pg_try_advisory_lock(hashtext('daily_report_job'))");
if result == true:
    run_daily_report()
    connection.execute("SELECT pg_advisory_unlock(hashtext('daily_report_job'))")
else:
    log("Another instance is running the job, skip")

-- 对 order_id=12345 加锁, 防止并发处理
SELECT pg_try_advisory_xact_lock(12345);
-- 返回 true: 获取成功, 处理订单
-- 返回 false: 已有其他事务在处理, 跳过或等待

-- 事务提交时自动释放, 无需手动 unlock
COMMIT;

BEGIN;
SELECT pg_advisory_xact_lock(order_id);
SELECT * FROM orders WHERE id = order_id FOR UPDATE;
-- 处理业务逻辑
COMMIT;

应用 A 持有 Advisory Lock(100) ← 存在共享内存中

数据库重启 (计划内 or 崩溃)
    ↓
共享内存清空
    ↓
Advisory Lock(100) 消失

数据库重启完成后:
- 应用 A 的连接已断开 (连接断开时锁本来就会自动释放)
- 即使应用 A 重新连接, 也需要重新申请锁
- 其他应用可以正常申请 Lock(100)

应用 A 连接 Primary, 持有 Advisory Lock(100)
Primary 宕机
    ↓
Standby 晋升为新 Primary
    ↓
新 Primary 共享内存中没有 Advisory Lock(100)
    ↓
应用 B 连接新 Primary, 申请 Advisory Lock(100) → 成功! ⚠️

同时:
应用 A 的连接已断开 (Primary 宕机)
应用 A 重连新 Primary, 发现自己没有锁了

-- 获得锁后, 先检查业务状态
SELECT pg_try_advisory_xact_lock(order_id);
IF acquired THEN
    -- 检查幂等: 是否已经处理过?
    IF NOT EXISTS (SELECT 1 FROM orders WHERE id = order_id AND status = 'processed') THEN
        -- 处理业务
        UPDATE orders SET status = 'processed' WHERE id = order_id;
    END IF;
END IF;

# postgresql.conf
synchronous_commit = remote_apply
synchronous_standby_names = 'standby1'

-- 查看当前所有 Advisory Lock
SELECT
    pid,
    locktype,
    classid,
    objid,
    mode,
    granted
FROM pg_locks
WHERE locktype = 'advisory';

-- 结合进程信息
SELECT
    l.pid,
    a.usename,
    a.application_name,
    l.classid,
    l.objid,
    l.mode,
    l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.locktype = 'advisory';

-- 开启串行化隔离
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- 读取操作会隐式在读取范围上加谓词锁
SELECT SUM(amount) FROM accounts WHERE user_id = 1;

-- 如果另一个并发事务在 user_id=1 的范围内插入了新行
-- PostgreSQL 会检测到冲突，并在提交时回滚其中一个事务
COMMIT;

ERROR:  could not serialize access due to concurrent update
SQLSTATE: 40001

-- 查看所有锁
SELECT
    pid,
    locktype,
    relation::regclass AS table_name,
    mode,
    granted,
    transactionid,
    classid,
    objid
FROM pg_locks
ORDER BY pid;

-- 查看锁：持有时长、模式、所属进程，按持有时间倒序
SELECT locker.pid,
       pc.relname,
       locker.mode,
       locker_act.application_name,
       least(query_start, xact_start)                        start_time,
       locker_act.state,
       CASE
           WHEN granted = 'f' THEN 'wait_lock'
           WHEN granted = 't' THEN 'get_lock'
       END                                                    lock_status,
       current_timestamp - least(query_start, xact_start) AS runtime,
       locker_act.query
FROM pg_locks locker
JOIN pg_stat_activity locker_act ON locker.pid = locker_act.pid
JOIN pg_class pc ON locker.relation = pc.oid
WHERE NOT locker.pid = pg_backend_pid()
  AND pc.reltype <> 0
ORDER BY runtime DESC;

-- 查看正在等待锁的会话
SELECT
    blocked.pid          AS blocked_pid,
    blocked_act.query    AS blocked_query,
    blocking.pid         AS blocking_pid,
    blocking_act.query   AS blocking_query,
    blocked_locks.mode   AS waiting_for_mode
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_act ON blocked_act.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    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.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_act ON blocking_act.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

SELECT
    l.pid,
    a.usename,
    a.application_name,
    l.classid,
    l.objid,
    l.mode,
    l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.locktype = 'advisory';