如何排查processlist中不可见的活跃MySQL事务及连接池占用问题

1. 查明事务2974372519的具体信息及执行操作 #

针对MariaDB 10.4.28，可通过以下步骤获取事务详情：

• 查询INNODB_TRX表获取事务核心信息
  执行SQL获取事务绑定的线程ID、启动时间、状态等关键数据：

  SELECT trx_id, trx_mysql_thread_id, trx_started, trx_state, trx_isolation_level 
  FROM INFORMATION_SCHEMA.INNODB_TRX 
  WHERE trx_id = 2974372519;
  

• 关联PROCESSLIST表查看执行的SQL
  用上一步得到的trx_mysql_thread_id，查询对应线程的完整执行语句：

  SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO 
  FROM INFORMATION_SCHEMA.PROCESSLIST 
  WHERE ID = <trx_mysql_thread_id>;
  

• 追溯历史执行语句（若当前无活跃SQL）
  若事务当前未执行SQL，可通过以下方式查询历史记录：
  • 若已开启通用查询日志或慢查询日志，直接在AWS RDS控制台的日志页面检索对应线程ID的执行记录；
  • 若启用了performance_schema，执行以下SQL查询该线程的历史语句：

    SELECT SQL_TEXT, TIMER_START, TIMER_END 
    FROM performance_schema.events_statements_history 
    WHERE THREAD_ID = (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = <trx_mysql_thread_id>);
    

2. 了解40个被占用的连接池连接的当前状态 #

从数据库端和应用端双维度排查：

数据库端视角 #

• 查看所有连接的实时状态
  执行SHOW FULL PROCESSLIST或查询INFORMATION_SCHEMA.PROCESSLIST，筛选应用服务器IP对应的连接，可查看每个连接的：
  • 连接状态（如Sleep、Query、Locked）
  • 当前执行的SQL语句
  • 连接持续时长
  • 关联的数据库用户等信息
• 关联事务状态
  结合INFORMATION_SCHEMA.INNODB_TRX，查看哪些连接绑定了未提交的活跃事务（这通常是连接被占用的核心原因）：

  SELECT p.ID, p.INFO, t.trx_id, t.trx_started, t.trx_state 
  FROM INFORMATION_SCHEMA.PROCESSLIST p
  JOIN INFORMATION_SCHEMA.INNODB_TRX t ON p.ID = t.trx_mysql_thread_id;
  

应用端（HikariCP）视角 #

• 启用HikariCP监控MBean
  在HikariCP配置中添加registerMbeans=true，通过JConsole/VisualVM连接应用进程，查看com.zaxxer.hikari下的MBean，可获取：
  • 每个连接的状态（ACTIVE/IDLE）
  • 连接的创建时间、最后使用时间
  • 连接的占用线程信息
• 开启HikariCP debug日志
  将日志级别调至DEBUG（通过SLF4J/Log4j配置），日志会输出连接的获取、释放、超时等细节，可定位到哪些业务线程占用了连接且未释放。
• 分析应用线程栈
  结合已有的ThreadStackDump，查看等待连接的线程调用栈，反推对应的业务代码逻辑；若存在持有连接但未提交事务的线程，栈中会出现JDBC/ORM框架（如MyBatis、Hibernate）的事务相关调用痕迹。

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