MySQL5.7 insert on duplicate key update 多线程死锁
2022-11-18 00:00:00 # MySQL

insert on duplicate key update 当发现有重复的唯一索引(unique key)或者主键(primary key)的时候，会进行更新操作；如果没有，那么执行插入操作。
由于工作业务有这种场景，而且这种写法比分批插入和分批更新的性能要好很多，所以直接采用了这种方式。但是问题来了，单线程用这个是可以的，当并发多线程使用insert on duplicate key update时就会出现死锁，而且概率还挺高。这篇文章并没有给出很好的解决方法，如果遇到此问题，建议不使用多线程或者不使用此语句。

MySQL行锁

此处先简单总结一下行锁的相关概念（此处使用小林coding网站内容），如果已经了解可以直接跳过。

共享锁（S锁）满足读读共享，读写互斥。独占锁/排它锁（X锁）满足写写互斥、读写互斥。

Record Lock 记录锁

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

  • 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
  • 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

Gap Lock 间隙锁

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

Next-Key Lock 临键锁

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

举个例子，假设事务 A 已经对表加了一个范围 id 为（3，5）间隙锁。

当事务 A 还没提交的时候，事务 B 向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事物 B 会生成一个插入意向锁，然后将锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），此时事务 B 就会发生阻塞，直到事务 A 提交了事务。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。

死锁问题

死锁日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-09-15 14:59:07 0x7f83d029c700
*** (1) TRANSACTION:
TRANSACTION 297675, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 36 lock struct(s), heap size 3520, 27 row lock(s), undo log entries 11
MySQL thread id 114712, OS thread handle 140204005881600, query id 960582
insert into table () .... on duplicate key update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 175 page no 99087 n bits 112 index PRIMARY of table `xxx`.`table` trx id 297675 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 297678, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
40 lock struct(s), heap size 3520, 29 row lock(s), undo log entries 18
MySQL thread id 114703, OS thread handle 140204109842176, query id 960586   update
insert into table () .... on duplicate key update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 175 page no 99087 n bits 112 index PRIMARY of table `xxx`.`table` trx id 297678 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 175 page no 99087 n bits 112 index PRIMARY of table `xxx`.`table` trx id 297678 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)

日志分析

首先看事务1的信息

TRANSACTION:
TRANSACTION 297675, ACTIVE 14 sec inserting

ACTIVE 14 sec 表示事务活动时间，inserting 为事务当前正在运行的状态，可能的事务状态有：fetching rows，updating，deleting，inserting 等。

mysql tables in use 1, locked 1
LOCK WAIT 36 lock struct(s), heap size 3520, 27 row lock(s), undo log entries 11

tables in use 1 表示有一个表被使用，locked 1 表示有一个表锁。LOCK WAIT 表示事务正在等待锁，36 lock struct(s) 表示该事务的锁链表的长度为 36，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及 autoinc 锁等。heap size 3520 为事务分配的锁堆内存大小。27 row lock(s) 表示当前事务持有的行锁个数。undo log entries 11 表示当前事务有 11 个 undo log 记录，因为二级索引不记 undo log，说明该事务已经更新了 11 条聚集索引记录。

MySQL thread id 114712, OS thread handle 140204005881600, query id 960582

事务的线程信息，以及数据库 IP 地址和数据库名(已经去掉关键信息)

insert into table () …. on duplicate key update

此处显示的是正在等待锁的 SQL 语句（已经去掉关键信息）

RECORD LOCKS space id 175 page no 99087 n bits 112 index PRIMARY of table xxx.table trx id 297675 lock_mode X insert intention waiting

这里显示的是事务正在等待什么锁。lock_mode X 表示该记录锁Next-key 锁，insert intention waiting 表示要加的锁为插入意向锁，并处于锁等待状态。

0: len 8; hex 73757072656d756d; asc supremum;;

从这一行就能看出是在supremum record 上加锁的, 相当于锁住最大值到正无穷大的区间。

事务2的日志同理，那么就可以看出2个事务都有间隙锁，而且互相都在等待插入，这样就构成了死锁。

问题重现

我找了很多参考的文章，也试了很多种相似的方法，但都没办法重现这个情形，只能待以后实现了

解决方法

  1. 使用单线程，单线程可以避免这种死锁
  2. 不使用insert on duplicate key update, 业务中分别处理插入和修改操作
  3. 捕获异常重试，但是我测试发现出现死锁概率还是比较高的，这个方法不太合适

参考

小林coding MySQL相关内容
MySQL锁详解
讲讲insert on duplicate key update 的死锁坑

上一页
2022-11-18 00:00:00 # MySQL
下一页