mysql死锁
#记录工作中碰到的一次mysql死锁
##声明
为了不暴漏公司信息（^^）,表名、字段、类、包已经被xx替换掉,请忽略这些小信息,抱歉!
##死锁产生的异常信息
14493452:org.springframework.dao.DeadlockLoserDataAccessException:
14493453:### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
14493454-### The error may involve xx.xx.XXMapper.insert-Inline
14493455-### The error occurred while setting parameters
14493456-### SQL:
/** insert into xxx (xx_id, xx_money ) values (?, ? )**/
14493457:### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadloc found when trying to get lock; try restarting transaction
14493458:; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
14493459- at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263) ~[spring-jdbc-4.1.0.RELEASE.jar:4.1.0.RELEASE]
14493460- at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.1.0.RELEASE.jar:4.1.0.RELEASE]
14493461- at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-1.2.2.jar:1.2.2]
14493462- at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371) ~[mybatis-spring-1.2.2.jar:1.2.2]
14493463- at com.sun.proxy.$Proxy18.insert(Unknown Source) ~[na:na]
14493464- at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:240) ~[mybatis-spring-1.2.2.jar:1.2.2]
14493465- at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51) ~[mybatis-3.2.7.jar:3.2.7]
14493466- at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52) ~[mybatis-3.2.7.jar:3.2.7]
14493467- at com.sun.proxy.$Proxy47.insert(Unknown Source) ~[na:na]
14493468- at xx.xx.xx.execute(XX.java:110) [classes/:na]
--
14493472- at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717) [spring-aop-4.1.0.RELEASE.jar:4.1.0.RELEASE]
14493473- at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.1.0.RELEASE.jar:4.1.0.RELEASE]
14493474- at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) [spring-tx-4.1.0.RELEASE.jar:4.1.0.RELEASE]
14493475- at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:266) [spring-tx-4.1.0.RELEASE.jar:4.1.0.RELEASE]
14493476- at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) [spring-tx-4.1.0.RELEASE.jar:4.1.0.RELEASE]
14493477- at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.1.0.RELEASE.jar:4.1.0.RELEASE]
14493478- at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653) [spring-aop-4.1.0.RELEASE.jar:4.1.0.RELEASE]
14493479- at xx.xx.xx.XXX$$EnhancerBySpringCGLIB$$d33cdec5.execute(<generated>) [spring-core-4.1.0.RELEASE.jar:na]
14493480- at com.taobao.pamirs.schedule.taskmanager.TBScheduleProcessorSleep.run(TBScheduleProcessorSleep.java:237) [tbschedule-3.2.18.jar:na]
14493481- at java.lang.Thread.run(Thread.java:745) [na:1.7.0_75]
14493482:Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadloc found when trying to get lock; try restarting transaction
14493483- at sun.reflect.GeneratedConstructorAccessor56.newInstance(Unknown Source) ~[na:na]
14493484- at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_75]
14493485- at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_75]
14493486- at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) ~[mysql-connector-java-5.1.6.jar:na]
14493487- at com.mysql.jdbc.Util.getInstance(Util.java:381) ~[mysql-connector-java-5.1.6.jar:na]
14493488- at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1045) ~[mysql-connector-java-5.1.6.jar:na]
14493489- at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) ~[mysql-connector-java-5.1.6.jar:na]
14493490- at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491) ~[mysql-connector-java-5.1.6.jar:na]
14493491- at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) ~[mysql-connector-java-5.1.6.jar:na]
14493492- at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) ~[mysql-connector-java-5.1.6.jar:na]
从上面异常里可以看到关键字Deadlock以及出现异常的sql语句: insert into xxx (xx_id, xx_money) values (?, ? ) 此sql在错误信息里已经被我注释掉，便于查看.
##分析
通过上面的信息可以获取到数据库发生了死锁 同时可以知道死锁 发生在insert into 这条语句上，好吧，来看看程序吧
####发生死锁的程序
// 1.1 查询账户信息
AccountPo account = accountMapper.selectByUserId(task.getUserId());
if (null == account) {
account = new AccountPo();
account.setXXId(task.getUserId());
account.setXX_money(task.getXX_money());
// 新增账户
accountMapper.insert(account);
} else {
// 更新账户金额
account.setAmount(account.getXX_money() + task.getXX_money());
accountMapper.updateByPrimaryKey(account);
}
通过查看上面的程序可以得到，业务要做如下操作： +根据用户ID查询是否已经插入了账户信息（账户金额） +如果没有则执行insert 操作 +如果已经存在则执行update操作 这样看代码好像没上面问题，问题是在如上的代码块里发生死锁，初步怀疑是有多线程操作 这样的代码块，且同时执行insert 由于我们这个job使用的是阿里的TBSchedule来作业的，同事配置了两个线程组，每个线程组里有 5个线程在执行,这个消息让我很振奋，但是就算是多线程操作insert也不会出现死锁啊，怎么办呢 ？来看看表结构吧
CREATE TABLE `xx` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`xx_id` varchar(32) NOT NULL DEFAULT '' COMMENT '用户Id',
`xx_money` bigint(20) NOT NULL DEFAULT '0' COMMENT '总额(分)',
PRIMARY KEY (`id`),
UNIQUE KEY `index_xx_account_xx_id` (`xx_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户总额表'
咦！！！xx_id(用户id)为唯一索引哦！这个发现令我更是振奋，赶紧来模拟一下insert 看看 Deadlock 怎么发生的。 模拟如下
| session1 | session2 | session3 |
|---|---|---|
| begin; | - | - |
| insert into xx(xx_id,xx_money) values(1,2); | - | - |
| - | begin; | - |
| - | insert into xx(xx_id,xx_money) values(1,2); | - |
| - | - | begin; |
| - | - | insert into xx(xx_id,xx_money) values(1,2); |
| rollback; | query ok | deadlock occur |
我比较幸运，一下就看到了Deadlock 发生，看来死锁就是发生在这个insert 语句上并且
insert 操作的肯定有一个唯一索引或者主键之类的列，如果是非唯一索引或者非主键的列，请
你自己去模拟吧，我这里不贴怎么做了，那样是没有死锁发生的。
既然死锁发生在insert 的唯一索引列上，那肯定是 mysql数据库在做这样的列操作的时候
至少有两把锁需要获取，那我们来翻翻mysql的文档看看insert到底需要哪些锁吧。我们使用mysql
版本为5.6+所以查看5.6的文档.
先来看看怎么找到Deadlock信息
在mysql开发者文档里14.5.3章节找到下面一段话 INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock(that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.If a duplicate-key error occurs,a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. 这段话的大体意思： INSERT 操作需要设置排他锁，这个锁是一个索引上的Record Lock(如果不了解Record Lock 参考14.5.1 InnoDB Locking 介绍),不是next-key lock 也不是gap lock，在他插入之前， 不会阻止其他会话进行insert gap（间隙，参考文档）操作。如果发生key重复异常， 在索引列上会加上S lock，如果多个事务去插入相同的行，并且其中一个事务已经获取到了 排他锁，这时候使用S Lock 会引起 deadlock，这个死锁可能因为其中的一个事务删除行 而发生 在回到我们的程序发现，我们代码被包裹在一个长事务中，并且在代码片段执行成功后， 后面会有段代码报错导致事务回滚，这样就会删除行啊，于是其他线程的insert 就会 发生死锁，好吧，到这问题大概就清楚了，我来总结一下：
当我们有多个线程操作同一行记录并且操作中带有unique key 或者primary key，
如果事务很长，而正巧这时有一个事务回滚了（回滚会发生delete row操作），其他的
线程事务中的insert 就会发生deadlock了，好了, 来看看为什么会发生死锁吧：
insert 操作唯一索引列，会获取排他锁（X Lock）和S Lock(不了解去参考14.5.1)
看看他们之间的关系吧：
-
X IX S IS X Conflict Conflict Conflict Conflict IX Conflict Compatible Conflict Compatible S Conflict Conflict Compatible Compatible IS Conflict Compatible Compatible Compatible
分析一下上面产生死锁的原因：
session1 执行insert操作，获得记录行上的XLock 和S Lock,这时候session2 和session3
也执行insert操作，因为S 和 X 互斥，所以session2和session3 都 被阻塞并且都获取不到
S Lock 和X Lock，于是他们向锁队列申请授权S 和 X，锁队列为：
session2(S)-->session3(S)--->session2(X)--->session3(X)
当session1 回滚后，释放了S Lock 和X Lock ，并且删除了行，这时候，session2 和session3
同时获取到了S Lock （因为S Lock 和S Lock 是兼容的，故可以同时拥有行上的S Lock），
这时，session2 去锁队列获取X Lock，发现S Lock 被session3 持有，session2 就等待，
session3 去获取X Lock ，发现session2 持有S Lock ，他也获取不到X Lock ，于是传说中
的死锁发生了。
mysql 在处理死锁时，会舍弃掉一个操作，这里舍弃掉session3 的操作，进行回滚，然后session2
就可以commit 了。（具体舍弃到哪些，可以参考14.5.2.2 章节介绍deadlock rollback）
找到死锁的原因了，就开始改造我们的代码吧，我们这里，将线程数调成2个，把容易出现
多线程操作的分组策略改掉使用用户ID分组（TBSchedule配置），让相同的用户ID进入同一个
线程组，对于出现死锁的数据，做log记录，重试3次后，由人工处理。下一步需要对代码进行
大的重构，让代码尽量避免死锁
写在后面，上面模拟的都是插入操作，如果第一个事务时删除操作也会出现死锁，主要是
对同一行操作，当这一行不存在时，后面阻塞的事务都会立即获取到SLock，那后面的
XLock 谁都获取不到了，就出现了死锁。