多线程下使用SQLAlchemy出现Lock wait timeout exceeded异常

核心疑问回应 #

你遇到的锁等待超时并非会话收尾逻辑的基础写法错误——你在线程正常收尾时调用Session.remove()的操作本身符合scoped_session的使用规范，问题来自三个叠加的设计缺陷：

• 裸线程无节制启动：你在build_network里循环对每个节点直接调用thread.start()，这个操作是非阻塞的，10万+节点量会瞬间拉起成百上千个并行线程，完全不受控。你日志里显示的“0 tasks are running”是自身计数逻辑的漏洞：因为没有跟踪每个线程的执行状态，误把刚启动的线程当成未运行状态，实际数据库侧已经堆积了大量并行的写事务。
• 异常路径会话泄漏：你当前的代码只在业务逻辑正常走到收尾阶段才执行Session.remove()，如果线程在查询、提交的任意一步抛出异常，执行流会直接中断，根本不会走到清理会话的代码。带着未提交事务的数据库连接会被一直挂着，持续持有行锁，直到数据库端触发锁等待超时。
• 连接池配置不匹配：默认SQLAlchemy的连接池无连接上限，每个新线程创建会话时都会新建独立连接，并行写的事务越多，InnoDB上的行锁、间隙锁冲突概率就越高，最终触发锁等待超时。

你可以在数据库侧执行SHOW ENGINE INNODB STATUS，就能看到报错时实际有大量活跃事务同时操作network_nodes表，互相等待锁释放，根本不是只有2个线程在运行。单线程模式下能正常运行，就是因为全程只有一个串行事务，不存在并行抢锁的冲突。

落地修正方案 #

• 替换裸线程创建逻辑：改用固定大小的线程池（比如concurrent.futures.ThreadPoolExecutor），把最大并行线程数控制在20-50区间——这个量级远低于常规MySQL的最大连接上限，也能避免锁冲突堆积。
• 补充会话清理的兜底逻辑：把会话操作放在try/except/finally结构里，确保不管业务执行成功还是报错，都能正确清理会话、回滚未提交的事务、释放锁：

  def _build_page_nodes(node_id, traversing_nodes, update):
      session = Session()
      try:
          # 原有业务逻辑
          ...
          logger.debug('Committing transaction')
          session.commit()
      except Exception:
          # 异常时回滚事务，避免锁残留
          session.rollback()
          raise
      finally:
          # 不管成功失败都清理会话，把连接归还连接池
          Session.remove()
          logger.debug('Done')
  

• 调整引擎的连接池配置，让连接数和线程池规模匹配，避免无限制新建连接：

  engine = create_engine(
      f"mysql+mysqldb://{DB_USER}:{DB_PASS}@{DB_HOST}/{DB_DB}",
      pool_size=20,
      max_overflow=10,
      pool_pre_ping=True,
      pool_recycle=3600
  )
  

• 校验UPDATE语句的执行计划：对触发报错的UPDATE network_nodes SET built_tree=1 WHERE id = xxx语句执行EXPLAIN，确认条件走了主键索引——如果因为索引缺失导致全表扫描，会直接在表上加范围写锁，就算线程数很少也会触发锁等待超时。

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