PostgreSQL 事务、隔离级别与锁：MVCC 快照、行锁/表锁、死锁检测与长事务治理

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 或 ROLLBACK 取消全部变更

-- 查看当前快照信息
SELECT txid_current(), txid_current_snapshot();
-- 输出示例：(100001, "100001:100003:100001,100002")

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- 或
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 查看当前会话
SHOW transaction_isolation;

-- 会话 A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 返回 100
-- 会话 B（并发）
BEGIN;
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;
-- 会话 A 再次查询
SELECT balance FROM accounts WHERE id = 1;  -- 返回 200（不一致！）

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance * 1.1 WHERE type = 'savings';
COMMIT;
-- 若发生可串行化冲突，事务会以 40001 serialization_failure 终止

-- 等值锁定行（最常用）
SELECT * FROM orders WHERE id = 1001 FOR UPDATE;

-- 只阻止 UPDATE/DELETE，允许改非键列
SELECT * FROM orders WHERE id = 1001 FOR NO KEY UPDATE;

-- 共享行锁：阻止 UPDATE/DELETE/NO KEY UPDATE
SELECT * FROM orders WHERE id = 1001 FOR SHARE;

-- 最弱共享：只阻止 UPDATE/DELETE
SELECT * FROM orders WHERE id = 1001 FOR KEY SHARE;

-- 防止并发下超卖：先锁库存行再扣减
BEGIN;
SELECT stock FROM products WHERE id = 10 FOR UPDATE;
-- 检查 stock > 0
UPDATE products SET stock = stock - 1 WHERE id = 10;
COMMIT;

-- 乐观锁替代：用 version 列比较，影响行数为 0 表示版本冲突
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 10 AND version = 5;

-- 常见显式加锁
LOCK TABLE orders IN SHARE MODE;
LOCK TABLE orders IN EXCLUSIVE MODE;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

-- 会话 A：长事务持有行级锁
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- 会话 B：执行 DDL（需要 ACCESS EXCLUSIVE）
ALTER TABLE orders ADD COLUMN note TEXT;
-- 会阻塞等待，直到会话 A 提交

SELECT
  l.locktype, l.mode,
  c.relname,
  a.pid, a.state, a.query
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.locktype = 'relation'
  AND l.granted = false;

-- 找出阻塞者与被阻塞者
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.state = 'active'
ORDER BY blocked.query_start;

-- 经典锁等待视图（含等待时间）
SELECT
  a.pid,
  now() - a.xact_start AS xact_age,
  a.state,
  a.wait_event_type,
  a.wait_event,
  a.query,
  array_length(pg_blocking_pids(a.pid), 1) AS blocked_by_count,
  pg_blocking_pids(a.pid) AS blocking_pids
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND pg_blocking_pids(a.pid) IS NOT NULL
ORDER BY xact_age DESC;

-- 统计各类锁的数量
SELECT locktype, mode, count(*)
FROM pg_locks
GROUP BY locktype, mode
ORDER BY count(*) DESC;

-- 找出被阻塞超过 N 秒的会话
SELECT pid, state, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND now() - state_change > INTERVAL '5 seconds';

-- 会话 A
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- 会话 B
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 2;
-- 会话 A
UPDATE accounts SET balance = balance + 10 WHERE id = 2;  -- 等待 B
-- 会话 B
UPDATE accounts SET balance = balance + 10 WHERE id = 1;  -- 等待 A → 死锁!

SHOW deadlock_timeout;  -- 默认 1s

-- 检测到死锁后，其中一个事务会收到：
-- ERROR: deadlock detected
-- DETAIL: Process 12345 waits for ShareLock on transaction 1001; blocked by process 12346.
-- Process 12346 waits for ShareLock on transaction 1002; blocked by process 12345.

-- 加锁超时保护，避免无限等待
SET lock_timeout = '3s';
UPDATE accounts SET balance = balance - 1 WHERE id = 1;

SELECT pid, state,
       now() - xact_start AS xact_age,
       now() - state_change AS idle_age,
       backend_xmin,
       wait_event_type, wait_event,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start ASC;

-- 会话级超时：事务空闲超时自动回滚（PG 9.6+）
SET idle_in_transaction_session_timeout = '5min';

-- 全局生效后 reload
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

-- 紧急终止长时间空闲事务（慎用）
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > INTERVAL '10 minutes';

# Python 伪代码：处理可重试错误
RETRYABLE = {
    '40001': 'serialization_failure',   # SSI 冲突
    '40P01': 'deadlock_detected',       # 死锁
}

def with_retry(sql, max_retries=3):
    for i in range(max_retries):
        try:
            execute_in_transaction(sql)
            return
        except SqlStateError as e:
            if e.sqlstate in RETRYABLE and i < max_retries - 1:
                time.sleep(0.1 * (2 ** i))   # 指数退避
                continue
            raise

-- 事务与死锁统计
SELECT
  sum(xact_commit) AS commits,
  sum(xact_rollback) AS rollbacks,
  sum(deadlocks) AS deadlocks,
  round(100.0 * sum(xact_rollback) /
        GREATEST(sum(xact_commit) + sum(xact_rollback), 1), 2) AS rollback_pct
FROM pg_stat_database;

-- 当前活跃事务列表（含年龄）
SELECT pid, state, now() - xact_start AS xact_age
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC;