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 锁超时,那就看下到底哪把锁发生了冲突,查看的方式有两种:
我们通过第二种方法来看下锁冲突时 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 中提交的事务在从上是可以并行的??
有了上面的假设,其实我们就可以构造具体的测试场景:
上面 4 个步骤中,比较难的是第 2 步,找到一张页面上的第一条记录,这个该如何操作?
再看一眼上面 INNODB_LOCKS 的信息,注意 lock_page 列就表示被锁的页面 (通过 show engine innodb status 也能获得类似的信息),既然从这些信息中可以找到加锁的页面,我们就可以通过构造锁冲突来找到这个页面上的第一行记录:
当然上面第 3 步还可以用二分法减少重复次数,找到页上的第一条记录了,后面我们需要做的就是删除上述得到的记录前面的若干条记录,进行测试即可。经过测试,果然验证了上面的假设。
能手动构造测试场景,离成功也就只有一步之遥。只要从代码上验证一下 session1 当记录处于页面的第一条记录时的加锁逻辑,就可以解释整个问题,即验证: 当一条记录处于页面的第一条记录时,如果需要对该记录加 X 锁,那么该记录所在页面的前一张页面上的 supremum 也同时会被加 X 锁。 整个代码调试过程也比较简单,跟踪下主键搜索路径即可,这里就不一一说明,简单说下主键加锁的逻辑,比如我们有如下一棵由主键构造的索引树:
MySQL 内部使用 cursor 方式进行索引搜索,给定一棵索引树,都是从 root 页开始 (spaceid,3),聚簇索引页的根节点页号始终为 3,space_id 根据不同的表具有不同的 space_id,搜索方式:
比如我们现在要找 id=27 的节点所在位置:
上面整个过程即为我们一般理解的加锁过程,结果也是对 id=27 的记录加了 X 锁,下面我们来看一种特殊情况,我们要对 id=60 的记录加锁。
所以对于上述情况,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 会一直报错,导致复制中断。
来源: http://www.tuicool.com/articles/7jQjIjJ