--- 水平有限，可能有误 ###一、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) ```