小编给大家分享一下MySQL怎么一个杀掉数据库空闲事务，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！
我们经常遇到一个情况，就是网络断开或程序Bug导致COMMIT/ROLLBACK语句没有传到数据库，也没有释放线程，但是线上事务锁定等待严重，连接数暴涨，尤其在测试库这种情况很多，线上也偶有发生，于是想为MySQL增加一个杀掉空闲事务的功能。下面亿速云小编来讲解下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的“”注释下增加如下代码注册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) { # 如果空闲时间超过阈值则杀掉链接
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) { # 如果空闲时间超过阈值则杀掉链接
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) { # 如果空闲时间超过阈值则杀掉链接
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怎么一个杀掉数据库空闲事务”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！
