필사 모드: 事务隔离级别与真实的异常现象 — 标准定义与实现产生分歧的地方

-- PostgreSQL
SHOW default_transaction_isolation;   -- 默认值: read committed
SELECT current_setting('transaction_isolation');

-- 按事务指定
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- 或者
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- MySQL
SELECT @@transaction_isolation;   -- 默认值: REPEATABLE-READ
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;

 transaction_isolation
-----------------------
 read uncommitted

-- 会话 A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- 10000

-- 会话 B
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;

-- 会话 A (同一个事务内)
SELECT balance FROM accounts WHERE id = 1;   -- 5000  <-- 值变了
COMMIT;

-- 会话 A
BEGIN;
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
-- 不提交,保持等待

-- 会话 B
BEGIN;
UPDATE accounts SET balance = balance - 4000 WHERE id = 1;   -- 等待

-- 会话 A
COMMIT;

-- 应用经常做的事
SELECT balance FROM accounts WHERE id = 1;   -- 读到 10000
-- 应用侧计算 10000 - 4000 = 6000
UPDATE accounts SET balance = 6000 WHERE id = 1;   -- 用常量覆盖

-- 会话 A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders WHERE status = 'pending';   -- 42

-- 会话 B
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- 会话 A
SELECT count(*) FROM orders WHERE status = 'pending';   -- 42  <-- 没变
COMMIT;

-- 会话 A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- 10000

-- 会话 B
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;

-- 会话 A
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;

ERROR:  could not serialize access due to concurrent update
SQLSTATE: 40001

ERROR:  could not serialize access due to concurrent delete

-- 会话 A (MySQL, REPEATABLE READ)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- 10000 (快照)

-- 会话 B
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;

-- 会话 A
SELECT balance FROM accounts WHERE id = 1;          -- 10000 (仍然是快照)
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- 5000 (最新)
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
COMMIT;

-- 会话 A
BEGIN;
SELECT * FROM orders WHERE user_id = 42 FOR UPDATE;
-- user_id = 42 的索引区间以及它前后的间隙都会被锁住

-- 会话 B
INSERT INTO orders (user_id, status) VALUES (42, 'pending');   -- 等待

-- 查看当前锁状态 (MySQL 8.0)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

CREATE TABLE doctors (
  id       int PRIMARY KEY,
  name     text NOT NULL,
  on_call  boolean NOT NULL
);
INSERT INTO doctors VALUES (1, 'Kim', true), (2, 'Lee', true);

-- 会话 A: 金医生退出值班
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true;   -- 2, 条件通过

-- 会话 B: 李医生同时退出值班
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true;   -- 2, 条件通过

-- 会话 A
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;

-- 会话 B
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;

SELECT count(*) FROM doctors WHERE on_call = true;

 count
-------
     0

-- 两个会话都用 SERIALIZABLE 重复上面的场景
COMMIT;

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.
SQLSTATE: 40001

BEGIN;
SELECT stock FROM products WHERE id = 77 FOR UPDATE;
-- 从这里开始其他事务无法锁住同一行
UPDATE products SET stock = stock - 1 WHERE id = 77;
COMMIT;

-- 应用层面的显式加锁 (事务结束时自动释放)
SELECT pg_advisory_xact_lock(hashtext('reserve-seat:' || 'A12'));

SELECT * FROM products WHERE id = 77 FOR UPDATE NOWAIT;        -- 立即报错
SELECT * FROM products WHERE id = 77 FOR UPDATE SKIP LOCKED;   -- 跳过被锁的行

-- 从任务队列中安全地取出一条
UPDATE jobs SET status = 'running', picked_at = now()
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'queued'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
RETURNING *;

-- 用版本列只做冲突检测
UPDATE documents
SET title = 'new title',
    version = version + 1
WHERE id = 5 AND version = 12;
-- 如果更新行数为 0,说明有人先改过了

-- 用约束而不是隔离级别来阻止重复预约
CREATE UNIQUE INDEX uq_seat_reservation
  ON reservations (showtime_id, seat_no)
  WHERE cancelled_at IS NULL;

-- 用约束阻止时间区间重叠
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings
  ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

import time, random
import psycopg

RETRYABLE = {"40001", "40P01"}  # serialization_failure, deadlock_detected

def run_in_tx(conn, fn, max_attempts=5):
    for attempt in range(max_attempts):
        try:
            with conn.transaction():
                return fn(conn)
        except psycopg.errors.Error as e:
            if e.sqlstate not in RETRYABLE or attempt == max_attempts - 1:
                raise
            # 指数退避 + 抖动。避免在同一瞬间再次冲突。
            time.sleep((2 ** attempt) * 0.05 * (0.5 + random.random()))