Mysql刨根：由Insert与uniqueKey的竞争引发死锁

0、前言

通过前文《数据库温故：Mysql底层原理起底》我们已经学过了Mysql的事务隔离级别等基础知识，现在我们可以利用所学，来分析一下生产环境出现的死锁问题了。

1、基础

1.1 数据库隔离级别

1.1.1RC

READ COMMITTED：只能读取已经提交的数据；此时：允许幻读和不可重复读，但不允许脏读，所以RC隔离级别要求解决脏读；

1.1.2RR

REPEATABLE READ：同一个事务中多次执行同一个select,读取到的数据没有发生改变；此时：允许幻读，但不允许不可重复读和脏读，所以RR隔离级别要求解决不可重复读；

1.2 加锁范围的锁

1. 行锁：Lock 也就是我们所说的记录锁，记录锁是对索引记录的锁，注意，它是针对索引记录，即它只锁定记录这一行数据
2. 间隙锁GapLock：将记录之间的间隙锁住，间隙锁住了便可以解决幻读问题，只在RR隔离级别有效。
3. NextKeyLock：既想锁定一行，又想锁定行之间的记录，就是NextKey，即1和2的结合体。

1.3 加锁角度的锁

1. S锁：共享锁/读锁，S LOCK可以同时分发给多个TX，允许多个TX读取同一个Record
2. X锁：排它锁/写锁，X LOCK不可以同时分发给多个TX，而且TX申请X LOCK的Record对象，必须没有其他的LOCK（不管是S还是X）；并且TX申请成功X LOCK之后，一直到锁被释放前，当前Record都不可以分发其他锁（即不可被其他TX读写）

任何数据库的锁，都是先确定范围，再确定加锁方式的，DML的类型将直接影响到锁的效果。

1.4 RC隔离级别可能造成的GapLock

在Mysql5.7版本官方文档下，有对 间隙锁GapLock 有这样一段陈述（见下图）。

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level toREAD COMMITTEDor enable theinnodb_locks_unsafe_for_binlogsystem variable (which is now deprecated). In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

翻译：

大意就是，间隙锁能够被直接明确禁用。比如将事务隔离改为RC或修改系统变量（innodb_locks_unsafe_for_binlog），这样能够在搜索和索引扫描禁用掉间隙锁GapLock；But，在外键约束和唯一键时会触发使用。

2、背景

一个表scan_file_licenses，

一个唯一索引uniq_index_on_task_id_and_project_id_and_file_license_source，

5个列字段（project_id、task_id、source_file_path_hash、license_hash、license_source）

3、死锁快照

从腾讯云给的结论看，死锁原因是TX1（已经持有了Next_key锁）和TX2（申请某个记录锁），两者出现了锁等待，进而导致TX2被回滚了。

3.1 造成死锁的事务

3.1.1 事务A

分析：

1. 事务A是一条insert语句，目的是批量写入数据
2. 命中了唯一索引uniq_index_on_task_id_and_project_id_and_file_license_source
3. 向记录（space id 51 page on 204462 ... 应该是二级索引数的结点描述）申请X锁；申请成功了才能正确写入数据

3.1.2 事务B

• 事务B是一条insert语句，目的是批量写入数据
• 命中了唯一索引uniq_index_on_task_id_and_project_id_and_file_license_source
• 已经持有记录（space id 51 page on 204461 ... 应该是二级索引数的结点描述）的S锁
• 向记录（space id 51 page on 204296 ... 应该是二级索引数的结点描述）申请X锁；只有申请成功了才能正确写入数据

3.1.3 死锁原因

分析：死锁原因一目了然了

1. 事务B因为在申请锁的路上，所以在本事务结束之前，是不会把已经持有S锁释放掉的；
2. 事务A则因为申请了事务B执行路上，用GapLock赋予了周围记录S锁，导致自己申请周围记录X锁失败了。

4、优化方案

4.1 业务层面优化

4.1.1控制并发插入的数据粒度

批量插入的数据量，控制在2~5条，避免概率性出现的死锁对业务造成的影响持续扩散。

4.1.2降低并发插入的概率

批量插入的异步线程之间，通过线程休眠的方式，既能降低并发insert操作的概率，也能降低Mysql-Server负载；

4.1.3先查后插

降低重复数据的并发插入，哪些已经持久化的数据，就过滤掉无需再插入；

4.2 数据库层面优化

4.2.1 修改索引类型

解决方案在技术上并不复杂，只需要把发生死锁的唯一索引替换成普通索引就可以了，但是要注意这种替换操作对业务的影响。

5、总结

参考了腾讯云给的一个文章

从一般的角度来考虑，这个额外的 S 锁似乎是不必要的，所以仔细搜索一下 MySQL bug 的信息，发现一个远古时代的 bug 单：Unexplainable InnoDB unique index locks on DELETE + INSERT with same values 中也描述了同样的问题，后来官方尝试进行了“修复”，不过之后又非常戏剧性的把这个“修复”给修复掉了：Duplicates in Unique Secondary Index Because of Fix of Bug#68021。

参考文章

MySQL案例：insert死锁与唯一索引（ https://cloud.tencent.com/developer/article/2017355?areaSource=& ;traceId=）

我正在参与 腾讯云开发者社区数据库专题有奖征文。