PostgreSQL Advisory Lock Critical Section

-- PostgreSQL 会话级咨询锁：try 锁拿不到立即返回假
SELECT pg_try_advisory_lock(4271);

-- 若返回 true，此会话独占编号 4271 的锁
-- 其他实例的尝试会得到 false：
SELECT pg_try_advisory_lock(4271);  -- false：已被占用

-- 临界区：只有一个实例在执行对账
BEGIN;
SELECT pg_advisory_xact_lock(4271);  -- 事务级：提交或回滚自动释放

UPDATE reconciliation_runs
  SET started_at = now(), status = 'running'
  WHERE id = 'nightly-2026-10-21';

-- ... 对账主体 ...

UPDATE reconciliation_runs
  SET finished_at = now(), status = 'done'
  WHERE id = 'nightly-2026-10-21';
COMMIT;  -- 锁随事务结束自动归还

-- 会话级锁的显式释放路径
SELECT pg_advisory_unlock(4271);

-- 双键形式：类目 ID 加实体 ID 组合编号
SELECT pg_try_advisory_lock(4271, 42);

-- 锁状态观测
SELECT pg_locks.locktype, pg_locks.objid, pg_locks.granted
FROM pg_locks
WHERE locktype = 'advisory';