如果MySQL事务中发生了网络异常？

用户1278550

发布于 2020-10-10 03:13:35

3.5K00

代码可运行

文章被收录于专栏：idbaidba

运行总次数：0

代码可运行

一前言

在我们运维 MySQL的时候，总会遇到各种情况导致程序和MySQL之间的会话异常中断，比如

假如强制关闭应用
假如client机器突然崩溃宕机/断电
假如网络发生抖动/网卡发生故障
机房级别断网

那么此时正在MySQL中执行的事务会何表现？

二实践

设计一个案例模拟client 在MySQL中执行事务，但是client机器突然down机，导致会话异常中断。

client 192.168.56.102  
MySQL  192.168.56.101

client 连接db 执行 select for update 动作给记录加上锁，当然实际上也可以是update，delete 这样的动作给记录加上锁。

然后关闭client机器模拟断电，断网。

此时server端网络层的连接状态依然是 ESTABLISH 数据库中的事务处于running状态。

再开启另外一个会话，对t1表进行加锁需要等待，说明断网之后的事务依然处于活跃状态。

ok 表演结束，我们接下来继续分析网络断开，事务为啥没有退出？

三分析

3.1 服务端为什么没有退出这个事务呢？

MySQL普通的会话连接没有保活机制，即没有设置socket属性，也没有设置心跳机制。如果网络连接异常断开服务端不能及时探测到该异常。更进一步，我们通过 TCP 关闭的四次握手来看

网络异常的时候，TCP连接的状态还是ESTABLISHED，说明 server 和 client 任何一方都没有主动发送FIN包，服务端还在等待 client端发送数据，此时的 MySQL 事务无法直接退出。

3.2 事务在网络断开后如何处理

事务正在执行

一个连接进行事务后，如果事务语句正在执行，那么网络断开后会在语句执行完成后回滚掉。因为执行状态包不能送达客户端,因此会感知到这种网络断开的错误。调试堆栈信息参考 堆栈1。

    if (thd->is_error() || (thd->variables.option_bits & OPTION_MASTER_SQL_ERROR))
      trans_rollback_stmt(thd);

事务执行完成未提交

如果事务中sql执行完成而没有提交，此时网络断开，那么事务还存在服务端，需要手动kill。client到server端的连接路径:

socket->listen->poll(socketfd)->accept->newthread->poll(newfd,wait_timeout)

一旦有新的数据到来，如果需要读取或者写入由于网络问题依旧使用poll进行等待，直到超时。其中参数 read_timeout/write_timeout 用于读取网络数据的，如果网络不可用，会话保持的时间就是等待网络可用的时间，也就是 wait_timeout和 read_timeout/write_timeout 均使用poll的timeout实现。见栈2，可见 vio_io_wait 函数用于处理各种超时，主要用poll来处理。

net_write_packet
 ->net_write_raw_loop  一个包大小16M
  ->vio_write
    ->mysql_socket_send 如果发送信息失败 inline_mysql_socket_send调用send命令
    ->如果是SOCKET_EAGAIN那么通过vio_socket_io_wait函数进行判定，需要等待的时间
      /* Wait for the output buffer to become writable.*/

3.3 何时退出呢？

这里需要分情况来讨论。

空闲连接状态时

此时连接的会话时间由 MySQL参数 wait_timeout 决定，默认是8h，也即会话时间空闲超过8h，会被MySQL自动关闭。详细知识可以移步到如下链接:

浅析interactive_timeout和wait_timeout

等待TCP超时

默认情况下会话会保持2小时+11次*75秒，此时服务端为啥没有退出这个事务呢？。(由TCP属性决定)

/proc/sys/net/ipv4/tcp_keepalive_time = 7200（等待空闲时间秒）
/proc/sys/net/ipv4/tcp_keepalive_intvl = 75（探测间隔秒）
/proc/sys/net/ipv4/tcp_keepalive_probes = 9（探测次数）

主动kill 异常会话

kill $thread_id;

我们之前遇到一次机房级别的断网，应用重连之后遇到大量sql 锁等待，于是乎，我们写了一个脚本定期kill 长时间活跃的事务，仅供大家参考。

# 针对系统运维层面
Kill_SQL="select concat('kill ', trx_mysql_thread_id ,' ;') from information_schema.processlist a,information_schema.INNODB_TRX b where a.ID=b.trx_mysql_thread_id and trx_started <=SUBDATE(now(),interval 60 second)  and STATE='' and COMMAND='Sleep' and TIME>=60"
LOGFILE='/data/logs/zandb_agent/kill_long_trx.log'
run_user=`whoami`

ret_log()
{
   msg="$1"
   echo `date +%Y%m%d_%H:%M:%S` "[info]" " $msg" >> ${LOGFILE}
}
if [ $#  -lt 1 ]; then
    ports=`ps -ef |grep mysqld| grep -v mysqld_safe |grep port=|awk -F"port=" '{print $NF}' |awk '{print $1}' | sort `
else
    ports=$1
fi
for port in ${ports};
do
   if [ -f /tmp/long_trx_${port}.lock ];then
      msg="there is a lock file ,so we skip instance ${port}.."
      ret_log "${msg}"
      continue
   fi

   if [ -S /srv/my${port}/run/mysql.sock  ]; then
      SOCKET="/srv/my${port}/run/mysql.sock"
   else
      msg="socket file does not exists,please check ."
      ret_log "${msg}"
   fi
   MYSQL="/opt/mysql/bin/mysql -uroot -S ${SOCKET}  "
   ${MYSQL} --skip-column-names -e "$Kill_SQL" > /tmp/kill_trx_${port}.sql.${run_user} 2>/tmp/kill_trx_${port}.log.${run_user}
   num=`grep kill -c /tmp/kill_trx_${port}.sql.${run_user}`
   if [[ ${num} -gt 0 ]]; then
      msg="${port}  ${num}  long trx was killed "
      ret_log "${msg}"
      ${MYSQL} -e "source /tmp/kill_trx_${port}.sql.${run_user}"
   fi
done

栈1：回滚栈

(gdb) bt
#0  innobase_rollback (hton=0x2e12440, thd=0x7ffefc000950, rollback_trx=false) at /home/mysql/soft/percona-server-5.7.29-32/storage/innobase/handler/ha_innodb.cc:5452
#1  0x0000000000ea6ab8 in ha_rollback_low (thd=0x7ffefc000950, all=false) at /home/mysql/soft/percona-server-5.7.29-32/sql/handler.cc:2019
#2  0x00000000017f0f23 in MYSQL_BIN_LOG::rollback (this=0x2d668a0 <mysql_bin_log>, thd=0x7ffefc000950, all=false) at /home/mysql/soft/percona-server-5.7.29-32/sql/binlog.cc:2532
#3  0x0000000000ea6d40 in ha_rollback_trans (thd=0x7ffefc000950, all=false) at /home/mysql/soft/percona-server-5.7.29-32/sql/handler.cc:2106
#4  0x00000000015c6a13 in trans_rollback_stmt (thd=0x7ffefc000950) at /home/mysql/soft/percona-server-5.7.29-32/sql/transaction.cc:515
#5  0x00000000014c08de in mysql_execute_command (thd=0x7ffefc000950, first_level=true) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:5325
#6  0x00000000014c2025 in mysql_parse (thd=0x7ffefc000950, parser_state=0x7fffe88824a0, update_userstat=false) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:5927
#7  0x00000000014b6c5f in dispatch_command (thd=0x7ffefc000950, com_data=0x7fffe8882c90, command=COM_QUERY) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:1539
#8  0x00000000014b5a94 in do_command (thd=0x7ffefc000950) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:1060
#9  0x00000000015e9d32 in handle_connection (arg=0x3c09eb0) at /home/mysql/soft/percona-server-5.7.29-32/sql/conn_handler/connection_handler_per_thread.cc:325
#10 0x00000000018b97f2 in pfs_spawn_thread (arg=0x3b784b0) at /home/mysql/soft/percona-server-5.7.29-32/storage/perfschema/pfs.cc:2198
#11 0x00007ffff7bc6ea5 in start_thread () from /lib64/libpthread.so.0
#12 0x00007ffff5fa08dd in clone () from /lib64/libc.so.6

栈2 写入等待timeout=60000 即默认的write_timeout=60S

#0  0x00007ffff5f95c3d in poll () from /lib64/libc.so.6
#1  0x0000000001da0e3b in vio_io_wait (vio=0x7ffefc005690, event=VIO_IO_EVENT_WRITE, timeout=60000) at /home/mysql/soft/percona-server-5.7.29-32/vio/viosocket.c:1173
#2  0x0000000001d9f352 in vio_socket_io_wait (vio=0x7ffefc005690, event=VIO_IO_EVENT_WRITE) at /home/mysql/soft/percona-server-5.7.29-32/vio/viosocket.c:127
#3  0x0000000001d9f74a in vio_write (vio=0x7ffefc005690, buf=0x7ffefc013838 "\001\061\001\060\004", size=12872) at /home/mysql/soft/percona-server-5.7.29-32/vio/viosocket.c:260
#4  0x00000000016f92a3 in net_write_raw_loop (net=0x7ffefc002528, buf=0x7ffefc013838 "\001\061\001\060\004", count=12872) at /home/mysql/soft/percona-server-5.7.29-32/sql/net_serv.cc:522
#5  0x00000000016f9588 in net_write_packet (net=0x7ffefc002528, packet=0x7ffefc012a80 "\001\061\001\060\004", length=16384) at /home/mysql/soft/percona-server-5.7.29-32/sql/net_serv.cc:661
#6  0x00000000016f9177 in net_write_buff (net=0x7ffefc002528, packet=0x7ffefc936f70 "\001\061\001\060", len=4) at /home/mysql/soft/percona-server-5.7.29-32/sql/net_serv.cc:474
#7  0x00000000016f8e1c in my_net_write (net=0x7ffefc002528, packet=0x7ffefc936f70 "\001\061\001\060", len=4) at /home/mysql/soft/percona-server-5.7.29-32/sql/net_serv.cc:347
#8  0x0000000001745635 in Protocol_classic::end_row (this=0x7ffefc001c68) at /home/mysql/soft/percona-server-5.7.29-32/sql/protocol_classic.cc:1204
#9  0x00000000014571d7 in Query_result_send::send_data (this=0x7ffefc0097a0, items=...) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_class.cc:2937
#10 0x0000000001477a0d in end_send (join=0x7ffefc009a70, qep_tab=0x7ffefc93b290, end_of_records=false) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_executor.cc:2946
#11 0x000000000147461b in evaluate_join_record (join=0x7ffefc009a70, qep_tab=0x7ffefc93b118) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_executor.cc:1652
#12 0x0000000001473a5b in sub_select (join=0x7ffefc009a70, qep_tab=0x7ffefc93b118, end_of_records=false) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_executor.cc:1304
#13 0x00000000014732dc in do_select (join=0x7ffefc009a70) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_executor.cc:957
#14 0x0000000001471243 in JOIN::exec (this=0x7ffefc009a70) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_executor.cc:206
#15 0x000000000150d2d5 in handle_query (thd=0x7ffefc000950, lex=0x7ffefc003000, result=0x7ffefc0097a0, added_options=0, removed_options=0)
    at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_select.cc:192
#16 0x00000000014c1097 in execute_sqlcom_select (thd=0x7ffefc000950, all_tables=0x7ffefc009160) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:5490
#17 0x00000000014ba323 in mysql_execute_command (thd=0x7ffefc000950, first_level=true) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:3016
#18 0x00000000014c2025 in mysql_parse (thd=0x7ffefc000950, parser_state=0x7fffe88824a0, update_userstat=false) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:5927
#19 0x00000000014b6c5f in dispatch_command (thd=0x7ffefc000950, com_data=0x7fffe8882c90, command=COM_QUERY) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:1539
#20 0x00000000014b5a94 in do_command (thd=0x7ffefc000950) at /home/mysql/soft/percona-server-5.7.29-32/sql/sql_parse.cc:1060
#21 0x00000000015e9d32 in handle_connection (arg=0x3c09eb0) at /home/mysql/soft/percona-server-5.7.29-32/sql/conn_handler/connection_handler_per_thread.cc:325
#22 0x00000000018b97f2 in pfs_spawn_thread (arg=0x3b784b0) at /home/mysql/soft/percona-server-5.7.29-32/storage/perfschema/pfs.cc:2198
#23 0x00007ffff7bc6ea5 in start_thread () from /lib64/libpthread.so.0
#24 0x00007ffff5fa08dd in clone () from /lib64/libc.so.6
(gdb)