从死锁认识MySQL锁

前言

作为后端开发人员，几乎每天都与数据库打交道。对着变化莫测的需求，紧张的工期，很多程序员日复一日写着CRUD代码，无暇理会数据库实际执行原理，这是技术提高的门槛。
秉着工作处处皆学问，知其然知其所以然的信念，日常生活中我都会把遇到的问题摘录下来，总结分析背后的技术原理。

一、遇见死锁

组长让我实现一个提交申请单的功能。因为用户可以重复提交申请，而申请只能保留一份，因此我在插入新申请insert操作前，先按照用户id删除delete他之前所有申请记录。确保两个操作的一致性，我加了事务@Transactional。
简单的功能，简洁的实现。事与愿违，表中出现重复用户id记录的。查看数据发现，新的记录插入成功，是旧的数据没有删除成功。既然是事务，为什么一个执行成功，一个失败呢？

+-----------------+-----------+---------------+-----------+
| id              | member_id | nick_name     | is_delete |
+-----------------+-----------+---------------+-----------+
| 181025163317920 |      3288 | 蕉仔伟🍌        |          |
| 181026142458439 |      3288 | 赛亚人        |          |
| 181030104733141 |      3288 | 百变小樱      |           |
| 181030105733272 |      3288 | 柴犬慕斯      |           |
+-----------------+-----------+---------------+-----------+
4 rows in set (0.01 sec)

带着问题找原因，看看数据库的状态报告：

mysql> show engine innodb status;
| InnoDB |      |
=====================================
LATEST DETECTED DEADLOCK
------------------------
2018-11-28 20:07:24 0x7f286deb7700
*** (1) TRANSACTION:
TRANSACTION 41443739, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 230974, OS thread handle 139810027362048, query id 73704659 172.31.0.8 apiuser update
INSERT INTO media_user_apply  ( id,real_name,nick_name,member_id,media_user_id,mobile,sex,id_num,education,contact_way,summary,is_delete,status,create_time,update_by,update_time,type,member_type ) VALUES( 181115110822284,'12','12',2910,null,null,1,'',0,'','',0,0,'2018-11-15 11:08:19.59',null,null,1,1 )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1045 page no 4 n bits 136 index member_id_index of table `mongcent_news`.`media_user_apply` trx id 41443739 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000b5f; asc    _;;
 1: len 8; hex 8000a4a5997e1503; asc      ~  ;;

*** (2) TRANSACTION:
TRANSACTION 41443740, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 242484, OS thread handle 139811600373504, query id 73704661 172.31.0.8 apiuser update
INSERT INTO media_user_apply  ( id,real_name,nick_name,member_id,media_user_id,mobile,sex,id_num,education,contact_way,summary,is_delete,status,create_time,update_by,update_time,type,member_type ) VALUES( 181115110822286,'12','12',2910,null,null,1,'',0,'','',0,0,'2018-11-15 11:08:19.591',null,null,1,1 )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1045 page no 4 n bits 136 index member_id_index of table `mongcent_news`.`media_user_apply` trx id 41443740 lock_mode X locks gap before rec
Record lock, heap no 34 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000b5f; asc    _;;
 1: len 8; hex 8000a4a5997e1503; asc      ~  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1045 page no 4 n bits 136 index member_id_index of table `mongcent_news`.`media_user_apply` trx id 41443740 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000b5f; asc    _;;
 1: len 8; hex 8000a4a5997e1503; asc      ~  ;;

*** WE ROLL BACK TRANSACTION (2)

从报告的LATEST DETECTED DEADLOCK中，我们可以看到执行事务过程中，发生的死锁。而发生死锁的原因，有句话很关键

RECORD LOCKS space id 1045 page no 4 n bits 136 index member_id_index of table mongcent_news.media_user_apply trx id 41443739 lock_mode X locks gap before rec insert intention waiting

这句话究竟告诉了我们什么呢？我们先来普及一下MySQL锁的知识

二、MySQL锁

1. 共享锁与排他锁

在 InnoDB 中实现了两个标准的行级锁，可以简单的看为两个读写锁：

• S ：共享锁(Share Lock)又叫读锁，其他事务可以继续加共享锁，但是不能继续加排他锁。
• X ：排他锁(Exclusive Lock)又叫写锁，一旦加了写锁之后，其他事务就不能加锁了。

锁与锁之间具有兼容性，兼容性是指事务 A 获得一个某行某种锁之后，事务 B 同样的在这个行上尝试获取某种锁，如果能立即获取，则称兼容，反之叫冲突。

纵轴是代表已有的锁，横轴是代表尝试获取的锁。

2.意向锁

意向锁在 InnoDB 中是表级锁，顾名思义它是用来表达一个事务想要获取什么。如果用行锁加表锁，需要遍历每一行是否有行锁，但使用意向锁，只需要判断意向锁中的行就可以了。
事务在请求某一行的S锁和X锁前，需要先获得对应表的IS、IX锁。
意向锁分为：

• IS：意向共享锁(Intent Share Lock)表达一个事务想要获取一张表中某几行的共享锁。
• IX：意向排他锁(Intent Exclusive Lock)表达一个事务想要获取一张表中某几行的排他锁。

在 InnoDB 中由于支持的是行级的锁，因此 InnboDB 锁的兼容性可以扩展如下：

三.锁算法

1.记录锁(Record Lock)

记录锁是锁住记录的，这里要说明的是这里锁住的是索引记录，而不是我们真正的数据记录：
如果锁的是非主键索引，会在自己的索引上面加锁之后然后再去主键上面加锁锁住。
如果没有表上没有索引(包括没有主键)，则会使用隐藏的主键索引进行加锁。
如果要锁的列没有索引，则会进行全表记录加锁。

2.间隙锁(Gap Lock)

间隙锁顾名思义锁间隙，不锁记录。锁间隙的意思就是锁定某一个范围，间隙锁又叫 gap lock，其不会阻塞其他的 gap lcok，但是会阻塞 插入间隙锁 ，这也是用来防止 幻读 的关键。

3.插入意向锁

插入意向锁是一种Gap Lock，在insert操作时产生。

• 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。

4.Next-key Lock

这个锁本质是记录锁+ 间隙锁。在 RR 隔离级别下(InnoDB 默认)，InnoDB 对于行的扫描锁定都是使用此算法，但是如果查询扫描中有唯一索引会退化成只使用记录锁。
为什么呢? 因为唯一索引能确定行数，而其他索引不能确定行数，有可能在其他事务中会再次添加这个索引的数据造成幻读。
这里也说明了为什么 MySQL 可以在 RR 级别下解决幻读。

四.解决死锁

理解完InnoDB的锁与锁算法，我们回到最初的问题。探索一下，为什么我的代码会发生死锁。
从网上找到日志的翻译：

lock_mode X locks gap before rec 表示Gap Lock,Gap锁是用来防止insert的
lock_mode X locks rec but not gap 表示Record Lock

lock_mode X 表示Next-Key Lock
lock_mode X insert intention waiting 表示Insert Intention Lock

因此锁日志中lock_mode X locks gap before rec insert intention waiting的意思就是在事务1、事务2持有Gap Lock的时，再请求Insert Intention Lock。我们之前说了在持有间隙锁时，请求插入意向锁，会导致冲突，阻塞事务。
本来两个事务如果分别独立运行都能顺利运行。但如果是同时执行的时候，就不一样了：
1.第一句命令执行了delete操作，事务1、事务2分别都对目标索引加了Next-key Lock。
2.事务1执行insert操作前，它会请求Insert Intention Lock，当它发现有事务2的Gap Lock，于是发生了冲突，事务1阻塞。同理，事务2执行insert操作也发生了冲突，事务1阻塞。
3.此时事务1等事务2释放锁，事务2等事务1释放锁，出现了回路等待，产生了死锁。
4.数据发现了死锁，最终决定WE ROLL BACK TRANSACTION (2)，回滚了事务2的插入操作，事务1的插入操作执行成功。

解决方法

死锁的原因在于Gap Lock与Insert Intention Lock的冲突，而从上面的Next-key Lock定义中得知，如果扫描中有唯一索引会退化成只使用记录锁。所以，我们只要把delete的where条件改成使用唯一索引或者主键，即可避免产生Gap Lock，从而避免了死锁。

后记

今晚是平安夜，在此我也祝大家的数据库能够平平安安。Merry Christmas !

我全部都要.jpeg