mysql - Mysql Gap-lock/Next-key 锁的规则

Question

我不确定为什么会发生以下行为。我认为它必须与间隙锁定/下一个键锁定有关。该文件提到了它们，但解释并不详细。

-- isolation level is Repeatable Read
-- create the table
create table t (id int primary key auto_increment, COL1 int, key idx_a(COL1));
insert into t (COL1) values(5), (10), (11), (13), (20);
select * from t;
----   ----
 id  |  COL1
----   ----
 1   |  5
 2   |  10
 3   |  11
 4   |  13
 5   |  20

-- in transaction 1
select * from t where COL1 = 13 for update;
-- in transaction 2
insert into t (COL1) values(10); -- success
insert into t (COL1) values(11); -- blocks
insert into t (COL1) values(12); -- blocks
.
.
insert into t (COL1) values(19); -- blocks
insert into t (COL1) values(20); -- success
-- in transaction 3
update t set COL1 = 11 where COL1 = 10; -- success
update t set COL1 = 12 where COL1 = 10; -- blocks
.
.
update t set COL1 = 20 where COL1 = 10; -- blocks
update t set a = 21 where a = 10; -- success

所以它看起来：

1. 为 COL1 锁定 INSERT，其值为 [11, 20)（11 到 20，不包括 20）

2. UPDATE 锁定 COL1，值为 (11, 20] (11 到 20，排除 11)

我想知道为什么 MySQL 会这样？这种锁定的一般规则是什么？

标签： mysqllocking

Answer 1

select * from t where COL1 = 13 for update; 

此 sql 子句将锁定范围：

((11,3), (13,4)]
((13,4), (20,5)]

所以，我将在您的交易 2 和 3 中解释结果

insert into t (COL1) values(10); -- success because (10, 6) not in the gap range.

insert into t (COL1) values(11); -- block   because (11, 7) in the gap range.

insert into t (COL1) values(12); -- block   because (12, 8) in the gap range.

insert into t (COL1) values(19); -- block   because (19, 9) in the gap range.

insert into t (COL1) values(20); -- success because (20, 10) not in the gap range.

update t set COL1 = 11 where COL1 = 10; --success because (11,2) not in the gap range.

update t set COL1 = 12 where COL1 = 10; -- blocks because (12,2) in the gap range.

update t set COL1 = 20 where COL1 = 10; -- blocks because (20,2) in the gap range.

update t set COL1 = 21 where COL1 = 10; -- success because (21,2) not in the gap range.