?

mysql> show global variables like "%timeout%";

+-----------------------------+----------+

| Variable_name ? ? ? ? ? ? ? | Value ? ?|

+-----------------------------+----------+

| connect_timeout ? ? ? ? ? ? | 10 ? ? ? |

| delayed_insert_timeout ? ? ?| 300 ? ? ?|

| innodb_flush_log_at_timeout | 1 ? ? ? ?|

| innodb_lock_wait_timeout ? ?| 120 ? ? ?|

| 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 ? ?|

+-----------------------------+----------+

12 rows in set (0.00 sec)

?

我们来分析下各个参数的意义：

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.1.23 and 5 seconds before that.

Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at ‘XXX’, system error: errno.

?

解释：在获取链接时，等待握手的超时时间，只在登录时有效，登录成功这个参数就不管事了。主要是为了防止网络不佳时应用重连导致连接数涨太快，一般默认即可。

?

delayed_insert_timeout

?

手册描述：

How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating.

解释：这是为MyISAM INSERT DELAY设计的超时参数，在INSERT DELAY中止前等待INSERT语句的时间。

?

innodb_lock_wait_timeout

?

手册描述：

The timeout in seconds an InnoDB transaction may wait 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 will hang for at most this many seconds before issuing the following error:

?

1

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the –innodb_rollback_on_timeout option, available as of MySQL 5.1.15. See also Section 13.6.12, “InnoDB Error Handling”.)

innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.

InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.

For the built-in InnoDB, this variable can be set only at server startup. For InnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values.

?

解释：描述很长，简而言之，就是事务遇到锁等待时的Query超时时间。跟死锁不一样，InnoDB一旦检测到死锁立刻就会回滚代价小的那个事务，锁等待是没有死锁的情况下一个事务持有另一个事务需要的锁资源，被回滚的肯定是请求锁的那个Query。

innodb_rollback_on_timeout

?

手册描述：

In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.

?

解释：这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query，打开的话事务遇到超时就回滚整个事务。

?

interactive_timeout/wait_timeout

手册描述：