MysqlInnodb死鎖情況分析與歸納

1、死鎖日志打印出的時間點表明，語句（1）運行過程中，當語句（2）開始運行時，發(fā)生了死鎖。當mysql檢測出死鎖時，除了查看mysql的日志，還可以通過showInnoDBSTATUSG語句在mysql客戶端中查看最近一次的死鎖記錄。由于打印出來的語句會很亂，所以，最好先使用pagerless命令，通過文件內(nèi)容瀏覽方式查看結(jié)果，會更清晰。（以nopager結(jié)束）得到的死鎖記錄如下：inserir:cselect喈句MysqlInnodb死鎖情況分析與歸納案例描述在定時腳本運行過程中，發(fā)現(xiàn)當備份表格的sql語句與刪除該表部分數(shù)據(jù)的sql語句同時運行時，mysql會檢測出死鎖，并打印出日志。兩個sql

2、語句如下：(1) insertintobackup_tableselect*fromsource_table(2) DELETBFROMource_tableWHEREI5ANDtitleWeight32768ANDjoinTimeTRANSACTIOS;TKA:i5ACTlONI339363495,ACThintroramtfiqlinuseLlockedLOCK咐IT103lockU/SQLthreadid796.querjidinsL-rtintottJEiinLi!er_201103-07*(1rAITIWFVFtTHISLOCKRECORDL0CK5soaccid0nazei叫Uei

3、dJ3393忸4。5I。E0secprocessno5496ThttapsizeM3201064)127+Q-0-1teanscriptselect*fromteuHiUserTOBEGRAFTED：a210436nbits312indexkw)dv$OSthrpdid116745129fifwPRIMARYoflableut忙oiltfRecordIck%heapno27PHVSICALHECORD:n_fields15;cempactforsat;infobits320:len4Thex00al3Fcl.asc?；;I:len4.hex000002cd;asc2:Ln6;hex000】14

4、3工5a2??；asc%;3：len7：henOOOOQOOQ2dH92：se-:：4二】&n9:h*x666c79ascflyawayr.3:Ion4,hex4c6caT$*,ascLI,.6:1en4,hei80000000,asc;7：len4.hexOMOOOOO.ase8；len4;hesOWOOOQOcsc.9:len4.hez00000000.ssc.：10:Ln1ihex00;asc.11;Itn2.h工0050.中,；12：SQLWLL+13;1Fh中齊QI,耳空,.14;1enI.31,* *+(2)TRANSACT05;TRAACTION33供加4

5、品.ACTIVE。*恥,prge5no澗9&051Wadid11645BK16hmhuuusize1420.undeIdatnfries34yySQLthreadidQueryidlOSfl127,0.0-!temuscriptSendingdataDELETEFKOJite&nlserM1ERE詣呷Id=7埼titleiShv務(wù)器本身，而是與存儲引擎相關(guān)，MylSAM支持事務(wù)、采用的是表級鎖，而InnoDB支持ACID事務(wù)、行級鎖、并發(fā)。MySQlB認的行為是在每條SQL語句執(zhí)行后執(zhí)行一個COMMI語句，從而有效的將每條語句作為一個單獨的事務(wù)來處理。2.2 兩語句加鎖情況在innodb默認的

6、事務(wù)隔離級別下，普通的SELEC碇不需要加行鎖的，但LOCKINSHAREMODEORUPDATE高用行化級別中的SELECT要力口鎖。有一個例外，此案例中，語句11)insertintoteamUser_20110121select*fromteamUser會對表teamUser_20110121(ENGINEMyISAM加表鎖，并對teamUser表所有行的主鍵索引(即聚簇索引)加共享鎖。默認對其使用主鍵索引。而語句(2)DELETEFROMteamUserWHEREteamId=$teamIdANDtitleWeight32768ANDjoinTimeDELETEFROMteamuser

7、*-seiecttfromteamUser?WHEREteamid=SteamfdAPJD/tttWeit327SSAhJD/joinJimeseauiocpmnniT二。*m/sqlsetsuxocommit=0;-QueryOK,Drov/iaffected(CLODseepQueryOK.0rovsaffecteo(0.00seelmf5qlselectfromtable_lwh-ere/jberem/sqlselect!fromtable_2;vhereforid=lforupjate；膿一些其他處理卡selec:from7able_2here=_upc君汨儆一些其他處理-因花達ion

8、j已取得捶他鉞，等待/m/sqbselect*fromtable_lwherewhereid=lrorLpcate?1irwiodb檢測死鍛卡解聿誨事第結(jié)束/事務(wù)操作，執(zhí)行結(jié)束條件：A、兩事務(wù)分別操作兩個表、相同表的同一行記錄B、申請的鎖互斥C、申請的順序不一致主鍵索引鎖沖突案例：本文案例，產(chǎn)生沖突在主鍵索引鎖上條件：A、兩sql語句即兩事務(wù)操作同一個表、使用不同索引B、申請的鎖互斥G操作多行記錄D查找到記錄的順序不一致主鍵索引鎖與非聚簇索引鎖沖突案例：同一行記錄，兩事務(wù)使用不同的索引進行更新操作此案例涉及TSK_TAS感，該表相關(guān)字段及索弓I如下：ID:主鍵；MON_TIME監(jiān)測時間；STA

9、TUS_ID任務(wù)狀態(tài)；索弓I:KEY_TSKTASK_MONTIME2(STATUS_ID,MON_TIME)Transaction!Transaction2updateTSK_TASKsetupdateTSK_TA5KsetSTATUS,：D=lO&4lUPDATE_TliME=novSTATUSj0=1067.UPDATEJTiJE=nov0where&TUTUSJ。，師anc()whereiDir;9921180MCN_TIMEP領(lǐng)定KEY_TSKTASK_MONTIME2的相鎖定聚簇索孫主醺一關(guān)索記系.申請鎖定簇索引（主罐索引）1申請鎖定EV_TSKTASdelete操作時，會修改表中

10、白數(shù)據(jù)信息。由于innodb存儲引擎中索引的數(shù)據(jù)存儲結(jié)構(gòu)，會根據(jù)修改語句使用的索引以及修改信息的不同執(zhí)行不同的加鎖順序。當使用索引進行查找并修改記錄時，會首先加使用的索引鎖，然后，如果修改了主鍵信息，會加主鍵索引鎖和所有非聚簇索引鎖，修改了非聚簇索引列值會加該種非聚簇索引鎖。此案例中，事務(wù)一使用非聚簇索引查找并修改主鍵值，事務(wù)二使用主鍵索引查找并修改主鍵值，加鎖順序不同，導(dǎo)致同時運行時產(chǎn)生資源循環(huán)等待。鎖升級造成鎖隊列阻塞案例：同一行記錄，事務(wù)內(nèi)進行鎖升級，與另一等待鎖發(fā)送鎖隊列阻塞，導(dǎo)致死鎖mysq5etauioconimit=mysqlsetflurtocomniit二5一QueryOK,

11、0r&ivsaffectEii/J.00secHQuery&K,0row5affected：3.&0sec)*,mysqsetect*fromtabij】whereid=llock*,instiaremode；/select*fromtable_lwhereid=lforupdate;因5E55icn二已取得共享鎖，等待口mysqbupdatetable_lsetid=2*./hereid=l；如果使用insert-select語句備份表格且數(shù)據(jù)量較大，在單獨的時間點操作，避免與其他sql語句爭奪資源，或使用selectintooutfile加上loaddatainfile代替insertse

12、lect,這樣不僅快，而且不會要求鎖定一個鎖定記錄集的事務(wù)，具操作結(jié)果集應(yīng)盡量簡短，以免一次占用太多資源，與其他事務(wù)處理的記錄沖突。更新或者刪除表格數(shù)據(jù)，sql語句的where條件都是主鍵或都是索引，避免兩種情況交叉，造成死鎖。對于where子句較復(fù)雜的情況，將其單獨通過sql得到后，再在更新語句中使用。sql語句的嵌套表格不要太多，能拆分就拆分，避免占有資源同時等因session；已在等待排他鎖隊列中口口innodb檢測死鎖亡卡解鎖，事務(wù)結(jié)束一事務(wù)操作，執(zhí)行結(jié)束4-條件：A、兩事務(wù)操作同一行記錄B、一事務(wù)對某一記錄先申請共享鎖，再升級為排他鎖C、另一事務(wù)在過程中申請這一記錄的排他鎖避免死鎖的方法InnoDB給MySQ提供了具有提交，回滾和崩潰恢復(fù)能力的事務(wù)安全（ACID兼容）存儲引擎。InnoDB鎖定在行級并且也在SELEC語句提供非鎖定讀。這些特色增