介绍mysql中一些常用的超时时间的作用。
概述
通过如下命令可以获取到所有与timeout相关的配置，下面来看看这些配置大致都有什么作用。
#查看会话级别（session）的变量
mysql> show 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 | 60 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 10 |
+-----------------------------+----------+
#查看全局级别（global）的变量
mysql> show global variables like '%timeout%';
Tips：在设置变量时，如果想要在全局生效需要加入global前缀，如set global wait_timeout=20。
connect_timeout
定义
| 属性 | 值 |
|---|---|
| 命令行格式 | –connect-timeout=# |
| 系统变量名 | connect_timeout |
| 作用域 | Global |
| 可动态配置 | Yes |
| 类型 | Integer |
| 默认值 | 10 |
| 最小值 | 2 |
| 最小值 | 31536000 |
服务器在返回错误握手信息前等待的时间，也可以说是握手超时时间。
- mysql大致连接过程：
- 1.监听到连接请求（会进行3次握手）
- 2.服务端创建线程处理请求（客户端此时阻塞，等待服务端消息）
- 3.服务端与客户端交互验证用户名密码
- 4.验证通用，握手完成
在整个连接握手过程中可能会出现出错，这个connect_timeout值就是指这个超时时间。
测试
#1.使用telnet连接
[root@DEV-HROEx64 ~]# telnet localhost 3306
Trying ::1...
Connected to localhost.
Escape character is '^]'.
N
5.7.11-log5+
-Tn)EF>g'uQR7mysql_native_password
#2.查看连接信息
mysql> show full processlist;
+-------+----------------------+--------------------+----------------+---------+------+-----------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------------------+--------------------+----------------+---------+------+-----------------------+-----------------------+
| 23693 | unauthenticated user | ::1:52495 | NULL | Connect | 4 | Receiving from client | NULL |
+-------+----------------------+--------------------+----------------+---------+------+-----------------------+-----------------------+
#3.等待10s，服务器关闭连接
Connection closed by foreign host
interactive_timeout & wait_timeout
定义
interactive_timeout
| 属性 | 值 |
|---|---|
| 命令行格式 | –interactive-timeout=# |
| 系统变量名 | interactive_timeout |
| 作用域 | Global, Session |
| 可动态配置 | Yes |
| 类型 | Integer |
| 默认值 | 28800 |
| 最小值 | 1 |
服务器在关闭交互式连接（客户端在通过mysql_real_connect连接时，使用CLIENT_INTERACTIVE 参数）之前等待活动的秒数。
wait_timeout
| 属性 | 值 |
|---|---|
| 命令行格式 | –wait-timeout=# |
| 系统变量名 | wait_timeout |
| 作用域 | Global, Session |
| 可动态配置 | Yes |
| 类型 | Integer |
| 默认值 | 28800 |
| 最小值 | 1 |
| 最大值（other） | 31536000 |
| 最大值（windows） | 2147483 |
服务器在关闭非交互式连接（客户端在通过mysql_real_connect连接时，不使用CLIENT_INTERACTIVE 参数）之前等待活动的秒数。
以上2个变量都是定义服务器在自动关闭连接前等待的时间，区别是如果连接是交互式连接则使用interactive_timeout，如果连接是非交互式连接（如程序代码中的连接）则使用wait_timeout。
测试
交互式连接
- SecureCRT，Navivat Premium等连接工具
#1.设置交互连接超时时间10s
mysql> set global interactive_timeout=10;
#2.退出，重新进入，当前wait_timeout=10
mysql> show 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 | 60 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 10 |
+-----------------------------+----------+
#3.等待10s，再执行如上命令，就会出现超时重连的信息了
mysql> show variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 22933
Current database: *** NONE ***
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 60 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 10 |
+-----------------------------+----------+
非交互式连接
- 程序代码中连接
#1.设置非交互连接超时时间20s
mysql> set global wait_timeout=20;
#2.建立连接后持续查看进程
mysql> show full processlist;
+-------+------------+---------------------+-------------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------+---------------------+-------------+---------+------+----------+-----------------------+
| 22974 | hrodbadmin | 10.100.50.115:34504 | interview | Sleep | 1 | | NULL |
+-------+------------+---------------------+-------------+---------+------+----------+-----------------------+
mysql> show full processlist;
+-------+------------+---------------------+-------------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------+---------------------+-------------+---------+------+----------+-----------------------+
| 22974 | hrodbadmin | 10.100.50.115:34504 | interview | Sleep | 15 | | NULL |
+-------+------------+---------------------+-------------+---------+------+----------+-----------------------+
mysql> show full processlist;
+-------+------------+---------------------+-------------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------+---------------------+-------------+---------+------+----------+-----------------------+
| 22974 | hrodbadmin | 10.100.50.115:34504 | interview | Sleep | 20 | | NULL |
+-------+------------+---------------------+-------------+---------+------+----------+-----------------------+
#3.当sleep超过20s后连接被自动关闭
mysql> show full processlist;
+-------+------------+---------------------+-------------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------+---------------------+-------------+---------+------+----------+-----------------------+
innodb_lock_wait_timeout & innodb_rollback_on_timeout
定义
innodb_lock_wait_timeout
| 属性 | 值 |
|---|---|
| 命令行格式 | –innodb-lock-wait-timeout=# |
| 系统变量名 | innodb_lock_wait_timeout |
| 作用域 | Global, Session |
| 可动态配置 | Yes |
| 类型 | Integer |
| 默认值 | 50 |
| 最小值 | 1 |
| 最大值 | 1073741824 |
InnoDB事务等待行锁的超时时间，当一个事务尝试获取被另一个事务锁定的行时会出现，超时会出现如下错误：
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
当错误出现时，会回滚当前语句，而不是整个事务（除非设置了innodb_rollback_on_timeout）。
innodb_rollback_on_timeout
| 属性 | 值 | |
|---|---|---|
| 命令行格式 | –innodb-rollback-on-timeout[={OFF | ON}] |
| 系统变量名 | innodb_rollback_on_timeout | |
| 作用域 | Global | |
| 可动态配置 | No | |
| 类型 | Boolean | |
| 默认值 | OFF |
默认情况下，InnoDB只回滚事务中超时的最后一条语句。如果设置了 innodb_rollback_on_timeout=ON，则事务超时将导致InnoDB中止并回滚整个事务。
测试
准备test表，里面包含2条数据。
mysql> select * from test;
+----+-------+
| id | cname |
+----+-------+
| 1 | a |
| 2 | b |
+----+-------+
#事务1
#1.开启事务
begin;
#2.加行锁
select * from test where id=2 for update;
#事务2
#1.开启事务
begin;
#2.不请求行锁
delete from test where id=1;
#3.请求行锁
delete from test where id=2;
##请求超时，出现如下错误
##[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
#4.数据查询，id=1的数据被删除了
select * from test;
+----+-------+
| id | cname |
+----+-------+
| 2 | b |
+----+-------+
如果想要id=1的数据不被删除，需要在启动mysql时设置innodb_rollback_on_timeout=On
lock_wait_timeout
定义
| 属性 | 值 |
|---|---|
| 命令行格式 | –lock-wait-timeout=# |
| 系统变量名 | lock_wait_timeout |
| 作用域 | Global, Session |
| 可动态配置 | Yes |
| 类型 | Integer |
| 默认值 | 31536000 |
| 最小值 | 1 |
| 最大值 | 31536000 |
尝试获取元数据锁的超时时间，适用于所有使用元数据锁的语句。包括表、视图、存储过程和存储函数上的DML（select,update,delete,insert）和DDL（create,alter,drop）操作，以及锁表、用读锁刷新表和处理程序语句。
测试
#事务1
#1.开启事务
begin;
#2.执行查询，获取表的元数据锁
select * from test;
#事务2
#1.开启事务
begin;
#2.修改表结构的元数据（drop,alter）
drop table test;
##请求超时，出现如下错误
##[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
net_read_timeout & net_write_timeout
定义
net_read_timeout
| 属性 | 值 |
|---|---|
| 命令行格式 | –net-read-timeout=# |
| 系统变量名 | net_read_timeout |
| 作用域 | Global, Session |
| 可动态配置 | Yes |
| 类型 | Integer |
| 默认值 | 30 |
| 最小值 | 1 |
当服务器从客户端获取数据时（如导入数据），等待从连接获取数据的超时时间。
net_write_timeout
| 属性 | 值 |
|---|---|
| 命令行格式 | –net-write-timeout=# |
| 系统变量名 | net_write_timeout |
| 作用域 | Global, Session |
| 可动态配置 | Yes |
| 类型 | Integer |
| 默认值 | 30 |
| 最小值 | 1 |
当服务器向客户端写入数据时（如执行查询），等待将数据写入连接的超时时间。
总结
- connect_timeout：用于连接过程中的超时控制
- interactive_timeout与wait_timeout：用于连接空闲阶段的超时控制
- net_read_timeout与net_write_timeout：用于连接繁忙阶段的超时控制
参考资料
InnoDB Startup Options and System Variables
