68 mysql 的临键锁

前言

我们这里来说的就是我们在 mysql 这边常见的一种锁, 行临键锁

虽然在平时我们用到的不是很多, 我们这里主要是讲一下它的主要的触发的场景

行临键锁等价于行锁 + 间隙锁, 行间隙锁是一个左开右开的区间, 行临键锁是一个左开右闭的区间

但是它和行锁的差异仅仅在于 mode 的标记, 行锁这边是 LOCK_NOT_GAP + LOCK_X, 行临键锁这边是 LOCK_ORDINARY + LOCK_X

我们这里测试表结构如下

CREATE TABLE `tz_test_04` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `field1` varchar(128) DEFAULT NULL,
  `field2` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `field_1_2` (`field1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

测试数据如下, 之所以留下一些区间是为了产生 “间隙”

添加临键锁

这里的整体流程, 以及相关上下文基本上都和添加行锁类似

仅仅是在不同的语境中, 一部分语境使用的是行锁, 一部分语境使用的行临键锁

我们来看一下两者的选择的区别

首先是默认值, 如果是空间索引或者已提交读隔离级别以下或者根据查询条件唯一定位记录等等情况默认值是行锁, 否则是临键锁

另外就是一个特殊的情况下使用使用行锁, 比如在 ”id >= 100” 的条件下, 匹配到了 “id = 100” 的记录, 则只在该记录上面添加行锁

是使用行锁还是临键锁的关键就是查询结果是否唯一

基于主键/唯一索引的查询

1. select * from tz_test_04 where id = 5 for update;

根据主键查询的一条存在的记录

可以看到添加的是行排他锁, 因为这个查询从理论上来说是可以最多定位到一条记录

2. select * from tz_test_04 where id = 7 for update;

根据主键查询的一条不存在的记录

可以看到添加的是行间隙排他锁

3. select * from tz_test_04 where id <> 5 for update;

不等于查询是转化为了两个区间查询, 这两个我们在 >, < 的地方查看具体的加锁处理

select * from tz_test_04 where id < 5 for update;

select * from tz_test_04 where id > 5 for update;

第一个查询区间

第二个查询区间

4.select * from tz_test_04 where id > 5 for update;
5.select * from tz_test_04 where id >= 5 for update;

因为是非唯一查询, 在扫描过的记录上面增加临键锁

sql, 会扫描 id=10, supremum 两条记录, 都加上临键锁, 锁定的区间是 (5, 正无穷]
sql, 会扫描 id=5, id=10, supremum 三条记录, id=5的记录增加行锁, 另外两条都加上临键锁, 锁定的区间是 [5, 正无穷]

select * from tz_test_04 where id >= 5 for update; 的 sql, 在 id=5 的记录上面增加行锁的处理如下

从 rec + 0x11, 可以判断当前记录是 id=5, 然后从 mode, gap_mode 可以判断出当前是在该记录上面增加的行排他锁

对于如下五条 sql, 第一条都可以正常执行

第二条, 对于限定 id>5 的 sql, 这条 sql 会正常执行, 报错主键重复, 对于限定 id>=5 的 sql, 这条 sql 会阻塞

对于后面三条 sql, 都会阻塞

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (4, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (5, 'field8', '8');

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (10, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field8', '8');

6.select * from tz_test_04 where id < 5 for update;
7.select * from tz_test_04 where id <= 5 for update;

这个和上面同理, 我们这里直接说结论了

因为是非唯一查询, 在扫描过的记录上面增加临键锁

第一条 sql, 会扫描 id=1, id=5 两条记录, 都加上临键锁, 锁定的区间是 (负无穷, 5]

第二条 sql, 会扫描 id=1, id=5, id=10 三条记录, 都加上临键锁, 锁定的区间是 (负无穷, 10]

对于如下五条 sql, 第一条, 第二条都会阻塞

第三条, 第四条对于 “id<5” 可以正常执行, 对于 “id<=5” 会阻塞

对于第五条 sql, 都可以正常执行

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (4, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (5, 'field8', '8');

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (10, 'field8', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field8', '8');

基于普通索引的查询

这里来看一下以上的情况对于普通索引上面查询的执行情况

1. select * from tz_test_04 where field1 = 'field5' for update;

根据索引查询的存在的记录

这个会增加三个锁, 会在 field1=’field5’ 上面增加一个临键锁, 在具体的数据行记录上面增加行排他锁, 在下一个索引记录上面增加一个间隙锁

所以在 field1 索引上面, 锁定的是 (field1, field9), 在 id=5 数据行上面加的行排他锁

但是因为我们这里的普通索引是可能重复的, 因此具体索引记录是添加在第一个 field9 之前, 还是最后一个 field9 之后, 这个取决于数据库的实现

但是实际锁定的区间还取决于带插入的索引记录在整体记录的排序

索引 field1=’field5’ 上面增加临键锁

id=5 的数据记录上面增加行锁

索引 field1=’field5’ 上面增加间隙锁, 但是这里实际的功能是等价于一个临键锁

对于如下四条 sql, 第一条, 第二条都会阻塞

第三条会阻塞, 第四条不会阻塞

然后第五条不会阻塞

和第四条的差异在于, 数据库中索引记录的排序是 (field1, id), 我们锁定的记录是 (field9, 10), 第三条 sql 插入的记录是 (field9, 8) 是排在锁定记录之前的, 还在锁定的逻辑区间, 因此算冲突

插入的记录是 (field9, 15) 是排在锁定记录之后的, 不在锁定的逻辑区间, 可以正常处理业务

因此确定索引锁定的区间是索引+聚簇索引来确定的

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field1', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (9, 'fielda', '8');

2. select * from tz_test_04 where field1 = 'field7' for update;

根据索引查询的不存在的记录

可以看到添加的是行间隙排他锁, 这里的 gap_mode 为 LOCK_GAP

逻辑上锁定的区间是索引字段 field1 的区间 (field5, field9), 但是实际锁定的区间还取决于带插入的索引记录在整体记录的排序

对于如下五条 sql, 第一条, 第二条不会阻塞

第三条, 第四条会阻塞

第五条, 第六条不会阻塞

具体的原因就和上面一致, 确定索引锁定的区间是索引+聚簇索引来确定的

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field1', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (2, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (9, 'fielda', '8');

3. select * from tz_test_04 where field1 <> 'field5' for update;

不等于查询是转化为了全表查询或者两个区间查询, 这两个我们在 >, < 的地方查看具体的加锁处理

如果是全表查询则是在扫描的记录上面增加行临键锁

我们这里的实际情况是进行了全表的扫描, mysql 认为这样开销较小一些

4. select * from tz_test_04 where field1 > 'field5' for update;
5. select * from tz_test_04 where field1 >= 'field5' for update;

第一条 sql 会扫描 field1='field9', supremum 两条记录, 都加上临键锁, 锁定的区间是 ((field5,5), 正无穷], 并会在 id=10 的数据记录上面增加行排他锁

第二条 sql 会扫描 field1='field5', field1='field9', supremum 三条记录, 都加上临键锁, 锁定的区间是 ((field1,1), 正无穷], 并会在 id=5, id=10 的数据记录上面增加行排他锁

对于如下六条 sql

对于 field1>’field5’ 前面两条可以正常执行, 后面四条会阻塞

对于 field1>=’field5’ 这里的六条都会阻塞

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field1', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (2, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (9, 'fielda', '8');

6. select * from tz_test_04 where field1 < 'field5' for update;
7. select * from tz_test_04 where field1 <= 'field5' for update;

第一条 sql 会扫描 field1='field1', field1='field5' 两条记录, 都加上临键锁, 锁定的区间是 (负无穷, (field5,5)], 并会在 id=1 的数据记录上面增加行排他锁

第二条 sql 会扫描 field1='field1', field1='field5', field1='field9' 三条记录, 都加上临键锁, 锁定的区间是 (负无穷, (field9,9)], 并会在 id=1, id=5 的数据记录上面增加行排他锁

对于如下六条 sql

对于 field1<’field5’ 前面两条会阻塞, 后面四条会正常执行

对于 field1<=’field5’ 前面四条会阻塞, 后面两条正常执行

INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field1', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (2, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field5', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (8, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (15, 'field9', '8');
INSERT INTO `test_02`.`tz_test_04`(`id`, `field1`, `field2`) VALUES (9, 'fielda', '8');