记一次mysql线上死锁的问题（INSERT操作的加锁分析）

记一次mysql线上死锁的问题 INSERT操作的加锁分析前言insert 的加锁逻辑实验插入数据实验插入主键冲突实验两个事务插入主键冲突实验两个insert事务 因为主键冲突 发生死锁实验: 二级唯一索引冲突insert的加锁逻辑insert ...on duplicate key update 的加锁逻辑实验: 主键冲突 on duplicate key update实验: 二级索引冲突 on duplicate key update后记参考前言前一段时间线上的数据库巡检到有死锁告警一通排查分析发现是并发的INSERT … ON DUPLICATE KEY UPDATE造成的死锁问题。 业务逻辑中有使用上述语句进行批量的插入更新数据形成了操作系统中经典的问题线程A持有锁lock1并请求lock2; 线程B 持有锁lock2 并请求lock1解决方案教材中也给了最后使用了顺序加锁的方式来解决。问题虽然搞定了但是在排查的过程中对于mysql中INSERT … ON DUPLICATE KEY UPDATE语句的加锁逻辑还有些不太清晰这里做个实验、具体分析总结下希望可以便人便己。建表语句CREATE TABLEtest_table(idbigint NOT NULL AUTO_INCREMENT COMMENT主键,uk_columnint NOT NULL COMMENT唯一索引,columnint NOT NULL COMMENT普通列, PRIMARY KEY(id), UNIQUE KEYuk_column_key(uk_column))ENGINEInnoDB;数据库隔离级别为RR可重复读。insert 的加锁逻辑实验插入数据首先看下表中已有的数据。开启session, 插入一条数据查看锁信息可以发现其实正常的插入操作如果不发生冲突的话其实是不加锁的其实加了一个隐式锁下文会说到。实验插入主键冲突首先看下表中已有的数据。开启一个会话插入数据id16。发现报错和已有的主键发生唯一性冲突查看锁信息可以看到加由于和已有主键冲突这里加了一个共享的记录锁。为什么这里还要加一个共享的锁呢 因为在判断主键冲突的时候是需要加锁判断当前准备插入的这条数据是否和已有的数据有可能是并发事务产生的数据发生冲突的。比如说另一个事务正好删除了这条记录但是还没有提交此时当前事务也许就可以顺利插入。实验两个事务插入主键冲突首先看下表中已有的数据。开启会话session1begin一个事务tx1。session1(tx1):插入数据id7的数据。 然后查询相关的锁信息Empty set表示目前无锁。开启另一个会话session2, begin事务tx2。插入相同的主键数据id7此时tx2已经开始等待了。再次查看锁信息发现tx1(事务id为20365)已经加了一把REC_NOT_GAP的X锁独占锁锁住的数据记录id7,也就是之前插入的那条数据; tx2(事务id为20366) 正在等待一把 REC_NOT_GAP的S锁共享锁等待的记录id也是7。好分析一下上面的结果insert操作在不冲突的情况下加的是隐式锁这其实是通过 记录里加的trx_id 隐藏列来实现的。一般情况下隐式锁就像不加锁一样正常执行结束。就像上述第2步查询到系统中没有任何锁信息一样。当其他事务需要对相同的记录加锁时会首先帮助当前事务生成一个锁结构把隐式的锁变成显 示的然后自己再生成一个锁结构进入等待状态。 上述tx2 也想要插入一个id7的记录但是由于tx1已经插入了数据但是还没提交事务说明其还有可能发生回滚tx2只能加锁等待。实验两个insert事务 因为主键冲突 发生死锁进一步的我们可以分析道如果两个并发事务insert多个数据有可能会发生死锁。 再来做个实验开启会话session1(tx1), 插入id15的一条记录开启会话session2(tx2), 插入id16的一条记录tx1再来插入id16的数据发现已经阻塞住了到目前为止和实验一其实类似。4.最后 tx2 插入id15的数据发现也阻塞住了。由此形成了经典的死锁问题tx1和tx2都持有了一把锁而在等待对方的锁。如下图所示ps:上述实验需要关闭mysql的自动死锁检测因此如果在一个事务中批量插入数据尽量避免可能的并发场景不然一不小心可能就发生死锁。实验: 二级唯一索引冲突查看数据begin一个session, 插入uk_column101的一条数据发生二级索引的唯一主键冲突查看锁信息对于二级索引冲突来说这里加了两把锁。一个是二级索引的NextKey锁锁住的是数据uk_column 101, id 16 及其之前的数据。有没有些疑问这里为什么要加NextKey锁呢直接加行锁不行吗需要注意的是对于RC级别这里二级索引唯一键冲突加的也是NextKey锁这也是RC级别为数不多加NextKey锁的场景这里 RC 级别加 NextKey 锁不是为了防止幻读而是为了保持唯一性【13】。这个问题其实还是有些复杂的查阅了好多资料找到了一个略微有些道理的原因即使用普通的记录锁可能会导致最终的索引不满足唯一性【9】中有具体的 case如下其中记录锁(RK)、间隙锁(GK)、插入意向锁(IK)、Next-Key(NK)兼容性矩阵如下普通的S型记录锁和插入意向锁是兼容的但是S型NextKey锁和插入意向锁是冲突的。所以加上了S型NextKey 锁之后可以防止这种场景违反唯一性约束的问题。202604 这里再补充说明一下对于二级索引来说MInnoDB 的实现允许同一个键具有不同的主键存在多条标记为删除记录甚至是待删除还未提交如上面的 case。这样的话仅仅锁住一条已有的记录在二级索引中这条已有的记录可能已经被打了删除的标记 在存储上可能不是 即将插入的那条记录因为他们的主键是不一样的是不够的需要加间隙锁NextKey来保证从唯一性检查到最终插入期间所有重复记录之间的间隙都必须保持安全以防止其他插入操作【13】。202604 那么问题来了为什么主键冲突没有加NextKey锁呢可能主键有其他的防止冲突的方式具体详细的我也不知道了后续有深入了解后可以在补充上。 ╮(╯▽╰)╭除了上面说的第一把锁之外我们还可以看到上述的过程还给主键加了第二把锁。也就是锁类型为X的NextKey锁锁住的数据是 supremum pseudo-record 。这是因为对于插入二级索引数据来说插入的时候是分步的先插入聚簇索引树上的数据发现没有冲突因为插入的时候没有指定id采用自增的id17可以正常插入。但是插入二级索引的时候发现有冲突这个时候就需要把聚簇索引中已经插入id17的记录给回滚删除了。在这个过程中为了防止其他事务读写这条记录就会id17的那个隐式锁转换为排他的记录锁即XREC_NOT_GAP。这还没完因为要删除这条记录如果删除了之后锁就没有依附的位置了所以就会让它的下一条记录来继承这个锁并且继承的方式是GAP所以锁的形式就变成了X,GAP。这还没完因为下一条记录是supremum 记录Page中虚拟的最大记录就命中了一个规则所有 supremum 记录不管原来要加什么锁统一变成 Next-Key 锁。最终成为了X, Next-Key锁.insert的加锁逻辑好简单总结一下insert的加锁逻辑对于Msql中的insert操作来说如果正常插入不发生冲突的话也没有其他锁竞争那么一切顺利基本不会加锁有一个隐式的锁。如果发现插入的间隙有间隙锁那么会生成一个插入意向锁进行等待当获取到锁之后表示可以插入判断插入记录是否有唯一键约束如果有的话需要进行唯一性约束检查如果不存在相同的唯一键值则正常插入如果有唯一键冲突则需要加S锁根据不同情况有可能是记录锁或者NextKey锁进行进一步的检查记录是否被标记删除…插入记录并对记录加X锁ps: 上面总结的比较简单实际的情况要比上面的更复杂些。insert …on duplicate key update 的加锁逻辑实验: 主键冲突 on duplicate key update先查看数据begin一个session, 插入id16的一条主键冲突的语句当主键冲突时更新其他两个字段查看加锁信息可以看到当主键冲突时给聚簇索引加了一个X型的记录锁锁住的记录是id16.如果看懂了之前insert的实验这里应该不难理解。insert …on duplicate key update就是 在冲突的时候更新某些字段。 insert中如果主键冲突加的是S型的记录锁这里由于后续要进行更新操作因此直接变成了排他锁。实验: 二级索引冲突 on duplicate key update先查看数据begin一个session, 插入uk_column16的一条主键冲突的语句当主键冲突时更新column字段查看加锁信息这里加了三把锁row1和row3基本和实验【二级唯一索引冲突】加的锁类似只不是这里对二级索引加的是排他锁X(如row1所示)因为这里是on duplicate key update ,当二级索引发生冲突时会进行更新操作所以直接加了排他锁。不同的是这里row2还对主键加了一把排他的记录锁因为当更新操作主要是更新主键索引中的数据需要将聚簇索引中的id16的记录中column更新为1020. 也就是下图中的结果。后记Mysql 的锁还是比较复杂的上文所做的实验算是一个入门的demo, 便于理解一些不那么复杂场景中插入操作的加锁类型。 有一些复杂insert有时可能会加七八个锁这就需要对mysq的锁机制有更深的理解了有些我也搞不懂。参考【1】解决死锁之路 - 常见 SQL 语句的加锁分析【2】MySQL 核心模块揭秘 | 41 期 | insert on duplicate 加锁分析2【3】MySQL 核心模块揭秘 | 34 期 | RC 隔离级别插入记录唯一索引冲突加什么锁【4】MYSQL的社死----死锁【5】《Mysql是怎样运行的》【6】MySQLinsert时唯一索引冲突为什么会加next-key锁【7】Why MySQL InnoDB set an S or X Next-Key lock on the duplicate index record when a duplicate-key error occurs?【8】读 MySQL 源码再看 INSERT 加锁流程【9】MySQL · 内核分析 · InnoDB主键约束和唯一约束的实现分析【10】Gap Locking in Read Committed isolation level in Mysql【11】MySQL 核心模块揭秘 | 40 期 | insert on duplicate 加锁分析1【12】MySQL 核心模块揭秘 | 41 期 | insert on duplicate 加锁分析2【13】Deep Dive into InnoDB Locks