幻读

工作原理

事务执行 SELECT count(*) FROM bookings WHERE room_id = 7 AND day = '2026-11-02'，看到 0。另一个事务插入同一房间同一天的预订并提交。第一个事务再次计数时看到 1。在读已提交下，每条语句获得新快照，这是预期行为。在可重复读下，计数可能仍为 0，因为快照固定，但后续对范围的更新仍可能冲突。

数据库的保护方式不同。PostgreSQL 使用快照，可串行化再增加谓词锁来检测危险的读写依赖。MySQL InnoDB 在可重复读下对某些索引范围使用临键锁，阻止插入到已锁定的间隙。没有合适索引的表可能锁定远大于预期的范围。

预防

最强的修复是数据库约束：(room_id, day) 上的唯一索引让重复插入无论时序如何都失败。当规则无法表达为约束时，使用更高隔离级别并重试，或锁定代表资源的父行。对房间行执行 SELECT ... FOR UPDATE 可以串行化检查加插入的序列。咨询锁也可行，但容易误用。

何时关注

审查任何针对集合的“检查后执行”流程：唯一性检查、容量检查、序列分配和余额阈值。也要审查在一个事务中读取两次并期望结果相同的报表。由版本列保护的单行更新不面临幻读，但可能面临丢失更新。

权衡与示例

可串行化隔离以中止和重试为代价防止幻读。间隙锁能防止幻读，但降低并发并可能死锁。唯一约束精确且便宜，却只覆盖模式能表达的规则。

示例：两个请求都检查用户名 alice 可用，然后都插入。没有唯一索引时两者都成功，登录查询随后返回两个用户。唯一索引把竞态变成一次失败的插入，应用可以把它转换为明确的冲突响应。

代码示例

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

engine = create_engine(
    'postgresql+psycopg://user:pass@localhost/reporting',
    isolation_level='READ COMMITTED',
    pool_size=10,
    max_overflow=5,
    pool_pre_ping=True,
)

reporting_engine = engine.execution_options(
    isolation_level='REPEATABLE READ'
)


def transfer_funds(session: Session, src: int, dst: int, amount: int) -> None:
    session.execute(
        text('UPDATE accounts SET balance = balance - :amt WHERE id = :id'),
        {'amt': amount, 'id': src},
    )
    row = session.execute(
        text('SELECT balance FROM accounts WHERE id = :id'),
        {'id': src},
    ).scalar_one()
    if row < 0:
        raise ValueError('overdraft rejected')
    session.execute(
        text('UPDATE accounts SET balance = balance + :amt WHERE id = :id'),
        {'amt': amount, 'id': dst},
    )


with Session(engine) as session:
    with session.begin():
        transfer_funds(session, src=1, dst=2, amount=75)

with Session(reporting_engine) as session:
    snapshot = session.execute(
        text('SELECT sum(balance) FROM accounts')
    ).scalar_one()
    print('consistent snapshot total:', snapshot)

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Step 1: Check available stock
SELECT stock_quantity INTO v_stock
FROM products
WHERE product_id = 101
FOR UPDATE;

IF v_stock < 5 THEN
    ROLLBACK;
    RAISE EXCEPTION 'Insufficient stock for product 101';
END IF;

-- Step 2: Create order
INSERT INTO orders (customer_id, order_date, status)
VALUES (p_customer_id, CURRENT_TIMESTAMP, 'pending')
RETURNING order_id INTO v_order_id;

-- Savepoint before risky operation
SAVEPOINT before_inventory_update;

-- Step 3: Update inventory
UPDATE products
SET stock_quantity = stock_quantity - 5
WHERE product_id = 101;

-- Step 4: Add order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (v_order_id, 101, 5, 29.99);

COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT before_inventory_update;
        RAISE;
END;