记录一次Mysql死锁事件（由Insert与uniqueKey导致）

| 导语记录一次于2023年01月23日遇到的死锁问题。

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 to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system 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、背景

1. 一个表scan_file_licenses，

2. 一个唯一索引uniq_index_on_task_id_and_project_id_and_file_license_source，

3. 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 死锁原因

死锁原因一目了然

事务B因为在申请锁的路上，所以在本事务结束之前，是不会把已经持有S锁释放掉的；

事务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 （https://bugs.mysql.com/bug.php?id=68021）中也描述了同样的问题，后来官方尝试进行了“修复”，不过之后又非常戏剧性的把这个“修复”给修复掉了：Duplicates in Unique Secondary Index Because of Fix of Bug#68021（https://bugs.mysql.com/bug.php?id=73170）。

参考文章

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