事务与并发控制

6.1 ACID 特性与事务命令

特性	含义	PostgreSQL 如何保证
Atomicity 原子性	事务内的操作要么全部成功，要么全部回滚	WAL + ROLLBACK 机制
Consistency 一致性	事务执行前后，数据满足所有约束	CHECK、FK、UNIQUE 约束在事务提交时检查
Isolation 隔离性	并发事务互不干扰	MVCC（多版本并发控制）
Durability 持久性	提交后的数据即使崩溃也不丢失	WAL 预写日志，同步刷盘

SQL-- 基本事务控制
BEGIN;                          -- 或 START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;   -- 提交：两条 UPDATE 同时生效
-- 或 ROLLBACK;  -- 回滚：两条 UPDATE 都撤销

-- SAVEPOINT：事务内的检查点，允许部分回滚
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 500);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LASTVAL(), 999, 1, 500);  -- 假设 product_id=999 不存在，违反 FK

ROLLBACK TO SAVEPOINT after_order;  -- 回滚到保存点，订单行保留

-- 继续正确操作
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LASTVAL(), 1, 1, 500);    -- 正确的 product_id

RELEASE SAVEPOINT after_order;     -- 释放保存点（可选）
COMMIT;

6.2 MVCC 原理

MVCC（Multi-Version Concurrency Control，多版本并发控制）是 PostgreSQL 并发的核心机制。读不阻塞写，写不阻塞读，极大提升了并发性能。

SQL-- 查看行的 MVCC 系统列（普通 SELECT * 不显示）
SELECT xmin, xmax, ctid, * FROM orders LIMIT 3;

-- 输出示例：
--  xmin  | xmax |  ctid  | id  | user_id | status  | total
-- -------+------+--------+-----+---------+---------+--------
--  12340 |    0 | (0,1)  | 1   |    42   | shipped | 1500.00
--  12341 |    0 | (0,2)  | 2   |    43   | pending |  300.00
--  12342 | 12350| (0,3)  | 3   |    44   | pending |  500.00
-- xmax=12350 表示该行被事务 12350 删除/更新（如果 12350 已提交，该行是死元组）

-- 查看当前活跃事务快照
SELECT txid_current();           -- 当前事务 ID
SELECT txid_current_snapshot();  -- 当前快照: xmin:xmax:xip_list

6.3 隔离级别

PostgreSQL 支持 SQL 标准定义的所有四个隔离级别：

隔离级别	脏读	不可重复读	幻读	序列化异常
Read Uncommitted	—	可能	可能	可能
Read Committed（PG 默认）	不可能	可能	可能	可能
Repeatable Read	不可能	不可能	PG 中不可能	可能
Serializable	不可能	不可能	不可能	不可能

SQL-- 设置事务隔离级别
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 演示 Read Committed 的不可重复读问题
-- 会话 1（Read Committed，默认）:
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 结果：1000
-- （此时会话 2 执行 UPDATE accounts SET balance=800 WHERE id=1; COMMIT;）
SELECT balance FROM accounts WHERE id = 1;  -- 结果：800（不可重复读！）
COMMIT;

-- 解决方案：使用 REPEATABLE READ
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- 结果：1000
-- （即使会话 2 已提交 UPDATE）
SELECT balance FROM accounts WHERE id = 1;  -- 结果：仍是 1000（快照隔离）
COMMIT;

6.4 锁机制

SQL-- SELECT FOR UPDATE：悲观锁，锁定查询到的行，阻止其他事务修改
BEGIN;
SELECT * FROM products
WHERE id = 1
FOR UPDATE;           -- 其他事务的 UPDATE/DELETE/SELECT FOR UPDATE 将等待
-- 安全地修改
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- FOR UPDATE SKIP LOCKED：跳过已锁定的行（任务队列场景）
BEGIN;
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;   -- 多个 Worker 并发取任务，互不冲突
COMMIT;

-- FOR SHARE：共享锁，允许多个事务读但阻止修改
SELECT * FROM categories FOR SHARE;

-- 查看当前锁等待情况
SELECT
  blocking.pid   AS blocking_pid,
  blocking.query AS blocking_query,
  blocked.pid    AS blocked_pid,
  blocked.query  AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

6.5 死锁检测与预防

SQL-- 经典死锁场景
-- 会话 1:  BEGIN; UPDATE accounts SET balance=balance-100 WHERE id=1;  -- 锁行1
-- 会话 2:  BEGIN; UPDATE accounts SET balance=balance-100 WHERE id=2;  -- 锁行2
-- 会话 1:  UPDATE accounts SET balance=balance+100 WHERE id=2;  -- 等待行2（被会话2持有）
-- 会话 2:  UPDATE accounts SET balance=balance+100 WHERE id=1;  -- 等待行1（被会话1持有）
-- → 死锁！PostgreSQL 自动检测并回滚其中一个事务（抛出 ERROR: deadlock detected）

-- 死锁预防策略 1：统一加锁顺序（按 ID 升序锁定）
BEGIN;
SELECT * FROM accounts
WHERE id IN (1, 2) ORDER BY id  -- 始终按 id 升序
FOR UPDATE;
-- 此时所有事务都先锁 id=1，再锁 id=2，不会形成循环等待
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

6.6 实战：并发安全的库存扣减

方案一：悲观锁（SELECT FOR UPDATE）

SQL-- 下单时扣减库存（悲观锁方案）
BEGIN;

-- Step 1：锁定商品行，防止其他并发事务同时修改
SELECT id, stock, price
FROM products
WHERE id = 1
FOR UPDATE;    -- 其他下单事务将在这里等待

-- Step 2：检查库存是否充足（在应用层或 PL/pgSQL 中做判断）
-- 假设查到 stock=10, price=9999

-- Step 3：扣减库存（CHECK 约束保证不会负数）
UPDATE products
SET stock = stock - 1, updated_at = NOW()
WHERE id = 1 AND stock >= 1  -- 双重检查
RETURNING stock;

-- 如果影响行数=0，说明库存不足（应用层判断 rowcount）
COMMIT;

方案二：乐观锁（版本号）

SQL-- 乐观锁：不阻塞读，提交时检查版本
ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

-- Step 1：读取数据（记录 version=5）
SELECT id, stock, version FROM products WHERE id = 1;

-- Step 2：提交时检查 version 是否还是 5
UPDATE products
SET
  stock   = stock - 1,
  version = version + 1,
  updated_at = NOW()
WHERE id = 1
  AND version = 5          -- 乐观锁检查：version 必须是我读到的值
  AND stock >= 1
RETURNING id;

-- 如果影响行数=0：说明有其他事务已修改（version 已变），需要重试
-- 如果影响行数=1：成功，继续

-- 乐观锁适合：并发冲突概率低的场景（读多写少）
-- 悲观锁适合：并发冲突概率高的场景（高并发秒杀库存）