首先看一下MySQL timeout相关的变量
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> show global variables like '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 | | wait_timeout | 28800 | +-----------------------------+----------+ 13 rows in set (0.07 sec) |
connect_timeout
默认值10，是给连接认证过程用的，读和写都用这个值，认证完成后，读和写分别设置为net_read_timeout和net_write_timeout。
delayed_insert_timeout
默认值300，此系统变量已被弃用（因为DELAYED不支持插入），并将在以后的版本中删除。
have_statement_timeout
语句执行超时功能是否可用，默认值为YES。
innodb_lock_wait_timeout
InnoDB事务在放弃之前等待行锁的时间长度（秒），默认值为50秒。尝试访问由另一个InnoDB事务锁定的行的事务在发出以下错误之前最多等待多少秒写入访问行：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.
当发生锁等待超时时，当前语句被回滚（而不是整个事务）。要使整个事务回滚，请使用该–innodb_rollback_on_timeout选项启动服务器。
innodb_lock_wait_timeout对于InnoDB仅适用于行锁，MySQL表锁不会发生在内部InnoDB，这个超时不适用于等待表锁。另外锁等待超时值不适用于死锁，因为InnoDB检测到死锁默认立即会回滚其中一个死锁事务。但是当innodb_deadlock_detect被禁用，InnoDB依赖于innodb_lock_wait_timeout对事务回滚发生死锁时。
innodb_lock_wait_timeout可以在运行时使用SET [ SESSION | GLOBAL ]语句设置。更改GLOBAL设置需要SUPER权限并影响随后连接的所有客户端的操作。任何客户端都可以更改SESSION设置innodb_lock_wait_timeout，仅影响该客户端。
innodb_rollback_on_timeout
有时侯会发生事务超时的情况，MySQL会返回类似这样的错误：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.
那事务超时后会发生什么呢？此时就需要注意到innodb_rollback_on_timeout参数了。在MySQL 5.6&5.7中默认值为OFF，当InnoDB默认情况下仅回滚事务超时的最后一条语句。如果innodb_rollback_on_timeout值为ON（不支持动态开启），则事务超时后将导致InnoDB中止并回滚整个事务。
详情可见：MySQL数据库innodb_rollback_on_timeout默认值的危害？
lock_wait_timeout
此变量指定尝试获取元数据锁的超时（以秒为单位）。允许的值范围为1到31536000（1年），默认值为31536000。
此超时适用于使用元数据锁的所有语句，这些措施包括对表，视图DML和DDL操作，存储过程和存储功能，以及LOCK TABLES，FLUSH TABLES WITH READ LOCK和HANDLER语句。
此超时不适用于对MySQL数据库中的系统表的隐式访问，例如由GRANT或REVOKE语句或表记录语句修改的授权表。超时功能适用于直接访问的系统表，例如SELECT或UPDATE。
lock_wait_timeout不适用于延迟插入，总是执行超时为1年。这样做是为了避免不必要的超时，因为发出延迟插入的会话不会收到延迟插入超时的通知。
net_read_timeout
在中止读取之前等待连接中的更多数据的秒数，默认值30秒。当服务器从客户端读取时，net_read_timeout值控制何时中止。当服务器正在写入客户端时， net_write_timeout值控制何时中止。参见slave_net_timeout。
net_write_timeout
对应写超时，在连接认证完成后，server和client交互过程中写超时一真是不变的。默认值60秒。
rpl_stop_slave_timeout
在MySQL 5.7.2及更高版本中，您可以STOP SLAVE通过设置此变量来控制在超时之前等待的时间长度（以秒为单位）。这可以用于避免STOP SLAVE与其他从属SQL语句之间的死锁，并使用与从库不同的客户端连接。最大和默认值为31536000秒（1年），最小为2秒。对此变量的更改将对STOP SLAVE后续语句生效。此变量仅影响发出STOP SLAVE语句的客户端 。当达到超时时，发卡客户端停止等待从线程停止，但从线程继续尝试停止。
slave_net_timeout
默认值60秒，在中止读取之前等待来自主/从连接的更多数据的秒数。设置此变量不会立即生效。变量的状态适用于所有后续START SLAVE命令。
interactive_timeout
在关闭交互式连接之前，服务器等待活动的秒数。 交互式客户端被定义为使用CLIENT_INTERACTIVE选项进行 mysql_real_connect() 的客户端。 请参阅wait_timeout。
wait_timeout
服务器等待非交互式连接上的活动的秒数，超过的关闭连接。 在MySQL 5.1.41之前，此超时仅适用于TCP/IP连接，而不适用于通过Unix套接字文件，命名管道或共享内存进行的连接。 在线程启动时，会话wait_timeout值从全局wait_timeout值或全局interactive_timeout值初始化，具体取决于客户端的类型（由CLIENT_INTERACTIVE连接选项定义为mysql_real_connect()）。
MySQL关于wait_timeout和interactive_timeout。他们之间在某些条件下会互相继承，那究竟这两个参数会在什么情况下起作用呢？ 下面通过一些测试实例来证明总结两者的相互关系。
Q：通过socket连接 timeout 会从哪个global timeout继承；
A：由下例可见，通过socket登录，timeout 继承于global.interactive_timeout；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
$ mysql -uroot -p -S /tmp/mysql.sock mysql> set global interactive_timeout = 11111; Query OK, 0 rows affected (0.00 sec) mysql> set global wait_timeout = 22222; Query OK, 0 rows affected (0.00 sec) mysql> show global variables like '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 11111 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 | | wait_timeout | 22222 | +-----------------------------+----------+ 13 rows in set (0.00 sec) $ mysql -uroot -p -S /tmp/mysql.sock mysql> show session variables like '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 11111 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 | | wait_timeout | 11111 | +-----------------------------+----------+ 13 rows in set (0.01 sec) |
Q：通过TCP/IP client连接， timeout会从哪个global timeout继承；
A：由下例可见，通过TCP/IP client连接后的wait_timeout仍然继承于global.interactive_timeout；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
$ mysql -uroot -p -h127.0.0.1 -P3306 mysql> show session variables like '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | have_statement_timeout | YES | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 11111 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 60 | | wait_timeout | 11111 | +-----------------------------+----------+ 13 rows in set (0.00 sec) |
Q：timeout值，对于处于运行状态SQL语句是否起效（即是否等价于执行超时）？
A：由下例可见SQL正在执行状态的等待时间不计入timeout时间，即SQL运行再久也不会因为timeout的配置而中断；
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> set session wait_timeout=10; Query OK, 0 rows affected (0.00 sec) mysql> set session interactive_timeout=10; Query OK, 0 rows affected (0.00 sec) mysql> select 1,sleep(20) from mysql.user; +---+-----------+ | 1 | sleep(20) | +---+-----------+ | 1 | 0 | | 1 | 0 | +---+-----------+ 2 rows in set (40.15 sec) |
interactive_timeout和wait_timeout总结
1. 超时时间只对非活动状态的connection进行计算。
2. 超时时间只以session级别的wait_timeout为超时依据，global级别只决定session初始化时的超时默认值。
3. 交互式连接的wait_timeout继承于global的interactive_timeout。非交互式连接的wait_timeout继承于global的wait_timeout。
4. 继承关系和超时对TCP/IP和Socket连接均有效果。
<参考>
