MySQL异常锁冲突一例

背景

MySQL版本:5.7, 隔离级别:RR

测试表结构:

CREATE TABLE `sbtest1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`k` int(11) NOT NULL DEFAULT ‘0’,

`c` char(120) NOT NULL DEFAULT ”,

`pad` char(60) NOT NULL DEFAULT ”,

PRIMARY KEY (`id`),

) ENGINE=InnoDB

标准的sysbench测试表,由于二级索引跟本次分析无关,所以没有创建。

整个表大概有50w行记录,表中的数据记录如下:

id k c pad
100 250731 xxx xxx
101 251240 xxx xxx
150 249472 xxx xxx
151 251323 xxx xxx

从上述表中可以看到id为101~150之间的记录不存在,其他记录与本次分析无关,故本例中没有给出,k、c、pad上述3列的值与相应的锁分析没有关系,用一般值替换。

开启两个session分别执行以下SQL:

session1:begin; select * from sbtest1 where id=150 for update;

session2: insert into sbtest1 values(110, 234534, ‘xxxx’,’xxxx’);

上述两条SQL语句,在执行session2时,会提示:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction。

说明session2在执行的时候出现了锁冲突,根据MySQL一般加锁规则,分析了下上述两条SQL语句各自的加锁行为:

session1:for update当前读,由于id=150的记录存在,对该记录加X锁

session2:插入,需要对插入的区间加插入意向锁,区间为(101, 150)

根据MySQL加锁原理,session1与session2上的两个加锁行为是可以兼容的,但本例中却发生了锁冲突,更奇葩的是,在复现该问题时,即使把所有数据导出后再导入到一个新的实例,同样执行session1 & session2的操作,在新的实例中,两个session没有发生冲突,也就是说同一份数据,在不同环境下的加锁行为可能是不一样的,非常诡异!!

分析

本着事实为依据的态度,我们来分析下该问题到底什么原因?

提示的是session2锁超时,那就看下到底哪把锁发生了冲突,查看的方式有两种:

  1. 通过在锁冲突时执行show engine innodb status来观察transaction的信息
  2. 通过在锁冲突时查询information_schema.INNODB_LOCKS来查看锁冲突信息

我们通过第二种方法来看下锁冲突时information_schema.INNODB_LOCKS中的信息如下:

mysql> select * from information_schema.INNODB_LOCKS;
+----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+------------------------+
| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table       | lock_index | lock_space | lock_page | lock_rec | lock_data              |
+----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+------------------------+
| 2388:29:2772:1 | 2388        | X         | RECORD    | `mydb`.`sbtest1` | PRIMARY    |         29 |      2772 |        1 | supremum pseudo-record |
| 2387:29:2772:1 | 2387        | X         | RECORD    | `mydb`.`sbtest1` | PRIMARY    |         29 |      2772 |        1 | supremum pseudo-record |
+----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+------------------------+

 

这个信息不看还好,一看更迷糊了,lock_data是:supremum pseudo-record,也就是说,两个session的语句在页的最大虚行(supremum)上发生了锁冲突,这两个SQL跟supremum又有什么关系???

再分析

其实到这里,笔者有个大胆的假设:

上述异常加锁行为跟具体的行存储的位置有关,被加X锁的记录可能处于页的边缘,而MySQL对于行加锁过程的特殊处理,导致supremum同时也被加了锁。

如果该假设真的成立,意味着即使两个表具备相同的数据,由于记录存储的物理位置的关系,导致两个表执行相同SQL时,加锁的行为也不同!!不经想起LOGICAL_CLOCK模式的并行复制:在主上能够在同一个group中提交的事务在从上是可以并行的??

有了上面的假设,其实我们就可以构造具体的测试场景:

  1. 创建一张具有主键的表
  2. 找到任意一张页面上的第一条记录
  3. 删除该条记录前面若干条记录
  4. 根据上面的分析,对该条记录加X锁,然后在区间中插入新的数据。

上面4个步骤中,比较难的是第2步,找到一张页面上的第一条记录,这个该如何操作?

再看一眼上面INNODB_LOCKS的信息,注意lock_page列就表示被锁的页面(通过show engine innodb status也能获得类似的信息),既然从这些信息中可以找到加锁的页面,我们就可以通过构造锁冲突来找到这个页面上的第一行记录:

  1. 准备两个session,同时执行: begin; select * from sbtest1 where id=xxx for update
  2. 通过INNODB_LOCKS表查找到相应的lock_page页面,记录相应的page值,比如:1234
  3. id依次递减,重复1,2操作,直到找到一条lock_page为1233的记录,这样就说明此时id+1这条记录处于页1234的第一条记录。

当然上面第3步还可以用二分法减少重复次数,找到页上的第一条记录了,后面我们需要做的就是删除上述得到的记录前面的若干条记录,进行测试即可。经过测试,果然验证了上面的假设。

源码验证

能手动构造测试场景,离成功也就只有一步之遥。只要从代码上验证一下session1当记录处于页面的第一条记录时的加锁逻辑,就可以解释整个问题,即验证:当一条记录处于页面的第一条记录时,如果需要对该记录加X锁,那么该记录所在页面的前一张页面上的supremum也同时会被加X锁。整个代码调试过程也比较简单,跟踪下主键搜索路径即可,这里就不一一说明,简单说下主键加锁的逻辑,比如我们有如下一棵由主键构造的索引树:

index

MySQL内部使用cursor方式进行索引搜索,给定一棵索引树,都是从root页开始(spaceid,3),聚簇索引页的根节点页号始终为3,space_id根据不同的表具有不同的space_id,搜索方式:

  1. 从根节点开始,采用二分法搜索slot段,每个slot就相当于一个记录的集合,系统用slot来加速记录的定位
  2. 找到相应的slot后,在该slot的记录集合中,采用二分法搜索指定的键值。
  3. 根据键值大小判断需要搜索的子树,然后在该节点上通过二分法分别搜索slot和记录集合。
  4. 根据找到的节点选择叶子节点页进行最后的记录搜索。

比如我们现在要找id=27的节点所在位置:

  1. 先从根节点上判断id=27大于1小于50,所以搜索左边子树
  2. 根据左边子树判断id=27处于20~35之间,选择值为20的node继续往下搜索。
  3. 值为20的node的子节点是第2张页面,在该页面上搜索到27然后加X锁。

上面整个过程即为我们一般理解的加锁过程,结果也是对id=27的记录加了X锁,下面我们来看一种特殊情况,我们要对id=60的记录加锁。

  1. 同样先搜索根节点,id=60大于50,搜索右边子树
  2. id=60位于50~60之间,这里就需要注意了,MySQL对于这种情况并没有特殊处理,所以按照一般逻辑直接搜索id=50节点对应的叶子节点,也就是第4张页。
  3. 然后发现该页上没有该条记录,那怎么处理?这时候,MySQL为了防止幻读,在该页的最大行supremum上加上了Next-key锁(如果不加这个锁,在58后面插入59将会被允许,造成MySQL两次读取数据不一致,产生幻读),保证58~60这个区间不会被插入数据,然后切换到第5页进行扫描,找到id=60的记录,然后加X锁。

所以对于上述情况,id=60记录在搜索时其实加了两把锁,本身记录的X锁和上张页supremum上的Next-Key锁。

再来看下,同样是页的第一条记录,如果我们要找的是id=51,加锁行为是怎么样的? id=51位于50~60之间,所以从第4张页开始查找,搜索id=51的记录没有跨页,所以只加记录锁即可(出现第一条记录与其父节点键值不一致主要可能是前面删除了id=50的记录)。最后的代码分析也验证了我们的猜测,最后结论:

如果一条数据位于页的第一条记录位置,并且与其父节点的键值相同,系统在对该条记录加锁时,为防止幻读,会对前一张页上的supremum也加锁。

最后来讨论下:既然实现上对supremum加了锁,那么该把锁是否有必要?个人认为该锁是MySQL索引搜索规则上的缺陷引入的,只要修正索引搜索上的问题,该锁自然就不会被添加,修正方式:当需要查找的id正好与索引节点的键值相同时,不走前节点进行搜索,直接走相同值节点搜索即可。

影响

最后来谈一谈该问题带来的影响,其实该问题在以前所有的MySQL版本中都存在(5.5,5.6,5.7都已经验证),个人认为:记录的加锁行为与物理存储有关,这个是绝对不允许的,这会带来一些列的问题。因为我们无法保证多个副本在具有相同的数据的情况下,各个副本中数据物理存储上也是一致的,比如某个副本启用了压缩,或者页大小配置与主库不同等等。当然一般碰到这种情况,重试下基本就可以了,LOGICAL_CLOCK的并行复制可能会受到一些影响。目前已知比较严重的问题是在存在XA事务的情况下,主从结构的Slave可能会复制出错,比如:

session1: xa start ‘aaa’; delete from sbtest1 where id=12345; xa end ‘aaa’; xa prepare ‘aaa’;

session2: xa start ‘bbb’; insert into sbtest1 values(12344, 0,’xx’,’xx’); xa end ‘bbb’; xa prepare ‘bbb’;

session1: xa commit ‘aaa’

session2: xa commit ‘bbb’

在MySQL 5.7中,xa prepare日志可以被记录到Binlog中,如果上面的两个事务,在master上没有冲突,但是在slave上由于id=12345记录的位置问题发生冲突,session2的insert会一直报错,导致复制中断。

发表评论

电子邮件地址不会被公开。 必填项已用*标注