mysql gap lock/next key locks的规则

ohfgkhjo 于 2021-06-19 发布在 Mysql

关注(0)|答案(3)|浏览(397)

我不知道为什么会发生以下行为。我想这一定和空档锁/下一把钥匙锁有关。文件提到了他们，但解释不详细。

-- 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

看来：
值在[11，20]中的col1的insert被锁定（11到20，排除20）
值为（11，20）的列1的更新被锁定（11到20，排除11）
我想知道mysql为什么会这样？这种锁的一般规则是什么？

mysql locking

来源：https://stackoverflow.com/questions/52399319/rules-of-mysql-gap-lock-next-key-locks

3条答案

按热度按时间

iklwldmw1#

执行后

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);

-- transaction 1
start transaction;
select * from t where COL1 = 13 for update;

输出 select * from performance_schema.data_locks 是：

+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 140043377180872:1075:140043381460688   |                  2368 |        49 |      180 | test          | t           | NULL           | NULL              | NULL       |       140043381460688 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 140043377180872:14:5:5:140043381457776 |                  2368 |        49 |      180 | test          | t           | NULL           | NULL              | idx_a      |       140043381457776 | RECORD    | X             | GRANTED     | 13, 4     |
| INNODB | 140043377180872:14:4:5:140043381458120 |                  2368 |        49 |      180 | test          | t           | NULL           | NULL              | PRIMARY    |       140043381458120 | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
| INNODB | 140043377180872:14:5:6:140043381458464 |                  2368 |        49 |      180 | test          | t           | NULL           | NULL              | idx_a      |       140043381458464 | RECORD    | X,GAP         | GRANTED     | 20, 5     |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+

事务1持有下一个键锁（（11，3），（13，4）]和间隙锁（（13，4），（20，5））。 insert into t (COL1) values(10) 以及 insert into t (COL1) values(20) 等于 insert into t (COL1, id) values(10, ?) 以及 ? 必须大于5，所以（10，？）和（20，？）都不在（（11，3），（13，4）]或（（13，4），（20，5）），这就是它们可以成功的原因。 insert into t (COL1) values(11) 至 insert into t (COL1) values(19) ，它们位于（（11，3），（13，4）]或（（13，4），（20，5）），这就是它们被阻止的原因。
更新就像删除然后插入。 update t set COL1 = 11 where COL1 = 10 将插入（11，2），（11，2）不在（（11，3），（13，4）]或（（13，4），（20，5）），这就是它成功的原因。 update t set COL1 = 12 where COL1 = 10 至 update t set COL1 = 20 where COL1 = 10 将插入（？，2）和 ? 在[12，20]中，所以（？，2）在（（11，3），（13，4）]或（（13，4），（20，5）），这就是它们被阻塞的原因。我想 update t set a = 21 where a = 10 应该是 update t set COL1 = 21 where COL1 = 10 ，它将插入（21，2），（21，2）不在（（11，3），（13，4）]或（（13，4），（20，5）），这就是它成功的原因。

赞(0）回复(0）举报 2021-06-20

g52tjvyc2#

MySQL5.7下innodb可重复读取
因为col1有一个非空索引， select * from t where COL1 = 13 for update; 语句锁定id=4的集群索引b+树记录，也锁定col1=10的记录所在的col1索引b+树，并使用gap lock将col1索引b+树从（k=11，id=3）锁定到（k=20，id=5）。 insert into t (COL1) values(10); equals to : insert into t (id, COL1) values(6,10); gap lock insert into t (COL1) values(11); equals to : insert into t (id, COL1) values(7,11); gap lock insert into t (COL1) values(12); equals to : insert into t (id, COL1) values(8,12); gap lock insert into t (COL1) values(19); gap lock insert into t (COL1) values(20); equals to : insert into t (id, COL1) values(9,20); 但看看这个条件： CREATE TABLEt(idint(11) NOT NULL,kint(11) DEFAULT NULL, PRIMARY KEY (id), KEYidx_k(k) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 insert into t values(2,2),(6,6); 然后插入：
1, insert into t values (1,2); success 2, insert into t values (3,2); block 3, insert into t values (5,6); block 4, insert into t values (7,6); success 如果它们都能插入这些值在k'b+树中的位置在哪里？

所以你可以看到（k=2，id=1）可以插入，因为它不在间隙中，同样值（k=6，id=7），（k=2，id=3）和（k=6，id=5）不能插入，因为它们在间隙中。

赞(0）回复(0）举报 2021-06-20

y3bcpkx13#

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.

赞(0）回复(0）举报 2021-06-20

我来回答

mysql gap lock/next key locks的规则

3条答案

相关问题

热门标签

最新问答