Mysql死锁场景总结 （引擎：innodb隔离级别：RR）

概述

死锁：死锁一般是事务相互等待对方资源，最后形成环路造成的。

 

此种场景常见于Springmvc模式中，把事务交由spring管理的场景。这种模式下，由于业务的比较复杂，会导致一个事务内会有多次和数据库进行通信的机会，导致事务一直没提交，产生大事务。

下面具体分析几类在工作中遇到过的死锁场景，主要介绍单表场景，死锁在多表场景中也有，可以按单表的思路进行分析。

死锁场景

一、update的记录顺序不一致

此种情况常见于一个update语句被许多地方共用的场景。

事务一：

Update table set status= #{value} where id in (1,2,3,7,8);

事务二：

Update table set status= #{value} where id in (9,10,3,5);

或

事务一：

Update table set status= #{value} where id in (1,2,6,7,8);

事务二：

Update table set status= #{value} where id = 5;

Where：后面我这是为了方便列举了id（此处的id并非是主键）的情况，实际业务中还有其他的场景，需要具体分析。

这个写法并不是说一定会发生死锁，是有发生死锁的概率。

二、同一个表中，唯一索引update

Table:business

id  bigint(20) 主键，自增

business_no bigint(20) 不为空，唯一索引（uniq_idx_business_no）

name varchar(50) 不为空

事务中的sql语句如下：

Insert into business(name) values(#{name});

Update business set business_no = #{id} where id = #{id};

  
第一组事务： 

每一个事务按正常的顺序执行完成后，commit是没问题的。（并发量小或没有的时候） 

第二组事务：

	事务一	事务二
SQL	Insert into business(name) values(#{name});	Insert into business(name) values(#{name});
已有锁		RECORD LOCKS space id 27 page no 26 n bits 824 index ` uniq_idx_business_no ` of table `business`  trx id 19268290 lock mode S locks gap before rec Record lock
等待锁	RECORD LOCKS space id 27 page no 26 n bits 824 index ` uniq_idx_business_no ` of table `business` trx id  19268220 lock_mode X locks gap before rec insert intention waiting
Record lock		RECORD LOCKS space id 27 page no 26 n bits 824 index `uniq_idx_business_no` of table ` business`  trx id 19268290 lock_mode X locks gap before rec insert intention waiting

  

事务一：已经持有间隙锁（gap+X），再等待插入意向锁（insert intention）；

事务二：持有行锁（S），在申请获得间隙锁（gap+X）的时候，需要先申请意向锁（insert intention）；

死锁发生后，根据事务的权重，有数据库决定回滚哪一个事务来解决死锁问题。

三、普通索引

普通索引这类和顺序不一致的场景有相似处。

Table：match_info

Id  bigint(20)  自增

no bigint(20) Idx_no

code varchar(50)

……    ……

事务死锁场景

	事务一	事务二
Sql	UPDATE match_info set type = 1 WHERE no = 1644100 AND code = 'CD275019634'	UPDATE match_info set type = 1 WHERE no = 1644100 AND code =  'CD275046788'
等待授权锁		RECORD LOCKS space id 48 page no 18862 n bits 824 index `idx _no` of table `match_info`  trx id 188167021 lock_mode X waiting Record lock;
已持有 锁	RECORD LOCKS space id 48 page no 20127 n bits 104 index `PRIMARY` of table ` match_info`  trx id 188167023 lock_mode X locks rec but not gap waiting Record lock;

 

猜想：index `PRIMARY` of table ` match_info`

RECORD LOCKS space id 48 page no 18862 n bits 824 index `idx _no` of table ` match_info` trx id 188167023 lock_mode X Record lock;

对事务一持有的锁的猜想对么？（最后给出答案）

对应的数据记录：

id	no	code
100011223	1644100	CD275019345
100011224	1644100	CD275019634
100011225	1644100	CD275046322
100011226	1644100	CD275046788
100011227	1644100	CD275077643

 

注意，普通索引和主键索引的关系：

         innodb对于主键使用了聚簇索引，这是一种数据存储方式，表数据是和主键一起存储，主键索引的叶结点存储行数据。对于普通索引，其叶子节点存储的是主键值。 

总结：

Update的时候，尽量按主键，一次更新一条；如果确实需要批量，则保证好每次更新的顺序是一致的。尽量保证小事务，使得事务尽快commit。 

问题解答： 
  在普通索引里，对事务一已有的锁的猜想可能是对的，也可能是错的（原因：可能还有其他的事务持有该锁，而并不一定是事务一持有），也或许事务一目前还没有持有任何锁。 

原文引用：http://qhxn-328-liaozx.blog.163.com/blog/static/8760673201762043235983

时间：2017-07-20 16:39:56