隔离级别

-- MySQL 8.0+
SELECT @@transaction_isolation;               -- 当前会话级别（默认 REPEATABLE-READ）
SELECT @@global.transaction_isolation;        -- 全局默认
SET SESSION transaction_isolation = 'READ-COMMITTED';
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

-- PostgreSQL
SHOW default_transaction_isolation;           -- 默认 read committed
ALTER DATABASE mydb SET default_transaction_isolation = 'repeatable read';
-- 单个事务（必须写在第一条查询语句之前）
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- 或
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 仅影响下一个事务（标准语法，各库通用）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

时间线：
T1: BEGIN; UPDATE accounts SET balance = 2000 WHERE id = 1;
T2:                                SELECT balance → 2000（脏读）
T1: ROLLBACK;  -- balance 恢复为 1000
T2: 基于 2000 做的任何决策都是基于从未存在过的数据

-- PostgreSQL 默认级别（Oracle、SQL Server 默认亦然）
BEGIN;                                          -- READ COMMITTED
SELECT balance FROM accounts WHERE id = 1;      -- 1000

-- 另一会话：UPDATE accounts SET balance = 2000 WHERE id = 1; COMMIT;

SELECT balance FROM accounts WHERE id = 1;      -- 2000（不可重复读）
COMMIT;

-- MySQL InnoDB 默认级别
BEGIN ISOLATION LEVEL REPEATABLE READ;          -- PostgreSQL 语法
SELECT balance FROM accounts WHERE id = 1;      -- 1000
-- 另一会话：UPDATE ... = 2000; COMMIT;
SELECT balance FROM accounts WHERE id = 1;      -- 仍为 1000（事务快照）
COMMIT;

-- MySQL InnoDB 示例（该行为专属于 MySQL 的"当前读"模型）
BEGIN;                                          -- REPEATABLE READ
SELECT COUNT(*) FROM accounts WHERE balance > 1500;   -- 0

-- 另一会话：INSERT INTO accounts VALUES (2, 2000); COMMIT;

SELECT COUNT(*) FROM accounts WHERE balance > 1500;   -- 仍为 0（快照读无幻读）
UPDATE accounts SET balance = balance + 100 WHERE balance > 1500;
-- UPDATE 是当前读：影响 1 行，读到了别的事务新插入的行
SELECT COUNT(*) FROM accounts WHERE balance > 1500;   -- 1（幻读显现）

-- 事务A
BEGIN;
SELECT * FROM employees WHERE dept_id = 5 FOR UPDATE;
-- 记录锁 + 间隙锁：锁住已有行及其间的"空隙"

-- 事务B
INSERT INTO employees (name, dept_id) VALUES ('new', 5);
-- 被阻塞！空隙不允许插入 → 范围查询的幻读被挡住

-- PostgreSQL SSI 示例：两个事务交叉读写对方读过的行
-- 事务A
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;

-- 事务B
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 2;

-- 事务A：UPDATE accounts SET balance = balance - 100 WHERE id = 2;  -- 写了B读过的行
-- 事务B：UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 写了A读过的行

-- 事务A COMMIT 成功
-- 事务B COMMIT 报错：
-- ERROR: could not serialize access due to read/write dependencies

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- 只锁这一行
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- 比全局 SERIALIZABLE 便宜得多，同样消除该行上的竞态

CREATE TABLE accounts (
    id      INT PRIMARY KEY,
    balance NUMERIC(10,2) NOT NULL
);
INSERT INTO accounts VALUES (1, 1000.00), (2, 1000.00);

-- A 终端
BEGIN;  -- READ COMMITTED 即可
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;   -- 1000，并持锁
-- B 终端：UPDATE ... 会阻塞在这里，直到 A 提交
SELECT balance FROM accounts WHERE id = 1;              -- 仍 1000
COMMIT;

for attempt in range(3):
    try:
        with conn.transaction():          # 事务开始：快照重建
            bal = query("SELECT balance FROM accounts WHERE id = %s", (1,))
            if bal >= 100:
                execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        break                              # 提交成功，退出重试
    except SerializationFailure:
        if attempt == 2:
            alert("转账连续 3 次序列化冲突，转人工")
            raise
        sleep(backoff(attempt))            # 指数退避：100ms, 200ms, 400ms