• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL InnoDB RR隔离级别下INSERT…SELECT对SELECT表加锁模型和死锁案列

MySQL FAQ 彭东稳 6年前 (2018-06-22) 23220次浏览 已收录 0个评论

最近有网友遇到了在RR隔离级别下insert A select B where B.COL=** 由于select表也就是B表引发的死锁的问题。分析死锁日志后,笔者进行模拟重现了这位网友遇到了2种场景并且在本文中进行详细的描述。

  • 本文使用版本Percona 5.7.14修改版,能够打印出事务所有的行锁信息结构链(不包含隐含锁)
  • 本文中的测试是在RR隔离级别下完成的,RC不存在这样的问题
  • 笔者对源码的理解有限,如有错误请指正
  • 本文使用了自制工具innblock(http://pan.baidu.com/s/1qYnyVWo)和bcview(http://pan.baidu.com/s/1num76RJ),前者用于扫描块结构,后者用于更加方便的查看二进制文件信息获取地址

一、基本概念

在开始正文之前我打算介绍一下一些基本概念,特别是锁模型和兼容矩阵会对本文的阅读有相当大的帮助。

1. InnoDB Lock模型

[LOCK_ORDINARY[next_key_lock]:]

默认是LOCK_ORDINARY即普通的next_key_lock,锁住行及以前的间隙。

[LOCK_GAP:]

间隙锁,锁住行以前的间隙,不锁住本行。

[LOCK_REC_NOT_GAP:]

行锁,锁住行而不锁住任何间隙。

[LOCK_INSERT_INTENTION:]

插入意向锁,如果插入的记录在某个已经锁定的间隙内为这个锁。

2. InnoDB Lock兼容矩阵

3. infimum和supremum

一个page中包含这两个伪记录。页中所有的行未删除(或删除未purge)的行逻辑上都连接到这两个虚拟列之间,表现为一个逻辑链表数据结构,其中supremum伪记录的锁始终为next_key_lock。

4. heap no

heap no 存储在 fixed_extrasize 中。heap no 为物理存储填充的序号,页的空闲空间挂载在 page free 链表中(头插法)可以重用,但是重用此heap no不变,如果一直是 insert 则 heap no 不断增加,并不是按照 ROWID(主键) 排序的逻辑链表顺序,而是物理填充顺序。

5. n bits

和这个page相关的锁位图的大小,每一行记录都有1 bit的位图信息与其对应,用来表示是否加锁,并且始终预留64bit。例如我的表有9条数据,同时包含infimum和supremum虚拟记录即 64+9+2 bits,即75bits但是必须被8整除向上取整为一个字节,结果也就是就是80 bits。注意不管是否加锁每行都会对应一bit的位图。

6. lock struct

这是LocK的内存结构体,源码中用lock_t表示其可以包含。

一般来说innodb上锁都会对表级加上IX,这占用一个结构体。然后分别对二级索引和主键进行加锁,每一个BlocK会占用这样一个结构体。

7. row lock

这个信息描述了当前事务加锁的行数,他是所有lock struct结构体中排除table lock以外所有加锁记录的总和,并且包含了infimum和supremum伪列。

8. 逐步加锁

如果细心的朋友应该会发现在 show engine 中事务信息中的row lock在对大量行进行加锁的时候会不断的增加,因为加行锁最终会调用lock_rec_lock逐行加锁,这也会增加了大数据量加锁的触发死锁的可能性。

二、Innodb层对insert…select 中select表的加锁模式

RR隔离级别下insert A select B where B.COL=*,innodb层会对B表满足条件的数据进行加锁,但是RC模式下B表记录不会加任何innodb层的锁,表现如下:

1. 如果B.COL有二级(非唯一),并且执行计划使用到了“非using index”

  • B表二级索引对选中记录加上LOCK_S|LOCK_ORDINARY[next_key_lock],并且对下一条记录加上LOCK_S|LOCK_GAP
  • B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

2. 如果B.COL有二级(唯一),并且执行计划使用到了“非using index”

  • B表二级索引对选中记录加上LOCK_S|LOCK_REC_NOT_GAP
  • B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

3. 如果B.COL没有二级索引

  • 对整个B表上的所有记录加上LOCK_S|LOCK_ORDINARY[next_key_lock]

三、Innodb层对insert…select中select表的加锁测试

下面我们分别对其进行测试和打印输出:

1. 如果B.COL有二级(非唯一),并且执行计划使用到了“非using index”

执行计划:

执行语句:

观察结果:

二级索引对记录加上LOCK_S|LOCK_ORDINARY[next_key_lock]

PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

对二级索引下一条记录加上LOCK_S|LOCK_GAP

如图红色部分都是需要锁定的记录

MySQL InnoDB RR隔离级别下INSERT...SELECT对SELECT表加锁模型和死锁案列

2. 如果B.COL有二级(唯一),并且执行计划使用到了“非using index”

使用语句:

执行计划:

执行语句:

观察输出:

B表二级索引对选中记录加上LOCK_S|LOCK_REC_NOT_GAP

B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

如图红色部分都是需要锁定的记录

MySQL InnoDB RR隔离级别下INSERT...SELECT对SELECT表加锁模型和死锁案列

3. 如果B.COL没有二级索引

使用语句:

执行计划:

执行语句:

观察输出:

如图红色部分都是需要锁定的记录

MySQL InnoDB RR隔离级别下INSERT...SELECT对SELECT表加锁模型和死锁案列

四、insert…select由于select查询表引起的死锁

有了上面的理论,我们知道在RR隔离级别下insert…select会对select符合条件的数据加上LOCK_S锁,我曾经总结过出现死锁的条件:

1. 至少2个独立的线程(会话)

2. 单位操作中包含多个相对独立的加锁步骤,有一定的时间差

3. 多个线程(会话)之间加锁对象必须有相互等待的情况发生,并且等待出现环状。

由于存在对select符合条件的数据加上LOCK_S锁的情况,RR模式下insert…select出现死锁的概率无疑更加高,我通过测试模拟出这种情况,严格意义上是相同的语句在高并发情况下表现为两种死锁情况。

测试脚本:

语句都是一样的:

TX1 TX2
begin;
update b set name2=’test’ where id=2999;
insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999);
update b set name2=’test’ where id=999;

但是在高并发下相同的语句却表现出不同的死锁情况,现在进行分析:

情况一

  • TX1:执行update将表b主键id=2999的记录加上LOCK_X。
  • TX2:执行insert…select语句b表上的记录(996,997,998,999,2995,2996,2997,2998,2999)会申请加上LOCK_S,
    但是id=2999已经加上LOCK_X,显然不能获得只能等待。
  • TX1:执行update需要获得表b主键id=999的LOCK_X显然这个记录已经被TX2加锁LOCK_S,只能等待,触发死锁检测。

如下图红色记录为不能获得锁的记录:

MySQL InnoDB RR隔离级别下INSERT...SELECT对SELECT表加锁模型和死锁案列

情况二

这种情况比较极端只能在高并发上出现

  • TX1:执行update将表b主键id=2999的记录加上LOCK_X。
  • TX2:执行insert…select语句b表上的记录(996,997,998,999,2995,2996,2997,2998,2999)会申请加上LOCK_S,因为上锁是有一个逐步加锁的过程,假设此时加锁到2997前那么TX2并不会等待。
  • TX1:执行update需要获得表b主键id=999的LOCK_X,显然这个记录已经被TX2加锁LOCK_S,只能等待。
  • TX2:继续加锁LOCK_S 2997、2998、2999 发现2999已经被TX1加锁LOCK_X,只能等待,触发死锁检测。

如下图红色记录为不能获得锁的记录:

MySQL InnoDB RR隔离级别下INSERT...SELECT对SELECT表加锁模型和死锁案列

五、源码修改和参数增加

情况二的测试需要在高并发下才会出现,因为insert…select语句是一条语句很难人为控制,也就是很让他在特定条件下停止。但是为了能够模拟出这种情况笔者对innodb增加了4个参数如下,为了方便识别我都加上了自己的名字的拼音:

默认情况都是0,即不启用。他们的意思如下:

  • innodb_gaopeng_sl_heap_no:记录所在的heap no
  • innodb_gaopeng_sl_ind_id:记录所在的index_id
  • innodb_gaopeng_sl_page_no:记录所在的page_no
  • innodb_gaopeng_sl_time:睡眠多少秒

有了index_id、page_no、heap no就能唯一限定一条数据了,并且睡眠时间也是可以人为指定的。

并且在源码lock_rec_lock 开头增加如下代码:


这样一旦判定为符合条件的记录,本条记录的加锁钱便会休眠指定的秒,如果我们拟定在LOCK_S:id=2997之前睡眠30秒,那么情况二能够必定发生如下图:

MySQL InnoDB RR隔离级别下INSERT...SELECT对SELECT表加锁模型和死锁案列

六、实际测试

情况一

TX1 TX2
begin;
update b set name2=’test’ where id=2999;对id:2999加LOCK_X锁
insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999);对id:996,997,998,999,2995,2996,2997,2998加LOCK_S锁,但是对id:2999加LOCK_S锁时发现已经加LOCK_X锁,需等待
update b set name2=’test’ where id=999;对id:999加LOCK_X锁,但是发现已经加LOCK_S锁,需等待,触发死锁检测
TX1触发死锁,TX1在权重判定下回滚

死锁报错语句:

死锁日志:

信息提取如下:

情况二

如上我们拟定在语句

对b表记录加锁时在2997加锁前停顿30秒,那么我就需要找到b表主键2997的index_id、page_no、heap_no三个信息,这里使用到我的innblock工具。

因为为顺序插入,那么2997必定到page 18中然后如下:

因为为顺序插入heap_no 84就是id为2997的记录。我们使用另外一个工具bcview进行验证。

当然0Xbb5就是2997,因此设置参数为:

那么情况二执行顺序如下:

TX1 TX2
begin;
update b set name2=’test’ where id=2999; 对id:2999加LOCK_X锁
insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999);对id:在加锁到996,997,998,999,2995,2996加LOCK_S锁,在对id:2997加锁前睡眠30秒,为下面的update语句腾出时间)
update b set name2=’test’ where id=999;对id:999加LOCK_X锁等待但发现已经加LOCK_S锁,需等待
醒来后继续对2997、2998、2999加LOCK_S锁,但是发现id:2999已经加LOCK_X锁,需等待,触发死锁检测
TX1权重回滚

死锁报错语句:

死锁日志:

信息提取如下:

我们通过死锁日志明显的看出同样的语句报出来的死锁信息却不一样,在高并发下相同语句,两种死锁场景都是可能发生的。

七、总结

分析死锁一般要从死锁日志中获取如下信息

1、加锁发生在主键还是辅助索引

2、加锁的模式是什么

3、是单行还是多行加锁

4、触发死锁事务最后的语句

5、死锁信息中事务顺序是怎么样的

在重现的时候,必须要做到和线上死锁信息完全匹配那么这个死锁场景才叫测试成功了,从这个例子我们就发现,同样的语句产生的死锁信息却不一样,我们当然就要按照不通的场景去考虑,本文中的情况2比较复杂一般只是在高并发先出现,测试也相对麻烦,本文通过修改源码的方式进行测试的,否则很难重现。找到原因后就需要采取必要的措施,比如本文中的例子需要考虑:

  • 对insert…select中select表的修改是否及时提交。
  • insert…select是否可以用其他方式代替。这种语句在自增锁上也存在一定风险。
  • 是否考虑使用RC隔离级别,在RC隔离级别下不存在对select表记录加锁的情况。

强调一点对于出现LOCK_S这样的锁最好深入分析,因为这种锁并不多见。

<转载>

https://www.jianshu.com/p/f08bf6f67410


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!