因為最近遇到一些超時的問題，正好就把所有的timeout引數都理一遍，首先資料庫裡查一下看有哪些超時：
| root@localhost : test 12:55:50> show global variables like "%timeout%"; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 120 | | innodb_rollback_on_timeout | ON | | interactive_timeout | 172800 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | table_lock_wait_timeout | 50 | # 這個引數已經沒用了 | wait_timeout | 172800 | +----------------------------+--------+ |
我們一個個來看
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.
解釋：在獲取連結時，等待握手的超時時間，只在登入時有效，登入成功這個引數就不管事了。主要是為了防止網路不佳時應用重連導致連線數漲太快，一般預設即可。
手冊描述：
How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating.
解釋：這是為MyISAM INSERT DELAY設計的超時引數，在INSERT DELAY中止前等待INSERT語句的時間。
手冊描述：
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:
| 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。
手冊描述：
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，開啟的話事務遇到超時就回滾整個事務。
手冊描述：
The number of seconds the server waits for activity on an interactive
connection before closing it. An interactive client is defined as a
client that uses the CLIENT_INTERACTIVE option to mysql_real_connect().
See also
解釋：一個持續SLEEP狀態的執行緒多久被關閉。執行緒每次被使用都會被喚醒為acrivity狀態，執行完Query後成為interactive狀態，重新開始計時。wait_timeout不同在於只作用於TCP/IP和Socket連結的執行緒，意義是一樣的。
手冊描述：
The number of seconds to wait for more data from a connection before
aborting the read. Before MySQL 5.1.41, this timeout applies only to
TCP/IP connections, not to
connections made through Unix socket files, named pipes, or shared
memory. 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. See also slave_net_timeout.
On Linux, the NO_ALARM build flag affects timeout behavior as indicated
in the description of the net_retry_count system variable.
解釋：這個引數只對TCP/IP連結有效，分別是資料庫等待接收客戶端傳送網路包和傳送網路包給客戶端的超時時間，這是在Activity狀態下的執行緒才有效的引數
手冊描述：
The number of seconds to wait for more data from the master before the
slave considers the connection broken, aborts the read, and tries to
reconnect. The first retry occurs immediately after the timeout. The
interval between retries is controlled by the MASTER_CONNECT_RETRY
option for the CHANGE MASTER TO statement or –master-connect-retry
option, and the number of reconnection attempts is limited by the
–master-retry-count option. The default is 3600 seconds (one hour).
解釋：這是Slave判斷主機是否掛掉的超時設定，在設定時間內依然沒有獲取到Master的回應就人為Master掛掉了