探秘mysql timeout变量
引荐（免费）：mysql视频教程
1、timeout变量知多少
翻开mysql，用show variables like '%timeout%'命令一看，不看不知道，一看吓一跳，结果如下面所示，这么多timeout相关变量，一下就吓尿了。。本来对mysql的理解本来是如此的不足，好了，这么些timeout毕竟各自是啥意思，花了一下午去学习，做了几个小实验，总算清楚了一二，如有错误，请不惜赐教啊。
mysql> show variables like '%timeout%'; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | 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 | 3600 | | wait_timeout | 28800 | +-----------------------------+----------+
2.剖析
下面从timeout里面寻些比力常用的出来逐个剖析下。
2.1 connect_timeout
connect_timeout指的是连接历程中握手的超不时间，在5.0.52今后默许为10秒，此前版本默许是5秒。官方文档是这样说的：
connect_timeout: The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds as of MySQL 5.0.52 and 5 seconds before that
mysql的根本道理应当是有个监听线程轮回接收恳求，当有恳求来时，创立线程（或者从线程池中取）来处置这个恳求。由于mysql连接采纳TCP和谈，那么此前势必是需要停止TCP三次握手的。TCP三次握手成功之后，客户端会进入堵塞，等候效劳端的新闻。效劳端这个时候会创立一个线程(或者从线程池中取一个线程)来处置恳求，主要验证部分包罗host和会员名密码验证。host验证我们比力熟知，由于在用grant命令授权会员的时候是有指定host的。会员名密码认证则是效劳端教师成一个随机数发送给客户端，客户端用该随机数和密码停止屡次sha1加密后发送给效劳端验证。假如通过，整个连接握手历程完成。（详细握手历程后续寻到材料再剖析）
因而可知，整个连接握手大概会有各种大概出错。所以这个connect_timeout值就是指这个超不时间了。可以简便测试下，运转下面的telnet命令会发明客户端会在10秒后超时返回。
telnet localhost 3306
在超时此前mysql中该连接状态如下：
256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL
2.2 interactive_timeout & wait_timeout
还是先看官方文档，从文档上来看wait_timeout和interactive_timeout都是指不活泼的连接超不时间，连接线程启动的时候wait_timeout会按照是交互模式还是非交互模式被设定为这两个值中的一个。假如我们运转mysql -uroot -p命令登陆到mysql，wait_timeout就会被设定为interactive_timeout的值。假如我们在wait_timeout时间内没有停止任何操纵，那么再次操纵的时候就会提醒超时，这是mysql client会从新连接。
The number of seconds the server waits for activity on a noninteractive connection before closing it. On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()).
测试如下：
mysql> set global interactive_timeout=3; ##设定交互超时为3秒
从新进入mysql，这时候可以看到：
mysql> show variables like '%timeout%'; ##wait_timeout已经被设定为3秒 +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 3 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 3 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 3600 | | wait_timeout | 3 | +-----------------------------+----------+
可以看到wait_timeout被设定为了interactive_timeout的值，这样，我们3秒后再施行其他命令，会提醒如下：
mysql> show variables like '%timeout%'; ERROR 2006 (HY000): MySQL server has gone away ##超时重连 No connection. Trying to reconnect... Connection id: 50 Current database: *** NONE *** +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 3 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 3 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 3600 | | wait_timeout | 3 | +-----------------------------+----------+
2.3 innodb_lock_wait_timeout & innodb_rollback_on_timeout
还是先祭出官方文档，从文档中看，这个值是针对innodb引擎的，是innodb中行锁的等候超不时间，默许为50秒。假如超时，则当前语句会回滚。假如设定了innodb_rollback_on_timeout，则会回滚整个事务，不然，只回滚事务等候行锁的这个语句。
The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
一样来测试下(先创立一个innodb引擎的表test，只要一列，列名为a)：
mysql> CREATE TABLE `test` ( `a` int primary key) engine=innodb;
第一插入三条测试数据
mysql> select * from test; +---+ | a | +---+ | 1 | | 2 | | 3 |
当前innodb_rollback_on_timeout=OFF，设定innodb_lock_wait_timeout=1，我们开启两个事务
##事务1 加行锁 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test where a=2 for update; +---+ | a | +---+ | 2 | +---+ 1 row in set (0.01 sec)
##事务2，恳求行锁 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from test where a=1; Query OK, 1 row affected (0.00 sec) mysql> delete from test where a=2; ##恳求行锁超时 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from test; +---+ | a | +---+ | 2 | | 3 | +---+ 2 rows in set (0.00 sec) mysql> begin; ##这里我们直接开启别的的事务(或者直接commit当前事务)，则本来的事务只会回滚第二条语句，终究结果就是test表中只剩下2和3.假如这里我们显示的rollback，则会回滚整个事务，保持1，2，3不变。
那么假如innodb_rollback_on_timeout=ON,一样事务2会超时，但是这个时候假如我们begin开启新的事务，那么会回滚恳求锁超时的整个事务，而不是像前面那样只回滚了超时的那条语句。
2.4 lock_wait_timeout
文档中描写如下，简便说来lock_wait_timeout是元数据锁等候超时，任意锁元数据的语句都会用到这个超时参数，默许为一年。元数据锁可以参加mysql metadata lock，为了包管事务可串行化，不管是myisam还是innodb引擎的表，只如果先在一个session里面开启一个事务，就会猎取操纵表的元数据锁，这时候假如另一个session要对表的元数据停止修改，则会堵塞直到超时。
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000. This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements
测试例子：
我们用一个myisam引擎的表myisam_test来测试。其中有一笔记录(1,1)，此刻我们先开启一个session，然后施行一个select语句。别的翻开一个session，然后施行表的元数据操纵，如删除表，会发明操纵堵塞直到lock_wait_timeout秒后提醒超时。
##第一个session，猎取metadata lock mysql> show create table myisam_test; -----------------------------------------------------------+ | Table | Create Table | +----------------------------------------------------------- | myisam_test | CREATE TABLE `myisam_test` ( `i` int(11) NOT NULL, `j` int(11) DEFAULT NULL, PRIMARY KEY (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from myisam_test; +---+------+ | i | j | +---+------+ | 2 | 1 | +---+------+ 1 row in set (0.00 sec) ##另一个session，删除表提醒超时 mysql> drop table myisam_test; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
其中更换表构造的元数据操纵指令有如下这些：
DROP TABLE t; ALTER TABLE t ...; DROP TABLE nt; ALTER TABLE nt ...; LOCK TABLE t ... WRITE；
当然，多说一句，关于myisam表的加锁乃至并发插入等，这篇博客myisam表锁非常具体，有乐趣的可以看看。
2.5 net_read_timeout & net_write_timeout
文档中描写如下，就是说这两个参数在网络前提不好的状况下起作用。比方我在客户端用load data infile的方式导入很大的一个文件到数据库中，然后半途用iptables禁用掉mysql的3306端口，这个时候效劳器端该连接状态是reading from net，在等候net_read_timeout后关闭该连接。同理，在程序里面查询一个很大的表时，在查询历程中一样禁用掉端口，制造网络不通的状况，这样该连接状态是writing to net，然后在net_write_timeout后关闭该连接。slave_net_timeout相似。
The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort
测试：
我创立一个120M的数据文件data.txt。然后登陆到mysql。
mysql -uroot -h 127.0.0.1 -P 3306 --local-infile=1
导入历程设定iptables禁用3306端口。
iptables -A INPUT -p tcp --dport 3306 -j DROP iptables -A OUTPUT -p tcp --sport 3306 -j DROP
可以看到连接状态为reading from net，然后经过net_read_timeout秒后关闭。
3.总结
经过几个实验可以发明，connect_timeout在握手认证阶段（authenticate）起作用，interactive_timeout 和wait_timeout在连接余暇阶段（sleep）起作用，而net_read_timeout和net_write_timeout则是在连接忙碌阶段（query）或者网络显现问题时起作用。
以上就是探秘mysql timeout变量的具体内容，更多请关注百分百源码网其它相关文章！
