MysqlInnodb死锁情况分析与归纳

1、死锁日志打印出的时间点表明，语句（1）运行过程中，当语句（2）开始运行时，发生了死锁。当mysql检测出死锁时，除了查看mysql的日志，还可以通过showInnoDBSTATUSG语句在mysql客户端中查看最近一次的死锁记录。由于打印出来的语句会很乱，所以，最好先使用pagerless命令，通过文件内容浏览方式查看结果，会更清晰。（以nopager结束）得到的死锁记录如下：inserir:cselect喈句MysqlInnodb死锁情况分析与归纳案例描述在定时脚本运行过程中，发现当备份表格的sql语句与删除该表部分数据的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务器本身，而是与存储引擎相关，MylSAM支持事务、采用的是表级锁，而InnoDB支持ACID事务、行级锁、并发。MySQlB认的行为是在每条SQL语句执行后执行一个COMMI语句，从而有效的将每条语句作为一个单独的事务来处理。2.2 两语句加锁情况在innodb默认的

6、事务隔离级别下，普通的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检测死锻卡解聿诲事第结束/事务操作，执行结束条件：A、两事务分别操作两个表、相同表的同一行记录B、申请的锁互斥C、申请的顺序不一致主键索引锁冲突案例：本文案例，产生冲突在主键索引锁上条件：A、两sql语句即两事务操作同一个表、使用不同索引B、申请的锁互斥G操作多行记录D查找到记录的顺序不一致主键索引锁与非聚簇索引锁冲突案例：同一行记录，两事务使用不同的索引进行更新操作此案例涉及TSK_TAS感，该表相关字段及索弓I如下：ID:主键；MON_TIME监测时间；STA

9、TUS_ID任务状态；索弓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领定KEY_TSKTASK_MONTIME2的相锁定聚簇索孙主醺一关索记系.申请锁定簇索引（主罐索引）1申请锁定EV_TSKTASdelete操作时，会修改表中

10、白数据信息。由于innodb存储引擎中索引的数据存储结构，会根据修改语句使用的索引以及修改信息的不同执行不同的加锁顺序。当使用索引进行查找并修改记录时，会首先加使用的索引锁，然后，如果修改了主键信息，会加主键索引锁和所有非聚簇索引锁，修改了非聚簇索引列值会加该种非聚簇索引锁。此案例中，事务一使用非聚簇索引查找并修改主键值，事务二使用主键索引查找并修改主键值，加锁顺序不同，导致同时运行时产生资源循环等待。锁升级造成锁队列阻塞案例：同一行记录，事务内进行锁升级，与另一等待锁发送锁队列阻塞，导致死锁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语句备份表格且数据量较大，在单独的时间点操作，避免与其他sql语句争夺资源，或使用selectintooutfile加上loaddatainfile代替insertse

12、lect,这样不仅快，而且不会要求锁定一个锁定记录集的事务，具操作结果集应尽量简短，以免一次占用太多资源，与其他事务处理的记录冲突。更新或者删除表格数据，sql语句的where条件都是主键或都是索引，避免两种情况交叉，造成死锁。对于where子句较复杂的情况，将其单独通过sql得到后，再在更新语句中使用。sql语句的嵌套表格不要太多，能拆分就拆分，避免占有资源同时等因session；已在等待排他锁队列中口口innodb检测死锁亡卡解锁，事务结束一事务操作，执行结束4-条件：A、两事务操作同一行记录B、一事务对某一记录先申请共享锁，再升级为排他锁C、另一事务在过程中申请这一记录的排他锁避免死锁的方法InnoDB给MySQ提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELEC语句提供非锁定读。这些特色增