InnoDB常见死锁分析

        MySQL InnoDB死锁问题在我们应用程序设计时，经常困扰着我们，一旦发生死锁就会造成事务回滚；对于百度钱包，发生死锁就会造成接口失败，从而严重影响用户体验，并且耗费系统资源，所以在业务设计上我们要尽量避免死锁。俗话说，知己知彼方能百战不殆，所以接下来我们将叙述MySQL中常见发生的四种死锁案例，同时帮助读者一起分析并深入了解这几种死锁发生的原因，通过掌握这些，我们就能避免大部分MySQL InnoDB的死锁问题，或者在发生死锁问题时，能够帮助大家更快排查到死锁原因。

1. 环境准备

        在具体案例分析之前，我们需要创建一些用于以下案例分析需要的表与初始化数据；具体如下：

        表authors（engine=InnoDB）：

Field

Type

Null

Key

Default

Extra

au_idint(11)NOPRINOT NULL emailvarchar(100)NO NULL au_namevarchar(100)NO NULL 

        存储记录为：

au_id

email

 au_name

[e-mail][e-mail][e-mail]_au

 

        表readers（engine=InnoDB）：

Field

Type

Null

Key

Default

Extra

re_idint(11)NOPRINULL phonevarchar(20)NO NULL re_namevarchar(100)NO NULL 

        存储记录为：

re_id

phone

 re_name

1

12345

first_re212367second_re312378third_re

2. 死锁案例与分析

2.1 锁表顺序造成死锁的例子

session 1

session 2

begin;begin;

select au_name from authors where au_id=1 for update；

  

select re_name from readers where re_id=3 tom for update

select re_name from readers where re_ id=3 tom for update  select au_name from authors where au_id=1 for update；

        分析： 开始时session1锁住了表au_id=1记录，然后session2锁住了re_id=3的记录；紧接着session1想获取re_id=3的记录 ，但是由于session2已经锁住了re_id=3的记录而等待；之后session2继续向下执行，当sessions2获取au_id=1的记录时，由于session1已经锁住了表au_id=1记录，所以session2就进入等待状态；因而由于session1、session2互相等待而造成了死锁。

        解决方案：这种错误属于简单但又最常见的死锁情况，比较容易发现，也比较容易解决，只要调整下sql的执行顺序就行了，比如将上面例子中的sql顺序调整如下：

session 1

session 2

begin;begin;

select au_name from authors where au_id=1 for update；

  

select au_name from authors where au_id=1 for update；

select re_name from readers where re_ id=3 tom for update  select re_name from readers where re_id=3 tom for update

2.2 锁申请级别不够出现的死锁的例子

session 1

session 2

begin;begin;

select au_name from authors where au_id=1 lock for share mode；

  

select au_name from authors where au_id=1 lock for share mode；

update authors set au_name=‘newname’ where au_id=1;  update authors set emal=‘[e-mail]’ where au_id=1;

        分析：开始两个事务由于上的都是共享锁，所以在对au_id=1加锁都是成功的，但是当session1去update au_id=1为记录时默认申请的是排它锁，由于排它锁与共享锁互斥所以等待，同样session1的互斥锁也阻塞了session2 的update语句造成两个事务互相等待而造成死锁。

        解决方案：在事务中如果需要更新数据就需要上对应级别的锁，即排它锁；而不应该先申请共享锁，update的时候再申请排它锁，否则就会出现上述的死锁情况。

        对于上述的两个死锁问题是比较好认知的，因为这个大家对下面这张表应该不陌生：

请求模式

当前模式

X

IX

S

IS

X

冲突

冲突

冲突

冲突

IX

冲突

兼容

冲突

兼容

S

冲突

冲突

兼容

兼容

IS

冲突

兼容

兼容

兼容

        其中 x为排它锁，IX为意向排它锁，S为共享所，IS为意向共享锁。

        上述的select for update和update操作上的都是X锁， lock for share mode上的S锁，出现死锁都是因为这个矩阵中的冲突所造成的，这里不再详述。

2.3 在REPEATATABLE-READ隔离级别下的死锁问题

session 1

session 2

begin;begin;

select re_id from readers where re_id=4 for update；

  

select re_id from readers where re_id=4 for update；

insert into readers value(4, 12312, forth_re);

  insert into readers value(4, 12312, forth_re);

        分析：大家会觉得很疑惑，这怎么会出现死锁呢，他们操作的顺序是一样的，记录也是一样的；其实由于re_id=4的记录在数据库中不存在，session1和session2加锁都会成功，大家可能有些疑惑，一会再作解释。其中两个session加锁成功的是gap锁，锁住了大于3的记录，加锁成功以后session1插入re_id=4记录的时候由于session1加了插入意向锁，导致session1插入数据等待，同理session2也会进入等待，这样就会死锁。

        为什么session1和session2 的select都加锁成功？

        其实除了上文中提到的锁，锁还可以细分为G(gap锁)、R(记录锁)、N(next-key锁)、I(插入意向锁)：

• •  间隙锁(Gap Lock)，只锁间隙。表现为锁住一个区间（注意这里的区间都是开区间，也就是不包括边界值）。
  • 记录锁(Record Lock)，只锁记录。表现为仅仅锁着单独的一行记录。
  • Next-Key锁(源码中称为Ordinary Lock)，同时锁住记录和间隙。从实现的角度为record lock+gap lock，而且两种锁有可能只成功一个，所以next-key是半开半闭区间，且是下界开，上界闭。一张表中的next-key锁包括：(负无穷大，最小的第一条记录]，(记录之间]，(最大的一条记录，正无穷大)。
  • 插入意图锁(Insert Intention Lock)，插入操作时使用的锁。在代码中，插入意图锁实际上是Gap锁上加了一个LOCK_INSERT_INTENTION的标记。也就是说insert语句会对插入的行加一个X记录锁，但是在插入这个行的过程之前，会设置一个Insert intention的Gap锁，叫做Insert intention锁。

        他们之间的兼容矩阵如下：

 

兼容性

G

I

R

N

当前持有的X锁类型

G

+

+

+

+

 

要加的X锁类型

I

-

+

+

-

R

+

+

-

-

N

+

+

-

-

 

        其中+代表兼容，-代表不兼容，而上述例子中由于两个session在select时用的都是gap锁，所以都会成功，但是由于gap锁和插入意向锁冲突，所以造成了死锁问题。

        解决方案：在插入记录之前不要进行select for update，这样就会出现insert相同的记录，为了解决重复插入，我们还需要忽略duplicate key错误，这样就会避免死锁问题。如果还是需要使用select for update，那么就需要修改隔离级别为READ COMMITTED，但是也要对duplicate key错误进行正确处理才可以。

2.4、唯一联合索引使用不正确造成的死锁问题

        假设authors表的的主键不再是au_id，并且以au_id和au_name作为联合唯一索引；

        存储的记录为：

au_id

email

au_name

[e-mail][e-mail][e-mail]_au

session 1

session 2

begin;begin;

 

select re_name from readers where re_id=3 for update;

select au_name from authors where au_id=3 and au_name is null for update；

 

  insert into authors value(4, '[e-mail]', 'fourth_au'); select re_name from readers where re_ id=3 tom for update; 

        分析：当session2执行select语句的时候，锁住了记录 re_id=3，session1执行第一条select的时候，由于 au_name is null 的原因，这条语句上锁为gap锁，加锁的范围是（3,5），此时session2 插入的时候就会出现锁等待，而session1的第二条sql语句也会出现等待，从而造成死锁问题。

        解决方案：其实这种问题可以通过例子1中的调整执行sql的顺序来解决，但是有的时候由于业务的特殊性不能调整顺序的话，我们采用的方案是不能让联合索引中的字段默认值为NULL，从而避免加gap锁来解决问题。

3. 总结

        本文介绍了四种MySQL出现死锁的例子，当然还会有其他出现死锁的情况，其中会有相当一部分的死锁原理会类似于上述的案例；本文的目的就是通过上述死锁的分析给读者一个参考，当读者在实际生产中遇到类似的问题能够马上定位问题，当然此文不能穷尽所有的死锁情况，如果真的遇到了一些其他情况，大家可以继续深入分析。