MySQL：网络断开后执行的事务怎么处理(read_timeou

作者: 重庆八怪 | 来源:发表于2020-08-24 10:13 被阅读0次

MySQL：网络断开后执行的事务怎么处理(read_timeou
20180521前端笔记
mysql之事务处理
发布订阅 - 确保用户注册成功发送短信通知
MySQL---数据库从入门走向大神系列(六)-事务处理与事务隔
mysql事物隔离机制
数据库基础知识
mysql 面试整理
99 MySQL性能实战优化
数据库锁及事务整理

水平有限，可能有误

一、read_timeout/write_timeout参数实现

另外一篇关于wait_timeout的文章
MySQL：参数wait_timeout和interactive_timeout以及空闲超时的实现

MySQL普通的链接没有保活机制，即没有设置socket属性，也没有设置心跳。如果网络链接断开不能及时服务端不能及时探测到。默认TCP 2小时+11次*75秒。

连接使用这种方式进行：
socket->listen->poll(socketfd)->accept->newthread->poll(newfd,wait_timeout)
一旦有新的数据到来，如果需要读取或者写入由于网络问题依旧使用poll进行等待，直到超时。
其中参数read_timeout/write_timeout用于读取网络数据的，如果网络不可用能够等待网络可用的时间，也就是wait_timeout和read_timeout/write_timeou均使用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.*/

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

测试方法很简单，做一个表16384行，然后使用

begin; 
insert into table select * from table limit 10; 
select sleep(0.01),* from table ;

执行期间断开客户端网卡连接，在服务端依旧可以看到

mysql> show processlist;
+----+-------+--------------------+------+---------+------+------------+-------------------------------------+-----------+---------------+
| Id | User  | Host               | db   | Command | Time | State      | Info                                | Rows_sent | Rows_examined |
+----+-------+--------------------+------+---------+------+------------+-------------------------------------+-----------+---------------+
|  7 | root  | localhost          | NULL | Query   |    0 | starting   | show processlist                    |         0 |             0 |
| 10 | ptest | 192.168.1.61:34540 | NULL | Query   |  258 | User sleep | select *,sleep(0.01) from test.oooo |      6651 |             0 |
+----+-------+--------------------+------+---------+------+------------+-------------------------------------+-----------+---------------+

直到write timeout时间后可以看到rollback栈。

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

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

一个连接进行事务后，如果事务执行完成而没有提交，如果网络断开，那么事务还存在语服务端，需要手动kill。因为默认会在2小时+11次*75秒后断开回滚。

[root@localhost ~]# netstat -anlp|grep 192.168.1.61
tcp6       0      0 192.168.1.62:3306       192.168.1.61:36926      ESTABLISHED 40913/mysqld

栈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)

MySQL：网络断开后执行的事务怎么处理(read_timeou
水平有限，可能有误一、read_timeout/write_timeout参数实现另外一篇关于wait_tim...
20180521前端笔记
1. websocket连接断开后怎么处理的，恢复网络后如何？ 2. nodejs模块机制，引入和导出，模块相互引...
mysql之事务处理
mysql之事务处理，在mysql版本5.5之前的引擎是MyISAM，后调整为支持事务的InnoDB。问题：1、...
发布订阅 - 确保用户注册成功发送短信通知
Spring事务监听机制—使用@TransactionalEventListener处理数据库事务提交成功后再执行...
MySQL---数据库从入门走向大神系列(六)-事务处理与事务隔
MySQL 事务处理简单介绍事务处理： MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管...
mysql事物隔离机制
Mac系统mysql设置事务隔离级别事务隔离级别指的是在处理同一个数据的多个事务中，一个事务修改数据后，其他事务...
数据库基础知识
MySQL事务 MySQL事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作，要么都执行修改，要...
mysql 面试整理
MySQL 事务特点，事务类型？事务的特点：原子性：要么执行成功要么执行失败。实现主要是以undo log隔离性...
99 MySQL性能实战优化
mysql 性能优化一 MySQL架构与执行流程原理二 MySQL 索引底层实现原理三 MYSQL事务...
数据库锁及事务整理
参考文档 MySQL的事务和隔离级别理解事务 - MySQL 事务处理机制《MySQL技术内幕》读书笔记一文说尽M...