Scenarios that could cause deadlocks in MySQL
1Overview
InnoDB自动检测死锁。如果死锁发生，那么InnoDB会回滚权重相对小的事务。实际上，InnoDB中存在以下两种类型的死锁：
真正的事务间循环等待。
在进行死锁检测的过程中，如果InnoDB认为检测的代价过大（例如需要递归检查超过200个事务等），那么InnoDB放弃死锁检测，并认为死锁发生。
本文中使用的MySQL版本:5.1.42，InnoDBplugin版本:1.0.6。
2Scenarios
如果死锁发生，除了应用程序的日志之外，最有价值的信息恐怕就是showinnodbstatus的输出了，然而showinnodbstatus的输出中死锁相关的信息并不完整（例如只记录导致死锁的最后两个事务，以及最后执行的两个SQL等）。基于在日常工作中的经验，笔者总结了以下一定/可能会导致死锁的场景。
2.1Scenario1
CREATETABLEtest(idINTPRIMARYKEY,nameVARCHAR(10))ENGINE=InnoDB;
INSERTINTOtestVALUES(1,'1'),(2,'2');
SET@@tx_isolation='READ-COMMITTED';
SessionA
SessionB
STARTTRANSACTION;
STARTTRANSACTION;
UPDATEtestSETname='11'WHEREid=1;
UPDATEtestSETname='22'WHEREid=2;
UPDATEtestSETname=‘21'WHEREid=2;
#BLOCKED
UPDATEtestSETname=‘12'WHEREid=1;
#DEADLOCK
点评：这是最常见的死锁场景之一，解决方法就是resourceordering，即确保所有关联事务均以相同的顺序持有锁。
2.2Scenario2
CREATETABLEt(idINTPRIMARYKEY,countINT)ENGINE=InnoDB;
INSERTINTOtVALUES(1,1);
SET@@tx_isolation='READ-COMMITTED';
SessionA
SessionB
STARTTRANSACTION;
STARTTRANSACTION;
SELECT*FROMtWHEREi=1LOCKINSHAREMODE;
SELECT*FROMtWHEREid=1LOCKINSHAREMODE;
UPDATEtSETcount=2WHEREid=1;
#BLOCKED
UPDATEtSETcount=3WHEREid=1;
#DEADLOCK
点评：在这种场景下，resourceordering也无济于事，SELECT...LOCKINSHAREMODE调整为SELECT...FORUPDATE即可。
2.3Scenario3
CREATETABLEparent(idintPRIMARYKEY,countINT)ENGINE=InnoDB;
CREATETABLEchild(idintPRIMARYKEY,parent_idINT,FOREIGNKEY(parent_id)REFERENCESparent(id))ENGINE=InnoDB;
INSERTINTOparentVALUES(1,0);
SET@@tx_isolation='READ-COMMITTED';
SessionA
SessionB
STARTTRANSACTION;
STARTTRANSACTION;
INSERTINTOchildVALUES(1,1);
INSERTINTOchildVALUES(2,1);
UPDATEparentSETcount=count+1WHEREid=1;
#BLOCKED
UPDATEparentSETcount=count+1WHEREid=1;
#DEADLOCK
点评：在进行外键完整性检查时，InnoDB会在被检查的记录上设置一把共享读锁。本例中，在对child进行插入时，parent表中id为1的记录也被设置了共享读锁。
需要注意的是，OpenSSO在登录时进行了类似的数据库操作，因此也存在潜在的死锁可能性。
2.4Scenario4
CREATETABLEparent(idintPRIMARYKEY,countINT)ENGINE=InnoDB;
CREATETABLEchild(idintPRIMARYKEY,parent_idINT)ENGINE=InnoDB;
INSERTINTOparentVALUES(1,0);
SET@@tx_isolation='READ-COMMITTED';
SessionA
SessionB
...
SessionN
STARTTRANSACTION;
STARTTRANSACTION;
STARTTRANSACTION;
INSERTINTOchildVALUES(1,1);
INSERTINTOchildVALUES(2,1);
INSERTINTOchildVALUES(n,1);
UPDATEparentSETcount=count+1WHEREid=1;
UPDATEparentSETcount=count+1WHEREid=1;
UPDATEparentSETcount=count+1WHEREid=1;
Deadlockmayoccurinsomesessions.
点评：以上场景中，如果N>200，并且这些事务并发执行，那么可能会导致死锁，并且一部分事务被会滚。这是第二种类型死锁的典型场景。在showinnodbstatus的输出中会包含如下内容：“TOODEEPORLONGSEARCHINTHELOCKTABLEWAITS-FORGRAPH”。
需要注意的是，在UPDATEparentSETcount=count+1WHEREid=1;语句之前进行过何种操作并不重要，关键是这些事务都并发更新同一条记录，最终导致InnoDB放弃了死锁检测。
2.5Scenario5
CREATETABLEtest(idvarchar(10)primarykey,countint)ENGINE=InnoDB;
INSERTINTOtestvalues('ID00000001',0),('ID00000002',0),('ID00000003',0);
SET@@tx_isolation='READ-COMMITTED';
SessionA
SessionB
STARTTRANSACTION;
STARTTRANSACTION;
updatetestinnerjoin(select*,sleep(15)fromtestwhereid<='ID00000002')tontest.id=t.idsettest.count=1;
#SLEEPING
updatetestsetcount=3whereid='ID00000001';
#BLOCKED
ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction#15×2（2recordsselected）secondslater
点评：由于InnoDB采用了MVCC，因此在通常情况下（非SERIALIZABLE事务隔离级别），普通的SELECT语句不会对查询结果集中的记录加锁，也不会被已有的锁阻塞住。但是，InnoDB会在update语句的select子句的查询结果集的每条记录上设置一把共享读锁。这是本例中导致死锁的原因。
需要注意的是，本例中select子句中的sleep函数调用只是为了更容易地重现死锁，并没有其它特殊作用。针对这种类型的死锁，最好还是调整业务逻辑，正如本例中SessionA的update语句试图有条件的更新test表的部分记录，应该调整该update语句以避免死锁。
2.6Scenario6
CREATETABLEt1(idINTPRIMARYKEY,nameVARCHAR(10))ENGINE=InnoDB;
SET@@tx_isolation='SERIALIZABLE';
SessionA
SessionB
STARTTRANSACTION;
STARTTRANSACTION;
select*fromt1whereid=1;
select*fromt1whereid=1;
insertintotestvalues(1,'a');
#BLOCKED
insertintotestvalues(1,'a');
#DEADLOCK
点评：在SERIALIZABLE事务隔离级别下，如果autocommit被禁用，那么InnoDB会隐式地将普通的SELECT语句转换为SELECT...LOCKINSHAREMODE，即在查询结果集的每条记录上设置共享读锁。
需要注意的是，如果完全采用默认配置，那么SpringBatch2.0.0会在SERIALIZABLE事务隔离级别下进行类似的数据库操作，最终可能导致死锁。如果使用MySQL存储SpringBatch相关的数据库表，那么需要调整SpringBatch的配置，将事务隔离级别从默认的SERIALIZABLE调整为REPEATABLEREAD。
2.7Scenario7
CREATETABLEt1(iINT,PRIMARYKEY(i))ENGINE=InnoDB;
SET@@tx_isolation='READ-COMMITTED';
SessionA
SessionB
SessionC
STARTTRANSACTION;
STARTTRANSACTION;
STARTTRANSACTION;
INSERTINTOt1VALUES(1);
INSERTINTOt1VALUES(1);
#BLOCKED
INSERTINTOt1VALUES(1);
#BLOCKED
ROLLBACK;
DeadlockoccursineitherSessionBorSessionC
点评：这种类型的死锁不常见，如果发生duplicate-keyerror，那么InnoDB会在重复的索引记录上设置一把共享读锁，最终导致了本例中的死锁。