如何杀掉空闲事务

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明网址: http://www.penglixun.com/tech/database/how_to_kill_idle_trx.html 我们经常遇到一个情况，就是网络断开或程序Bug导致COMMIT/ROLLBACK语句没有传到数

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明网址: http://www.penglixun.com/tech/database/how_to_kill_idle_trx.html

我们经常遇到一个情况，就是网络断开或程序Bug导致COMMIT/ROLLBACK语句没有传到数据库，也没有释放线程，但是线上事务锁定等待严重，连接数暴涨，尤其在测试库这种情况很多，线上也偶有发生，于是想为MySQL增加一个杀掉空闲事务的功能。

那么如何实现呢，通过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变量。

<span style="color: #0000ff;">static</span> MYSQL_SYSVAR_LONG<span style="color: #008000;">&#40;</span>idle_trx_timeout, srv_idle_trx_timeout,
  PLUGIN_VAR_RQCMDARG,
  <span style="color: #FF0000;">"If zero then this function no effect, if no-zero then wait idle_trx_timeout seconds this transaction will be closed"</span>,
  <span style="color: #FF0000;">"Seconds of Idle-Transaction timeout"</span>,
  <span style="color: #0000ff;">NULL</span>, <span style="color: #0000ff;">NULL</span>, <span style="color: #0000dd;">0</span>, <span style="color: #0000dd;">0</span>, <span style="color: #0000ff;">LONG_MAX</span>, <span style="color: #0000dd;">0</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>

代码往下找在innobase_system_variables结构体内加上：

MYSQL_SYSVAR<span style="color: #008000;">&#40;</span>idle_trx_timeout<span style="color: #008000;">&#41;</span>,

有了这个变量，我们需要在Master Thread(storage/innodb_plugin/srv/srv0srv.c )中执行检测函数查找空闲事务。在loop循环的if (sync_array_print_long_waits(&waiter, &sema)判断后加上这段判断

    <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>srv_idle_trx_timeout <span style="color: #000040;">&&</span> trx_sys<span style="color: #008000;">&#41;</span> <span style="color: #008000;">&#123;</span>
        trx_t<span style="color: #000040;">*</span>  trx<span style="color: #008080;">;</span>
        <span style="color: #0000ff;">time_t</span>  now<span style="color: #008080;">;</span>
rescan_idle<span style="color: #008080;">:</span>
        now <span style="color: #000080;">=</span> <span style="color: #0000dd;">time</span><span style="color: #008000;">&#40;</span><span style="color: #0000ff;">NULL</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
        mutex_enter<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>kernel_mutex<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
        trx <span style="color: #000080;">=</span> UT_LIST_GET_FIRST<span style="color: #008000;">&#40;</span>trx_sys<span style="color: #000040;">-</span><span style="color: #000080;">></span>mysql_trx_list<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span> <span style="color: #339900;"># 从当前事务列表里获取第一个事务</span>
        <span style="color: #0000ff;">while</span> <span style="color: #008000;">&#40;</span>trx<span style="color: #008000;">&#41;</span> <span style="color: #008000;">&#123;</span> <span style="color: #339900;"># 依次循环每个事务进行检查</span>
            <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>trx<span style="color: #000040;">-</span><span style="color: #000080;">></span>conc_state <span style="color: #000080;">==</span> TRX_ACTIVE
                <span style="color: #000040;">&&</span> trx<span style="color: #000040;">-</span><span style="color: #000080;">></span>mysql_thd
                <span style="color: #000040;">&&</span> innobase_thd_is_idle<span style="color: #008000;">&#40;</span>trx<span style="color: #000040;">-</span><span style="color: #000080;">></span>mysql_thd<span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span> <span style="color: #008000;">&#123;</span> <span style="color: #339900;"># 如果事务还活着并且它的状态时空闲的</span>
 
                ib_int64_t  start_time <span style="color: #000080;">=</span> innobase_thd_get_start_time<span style="color: #008000;">&#40;</span>trx<span style="color: #000040;">-</span><span style="color: #000080;">></span>mysql_thd<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span> <span style="color: #339900;"># 获取线程最后一个语句的开始时间</span>
                ulong       thd_id <span style="color: #000080;">=</span> innobase_thd_get_thread_id<span style="color: #008000;">&#40;</span>trx<span style="color: #000040;">-</span><span style="color: #000080;">></span>mysql_thd<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span> <span style="color: #339900;">#获取线程ID，因为存储引擎内直接操作THD不安全</span>
 
                <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>trx<span style="color: #000040;">-</span><span style="color: #000080;">></span>last_stmt_start <span style="color: #000040;">!</span><span style="color: #000080;">=</span> start_time<span style="color: #008000;">&#41;</span> <span style="color: #008000;">&#123;</span> <span style="color: #339900;"># 如果事务最后语句起始时间不等于线程最后语句起始时间说明事务是新起的</span>
                    trx<span style="color: #000040;">-</span><span style="color: #000080;">></span>idle_start <span style="color: #000080;">=</span> now<span style="color: #008080;">;</span> <span style="color: #339900;"># 更新事务的空闲起始时间</span>
                    trx<span style="color: #000040;">-</span><span style="color: #000080;">></span>last_stmt_start <span style="color: #000080;">=</span> start_time<span style="color: #008080;">;</span> <span style="color: #339900;"># 更新事务的最后语句起始时间</span>
                <span style="color: #008000;">&#125;</span> <span style="color: #0000ff;">else</span> <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span><span style="color: #0000dd;">difftime</span><span style="color: #008000;">&#40;</span>now, trx<span style="color: #000040;">-</span><span style="color: #000080;">></span>idle_start<span style="color: #008000;">&#41;</span> <span style="color: #339900;"># 如果事务不是新起的，已经执行了一部分则判断空闲时间有多长了</span>
                       <span style="color: #000080;">></span> srv_idle_trx_timeout<span style="color: #008000;">&#41;</span> <span style="color: #008000;">&#123;</span> <span style="color: #339900;"># 如果空闲时间超过阈值则杀掉链接</span>
                    <span style="color: #ff0000; font-style: italic;">/* kill the session */</span>
                    mutex_exit<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>kernel_mutex<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
                    thd_kill<span style="color: #008000;">&#40;</span>thd_id<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span> <span style="color: #339900;"># 杀链接</span>
                    <span style="color: #0000ff;">goto</span> rescan_idle<span style="color: #008080;">;</span>
                <span style="color: #008000;">&#125;</span>
            <span style="color: #008000;">&#125;</span>
            trx <span style="color: #000080;">=</span> UT_LIST_GET_NEXT<span style="color: #008000;">&#40;</span>mysql_trx_list, trx<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span> <span style="color: #339900;"># 检查下一个事务</span>
        <span style="color: #008000;">&#125;</span>
        mutex_exit<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>kernel_mutex<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
    <span style="color: #008000;">&#125;</span>

其中trx中的变量是新加的，在storage/innodb_plugin/include/trx0trx.h的trx_truct加上需要的变量：

<span style="color: #0000ff;">struct</span> trx_struct<span style="color: #008000;">&#123;</span>
...
    <span style="color: #0000ff;">time_t</span>      idle_start<span style="color: #008080;">;</span>
    ib_int64_t  last_stmt_start<span style="color: #008080;">;</span>
...
<span style="color: #008000;">&#125;</span>

这里有几个函数是自定义的：

ibool      innobase_thd_is_idle<span style="color: #008000;">&#40;</span><span style="color: #0000ff;">const</span> <span style="color: #0000ff;">void</span><span style="color: #000040;">*</span> thd<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
ib_int64_t innobase_thd_get_start_time<span style="color: #008000;">&#40;</span><span style="color: #0000ff;">const</span> <span style="color: #0000ff;">void</span><span style="color: #000040;">*</span> thd<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
ulong      innobase_thd_get_thread_id<span style="color: #008000;">&#40;</span><span style="color: #0000ff;">const</span> <span style="color: #0000ff;">void</span><span style="color: #000040;">*</span> thd<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>

这些函数在ha_innodb.cc中实现，需要在storage/innodb_plugin/srv/srv0srv.c头文件定义下加上这些函数的引用形势。

然后在storage/innodb_plugin/handler/ha_innodb.cc 中定义这些函数的实现：

<span style="color: #0000ff;">extern</span> <span style="color: #FF0000;">"C"</span>
ibool
innobase_thd_is_idle<span style="color: #008000;">&#40;</span>
    <span style="color: #0000ff;">const</span> <span style="color: #0000ff;">void</span><span style="color: #000040;">*</span> thd<span style="color: #008000;">&#41;</span>    <span style="color: #ff0000; font-style: italic;">/*!< in: thread handle (THD*) */</span>
<span style="color: #008000;">&#123;</span>
    <span style="color: #0000ff;">return</span><span style="color: #008000;">&#40;</span><span style="color: #008000;">&#40;</span><span style="color: #008000;">&#40;</span><span style="color: #0000ff;">const</span> THD<span style="color: #000040;">*</span><span style="color: #008000;">&#41;</span>thd<span style="color: #008000;">&#41;</span><span style="color: #000040;">-</span><span style="color: #000080;">></span>command <span style="color: #000080;">==</span> COM_SLEEP<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
<span style="color: #008000;">&#125;</span>
<span style="color: #0000ff;">extern</span> <span style="color: #FF0000;">"C"</span>
ib_int64_t
innobase_thd_get_start_time<span style="color: #008000;">&#40;</span>
    <span style="color: #0000ff;">const</span> <span style="color: #0000ff;">void</span><span style="color: #000040;">*</span> thd<span style="color: #008000;">&#41;</span>    <span style="color: #ff0000; font-style: italic;">/*!< in: thread handle (THD*) */</span>
<span style="color: #008000;">&#123;</span>
    <span style="color: #0000ff;">return</span><span style="color: #008000;">&#40;</span><span style="color: #008000;">&#40;</span>ib_int64_t<span style="color: #008000;">&#41;</span><span style="color: #008000;">&#40;</span><span style="color: #008000;">&#40;</span><span style="color: #0000ff;">const</span> THD<span style="color: #000040;">*</span><span style="color: #008000;">&#41;</span>thd<span style="color: #008000;">&#41;</span><span style="color: #000040;">-</span><span style="color: #000080;">></span>start_time<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
<span style="color: #008000;">&#125;</span>
<span style="color: #0000ff;">extern</span> <span style="color: #FF0000;">"C"</span>
ulong
innobase_thd_get_thread_id<span style="color: #008000;">&#40;</span>
        <span style="color: #0000ff;">const</span> <span style="color: #0000ff;">void</span><span style="color: #000040;">*</span> thd<span style="color: #008000;">&#41;</span>
<span style="color: #008000;">&#123;</span>
    <span style="color: #0000ff;">return</span><span style="color: #008000;">&#40;</span>thd_get_thread_id<span style="color: #008000;">&#40;</span><span style="color: #008000;">&#40;</span><span style="color: #0000ff;">const</span> THD<span style="color: #000040;">*</span><span style="color: #008000;">&#41;</span> thd<span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
<span style="color: #008000;">&#125;</span>

还有最重要的thd_kill函数负责杀线程的，在sql/sql_class.cc中，找个地方定义这个函数：

<span style="color: #0000ff;">void</span> thd_kill<span style="color: #008000;">&#40;</span>ulong id<span style="color: #008000;">&#41;</span>
<span style="color: #008000;">&#123;</span>
    THD <span style="color: #000040;">*</span>tmp<span style="color: #008080;">;</span>
    VOID<span style="color: #008000;">&#40;</span>pthread_mutex_lock<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>LOCK_thread_count<span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
    I_List_iterator<span style="color: #000080;"><</span>THD<span style="color: #000080;">></span> it<span style="color: #008000;">&#40;</span>threads<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
    <span style="color: #0000ff;">while</span> <span style="color: #008000;">&#40;</span><span style="color: #008000;">&#40;</span>tmp<span style="color: #000080;">=</span>it<span style="color: #000040;">++</span><span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span>
    <span style="color: #008000;">&#123;</span>
        <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>tmp<span style="color: #000040;">-</span><span style="color: #000080;">></span>command <span style="color: #000080;">==</span> COM_DAEMON <span style="color: #000040;">||</span> tmp<span style="color: #000040;">-</span><span style="color: #000080;">></span>is_have_lock_thd <span style="color: #000080;">==</span> <span style="color: #0000dd;">0</span> <span style="color: #008000;">&#41;</span> <span style="color: #339900;"># 如果是DAEMON线程和不含锁的线程就不要kill了</span>
            <span style="color: #0000ff;">continue</span><span style="color: #008080;">;</span>
        <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>tmp<span style="color: #000040;">-</span><span style="color: #000080;">></span>thread_id <span style="color: #000080;">==</span> id<span style="color: #008000;">&#41;</span>
        <span style="color: #008000;">&#123;</span>
            pthread_mutex_lock<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>tmp<span style="color: #000040;">-</span><span style="color: #000080;">></span>LOCK_thd_data<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
            <span style="color: #0000ff;">break</span><span style="color: #008080;">;</span>
        <span style="color: #008000;">&#125;</span>
    <span style="color: #008000;">&#125;</span>
    VOID<span style="color: #008000;">&#40;</span>pthread_mutex_unlock<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>LOCK_thread_count<span style="color: #008000;">&#41;</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
    <span style="color: #0000ff;">if</span> <span style="color: #008000;">&#40;</span>tmp<span style="color: #008000;">&#41;</span>
    <span style="color: #008000;">&#123;</span>
        tmp<span style="color: #000040;">-</span><span style="color: #000080;">></span>awake<span style="color: #008000;">&#40;</span>THD<span style="color: #008080;">::</span><span style="color: #007788;">KILL_CONNECTION</span><span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
        pthread_mutex_unlock<span style="color: #008000;">&#40;</span><span style="color: #000040;">&</span>tmp<span style="color: #000040;">-</span><span style="color: #000080;">></span>LOCK_thd_data<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>
    <span style="color: #008000;">&#125;</span>
<span style="color: #008000;">&#125;</span>

为了存储引擎能引用到这个函数，我们要把它定义到plugin中：
include/mysql/plugin.h和include/mysql/plugin.h中加上

<span style="color: #0000ff;">void</span> thd_kill<span style="color: #008000;">&#40;</span><span style="color: #0000ff;">unsigned</span> <span style="color: #0000ff;">long</span> id<span style="color: #008000;">&#41;</span><span style="color: #008080;">;</span>

如何判定线程的is_have_lock_thd值？首先在THD中加上这个变量（sql/sql_class.cc）：

<span style="color: #0000ff;">class</span> THD <span style="color: #008080;">:</span><span style="color: #0000ff;">public</span> Statement,
           <span style="color: #0000ff;">public</span> Open_tables_state
<span style="color: #008000;">&#123;</span>
....
  <span style="color: #007788;">uint16</span>    is_have_lock_thd<span style="color: #008080;">;</span>
....
<span style="color: #008000;">&#125;</span>

然后在SQL的必经之路mysql_execute_command拦上一刀，判断是有锁操作发生了还是事务提交或新起事务。

  <span style="color: #0000ff;">switch</span> <span style="color: #008000;">&#40;</span>lex<span style="color: #000040;">-</span><span style="color: #000080;">></span>sql_command<span style="color: #008000;">&#41;</span> <span style="color: #008000;">&#123;</span>
  <span style="color: #0000ff;">case</span> SQLCOM_REPLACE<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_REPLACE_SELECT<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_UPDATE<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_UPDATE_MULTI<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_DELETE<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_DELETE_MULTI<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_INSERT<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_INSERT_SELECT<span style="color: #008080;">:</span>
      thd<span style="color: #000040;">-</span><span style="color: #000080;">></span>is_have_lock_thd <span style="color: #000080;">=</span> <span style="color: #0000dd;">1</span><span style="color: #008080;">;</span>
      <span style="color: #0000ff;">break</span><span style="color: #008080;">;</span>
  <span style="color: #0000ff;">case</span> SQLCOM_COMMIT<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_ROLLBACK<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_XA_START<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_XA_END<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_XA_PREPARE<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_XA_COMMIT<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_XA_ROLLBACK<span style="color: #008080;">:</span>
  <span style="color: #0000ff;">case</span> SQLCOM_XA_RECOVER<span style="color: #008080;">:</span>
      thd<span style="color: #000040;">-</span><span style="color: #000080;">></span>is_have_lock_thd <span style="color: #000080;">=</span> <span style="color: #0000dd;">0</span><span style="color: #008080;">;</span>
      <span style="color: #0000ff;">break</span><span style="color: #008080;">;</span>
  <span style="color: #008000;">&#125;</span>

为了尽可能兼容Percona的补丁，能引用的都引用了Percona的操作，有些函数调用是在层次太多看不下去了就简化了。
另外还有一个版本是我自己弄的，在THD中增加了一个last_sql_end_time，在do_command结束后更新last_sql_end_time，然后在事务中拿到THD查看last_sql_end_time就可以得出idle时间，Oracle版我还是建议这么做，不要去改trx_struct结构体了，那个感觉更危险。