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

5
+50

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.

  • @mustacciao, can you explain why there is a necessity of gap lock on referenced record (foreign key), instead a simple shared lock(S) on it may have prevented insertion of a potential orphan record (because if you attempt to get S lock parent record , you can't get it if the record is already deleted(X lock) by some other transaction which is not visible to this transaction) ? – Dinesh Kumar May 11 at 5:49
1

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.

  • your answer is related to READ_REPEATABLE. But I am in need of READ Committed. Can you explain me in that way? – Dinesh Kumar May 10 at 14:38
  • Phantom reads do not occur if you’re simply doing a SELECT. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ COMMITTED for all write queries, in spite of your chosen transaction isolation level (considering only the two most common isolation levels, REPEATABLE READ and READ COMMITTED). – raphael75 May 10 at 14:40
  • please share me a pictorial example(step by step) – Dinesh Kumar May 10 at 15:08
  • With read committed, MySQL will get a fresh snapshot of the table each time it is read in the current transaction. However phantom problems can still occur. Please see this page that includes a pictorial example near the bottom (search for vkrishna17): stackoverflow.com/questions/4034976/… – raphael75 May 10 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.