同一线程使用两个SQLAlchemy连接器时MySQL连接丢失问题

两个独立问题共同导致故障，其中一个直接触发连接丢失错误，另一个是架构层面的逻辑隐患：

• 直接触发2013错误的原因是隔离级别设置方式不符合SQLAlchemy与MySQL的交互规范。SET TRANSACTION ISOLATION LEVEL 必须在事务未启动时执行才合法，你当前的逻辑是先创建session（此时SQLAlchemy已经从连接池取出连接、初始化事务上下文），再调用session.connection()动态传入隔离级别执行选项。第一次循环时连接是全新创建的，事务尚未真正发起，设置语句可以正常执行；第一次请求结束后连接被归还到连接池，第二次循环复用该连接时，session初始化已经触发事务启动流程，此时再执行设置隔离级别的语句会触发MySQL服务端的事务状态错误，连接被主动断开，最终抛出连接丢失异常。
• 架构层面的隐性缺陷：MySQL的GET_LOCK是单实例本地的连接级锁，锁状态不会通过主从复制同步到其他实例。你当前将锁操作固定在单个从库、数据查询分发到多个从库的设计，会导致除了锁连接所在的从库外，其他所有从库完全感知不到锁的存在，就算解决了连接错误，也会出现多节点重复获取锁、重复处理数据的问题，锁机制完全失效。之前共用连接器时无异常，是因为锁和查询都落在同一个连接对应的实例上，锁可见性符合预期。

• 第一步验证连接错误根因：直接在初始化mysql_connector_data的Engine时全局配置READ UNCOMMITTED隔离级别，删除session创建后动态设置隔离级别的代码，运行循环验证第二次迭代是否还会抛出2013错误，绝大多数情况下这一步就能直接解决连接异常。
• 第二步验证锁可见性缺陷：在成功获取锁后，分别在锁使用的固定从库、数据查询可能命中的其他从库上执行SELECT IS_USED_LOCK('对应数据子集的锁名')，可以观察到只有锁连接所在的从库会返回持有锁的连接ID，其余从库均返回NULL，证明锁在其他实例上不生效。

优先保留MySQL锁的实现方案 #

该方案不需要引入额外组件，性能与可靠性都有保障：

1. 修正隔离级别设置逻辑：不要在session实例化后动态修改隔离级别，在创建Engine时就通过execution_options全局指定隔离级别，同时为所有Engine开启pool_pre_ping=True，自动过滤连接池中的失效连接。
   修正后的参考代码：

   # 初始化数据查询引擎时直接指定隔离级别
   data_engine = create_engine(
       db_conn_url,
       execution_options={"isolation_level": IsolationLevel.READ_UNCOMMITTED.value},
       pool_pre_ping=True,
       # 保留原有其他引擎配置
   )
   
   # 简化后的session获取方法，移除动态设置隔离级别的逻辑
   def get_mysql_session(self):
       if not self.session_maker:
           self.session_maker = sessionmaker(bind=self.engine)
       session = self.session_maker()
       try:
           yield session
           session.commit()
       except Exception:
           session.rollback()
           raise
       finally:
           session.close()
   

2. 修正锁服务的部署逻辑：不要在从库上执行锁操作，单独创建一个指向主库的连接器专门处理GET_LOCK/RELEASE_LOCK请求。主库是全局唯一的可写实例，所有服务节点访问的都是同一个主库，锁的全局可见性完全可靠。锁操作本身QPS极低（每个数据子集仅执行一次加锁/释放锁），完全不会对主库造成性能压力。
   注意禁止用从库作为锁服务节点：从库存在主从延迟、复制中断、实例切换等风险，会导致锁状态不一致，无法保证分布式锁的正确性。

备选Redis分布式锁方案 #

如果确实不想占用主库连接资源，可以切换为Redis实现分布式锁，实现时注意两个核心点即可：

• 加锁时给锁设置唯一客户端标识，避免释放其他节点持有的锁
• 合理设置锁过期时间，避免节点宕机后锁永久不释放

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