共计 4176 个字符，预计需要花费 11 分钟才能阅读完成。
自动写代码机器人，免费开通
丸趣 TV 小编给大家分享一下 MySQL 怎么一个杀掉数据库空闲事务，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！
我们经常遇到一个情况，就是网络断开或程序 Bug 导致 COMMIT/ROLLBACK 语句没有传到数据库，也没有释放线程，但是线上事务锁定等待严重，连接数暴涨，尤其在测试库这种情况很多，线上也偶有发生，于是想为 MySQL 增加一个杀掉空闲事务的功能。下面丸趣 TV 丸趣 TV 小编来讲解下 MySQ 如何一个杀掉数据库空闲事务?
MySQ 如何一个杀掉数据库空闲事务
通过 MySQL Server 层有很多不确定因素，最保险还是在存储引擎层实现，我们用的几乎都是 InnoDB/XtraDB，所以就基于 Percona 来修改了，Oracle 版的 MySQL 也可以照着修改。
需求：
1. 一个事务启动，如果事务内最后一个语句执行完超过一个时间 (innodb_idle_trx_timeout)，就应该关闭链接。
2. 如果事务是纯读事务，因为不加锁，所以无害，不需要关闭，保持即可。
虽然这个思路被 Percona 的指出 Alexey Kopytov 可能存在“Even though SELECT queries do not place row locks by default (there are exceptions), they can still block undo log records from being purged.”的问题，但是我们确实有场景 SELECT 是绝对不能 kill 的，除非之后的 INSERT/UPDATE/DELETE 发生了，所以我根据 我们的业务特点来修改。
跟 Percona 的 Yasufumi Kinoshita 和 Alexey Kopytov 提出过纯 SELECT 事务不应被 kill，但通过一个参数控制的方案还没有被 Alexey Kopytov 接受，作为通用处理我提出了用两个变量分别控制纯读事务的空闲超时时间和有锁事务的空闲超时时间，还在等待 Percona 的回复，因为这个 方案还在测试，就先不开放修改了，当然如果你很熟悉 MYSQL 源码，我提出这个思路你肯定知道怎么分成这两个参数控制了。
根据这两个需 求我们来设计方法，首先想到这个功能肯定是放在 InnoDB Master Thread 最方便，Master Thread 每秒调度一次，可以顺便检查空闲事务，然后关闭，因为在事务中操作 trx- mysql_thd 并不安全，所以一般来说最好在 InnoDB 层换成 Thread ID 操作，并且 InnoDB 中除了 ha_innodb.cc，其他地方不能饮用 THD，所以 Master Thread 中需要的线程数值，都需要在 ha_innodb 中计算好传递整型或布尔型返回值给 master thread 调用。
首先，我们要增加一个参数：idle_trx_timeout，它表示事务多久没有下一条语句发生就超时关闭。
在 storage/innodb_plugin/srv/srv0srv.c 的“/* plugin options */”注释下增加如下代码注册 idle_trx_timeout 变量。
if (srv_idle_trx_timeout trx_sys) {
trx_t* trx;
time_t now;
rescan_idle:
now = time(NULL);
mutex_enter(kernel_mutex);
trx = UT_LIST_GET_FIRST(trx_sys- mysql_trx_list); # 从当前事务列表里获取第一个事务
while (trx) {# 依次循环每个事务进行检查
if (trx- conc_state == TRX_ACTIVE
trx- mysql_thd
innobase_thd_is_idle(trx- mysql_thd)) {# 如果事务还活着并且它的状态时空闲的
ib_int64_t start_time = innobase_thd_get_start_time(trx- mysql_thd); # 获取线程最后一个语句的开始时间
ulong thd_id = innobase_thd_get_thread_id(trx- mysql_thd); #获取线程 ID，因为存储引擎内直接操作 THD 不安全
if (trx- last_stmt_start != start_time) {# 如果事务最后语句起始时间不等于线程最后语句起始时间说明事务是新起的
trx- idle_start = now; # 更新事务的空闲起始时间
trx- last_stmt_start = start_time; # 更新事务的最后语句起始时间
} else if (difftime(now, trx- idle_start) # 如果事务不是新起的，已经执行了一部分则判断空闲时间有多长了
srv_idle_trx_timeout) {# 如果空闲时间超过阈值则杀掉链接
/* kill the session */
mutex_exit(kernel_mutex);
thd_kill(thd_id); # 杀链接
goto rescan_idle;
}
}
trx = UT_LIST_GET_NEXT(mysql_trx_list, trx); # 检查下一个事务
}
mutex_exit(kernel_mutex);
}
代码往下找在 innobase_system_variables 结构体内加上：
MySQ 如何一个杀掉数据库空闲事务
if (srv_idle_trx_timeout trx_sys) {
trx_t* trx;
time_t now;
rescan_idle:
now = time(NULL);
mutex_enter(kernel_mutex);
trx = UT_LIST_GET_FIRST(trx_sys- mysql_trx_list); # 从当前事务列表里获取第一个事务
while (trx) {# 依次循环每个事务进行检查
if (trx- conc_state == TRX_ACTIVE
trx- mysql_thd
innobase_thd_is_idle(trx- mysql_thd)) {# 如果事务还活着并且它的状态时空闲的
ib_int64_t start_time = innobase_thd_get_start_time(trx- mysql_thd); # 获取线程最后一个语句的开始时间
ulong thd_id = innobase_thd_get_thread_id(trx- mysql_thd); #获取线程 ID，因为存储引擎内直接操作 THD 不安全
if (trx- last_stmt_start != start_time) {# 如果事务最后语句起始时间不等于线程最后语句起始时间说明事务是新起的
trx- idle_start = now; # 更新事务的空闲起始时间
trx- last_stmt_start = start_time; # 更新事务的最后语句起始时间
} else if (difftime(now, trx- idle_start) # 如果事务不是新起的，已经执行了一部分则判断空闲时间有多长了
srv_idle_trx_timeout) {# 如果空闲时间超过阈值则杀掉链接
/* kill the session */
mutex_exit(kernel_mutex);
thd_kill(thd_id); # 杀链接
goto rescan_idle;
}
}
trx = UT_LIST_GET_NEXT(mysql_trx_list, trx); # 检查下一个事务
}
mutex_exit(kernel_mutex);
}
有了这个变量，我们需要在 Master Thread(storage/innodb_plugin/srv/srv0srv.c) 中执行检测函数查找空闲事务。在 loop 循环的 if (sync_array_print_long_waits( waiter, sema) 判断后加上这段判断
if (srv_idle_trx_timeout trx_sys) {
trx_t* trx;
time_t now;
rescan_idle:
now = time(NULL);
mutex_enter(kernel_mutex);
trx = UT_LIST_GET_FIRST(trx_sys- mysql_trx_list); # 从当前事务列表里获取第一个事务
while (trx) {# 依次循环每个事务进行检查
if (trx- conc_state == TRX_ACTIVE
trx- mysql_thd
innobase_thd_is_idle(trx- mysql_thd)) {# 如果事务还活着并且它的状态时空闲的
ib_int64_t start_time = innobase_thd_get_start_time(trx- mysql_thd); # 获取线程最后一个语句的开始时间
ulong thd_id = innobase_thd_get_thread_id(trx- mysql_thd); #获取线程 ID，因为存储引擎内直接操作 THD 不安全
if (trx- last_stmt_start != start_time) {# 如果事务最后语句起始时间不等于线程最后语句起始时间说明事务是新起的
trx- idle_start = now; # 更新事务的空闲起始时间
trx- last_stmt_start = start_time; # 更新事务的最后语句起始时间
} else if (difftime(now, trx- idle_start) # 如果事务不是新起的，已经执行了一部分则判断空闲时间有多长了
srv_idle_trx_timeout) {# 如果空闲时间超过阈值则杀掉链接
/* kill the session */
mutex_exit(kernel_mutex);
thd_kill(thd_id); # 杀链接
goto rescan_idle;
}
}
trx = UT_LIST_GET_NEXT(mysql_trx_list, trx); # 检查下一个事务
}
mutex_exit(kernel_mutex);
}
其中 trx 中的变量是新加的，在 storage/innodb_plugin/include/trx0trx.h 的 trx_truct 加上需要的变量。
看完了这篇文章，相信你对“MySQL 怎么一个杀掉数据库空闲事务”有了一定的了解，如果想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
向 AI 问一下细节
