how the gap lock work in the case of share lock mode and update?

Question

Preconditions：

mysql version: 5.7.31
isolation level: RR

the table building statement is shown below：

CREATE TABLE `lockt` (
  `id` int(11) NOT NULL,
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col1_ind` (`col1`),
  KEY `col2_ind` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

there is some data for testing:

INSERT INTO `lockt` (`id`, `col1`, `col2`)
VALUES
    (1,1,1),
    (2,2,3),
    (5,5,5),
    (6,6,9),
    (10,10,25),
    (123,123,8),
    (1007,10077,144),
    (1008,1008,220),
    (1019,1019,200),
    (1020,1020,201),
    (1111,1111,32),
    (1234,1234,33);

In the process of learning the gap lock in mysql, I encountered a case that made me difficult to understand:

in Transaction 1:

set autocommit=0;
begin;
select * from lockt;
select * from lockt where col2=25 lock in share mode;

and then,I start another Transaction:

set autocommit=0;
begin;
select * from lockt;
update lockt set col2=66666 where col2 > 33;

but I figure out the update statement was blocked.In my opinition,the SQL "select * from lockt where col2=25 lock in share mode" will apply for a share lock, and add gap lock on the range(9,25],(25,32],but the range (33,+∞] was out of theses range,why the second transaction was still blocked,that's beyond my expectation. I was confused why it behaves like this. Is there any special point on gap lock that I was misunderstanding? anyone that can help me out of this query will be appreciated.

Answer 1

Adding some background on locks (and making this answer a bit longer than intended), it is important to understand that, generally speaking, MySQL will only consider locking rows/gaps that it looks at when executing the query.

All other rows/gaps are irrelevant: if a second query neither looks at the same rows/gaps, and would not make changes to those rows/gaps (e.g. by adding a row), it cannot change the outcome of the first query, nor can, whatever the first query does, change the outcome of the second query.

So to investigate locks, it is absolutely crucial to understand which rows MySQL looks at. (If locks are actually in conflict will depend on the query and isolation level, and is what you are trying to experiment with, but is not actually part of this answer, as it is not the reason for your confusion.)

So how does MySQL find your rows? MySQL can obviously use the index on col2. If MySQL uses an index to find the rows, it locks both in the index and the row itself (which technically means it locks a row in the primary key).

This is what you are expecting: MySQL finds the row with col2 = 25, locks it (in the index and the primary key), then should look for rows with col2 > 33, and looking for those using the index should not be in conflict.

And that is correct. If MySQL uses the index, there is no overlap. Confusingly, MySQL has a different way to execute your query: it can just go through the whole table and update the rows that fit your condition.

This can actually be faster (which is all the optimizer cares about), as using an index to find the rows is slower per row than to traverse the whole table. It's just a numbers game: at some point, it is faster to just read all rows (at a higher speed per row) than to look up only the right rows using the index (at a lower speed per row).

And apparently, for col2 > 33, MySQL decided to do that. And since it did, the rows it looks at are now all rows. And this will conflict with the lock on col2 = 25 (which had been locked in the primary key using the index on col2`). This is not because of a gaplock (which you are trying to investigate), just a simple plain old locked row.

You can retry your query with a larger value, then MySQL might decide to use the index. You can check which index MySQL uses by running explain update lockt ..., and according to your comment, the critical value (with your specific data) seems to be col2 > 144. For this, the exectution plan should show that is uses the index on col2 (the value in the column key), while for col2 > 143, it should use the primary key.

You can actually force MySQL to use the index you want it to use (to lock as you expect it to lock) with an index hint like

update lockt force index (col2_ind) set col2=66666 where col2 > 33;

And to emphasize again: MySQL does not have to lock all rows and/or gaps it looks at, it can release locks if unused (e.g. if they are not updated), and not all locks conflict with all locks. Details about this will depend on the query and the isolation level and will allow MySQL for a wide range of actual locking behaviour.

So to answer your question in short: MySQL locks based on indexes, so if you experiment with locks, make sure you check the index.

Answer 2

Agree with @Solarflare

In session 1:lock col2 data row with a share lock. In session 2:execute udpate statement with condition col2>33 and query locked. And then we can use "show engine innodb status" to show lock details. Such as the following:

TRANSACTIONS
------------
Trx id counter 8182
Purge done for trx's n:o < 8177 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479682839888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479682837176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479682838984, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 8181, ACTIVE 45 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 25, OS thread handle 123145535516672, query id 3434 localhost root updating
update lockt set col2=111 where col2 >33
------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37 page no 3 n bits 96 index PRIMARY of table
 `demo`.`lockt` trx id 8181 **lock_mode X waiting**
Record lock, heap no 17 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000001fed; asc       ;;
 2: len 7; hex b3000001270110; asc     '  ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 80000019; asc     ;;

It's a X lock cause by update statement in session 2, not the query statement in session 1.

And also we can update data with col2 more than a larger value(col2 > 200), or update data with a small range(col2 > 32 and col2 < 36), to reduce the range of x lock.

    mysql> update lockt set col2=36 where col2 > 33 and col2 < 36;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 0  Changed: 0  Warnings: 0
    
    mysql> update lockt set col2=35 where col2 > 32 and col2 < 36;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> update lockt set col2=35 where col2 > 200;
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    
    mysql> update lockt set col2=35 where col2 > 33;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction