Rules of Mysql Gap-lock/Next-key Locks

Question

I am not sure why the following behavior happens. I think it has to be related to gap-lock/next-key lock. The document mentions them but the explanation is not detailed.

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

So it seems:

1. INSERT is locked for COL1 with value in [11, 20) (11 to 20, with 20 excluded)

2. UPDATE is locked for COL1 with value in (11, 20] (11 to 20, with 11 excluded)

I am wondering why MySQL behaves this way? And what is the rule in general for this kind of locking?

Answer 1

Under MySQL 5.7 InnoDB REPEATABLE READ
Because COL1 has a non-unque-index,
select * from t where COL1 = 13 for update;
The statement locks the cluster-index B+Tree record where id = 4, also locks the the COL1 index B+Tree where the record having COL1=10, and locks the COL1 index B+Tree from (k=11, id=3) to (k=20, id=5) with gap lock. 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);

But see this condition:
CREATE TABLE `t` ( `id` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_k` (`k`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into t values(2,2),(6,6); then insert:
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 if they all can insert where are these values location in k'B+Tree?

so you can see that (k=2, id=1) can insert, because it not in the gap, also value (k=6, id= 7), (k=2, id=3) and (k=6, id=5) can not insert because they are in the gap.

Answer 2

select * from t where COL1 = 13 for update; 

this sql clause will lock the range:

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

so, i will explain the result in your transaction 2 and 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. 

Answer 3

After executing

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;

The output of select * from performance_schema.data_locks is:

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

Transaction 1 is holding next-key lock ((11, 3), (13, 4)] and gap lock ((13, 4), (20, 5)).

insert into t (COL1) values(10) and insert into t (COL1) values(20) is equal to insert into t (COL1, id) values(10, ?) and ? must be greater than 5, so both (10, ?) and (20, ?) are not in ((11, 3), (13, 4)] or ((13, 4), (20, 5)), that's why they can succeed. insert into t (COL1) values(11) to insert into t (COL1) values(19), they are in ((11, 3), (13, 4)] or ((13, 4), (20, 5)), that's why they are blocked.

An update is like deletion and then insertion. update t set COL1 = 11 where COL1 = 10 will insert (11, 2), (11, 2) is not in ((11, 3), (13, 4)] or ((13, 4), (20, 5)), that's why it succeed. update t set COL1 = 12 where COL1 = 10 to update t set COL1 = 20 where COL1 = 10 will insert (?, 2) and ? is in [12, 20], so (?, 2) is in ((11, 3), (13, 4)] or ((13, 4), (20, 5)), that's why they are blocked. I think update t set a = 21 where a = 10 should be update t set COL1 = 21 where COL1 = 10, it will insert (21, 2), (21, 2) is not in ((11, 3), (13, 4)] or ((13, 4), (20, 5)), that's why it succeed.