MySQL Gap locks Gap Lock

MySQL InnoDB supports three kinds of line locking methods:

Row lock (Record Lock): The lock is added directly above the index record.

Gap Lock: Locks are added to a nonexistent free space, which can be between two index records or the space after the first index record or after the last index.

Next-key Lock: The combination of row and gap locks is called Next-key lock.

By default, InnoDB works under the REPEATABLE read isolation level and locks the data rows in Next-key lock, which effectively prevents phantom reads from occurring. Next-key Lock is a combination of a row lock and a gap lock, so that when InnoDB scans the index record, the selected index record is preceded by a row lock (record Lock), and then the gap on both sides of the index record (scanning left sweeps to the first value smaller than the given parameter, Scanning to the right scans to the first value larger than the given parameter, then bounds, builds an interval, and Gap lock. If a gap is locked by the transaction T1, other transactions cannot be inserted into the record at this interval.

As an example:

Table Task_queue

Id taskId

1 2

3 9

10 20

40 41

Open a session: Session 1

Sql> set autocommit=0;

##

Cancel Auto-Commit

Sql> Delete from task_queue where taskId = 20;

sql> INSERT into task_queue values (20, 20);

Opening a session: Session 2

Sql> set autocommit=0;

##

Cancel Auto-Commit

Sql> Delete from task_queue where taskId = 25;

sql> INSERT into task_queue values (30, 25);

In the case of no concurrency, or very little concurrency, this can be done normally, in MySQL, the transaction is ultimately run through, but in high concurrency, the order of execution is very likely to change, to look like this:

Sql> Delete from task_queue where taskId = 20;

Sql> Delete from task_queue where taskId = 25;

sql> INSERT into task_queue values (20, 20);

sql> INSERT into task_queue values (30, 25);

This time the last statement: INSERT into task_queue values (30, 25); A deadlock error occurs during execution. Since the deletion of TaskID = 20 of this record, 20-41 are locked, they all have a shared lock on this data segment, so there is a deadlock when acquiring an exclusive lock on this data segment.

The only function of the gap lock in InnoDB is to prevent the insertion of other transactions, so as to prevent the occurrence of phantom reading, so the gap lock does not divide what shared lock and exclusive lock. Also, in the above example, we chose a normal (non-unique) index field to test, which is not optional, because if INNODB scans a primary key, or a unique index, the InnoDB will only be locked with a row lock instead of Next-key Lock way, that is, the gap between the index is not locked, carefully think about it, this is not difficult to understand, we can also test their own.

To disable the clearance lock, you can reduce the isolation level to read Committed, or open the parameter innodb_locks_unsafe_for_binlog.

MySQL Gap locks Gap Lock