SQLAlchemy 事务隔离级别

示例说明

隔离级别是性能与一致性的旋钮。读已提交适合常规流量——只看到已提交数据且锁竞争小；可重复读让报表会话在首个查询后锁定一致性快照，汇总过程中并发写入不会让分项与总计对不上。引擎级设置默认值，execution_options 派生只读场景的专用引擎，两种口径各得其所。

技术要点

• 引擎默认与派生选项分离两种口径
• begin 上下文自动提交回滚
• 快照内多次读取结果一致

代码解读

transfer_funds 的扣减后校验依赖同事务可见性：若余额为负抛 ValueError，begin 上下文捕获异常自动回滚，两边金额都不动。报表会话在可重复读下做汇总，执行期间的其他事务提交不影响其快照读数。

使用建议

可重复读下的写冲突由数据库检测，重试逻辑按需补充。pool_pre_ping 防止借用已被服务端断开的连接。隔离级别名称按数据库方言核对。

代码示例

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)