MySQL / 系统课时

发现并治理长事务

发现并治理长事务

长事务不只是“执行很久的 SQL”:会话可能早已空闲,却仍保持未提交事务、行锁和旧版本可见性。本课用 MySQL 8.x 的事务与锁视图识别风险会话,并给出告警、终止和应用侧治理的可执行顺序。

学习目标

  • 从事务持续时间、最后 SQL、锁等待和历史版本压力识别危险长事务
  • 区分“慢 SQL”与“事务长期未提交”,避免只盯语句耗时
  • 建立从告警、止血到应用改造的治理闭环

先找真正仍然存活的事务

InnoDB 事务的关键视图是 information_schema.innodb_trx。trx_started 表示事务开始时间,即使连接当前处于 Sleep,只要没有提交或回滚,该事务仍可能继续持有锁并阻止 Purge 清理旧版本。

先按事务年龄排序,并把会话当前状态一起拉出来:

sql
SELECT
    t.trx_mysql_thread_id AS processlist_id,
    TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_seconds,
    t.trx_state,
    t.trx_rows_locked,
    t.trx_rows_modified,
    p.COMMAND,
    p.TIME AS session_state_seconds,
    p.STATE,
    p.INFO AS current_sql
FROM information_schema.innodb_trx AS t
LEFT JOIN information_schema.processlist AS p
    ON p.ID = t.trx_mysql_thread_id
ORDER BY trx_age_seconds DESC;

预期结果

text
+----------------+-----------------+-----------+-----------------+-------------------+---------+-----------------------+-------+----------------------+
| processlist_id | trx_age_seconds | trx_state | trx_rows_locked | trx_rows_modified | COMMAND | session_state_seconds | STATE | current_sql          |
+----------------+-----------------+-----------+-----------------+-------------------+---------+-----------------------+-------+----------------------+
| <ID>           | 420             | RUNNING   | 1               | 1                 | Sleep   | 415                   | NULL  | NULL                 |
+----------------+-----------------+-----------+-----------------+-------------------+---------+-----------------------+-------+----------------------+

其中 <ID> 等实际值会随实例和会话状态变化。这里最危险的信号不是 current_sql 为空,而是“事务已持续 420 秒且会话已经 Sleep 415 秒”。这通常说明应用开启事务后没有及时提交或回滚。

长事务从会话到锁与历史版本压力的关系

把“最后执行 SQL”补回来

PROCESSLIST.INFO 只显示当前正在执行的语句,空闲长事务常常看不到刚才执行过什么。若已启用 Performance Schema,可以从线程映射和 statement history 追溯近期 SQL。

sql
SELECT
    t.PROCESSLIST_ID,
    es.EVENT_ID,
    es.SQL_TEXT,
    ROUND(es.TIMER_WAIT / 1000000000000, 6) AS elapsed_seconds
FROM performance_schema.threads AS t
JOIN performance_schema.events_statements_history AS es
    ON es.THREAD_ID = t.THREAD_ID
WHERE t.PROCESSLIST_ID = <ID>
  AND es.SQL_TEXT IS NOT NULL
ORDER BY es.EVENT_ID DESC
LIMIT 5;

预期结果

text
+----------------+----------+------------------------------------------+-----------------+
| PROCESSLIST_ID | EVENT_ID | SQL_TEXT                                 | elapsed_seconds |
+----------------+----------+------------------------------------------+-----------------+
| <ID>           | <ID>     | UPDATE orders SET status='PAID' ...     | <VALUE>         |
| <ID>           | <ID>     | BEGIN                                    | <VALUE>         |
+----------------+----------+------------------------------------------+-----------------+

实际能保留多少历史语句取决于 Performance Schema consumer 和 history 大小;如果结果为空,先确认 events_statements_history 是否启用,而不是据此判断“这个事务没有执行过 SQL”。

判断它是否已经伤害其他会话

长事务本身不一定需要立刻终止。优先级应由业务影响决定:是否阻塞别的事务、是否修改大量数据、是否长期保留旧版本。锁等待可以直接从 performance_schema.data_lock_waits 与线程信息关联。

sql
SELECT
    rtrx.trx_mysql_thread_id AS waiting_processlist_id,
    btrx.trx_mysql_thread_id AS blocking_processlist_id,
    TIMESTAMPDIFF(SECOND, btrx.trx_started, NOW()) AS blocking_trx_age_seconds,
    r.OBJECT_SCHEMA,
    r.OBJECT_NAME,
    r.INDEX_NAME,
    r.LOCK_TYPE,
    r.LOCK_MODE
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
    ON r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
    ON b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
JOIN information_schema.innodb_trx AS rtrx
    ON rtrx.trx_id = r.ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx AS btrx
    ON btrx.trx_id = b.ENGINE_TRANSACTION_ID;

预期结果

text
+------------------------+-------------------------+--------------------------+---------------+-------------+------------+-----------+-----------+
| waiting_processlist_id | blocking_processlist_id | blocking_trx_age_seconds | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE |
+------------------------+-------------------------+--------------------------+---------------+-------------+------------+-----------+-----------+
| <ID>                   | <ID>                    | 430                      | app           | orders      | PRIMARY    | RECORD    | X         |
+------------------------+-------------------------+--------------------------+---------------+-------------+------------+-----------+-----------+

如果一个长事务已经成为阻塞者,应先确认其业务操作能否回滚,再决定止血。KILL QUERY 只终止当前语句;对空闲但事务仍打开的连接,通常需要 KILL CONNECTION <ID> 才能让服务器断开连接并回滚未提交事务。

sql
KILL CONNECTION <ID>;

预期结果

text
Query OK, 0 rows affected

同时观察历史版本压力

长事务持有较旧 Read View 时,Purge 不能清理仍可能被该事务访问的旧版本。可以用 InnoDB 状态中的 History list length 观察趋势,但不要把某个绝对值直接当成统一故障阈值。

sql
SHOW ENGINE INNODB STATUS;

预期结果

text
------------
TRANSACTIONS
------------
Trx id counter <ID>
Purge done for trx's n:o <ID> undo n:o <ID> state: running
History list length <VALUE>

关键是看趋势:事务年龄持续增加,同时 History list length 长时间上升且不能回落,比某一瞬间的数值更值得告警。若系统还有批量更新、备份读取或只读分析任务,要把这些合法长事务单独分组,避免与异常事务混在一个阈值里。

建立可执行的治理规则

监控至少应同时记录事务年龄、会话状态、锁定行数、修改行数、阻塞数量和数据库账号。告警阈值应根据业务事务正常耗时分层,例如“超过应用 P99 若干倍先告警,成为阻塞者则升高优先级”,而不是全库只用一个固定秒数。

应用侧治理通常比数据库侧反复杀会话更重要。缩小事务边界,把网络调用、人工等待、消息发送和大批量计算移到事务外;为连接池设置合理的事务超时或借助框架超时控制;所有异常路径必须显式回滚,并禁止把开启事务的连接长时间放回池中。

上线后的验收标准也应可观测:异常长事务数量回落、锁等待链缩短、History list length 能在峰值后恢复,且业务没有因为过度终止连接而出现重复提交或部分失败。做到这些,长事务治理才从一次性止血变成稳定的运行机制。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

发现并治理长事务

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通