线上INSERT ON DUPLICATE 死锁问题排查

6,150 阅读8分钟

前言

最近在并发量稍微高的情况,线上出现很多deadlock日志,日志中同时也打印出表名和MDL,是一条insert语句。于是,我查看表结构和代码中真正的执行语句。(这里为了避免使用真实表,我稍微改了一下表名和字段名。)

隔离级别:RR

数据库版本:5.7

表结构

CREATE TABLE IF NOT EXISTS `t_test` (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        userid BIGINT(11) NOT NULL,
        seq BIGINT(20) UNSIGNED NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY _uk_userid (userid),
        KEY _k_userid_seq (userid,seq)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

执行语句

INSERT INTO t_test (userid,seq) VALUES (10,1638504289244987791),(50,1638504289244987791) 
ON DUPLICATE KEY UPDATE seq=VALUES(seq);

查看日志

打开Mysql监控,查看show engin innodb status,这里会记录数据库最后一次死锁相关事务阻塞语句和锁等待/持有状况。我们主要分析LATEST DETECTED DEADLOCK部分。

官网详细介绍:dev.mysql.com/doc/refman/…

我们详细看一下比较重要的行表达的意思和作用。

2021-12-03 12:04:48 0x7f06a6109700

  • 记录死锁发生时间,可以通过这个时间戳与项目报错日志匹配。

事务1的记录

*** (1) TRANSACTION:
TRANSACTION 1006151140, ACTIVE 0 sec inserting

  • 事务1开始的标记,下面显示的内容都是事务1的操作。
  • 事务1的ID为1006151140

mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1

  • 表示事务1(1006151140)正在使用的表数量为1,同时涉及到的锁定状态表数据为1。
  • 表示在等待6把锁,占用内存1136字节,涉及到4行记录,涉及到1行undo log。

INSERT INTO t_test (userid,seq) VALUES (10,1638504289244987791),(50,1638504289244987791)
ON DUPLICATE KEY UPDATE seq=VALUES(seq);

  • 表示事务1(1006151140)涉及到的SQL,非常重要!

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 383 page no 15866 n bits 272 index PRIMARY of table mytest.t_test trx id 1006151140 lock_mode X insert intention waiting

  • 表示事务1(1006151140)正在等待锁被授予。
  • 表示在等待记录锁RECORD LOCKS,空间id为383,页编号为15866,大概位置在页的272位处;锁发生在表t_test的PRIMARY主键上,是一个插入意向锁insert intention lock,锁模式为排它锁X锁

事务2的记录

*** (2) TRANSACTION:
TRANSACTION 1006151141, ACTIVE 0 sec inserting

  • 事务2开始的标记,下面显示的内容都是事务2的操作。
  • 事务2的ID为1006151142

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 383 page no 15866 n bits 272 index PRIMARY of table mytest.t_test trx id 1006151141 lock_mode X

  • 表示事务2(1006151142)持有的行锁;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 383 page no 15866 n bits 272 index PRIMARY of table mytest.t_test trx id 1006151141 lock_mode X insert intention waiting

  • 表示事务2(1006151141)正在等待锁被授予。
  • 表示在等待记录锁RECORD LOCKS,空间id为383,页编号为15866,大概位置在页的272位处;锁发生在表t_test的PRIMARY主键上,是一个插入意向锁insert intention lock,锁模式为排它锁X锁

*** WE ROLL BACK TRANSACTION (2)

  • 表示事务2被回滚了。因为两个事务的回滚开销一样,所以选择了后提交的事物进行回滚,如果两个事务回滚的开销不同(undo log数量不同),那么就回滚开销最小的那个事务。

分析问题

对于各种锁概念,不是很清晰的同学,可以回顾这篇文章《InnoDB锁类型介绍》

另外根据林晓斌(丁奇)大佬对Innodb加锁规则总结的进行分析。

我总结的加锁规则里面,包含了两个“原则”、两个“优化”和一个“bug”。
原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。
原则 2:查找过程中访问到的对象才会加锁。
优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

这里再简单区分一下INSERTINSERT...ON DUPLICATE KEY UPDATE的不同:

Innodb-locks-set官方说明:dev.mysql.com/doc/refman/…

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

INSERT语句在插入的行上会设置排它锁。这个锁是索引记录锁,不是临键锁(即没有间隙锁),并且不会阻止其他会话在插入行之前插入间隙。

在插入行之前,会设置一个插入意向锁(这是一个间隙锁)。这个锁表示插入的意图,在同一索引间隙中,如果多个事务没有插入间隙内的相同位置,则不需要互相等待。

INSERT...ON DUPLICATE KEY UPDATdiffers from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.

INSERT...ON DUPLICATE KEY UPDAT操作与简单的INSERT操作不同,当Insert发生duplicate-key错误的时候,将会在要update的行上设置排它锁。对重复的主键值设置排它记录锁。对重复的唯一键值设置排它临键锁。

实践发现,即使没有主键冲突,INSERT ON DUPLICATE KEY UPDAT操作会在主键间隙上加间隙锁。

锁模式兼容表(横向是已持有的锁,纵向是请求的锁)

兼容性Insert IntentionRecordGapNext-Key
Insert Intention兼容兼容冲突冲突
Record兼容冲突兼容冲突
Gap兼容兼容兼容兼容
Next-Key兼容冲突兼容冲突

好了,理解了上面的理论基础,我们接着分析。

  1. INSERT操作,在插入行之前会设置一个插入意向锁。如果该间隙已被加上了 GAP 锁或 Next-Key 锁,则加锁失败进入等待;(注意:Gap锁是为了防止insert, 插入意向锁是为了insert并发更快,两者是有区别的 )
  2. 如果是简单INSERT操作,并且存在唯一主键,那么根据「优化1」,next-key lock 退化为记录锁(即行锁)。
  3. 如果是INSERT...ON DUPLICATE KEY UPDAT会加上间隙锁。若再发生duplicate-key错误的时候则需要执行UPDATE操作,对重复的主键值设置排它记录锁,对重复的唯一键值设置排它临键锁,还会加一个共享记录锁(S)。

问题复现

表结构

CREATE TABLE IF NOT EXISTS `t_test` (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        userid BIGINT(11) NOT NULL,
        seq BIGINT(20) UNSIGNED NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY _uk_userid (userid),
        KEY _k_userid_seq (userid,seq)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表初始化数据

时间事务1事务2事务3
T1begin;insert into t_test (userid,seq) values (6,1638504289244987791) on duplicate key update seq=seq+1;获取插入意向锁,排它的间隙锁(5,10);执行插入成功;
T2begin;insert into t_test (userid,seq) values (7,1638504289244987791) on duplicate key update seq=seq+1;准备插入意向锁,等待事务1的间隙锁;
T3锁等待中insert into t_test (userid,seq) values (8,1638504289244987791) on duplicate key update seq=seq+1;准备插入意向锁,等待事务1的间隙锁;
T4锁等待中
T5rollback;死锁出现

预防死锁

  1. 尽量使用INSERT来替换INSERT...ON DUPLICATE KEY UPDATINSERT将在唯一键和主键中添加记录 x 锁,而不是获取间隙锁,因此不会造成死锁。
  2. 使用INSERT,然后业务上判断duplicate-key错误,进行UPDATE操作。
  3. 尽量减少使用唯一键,可以的话从业务层面保证。

总结

MySQL锁这块知识真的很多,需要我们平时不断思考和积累。本次主要介绍当线上遇到死锁的情况,我们怎样去定位,排查和分析。通过SHOW ENGINE INNODB STATUS\G查看最后一次死锁情况,找出导致死锁的SQL语句,再分析具体的死锁原因,比如持有什么锁,等待什么锁。最后验证修复死锁问题。