图片 Akame-Akame-Ga-Kill
一前言
因为各种原因慢查询，错误的SQL语句等，MySQL DBA在做系统维护的时候，都使用过kill 命令终止会话。我们在数据库服务器会针对每个实例设置一个监控程序用来kill掉那些超过阈值的sql，默认的阈值设置是1.75s。今天我们就聊聊MySQL kill 命令的那些事儿。
二 基础知识
2.1 kill命令
我们通过kill命令可以kill终止sql执行或中断数据库连接。 kill的语法如下:
KILL [CONNECTION | QUERY] processlist_id
其中
KILL CONNECTION processlist_id
意思是终止SQL执行并且中断连接
KILL QUERY processlist_id
意思是 中断sql语句的执行，但是会话依然保持着。我们通过如下两个例子来比较其中的差异:
kill connection 的表现
mysql [localhost] {msandbox} ((none)) > select sleep(30);
ERROR 2013 (HY000): Lost connection to MySQL server during query此时该会话已经中断,再次复用时会报错
kill querey 的表现
mysql [localhost] {msandbox} ((none)) > select sleep(30);
+-----------+
| sleep(30) |
+-----------+
| 1 |
+-----------+
1 row in set (16.21 sec)
mysql [localhost] {msandbox} ((none)) >我们可以看到sql执行中断了，但是会话连接依然保持。
2.2 kill 的基本原理
MySQL接收到kill命令之后，会通过函数thd_set_kill_status将相关线程实例thd设置为killed状态，该函数根据 kill connection/kill query 做出相应的反应。 若为会kill connection，则主动关闭连接。
然后需要特别说明的是无论kill connection还是kill query，执行中的SQL并不一定不立即终止,因为在MySQL在很多场景中都会调用trx_is_interrupted函数判断自身的状态是否为killed，若是，则报错返回，终止执行。 
2.3 kill命令的场景
哪些场景会进行检查呢？官方文档中做了如下说明:(不做额为翻译了，大家自己阅读)
During SELECT operations, for ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.
ALTER TABLE operations that make a table copy check the kill flag periodically for each few copied rows read from the original table. If the kill flag was set, the statement is aborted and the temporary table is deleted. The KILL statement returns without waiting for confirmation, but the kill flag check aborts the operation within a reasonably small amount of time. Aborting the operation to perform any necessary cleanup also takes some time.
During UPDATE or DELETE operations, the kill flag is checked after each block read and after each updated or deleted row. If the kill flag is set, the statement is aborted. If you are not using transactions, the changes are not rolled back.
GET_LOCK() aborts and returns NULL.
If the thread is in the table lock handler (state: Locked), the table lock is quickly aborted.
If the thread is waiting for free disk space in a write call, the write is aborted with a “disk full” error message.
如果空闲连接被kill，那么这个连接何时被关闭呢？执行kill connection xxx时，会首先将thd的状态打标，然后会调用对应thd的vio_cancel来关闭socket连接，进而产生一个网路事件，监听线程捕获到对应连接的event，交给worker线程处理，worker线程检查thd的状态为killed，则结束连接，将其从global_thread_list中摘除。
如果被kill的连接正在等一个锁(行锁，表锁)，则调用引擎层的kill接口，对于innodb是innobase_kill_connection，将请求等待的锁释放，并将其唤醒，被唤醒的线程发现自己的状态我killed，则退出,具体可以参考2.4章节的awake函数。
2.4 相关函数的源代码
enum killed_state
{
NOT_KILLED=0,
KILL_BAD_DATA=1,
KILL_CONNECTION=ER_SERVER_SHUTDOWN,
KILL_QUERY=ER_QUERY_INTERRUPTED,
KILL_TIMEOUT=ER_QUERY_TIMEOUT,
KILLED_NO_VALUE /* means neither of the states */
};Determines if the currently running transaction has been interrupted.
@return TRUE if interrupted */
ibool
trx_is_interrupted(
/*===============*/
const trx_t* trx) /*!< in: transaction */
{
return(trx && trx->mysql_thd && thd_killed(trx->mysql_thd));
}/**
Check the killed state of a user thread
@param thd user thread
@retval 0 the user thread is active
@retval 1 the user thread has been killed
*/
extern "C" int thd_killed(const MYSQL_THD thd)
{
if (thd == NULL)
return current_thd != NULL ? current_thd->killed : 0;
return thd->killed;
}
/**
Set the killed status of the current statement.
@param thd user thread connection handle
*/
extern "C" void thd_set_kill_status(const MYSQL_THD thd)
{
thd->send_kill_message(); // 该函数会根据kill命令返回客户端响应的信息。
}awake函数对应如果一个SQL持有锁相关操作.
void THD::awake(THD::killed_state state_to_set)
{
DBUG_ENTER("THD::awake");
DBUG_PRINT("enter", ("this: %p current_thd: %p", this, current_thd));
THD_CHECK_SENTRY(this);
mysql_mutex_assert_owner(&LOCK_thd_data);
/*
Set killed flag if the connection is being killed (state_to_set
is KILL_CONNECTION) or the connection is processing a query
(state_to_set is KILL_QUERY and m_server_idle flag is not set).
If the connection is idle and state_to_set is KILL QUERY, the
the killed flag is not set so that it doesn't affect the next
command incorrectly.
*/
if (this->m_server_idle && state_to_set == KILL_QUERY)
{ /* nothing */ }
else
{
killed= state_to_set;
}
if (state_to_set != THD::KILL_QUERY && state_to_set != THD::KILL_TIMEOUT)
{
if (this != current_thd)
{
if (active_vio)
vio_cancel(active_vio, SHUT_RDWR);
}
/* Send an event to the scheduler that a thread should be killed. */
if (!slave_thread)
MYSQL_CALLBACK(this->scheduler, post_kill_notification, (this));
}
/* Interrupt target waiting inside a storage engine. */
if (state_to_set != THD::NOT_KILLED)
ha_kill_connection(this);
if (state_to_set == THD::KILL_TIMEOUT)
{
DBUG_ASSERT(!status_var_aggregated);
status_var.max_execution_time_exceeded++;
}
/* Broadcast a condition to kick the target if it is waiting on it. */
if (is_killable)
{
mysql_mutex_lock(&LOCK_current_cond);
/*
This broadcast could be up in the air if the victim thread
exits the cond in the time between read and broadcast, but that is
ok since all we want to do is to make the victim thread get out
of waiting on current_cond.
If we see a non-zero current_cond: it cannot be an old value (because
then exit_cond() should have run and it can't because we have mutex); so
it is the true value but maybe current_mutex is not yet non-zero (we're
in the middle of enter_cond() and there is a "memory order
inversion"). So we test the mutex too to not lock 0.
Note that there is a small chance we fail to kill. If victim has locked
current_mutex, but hasn't yet entered enter_cond() (which means that
current_cond and current_mutex are 0), then the victim will not get
a signal and it may wait "forever" on the cond (until
we issue a second KILL or the status it's waiting for happens).
It's true that we have set its thd->killed but it may not
see it immediately and so may have time to reach the cond_wait().
However, where possible, we test for killed once again after
enter_cond(). This should make the signaling as safe as possible.
However, there is still a small chance of failure on platforms with
instruction or memory write reordering.
*/
if (current_cond && current_mutex)
{
DBUG_EXECUTE_IF("before_dump_thread_acquires_current_mutex",
{
const char act[]=
"now signal dump_thread_signal wait_for go_dump_thread";
DBUG_ASSERT(!debug_sync_set_action(current_thd,
STRING_WITH_LEN(act)));
};);
mysql_mutex_lock(current_mutex);
mysql_cond_broadcast(current_cond);
mysql_mutex_unlock(current_mutex);
}
mysql_mutex_unlock(&LOCK_current_cond);
}
DBUG_VOID_RETURN;
}2.5案例分析
某天中午我们收到报警很多sql被kill，而且是同一条sql被工具sql-killer kill很多次。部分信息如下图：
注意截图中红色标记的错误代码1161,正常情况下被kill会返回错误码为1317。为什么这里是1161,它又意味着什么？
perror 1161 MySQL error code 1161 (ER_NET_WRITE_INTERRUPTED): Got timeout writing communication packets
经同事王航威排查，大概的经过是:业务程序接收一批数据后，在发送给client的过程中,网络发生异常,导致应用服务器不能接收数据，此时sql-killer检查到查询超时并发送kill query命令kill掉长查询，而MySQL则需要等这批数据发送完成后，去检查会话的killed_flag,但是数据一直没发送完成,直到net_write_timeout(30秒)超时，在30秒钟，SQL被sql-killer多次kill。
net_write_timeout:The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.
三 总结
kill命令好多DBA经常使用，但是细微之处见真章，深入分析之后还是有很多技术知识点可以学习的。kill 之后并不会直接结束，对于涉及到回滚，锁释放等等场景大家可以自己去探索。
另外自己对阅读源码还不够深入，部分表述可能存在偏差，大家可以留言指正。
