首页
学习
活动
专区
圈层
工具
发布
社区首页 >专栏 >空闲事务钉住 read view:与 wait_timeout 取较小值

空闲事务钉住 read view:与 wait_timeout 取较小值

原创
作者头像
一只特立独行的兔先生
发布于 2026-11-20 15:20:36
发布于 2026-11-20 15:20:36
440
举报

业务连接在 Sleep,Threads_running 也不高,undo 却在涨,锁等待的阻塞方 Command 还是 Sleep。这不是慢 SQL。云数据库 MySQL 的 wait_timeout 会在非交互连接闲置到期后断开它,但这个值经常是数小时。内核参数 cdb_kill_idle_trans_timeout 只对「事务还开着、下一条语句迟迟不来」的连接做更短的 kill,默认 0,即关闭。

空着的事务占的不是一条连接

information_schema.innodb_trx 里的未提交事务,和 processlist 里 Command = 'Sleep' 对上,才是空闲事务。它没有正在执行的语句,不进 Threads_running。只看活跃线程,会把它当成空闲连接。

占什么,取决于发呆之前做过什么,以及隔离级别。默认是 REPEATABLE READ(改过的话看 transaction_isolation,5.7 还有 tx_isolation)。这个级别下,事务里第一次一致性读定下快照,之后的一致性读都看它。快照还在,purge 就不能清掉它还可能要读的 undo。只执行了 BEGIN、还没读没写,主要是占着连接,不一定已经钉住 history list。START TRANSACTION WITH CONSISTENT SNAPSHOT 会在第一条 SELECT 之前就建立快照。

READ COMMITTED 每次一致性读各自取快照,语句结束后这次快照不再被该事务持有,两条语句之间的空闲通常不钉 history list。两种级别有一点相同:UPDATE、DELETE、SELECT ... FOR UPDATE 的行锁要到 COMMIT 或 ROLLBACK 才放。trx_rows_locked = 0 不能说明无害,只读快照照样拖 purge。元数据锁也要到事务结束。只读实例上一个未提交的用户事务,可以堵住从主库复制过来的 DDL,只读延迟跟着起来。

超时从下一条语句没来算起

官方定义很窄:连接处于事务中(显式 BEGIN / START TRANSACTION,或隐式开启的事务),超时时间内没有下一条语句,就 kill 这条连接。隐式开启最常见的是 autocommit = 0:第一条语句把事务打开,不提交就一直开着。单语句自动提交在语句结束就结束了,不属于这里。

计时不是 trx_started。事务开了一个小时,但每几秒都有语句,不会被杀掉。只做了一次 SELECT 然后去调下游,超过阈值没有下一条 SQL,就会被杀。SELECT SLEEP() 和正在等锁的语句都是 Command = 'Query',不算空闲。阻塞方如果自己也在跑查询,这个参数不会取消它。

kill 的是连接,不是回滚完再把连接留在池子里。未提交的修改随会话中止回滚。客户端下一次使用会收到断开错误,错误码以客户端日志为准,不要写死某一个 errno。

和 wait_timeout 取较小值

参数名 cdb_kill_idle_trans_timeout,动态,范围 [0, 31536000],默认 0。0 表示关闭;非 0 表示空闲超过这么多秒就 kill。内核要求:MySQL 5.6 为 20180915 及以上,5.7 为 20180918 及以上,8.0 为 20200630 及以上。

开启后,实际阈值是它和该会话 wait_timeout 的较小值。社区版 wait_timeout 默认 28800 秒,云上以参数设置页为准。比较的是会话值。全局 kill 设成 60,会话把 wait_timeout 调到 3600,生效仍是 60,长批处理不能靠调大会话超时豁免。全局 kill 设成 120、会话 wait_timeout 只有 30,生效是 30,改 kill 参数会看起来没生效。kill 保持 0 时,空闲事务只受 wait_timeout 约束。

修改入口:单击实例 ID,数据库管理,参数设置。文档标为动态参数,不需要重启。SELECT @@GLOBAL.cdb_kill_idle_trans_timeout 报未知变量就停,先核对实例详情里的内核小版本。只读实例是否能改这个参数,以该实例的参数页为准,不要假设和主实例联动。

阈值要高于合法间隙

低峰采样正常事务两条语句之间的 sleep_sec,阈值要高于这个合法间隙,否则批处理会在空档被拆掉。也要明显低于当前 wait_timeout,否则新参数没有更紧的约束。不要直接写成 1 秒。

应用事务超时应该短于服务端 kill。服务端只拆连接,不给干净的回滚回调。应用若在自己的超时里 ROLLBACK,调用方至少知道这轮没提交。连接借出后去调 HTTP、发消息,这段时间在 MySQL 看来就是空闲事务。kill 之后库内修改回滚,库外动作还在。重试单位必须是整个事务,库外动作要有幂等键。

连接归还时不 ROLLBACK、又把 autocommit 设成 0,池里每条空闲连接都是未提交事务,kill 会成批拆连接,下一轮继续。这不是阈值能修好的。走数据库代理时,MySQL 看到的是代理后端连接,两条语句之间的发呆一样算空闲。

先查出谁在空转,再用探活确认

代码语言:sql
复制
SELECT @@GLOBAL.cdb_kill_idle_trans_timeout AS kill_idle,
       @@GLOBAL.wait_timeout AS global_wait,
       @@SESSION.wait_timeout AS session_wait;

sleep_sec 和 kill 的计时一致,trx_age_sec 用来看快照可能有多旧。需要 PROCESS 权限。

代码语言:sql
复制
SELECT p.id, p.user, p.host, p.db,
       p.time AS sleep_sec,
       TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_sec,
       t.trx_rows_locked, t.trx_rows_modified, t.trx_isolation_level
FROM information_schema.processlist AS p
JOIN information_schema.innodb_trx AS t
  ON t.trx_mysql_thread_id = p.id
WHERE p.command = 'Sleep'
ORDER BY p.time DESC;

要看这些会话是不是已经堵住别人,8.0 用 performance_schema.data_lock_waits。REQUESTING_THREAD_ID 是 Performance Schema 线程号,不是 processlist.ID。8.0.1 起 information_schema.innodb_lock_waits 已删除;5.7 仍用那张表,innodb_trx.trx_id 对 blocking_trx_id。阻塞方是 Sleep 才是这个参数能碰到的情况。表不存在时先确认 performance_schema 已开,空结果也可能只是当前没有等待。

代码语言:sql
复制
SELECT waiting.PROCESSLIST_ID AS waiting_pid,
       waiting.PROCESSLIST_INFO AS waiting_sql,
       blocking.PROCESSLIST_ID AS blocking_pid,
       blocking.PROCESSLIST_COMMAND AS blocking_command,
       blocking.PROCESSLIST_TIME AS blocking_sleep_sec
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.threads AS waiting
  ON waiting.THREAD_ID = w.REQUESTING_THREAD_ID
JOIN performance_schema.threads AS blocking
  ON blocking.THREAD_ID = w.BLOCKING_THREAD_ID
WHERE blocking.PROCESSLIST_COMMAND = 'Sleep';

参数改到小于休眠时间之后,在测试库探活。先建 idle_trx_probe.probe(id INT PRIMARY KEY) 并插入一行。客户端在两次执行之间停住,不要用 SELECT SLEEP()。70 换成大于生效阈值的秒数;参数仍是 0 且 wait_timeout 以小时计时,这次休眠不会被拆。预期第二次执行抛连接断开。第一次查询就失败,是测试表或账号问题,不是 kill。

代码语言:python
复制
import time
import pymysql

conn = pymysql.connect(
    host='10.0.0.12', port=3306, user='probe',
    password='example-password', database='idle_trx_probe',
    autocommit=False,
)
try:
    with conn.cursor() as cur:
        cur.execute('SELECT id FROM probe WHERE id = 1')
        cur.fetchall()
        time.sleep(70)
        cur.execute('SELECT 1')
    conn.commit()
except pymysql.MySQLError as exc:
    print(type(exc).__name__, exc)
finally:
    conn.close()

探活线程超过阈值后应从 innodb_trx 消失,再用上面的 Sleep 查询核对。高权限账号执行 SHOW ENGINE INNODB STATUS,在 TRANSACTIONS 段看 History list length。还有别的事务钉着更老的快照时,它不会立刻掉下去。

常见误判

Threads_running 正常,不代表没有空闲事务。这个参数不杀 Command = 'Query' 的慢 SQL,也不杀还在等锁的当前语句。kill 之后只重试刚才那一条 SQL,事务前半段已经回滚,外部调用不会跟着回滚。只改主实例时,只读上的发呆事务照样堵复制过来的 DDL。

上线前对一下

  • 内核小版本在支持列表里;当前值是 0 还是已经被改过。
  • 全局和业务会话的 wait_timeout 都量过,生效值按较小值算。会话调大 wait_timeout 不能越过 kill 阈值。
  • 低峰采样过 sleep_sec,阈值高于合法间隙,且低于 wait_timeout。
  • 应用事务超时短于服务端 kill;跨 RPC 不持有事务;归还连接前 ROLLBACK。
  • 只读实例单独看过。阻塞方是 Sleep 的锁等待,和正在执行的慢 SQL 分开处理。
  • 用测试库探活确认拆的是连接,而不是把 SELECT SLEEP() 当成验证。

参考文档:

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

目录
  • 空着的事务占的不是一条连接
  • 超时从下一条语句没来算起
  • 和 wait_timeout 取较小值
  • 阈值要高于合法间隙
  • 先查出谁在空转,再用探活确认
  • 常见误判
  • 上线前对一下
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档