SqlAlchemy长时间锁定行遇MySQL连接丢失问题求助

问题根源 #

你遇到的Lost connection to MySQL server during query错误，本质原因包括：

• MySQL默认存在连接超时限制（wait_timeout/interactive_timeout），若连接空闲或事务持续时长超过阈值，服务器会主动断开连接；
• SqlAlchemy连接池的pool_recycle配置可能在事务未完成时回收连接，导致中断；
• 最关键的是，长时间持有行锁会严重阻塞其他业务操作，属于不合理的数据库实践。

最优解决方案：拆分锁与长时间操作 #

不要在持有行锁的事务中执行耗时1小时的操作，拆分流程，仅在必要时短时间持有锁：

1. 短时间锁行并标记锁定状态，提交事务释放锁；
2. 执行长时间处理任务；
3. 再次锁行验证状态，完成数据更新后释放锁。

代码示例：

import datetime

# 第一步：短时间锁行，标记锁定状态
with get_orm().session() as session:
    instance = session.query(Model).filter(Model.id == 1).with_for_update().first()
    # 标记行已锁定，设置1小时后过期
    instance.is_locked = True
    instance.lock_expire_at = datetime.datetime.now() + datetime.timedelta(hours=1)
    session.commit()

# 执行耗时1小时的操作
very_slow_process()

# 第二步：再次锁行，验证状态后更新数据
with get_orm().session() as session:
    # 确保行仍处于锁定状态且未过期
    instance = session.query(Model).filter(
        Model.id == 1,
        Model.is_locked == True,
        Model.lock_expire_at >= datetime.datetime.now()
    ).with_for_update().first()
    
    if instance:
        instance.retry += 1
        # 解锁行
        instance.is_locked = False
        instance.lock_expire_at = None
        session.commit()
    else:
        # 处理行已失效的异常情况
        raise RuntimeError("目标行已被解锁、过期或修改，无法完成更新")

备选方案：调整超时配置（不推荐） #

若必须长时间持有锁，需修改MySQL和SqlAlchemy的超时设置，但会带来并发和资源占用问题：

• MySQL端：修改my.cnf（或my.ini）中的wait_timeout和interactive_timeout，设置为大于3600秒（如wait_timeout=7200），重启MySQL生效；
• SqlAlchemy端：创建引擎时设置pool_recycle参数为略小于MySQL的超时值（如pool_recycle=3500），避免事务期间连接被回收。

注意：此方案会增加连接资源占用，且长时间行锁会阻塞其他业务操作，仅适用于特殊场景。

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