从一个死锁看mysql innodb的锁机制

柔光的暖阳◎ 2022-04-24 13:42 165阅读 0赞

# 背景及现象 #

线上生产环境在某些时候经常性的出现数据库操作死锁，导致业务人员无法进行操作。经过DBA的分析，是某一张表的insert操作和delete操作发生了死锁。简单介绍下数据库的情况（因为涉及到真实数据，这里做了模拟，不影响具体的分析和分析的结果。）假设存在如下2张表：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyNzE4OTQ_size_16_color_FFFFFF_t_70][]              ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyNzE4OTQ_size_16_color_FFFFFF_t_70 1][]  
 Order 表的数据如下：

![20190426154817545.png][]

Customer表的数据如下：

![2019042615483467.png][]

Order和Customer 在实体关系上存在一个关联，即order实体拥有一个指向customer实体的指针。在数据库设计的时候，order表的customer\_id没有被设计成一个外键，这是因为在对order表做操作的时候不希望外键影响数据库的操作性能。这里把对外键约束的检查放到了应用程序里面，即数据库仅仅当成一个持久化和事务性的保证。同时为了查询方便，对customer\_id做了索引。

在这个模拟业务场景中存在一个业务（因为是模拟的，所以不关心现实中是不是正确），一个客户拥有的订单会经常性的发生变化。即这个客户可能删除他名下的一些已经存在的订单，又增加一些新的订单，或者修改一些存在的订单，这3种操作可能都会发生在一个请求中。这时，应用人员做了一个不太好的实现：当一个客户把他这次改动的订单传到后台以后。开发人员不管这次有没有发生变化都针对这个客户的订单进行了先delete后insert的操作，来替代update操作。这里的实现是可以理解的，因为这一次请求中的订单可能需要delete,insert和update 三种操作，这样就要分辨出这批从页面传入的数据那些是delete,那些是insert,那些是upadte,还不如统一做成先delete再insert操作。

因为上面业务场景的实现的关系，抽象出来的一个事务中的数据库操作如下：

Start transaction; // 开事务

Delete from \`order\` where customer\_id = XXX;  // 先删除XXX名下所有订单

Insert into ‘order’ (customer\_id) values (xxx);   // 再Inset多条XXX名下的订单

Insert into ‘order’ (customer\_id) values (xxx);

……….

Commit; //事务提交

这样的操作在高并发的情况下，经常性的出现数据库死锁。

假设我们进行如下2个事务的操作（客户3和客户5都想增加一条自己的订单记录）：

T1 ：

![20190426154959790.png][]  
 T2：

![20190426155015486.png][]

如果在mysql服务器端，执行顺序如下：

T1  Start Transaction ;

T2  Start Transaction ;

T1   delete from \`order\` where customer\_id = 3;

T2   delete from \`order\` where customer\_id = 5;

T1  insert into \`order\` (customer\_id) values (3);

T2  insert into \`order\` (customer\_id) values (5);

…….

这个时候，T1 insert 语句没有办法执行，一直在等待一个锁授权。Mysql 的锁信息如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyNzE4OTQ_size_16_color_FFFFFF_t_70 2][]

Thread 5 尝试在 insert 的时候在等待一个锁授权，已经等待了10秒。可以看到事务0 10248 持有了2个锁；事务0 10247 有2个锁，1个等待锁授权。整个数据库只有这2个事务，所以导致insert等待的锁一定被0 10248持有了。

如果 T2 的insert语句继续执行，那么死锁就发生了，mysql的信息如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyNzE4OTQ_size_16_color_FFFFFF_t_70 3][]

# 分析 #

首先我们先要了解下基本的数据库的锁的知识。

数据库为了提高并发性，对于读和写进行2种不同的锁控制，分别称为共享锁（S锁）和排他锁（X锁）。这两种锁不是mysql独有的，在一般性的数据库基本原理介绍中都会提到。同时还有相应的意向锁的概念。

在mysql的innodb 存储引擎里面，使用的是行锁（S，X），以及表锁（IS，IX）。这里4种锁有个兼容矩阵（兼容矩阵做什么用的？不需要解释了吧，可以参考数据库基本原理的书）如下：

![20190426160907344.png][]  
 我们打开锁监控，然后再具体观察下在事务执行之中的锁情况。

A   ：T1  Start Transaction ;

B   ：T2  Start Transaction ;

C   ：T1  delete from \`order\` where customer\_id = 3;

D   ：T2  delete from \`order\` where customer\_id = 5;

E   ：T1  insert into \`order\` (customer\_id) values (3);

F   ：T2  insert into \`order\` (customer\_id) values (5);

…….

我们先按照顺序执行到E，下面是mysql的锁情况：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyNzE4OTQ_size_16_color_FFFFFF_t_70 4][]  
 T2

![20190426160951162.png][]

我们可以清楚的看到 T1 持有（包括等待授权的）3个锁：一个是对表order的IX锁；一个是对表order上面的index customer\_id的 Gap类型的X锁； 还有一个是对表Order上面index customer\_id 的 Insert intention 类型的X锁等待被授权。

T2 持有2个锁：一个是对表Order的IX锁； 一个是对表order上面的index customer\_id 的Gap锁。

注意 T1 的Gap，Insert intention ,T2 的Gap 都是锁的同一个地方 “space id 0 page no 198 n bits 80”

这里介绍下mysql innodb下的锁类型：

常见的三种类型

![20190426161027760.png][]

拿上面的例子来说

Record 类型，简单的理解就是执行delete from \`order\` where id = 1,锁住的order表里面id =１的记录。

Gap 类型：简单的理解就是执行 delete from \`order\` where customer\_id = 3。这里在order表里面没有customer\_id=3 的记录。但是又由于customer\_id存在一个索引，mysql根据索引进行搜索，索引的key是(1,2,6)，3不在这些key里面而是位于(2,6)之间的gap（间隙）中。Mysql对于(2,6)这个间隙加的锁就叫做Gap锁。这个例子中的间隙一共有(-∞,1),(1,2),(2,6),(6,+∞)这4个。注意gap只锁间隙不锁记录。

Next-Key 类型 : 简单的理解就是 Gap + 下一个 Record 。拿上面Gap的例子来说的话，锁住的就是(2,6\]。这里包括了6这个记录。

除开以上三种常见的锁类型，还有一种对于Insert语句的特殊锁类型

![2019042616110927.png][]

也就是说insert语句会对插入的行加一个X锁，但是在插入这个行的过程之前，会设置一个Insert intention的Gap锁，叫做Insert intention锁。

以上面的例子来说，在执行 insert into \`order\` (customer\_id) values (3)的时候，由于存在customer\_id的索引，所以会对这个索引的(2,6)增加一个Insert Intention 类型的X锁。

了解了这些之后，我们回到上面的例子。

这里我们清楚的知道 --“注意 T1 的Gap，Insert intention ,T2 的Gap 都是锁的同一个地方 “space id 0 page no 198 n bits 80””—3个锁锁住同一个地方的原因了。因为customer\_id = 3 和customer\_id =5 都是属于同一个gap（2,6）。

T1 持有 gap (2,6) X锁，同时有个 insert intention (2,6)的X锁在等待gap(2,6)的X锁的释放；

T2 持有 gap(2,6) X锁。

这就是导致T1的insert 语句执行不下去的真正原因。 当T2的insert 语句执行的时候，（即F语句）可以预见，T2也会有个 insert intention(2,6)的X锁在等待gap(2,6)的X锁的释放。这样就形成了死锁。

分析到这里就结束了么？好像那个地方有点不对。T1本身不就是拥有了一个gap(2,6)的X锁么？等等，为什么在T1拥有gap(2,6)X锁的情况下，T2还可以拥有gap(2,6)X锁？X锁同X锁不是不兼容的么（看看兼容矩阵）？

是的，看看上面的兼容矩阵。IX与IX兼容，X与X不兼容。T1和T2 同时拥有对于表order的IX锁是可以理解的；但是T1和T2 同时拥有对于表order的index customer\_id的X锁似乎就无法理解了。按照兼容矩阵的说法，在T2 执行D语句的时候就应该被block，因为它需要获取Gap(2,6)的X锁，但是这个锁已经被T1执行C语句的时候持有了，所以只有在T1事务执行完以后，T2才能继续执行，按照这个顺序下来，是不会发生死锁的。

Mysql 或者说是 Innodb 是不是弄错了什么？

其实，我们分析的没有错，Mysql也没有弄错，唯一错的地方是官方文档上面没有介绍除了这个（IS,IX,S,X）的兼容矩阵外，在Mysql实现内部还有一个更加精确的被称为“precise mode”的兼容矩阵。（该矩阵没有出现在官方文档上，是有人通过Mysql lock0lock.c:lock\_rec\_has\_to\_wait源代码推测出来的。）下面这个是“precise mode”的兼容矩阵：（这个兼容矩阵发生在X与X，S与X不兼容的情况下再进行比对的）

G    I     R    N (已经存在的锁,包括等待的锁)  
  G   +     +    +     +   
  I    **-**      +    +     -  
  R   +     +     -     -  
  N   +     +     -     -  
  + 代表兼容， -代表不兼容. I代表插入意图锁,  
  G代表Gap锁，I代表插入意图锁,R代表记录锁，N代表Next-Key锁.

([http://www.mysqlops.com/2012/05/19/locks\_in\_innodb.html\#more-3169][http_www.mysqlops.com_2012_05_19_locks_in_innodb.html_more-3169])

这里需要注意的一点是，存在Insert Intention 锁时，申请Gap锁是允许的；但是存在Gap锁时，申请Insert Intention锁时是被阻止的。

回到上面的例子，这下就可以解释清楚了。

执行C语句完毕，T1持有了Gap(2,6)的X锁；

执行D语句，T2 申请Gap(2,6)的X锁，根据“precise mode”兼容矩阵，该申请被授权，所以T2 持有了Gap(2,6)的X锁。

执行E语句，T1 申请Insert Intention (2,6)的X锁，根据“precise mode”兼容矩阵，由于T2持有Gap(2,6)的X锁，该申请被T2 block。

执行F语句，T2 申请 Insert Intention(2,6)的X锁，根据“precise mode”兼容矩阵，由于T1持有Gap(2,6)的X锁，该申请被T1 block。

这里一个死锁很明显的出现，T1与T2都持有一个锁，同时都在等对方释放一个锁。到这里，整个死锁的原因分析清楚了。

# 解决 #

我们分析清楚了死锁形成的原因，就很好去解决这个问题了。可以看出T1，T2 都是持有了Gap 锁，等待insert intention被授权。

只要消除了Gap锁，这个死锁就解决了。方案有几种：

A delete 表 order上面的index customer\_id。这样在delete的时候就不会产生Gap锁，insert 的时候也不会有insert intention锁。不过对于查询会有影响。

B 在delete的时候，不让事务获取到Gap锁。比如，在执行delete from        \`order\`  where customer\_id = 3 ;之前，先通过数据库查询 select \* from \`order\` where customer\_id = 3; 看是否存在记录。不存在记录这不执行delete操作。因为insert总是要发生，delete则不是必须一定要发生的。

# 后记 #

在真实解决线上这个问题的时候，走过了一些弯路，某些现象也让我认为是找到了真实的原因，其实那只是虚幻的假象。

因为死锁发生在Insert 语句上面，一开始我们认为是\`order\` 表上面的主键id自增锁引起的（有点主观臆断，病急乱投医）。然后，我们把\`order\`上面的主键id转换成类似Oracle的sequence 序列，通过应用程序给予其赋值id。大家可以去尝试操作下，把一张表的主键id的auto\_increment 给改掉，是多么恶心的一个操作(不是说多复杂，而是说这个操作的方式让有“操作洁癖”的人无法忍受)。等到上线以后，确实似乎好了很多，但是根源还是存在，只是它现在不想咬你。又过了段时间，系统压力上来了，这个问题又暴露出来了。正是应了那句“屋漏偏招连夜雨”，祸不单行，当问题出现的时候，开始我们还是认为是insert语句生成id的方式造成的，慢慢的对于这个问题的分析越来越详尽，终于意识到“id 生成方式”是替罪羔羊，真正的原因在于过多的无意义的delete操作的时候，这个问题才算是解决。

为了避免大家对主键id自增锁的偏见，我简单介绍下主键id自增锁的机制，也算是我对冤枉它的一种补偿吧。

主键自增锁基本上是通过 select Max(id) from table for update来实现的。很明显，for update 加的是表锁而且是X的。和其他的锁的区别就在于它的释放时机，其他的锁是跟随事务的。自增锁不跟着事务走，而是跟着那条Insert语句走。

在Mysql 5.1.22版本以后，增加了 innodb\_autoinc\_lock\_mode的参数，来调整主键自增锁的性能。这个时候不一定会进行锁表操作了，有可能就是直接在内存里面算好id值。在这种情况下面，mysql会对Insert语句进行分类，不同的分类在不同的参数 innodb\_autoinc\_lock\_mode 下面会有不同的自增方式。大家可以参考《mysql技术内幕 InnoDB存储引擎》 。

原文转自:[https://narcissusoyf.iteye.com/blog/1637309][https_narcissusoyf.iteye.com_blog_1637309]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyNzE4OTQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190426154002488.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyNzE4OTQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyNzE4OTQ_size_16_color_FFFFFF_t_70 1]: /images/20220214/0d8c4f1a817a4181b7eb55f0e5843626.png
[20190426154817545.png]: /images/20220214/b0bc341028524ec7a53c3cf748a1589f.png
[2019042615483467.png]: /images/20220214/6c5cbce3f5384984947792bcb819dd18.png
[20190426154959790.png]: /images/20220214/2a0ea74ee6ac40b5a1c7d4edc71a0d9f.png
[20190426155015486.png]: /images/20220214/dd67e5a6c09f48dcaf7459cc61e7d882.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyNzE4OTQ_size_16_color_FFFFFF_t_70 2]: /images/20220214/adb886c2cab94a66bc5507b10e7fb457.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyNzE4OTQ_size_16_color_FFFFFF_t_70 3]: /images/20220214/9ef7d479bfc844219d37515f69c7f207.png
[20190426160907344.png]: /images/20220214/68068a4268384cb9bd30c11a06a32408.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTEyNzE4OTQ_size_16_color_FFFFFF_t_70 4]: /images/20220214/bdb66212f1104d9289432d585fd1a1ed.png
[20190426160951162.png]: /images/20220214/fb727dd9996e47618dfbbeb7a129a3ba.png
[20190426161027760.png]: /images/20220214/17b38d64102f4884a9a7de1c3035b9fe.png
[2019042616110927.png]: /images/20220214/54e32b00508b4b64b8ff8c16b407477a.png
[http_www.mysqlops.com_2012_05_19_locks_in_innodb.html_more-3169]: http://www.mysqlops.com/2012/05/19/locks_in_innodb.html#more-3169
[https_narcissusoyf.iteye.com_blog_1637309]: https://narcissusoyf.iteye.com/blog/1637309