Gap Locking in Read Committed isolation level in Mysql

Question

MYSQL VERSION : 5.7.X
STORAGE ENGINE : Innodb

I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where even Read Committed has to use gap locking.

READ COMMITTED ..... For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

IMHO, only record lock is enough. Can anyone explain the scenario for Gap locking and why mysql does that?

Answer 1

Gap locks are necessary under the read committed (RC) isolation level to prevent potential integrity violations due to concurrent inserts -- this is what the documentation statement

Gap locking is only used for foreign-key constraint checking and duplicate-key checking.

implies.

Suppose transaction T1, with the RC isolation, updates the value of a column that is part of a unique key (index). Clearly this is only possible if the new key value does not already exist. Suppose also transaction T2, also with the RC isolation, attempts to insert a new record with the key value equal to that just created by T1. Due to the RC isolation level T2 does not see the change made by T1, since it has not been committed yet, so the duplicate key error is not raised. If it weren't for a gap lock on the unique index in question, this would eventually result in two records with the same purportedly unique key.

With MySQL 8 you can see this in action using the views data_locks and data_lock_waits in performance_schema. I've created this sample table:

create table fruits (
  id int not null auto_increment primary key, 
  name varchar(10), 
  property varchar(20)
);
create unique index fruits_ux1 on fruits (name, property);
insert into fruits (name, property) 
values ('apple', 'red'), ('apple', 'tart'), 
       ('banana', 'yellow'), ('banana', 'sweet');

In transaction 1 I update banana properties:

T1> set transaction isolation level read committed;

T1> begin;

T1> update fruits set property = concat(property, '?') where name = 'banana';

I can see that key entries with new values have gap locks set on them (these rows don't really exist until the transaction is committed, so standard X locks can't be used here):

mysql> select thread_id, object_name, index_name, engine_lock_id, lock_type, lock_mode, lock_data  from performance_schema.data_locks;
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
| thread_id | object_name | index_name | engine_lock_id                        | lock_type | lock_mode     | lock_data              |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+
|        47 | fruits      | NULL       | 140491829069632:1066:140491735749880  | TABLE     | IX            | NULL                   |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:1:140491735746840 | RECORD    | X             | supremum pseudo-record |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:4:140491735746840 | RECORD    | X             | 'banana', 'yellow', 3  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:5:140491735746840 | RECORD    | X             | 'banana', 'sweet', 4   |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:6:140491735747184 | RECORD    | X,REC_NOT_GAP | 4                      |
|        47 | fruits      | PRIMARY    | 140491829069632:5:4:7:140491735747184 | RECORD    | X,REC_NOT_GAP | 3                      |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:6:140491735747528 | RECORD    | X,GAP         | 'banana', 'sweet?', 4  |
|        47 | fruits      | fruits_ux1 | 140491829069632:5:5:7:140491735747528 | RECORD    | X,GAP         | 'banana', 'yellow?', 3 |
+-----------+-------------+------------+---------------------------------------+-----------+---------------+------------------------+

In transaction 2 I now try to insert a possible duplicate:

T2> set transaction isolation level read committed;

T2> begin;

T2> insert into fruits (name, property) values ('banana', 'sweet?');

The statement blocks, and I can see that it blocks on the gap lock for that unique index key (note the blocking_engine_lock_id value):

mysql> select requesting_engine_lock_id, blocking_engine_lock_id from performance_schema.data_lock_waits;
+---------------------------------------+---------------------------------------+
| requesting_engine_lock_id             | blocking_engine_lock_id               |
+---------------------------------------+---------------------------------------+
| 140491829070536:5:5:6:140491735753104 | 140491829069632:5:5:6:140491735747872 |
+---------------------------------------+---------------------------------------+

Similarly, if T1 deletes a row from a parent table, while T2 attempts to insert a new record to a child table referencing the row just deleted by T1 (which it still sees), the gap lock on the foreign key index prevents insertion of a potential orphan record.

Answer 2

From here:

https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

A gap lock is a lock on the gap between index records. Thanks to this gap lock, when you run the same query twice, you get the same result, regardless other session modifications on that table. This makes reads consistent and therefore makes the replication between servers consistent. If you execute SELECT * FROM id > 1000 FOR UPDATE twice, you expect to get the same value twice. To accomplish that, InnoDB locks all index records found by the WHERE clause with an exclusive lock and the gaps between them with a shared gap lock.