脏读、不可重复读与幻读

-- 初始状态：账户1余额 = 1000

-- 事务A
BEGIN;
UPDATE accounts SET balance = 5000 WHERE id = 1;
-- 未提交

-- 事务B（READ UNCOMMITTED）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- MySQL 下返回 5000 ← 脏读！读到事务A未提交的修改
-- PostgreSQL 下返回 1000（READ UNCOMMITTED 形同 READ COMMITTED，无脏读）

-- 事务A回滚
ROLLBACK;
-- balance 恢复为 1000

-- 事务B基于 5000 做出的决策是错误的

场景：电商库存
T1: UPDATE stock SET count = 0 WHERE product_id = 100;  -- 库存清零
T2: SELECT count FROM stock WHERE product_id = 100;     -- 读到 0
T2: -- 判断库存不足，拒绝用户下单
T1: ROLLBACK;  -- 库存恢复为 10
-- 结果：用户被错误拒绝，实际有库存

-- 初始状态：账户1余额 = 1000

-- 事务A（READ COMMITTED）
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- 返回 1000

-- 事务B
BEGIN;
UPDATE accounts SET balance = 2000 WHERE id = 1;
COMMIT;

-- 事务A再次读取同一行
SELECT balance FROM accounts WHERE id = 1;
-- 返回 2000 ← 不可重复读！同一事务内两次读取结果不同

场景：审计对账
T1: SELECT SUM(balance) FROM accounts;       -- 总额 10000
T2: UPDATE accounts SET balance = balance + 1000 WHERE id = 1; COMMIT;
T1: SELECT SUM(balance) FROM accounts;       -- 总额 11000
-- 两次汇总结果不一致，审计报告不准确

-- 方法1：使用 REPEATABLE READ 隔离级别
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- 1000
-- 事务B修改并提交
SELECT balance FROM accounts WHERE id = 1;  -- 仍然是 1000

-- 方法2：使用 SELECT FOR UPDATE 锁定行
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- 加锁
-- 事务B无法修改该行（被锁阻塞）
SELECT balance FROM accounts WHERE id = 1;  -- 一致
COMMIT;

-- 初始状态：dept_id = 5 有3名员工

-- 事务A（REPEATABLE READ，MySQL InnoDB）
BEGIN;
SELECT COUNT(*) FROM employees WHERE dept_id = 5;
-- 返回 3

-- 事务B
INSERT INTO employees (name, dept_id) VALUES ('新员工', 5);
COMMIT;

-- 事务A再次查询
SELECT COUNT(*) FROM employees WHERE dept_id = 5;
-- 返回 3（普通 SELECT 是快照读，无幻读）
-- 但当前读会出现幻读：

-- 事务A执行更新（UPDATE 是当前读，读最新已提交版本）
UPDATE employees SET salary = salary + 100 WHERE dept_id = 5;
-- 影响了4行！（包括事务B插入的行）

SELECT COUNT(*) FROM employees WHERE dept_id = 5;
-- 返回 4 ← 幻读显现

-- 方法1：使用 SERIALIZABLE 隔离级别
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- 方法2：MySQL InnoDB 使用 Next-Key Lock
BEGIN;
SELECT * FROM employees WHERE dept_id = 5 FOR UPDATE;
-- 锁定 dept_id = 5 的所有行及间隙
-- 事务B无法插入 dept_id = 5 的新行

-- 方法3：应用层使用 advisory lock（PostgreSQL）
SELECT pg_advisory_lock(5);  -- 锁定部门5
-- 执行操作
SELECT pg_advisory_unlock(5);

T1: BEGIN;     UPDATE → value=200 (未提交)
T2:                    SELECT → 200 (脏读!)
T1: ROLLBACK;  (value 恢复为 100)

T1: BEGIN;     SELECT → 100
T2:                    UPDATE → 200; COMMIT;
T1:             SELECT → 200 (不可重复读!)

T1: BEGIN;     SELECT COUNT → 3
T2:                    INSERT; COMMIT;
T1:             SELECT COUNT → 4 (幻读!)

-- 大多数 OLTP 场景：READ COMMITTED 足够
-- 需要一致性读取：REPEATABLE READ
-- 严格一致性：SERIALIZABLE（性能代价大）

-- 使用版本号实现乐观锁
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 100 AND version = 5 AND stock > 0;
-- 如果影响行数为0，说明并发冲突，重试

-- 只锁定需要的行，避免提升隔离级别
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 检查余额
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

读到的事务后来 ROLLBACK 了        → 脏读（数据是假的）
两次读同一行，值变了              → 不可重复读（行被 UPDATE）
两次读同一范围，行数变了          → 幻读（有 INSERT/DELETE）

时间线 a：T1 读 balance=100 → T2 UPDATE 成 200 并 COMMIT → T1 读到 200
时间线 b：T1 读 balance=100 → T2 UPDATE 成 200 未提交 → T1 读到 200 → T2 ROLLBACK
时间线 c：T1 统计行数=10 → T2 INSERT 并 COMMIT → T1 再统计=11

-- 方案一：快照固定，两次 SUM 口径一致（PostgreSQL）
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM accounts;   -- 10000
-- 无论别人怎么改提交，再查还是 10000
SELECT SUM(balance) FROM accounts;   -- 10000
COMMIT;

-- 方案二：如果「两次读」本来就是应用循环拼的，合并成一条 SQL 最便宜
SELECT region, SUM(balance) FROM accounts GROUP BY region;

-- 做法 1：先锁范围（Next-Key Lock 挡住插入），再更新
BEGIN;
SELECT id FROM employees WHERE dept_id = 5 FOR UPDATE;
UPDATE employees SET salary = salary + 100 WHERE dept_id = 5;
COMMIT;

-- 做法 2：条件收窄——只更新确认过的行（id 集合来自本事务快照读）
UPDATE employees SET salary = salary + 100
WHERE id IN (101, 102, 103);            -- 第一次快照读拿到的 3 个 id