使用SqlAlchemy+MySQL遭遇诡异死锁问题求助

一、死锁的触发机制 #

从你提供的InnoDB死锁日志与业务代码来看，尽管两个事务看似更新不同主键的行，但死锁的发生源于以下核心逻辑：

1. 同一数据页的循环等待
   两个更新操作的主键265068882和265068879落在同一个InnoDB数据页（page no 966870）上。并发场景下，若两个事务的锁获取顺序相反：

   • 事务A先对id=265068879加共享锁（插入unique_object时，外键约束触发的锁），随后尝试更新id=265068882需要排他锁；
   • 事务B先对id=265068882加共享锁，随后尝试更新id=265068879需要排他锁；
     此时两个事务互相等待对方释放锁，形成循环等待，触发InnoDB死锁检测并回滚其中一个事务。

2. 嵌套事务中的锁升级冲突
   在嵌套事务内，你先执行插入关联对象的操作，再更新父表记录：

   • 插入unique_object时，InnoDB为保证外键完整性，会对my_table中对应的existing_object加共享锁（S锁）；
   • 后续修改existing_object.value时，需要将S锁升级为排他锁（X锁）；
     多个并发事务同时执行此流程时，会出现多个事务持有不同记录的S锁，同时等待对方记录的X锁的情况，满足死锁的四个必要条件（互斥、持有并等待、不可抢占、循环等待），最终触发死锁。

二、SqlAlchemy未抛出异常的原因 #

MySQL回滚的是嵌套事务对应的Savepoint，而非整个外层事务，这是SqlAlchemy未抛出异常的关键：

1. Savepoint的局部回滚特性
   当嵌套事务内部发生死锁时，MySQL仅回滚到该嵌套事务的Savepoint，外层事务仍处于活跃状态。SqlAlchemy对Savepoint的异常处理逻辑中，默认不会将局部回滚视为全局事务异常，因此不会向外抛出死锁相关的异常。

2. 事务提交的延迟感知
   外层事务通过with session.begin()管理，所有操作会在with块结束时统一提交。如果死锁发生在嵌套事务阶段，且Savepoint回滚后外层事务没有后续冲突操作，SqlAlchemy不会主动感知到死锁的发生，自然不会抛出异常。

相关代码片段 #

with session.begin():
  ....
  
  # session started earlier in the parent code
  session.flush()
  
  # find object from my_table by criteria
  existing_object = ...

  with session.begin_nested():
            # Unique object that will be inserted.
            # These object are stored in separate table.
            unique_object = InsertObject()
            # some init of unique_object
            # new unique object references existing_object
            unique_object.existing_object = existing_object
            existing_object.value = new_value # integer

内容的提问来源于stack exchange，提问作者Alexei