MySQL的timeout那点事
因为最近遇到一些超时的问题，正好就把所有的timeout参数都理一遍，首先数据库里查一下看有哪些超时：
root@localhost:test12:55:50>showglobalvariableslike"%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
手册描述:
ThenumberofsecondsthatthemysqldserverwaitsforaconnectpacketbeforerespondingwithBadhandshake.Thedefaultvalueis10secondsasofMySQL5.1.23and5secondsbeforethat.
Increasingtheconnect_timeoutvaluemighthelpifclientsfrequentlyencountererrorsoftheformLostconnectiontoMySQLserverat‘XXX’,systemerror:errno.
解释：在获取链接时，等待握手的超时时间，只在登录时有效，登录成功这个参数就不管事了。主要是为了防止网络不佳时应用重连导致连接数涨太快，一般默认即可。
delayed_insert_timeout
手册描述：
HowmanysecondsanINSERTDELAYEDhandlerthreadshouldwaitforINSERTstatementsbeforeterminating.
解释：这是为MyISAMINSERTDELAY设计的超时参数，在INSERTDELAY中止前等待INSERT语句的时间。
innodb_lock_wait_timeout
手册描述：
ThetimeoutinsecondsanInnoDBtransactionmaywaitforarowlockbeforegivingup.Thedefaultvalueis50seconds.AtransactionthattriestoaccessarowthatislockedbyanotherInnoDBtransactionwillhangforatmostthismanysecondsbeforeissuingthefollowingerror:
ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
Whenalockwaittimeoutoccurs,thecurrentstatementisnotexecuted.Thecurrenttransactionisnotrolledback.(Tohavetheentiretransactionrollback,starttheserverwiththe–innodb_rollback_on_timeoutoption,availableasofMySQL5.1.15.SeealsoSection13.6.12,“InnoDBErrorHandling”.)
innodb_lock_wait_timeoutappliestoInnoDBrowlocksonly.AMySQLtablelockdoesnothappeninsideInnoDBandthistimeoutdoesnotapplytowaitsfortablelocks.
InnoDBdoesdetecttransactiondeadlocksinitsownlocktableimmediatelyandrollsbackonetransaction.Thelockwaittimeoutvaluedoesnotapplytosuchawait.
Forthebuilt-inInnoDB,thisvariablecanbesetonlyatserverstartup.ForInnoDBPlugin,itcanbesetatstartuporchangedatruntime,andhasbothglobalandsessionvalues.
解释：描述很长，简而言之，就是事务遇到锁等待时的Query超时时间。跟死锁不一样，InnoDB一旦检测到死锁立刻就会回滚代价小的那个事务，锁等待是没有死锁的情况下一个事务持有另一个事务需要的锁资源，被回滚的肯定是请求锁的那个Query。
innodb_rollback_on_timeout
手册描述：
InMySQL5.1,InnoDBrollsbackonlythelaststatementonatransactiontimeoutbydefault.If–innodb_rollback_on_timeoutisspecified,atransactiontimeoutcausesInnoDBtoabortandrollbacktheentiretransaction(thesamebehaviorasinMySQL4.1).ThisvariablewasaddedinMySQL5.1.15.
解释：这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query，打开的话事务遇到超时就回滚整个事务。
interactive_timeout/wait_timeout
手册描述：
Thenumberofsecondstheserverwaitsforactivityonaninteractiveconnectionbeforeclosingit.AninteractiveclientisdefinedasaclientthatusestheCLIENT_INTERACTIVEoptiontomysql_real_connect().Seealso
解释：一个持续SLEEP状态的线程多久被关闭。线程每次被使用都会被唤醒为acrivity状态，执行完Query后成为interactive状态，重新开始计时。wait_timeout不同在于只作用于TCP/IP和Socket链接的线程，意义是一样的。
net_read_timeout/net_write_timeout
手册描述：
Thenumberofsecondstowaitformoredatafromaconnectionbeforeabortingtheread.BeforeMySQL5.1.41,thistimeoutappliesonlytoTCP/IPconnections,nottoconnectionsmadethroughUnixsocketfiles,namedpipes,orsharedmemory.Whentheserverisreadingfromtheclient,net_read_timeoutisthetimeoutvaluecontrollingwhentoabort.Whentheserveriswritingtotheclient,net_write_timeoutisthetimeoutvaluecontrollingwhentoabort.Seealsoslave_net_timeout.
OnLinux,theNO_ALARMbuildflagaffectstimeoutbehaviorasindicatedinthedescriptionofthenet_retry_countsystemvariable.
解释：这个参数只对TCP/IP链接有效，分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间，这是在Activity状态下的线程才有效的参数
slave_net_timeout
手册描述：
Thenumberofsecondstowaitformoredatafromthemasterbeforetheslaveconsiderstheconnectionbroken,abortstheread,andtriestoreconnect.Thefirstretryoccursimmediatelyafterthetimeout.TheintervalbetweenretriesiscontrolledbytheMASTER_CONNECT_RETRYoptionfortheCHANGEMASTERTOstatementor–master-connect-retryoption,andthenumberofreconnectionattemptsislimitedbythe–master-retry-countoption.Thedefaultis3600seconds(onehour).
解释：这是Slave判断主机是否挂掉的超时设置，在设定时间内依然没有获取到Master的回应就人为Master挂掉了