Gap Lock ve Next-key Lock

1. Gap Lock

Açıklaması şöyle. Sanırım sadece Repeatable Read seviyesinde Gap Lock var

A gap lock is a shared lock that locks the gaps between two existing index keys in a table.

If a gap lock is held, no other statement is allowed to INSERT, UPDATE, or DELETE a record that would fall within that gap.

This prevents phantom reads and ensures that a range of values remains consistent throughout the lifetime of a transaction.

Açıklaması şöyle

Here are two scenarios where a gap lock is acquired automatically.

- Range query. When a transaction queries a range of records.

- Index query using shared or exclusive lock. InnoDB automatically acquires gap locks on the gap before and after the indexed record. (Also known as next-key lock)

Örnek

Başlarken şöyle olsun

# Confirm the database version.
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.30    |
+-----------+
1 row in set (0.00 sec)

# Confirm the database isolation level.
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

# Turn off autocommit for MySQL.
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

Elimizde şöyle bir tablo olsun. age sütununda index var. Yaşlar 10 ve 20

CREATE TABLE `students` (
  `id` int NOT NULL AUTO_INCREMENT,
  `age` int DEFAULT 0,
  `name` varchar(64) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1;

# insert data
mysql> insert into students(`age`, `name`) values (10, 'foo'), (20, 'bar');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from students;
+----+------+------+
| id | age  | name |
+----+------+------+
|  1 |   10 | foo  |
|  2 |   20 | bar  |
+----+------+------+
2 rows in set (0.00 sec)

İki tane transaction başlatalım 

TA (10, +∞) için lock koyar. 

TB (-∞, 20) için lock koyar. 

# TA
mysql> begin;

mysql> update students set name = 'bar_a' where age = 20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# TB
mysql> begin;

mysql> update students set name = 'foo_b' where age = 10;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

TA bloke olur, çünkü TB'nin alanına girmektedir

mysql> insert into students(`age`, `name`) values (15, 'lee');
|

TB de bloke olur ancak bir müddet sonra TA rollback yaptığı için devam edebilir

mysql> insert into students(`age`, `name`) values (30, 'dec');
Query OK, 1 row affected (0.00 sec)

Dead lock loglarına bakmak için şöyle yaparız

show engine innodb status;

2. Next-key Lock

Açıklaması şöyle

Similar to gap locks, next-key locks prevent phantom reads.

A next-key lock is a combination of two different locks

- A record lock (shared or exclusive lock)

- A gap lock

When a read statement is executed via a non-clustered index with a shared or exclusive lock, the database acquires

- A record lock on the record

- Gap locks on the gap before and after the indexed record.

This ensures that no new rows can be inserted before and after the indexed entry, ensuring data consistency.

Açıklaması şöyle

But here’s the intriguing part, the behaviour of a next-key lock varies depending on the type of index being used:

Querying using a non-clustered index

The database acquires a record lock and gap locks on the gaps before and after the index entry.

Querying using a clustered index

The database acquires only a record lock without a gap lock.

Querying using a non-indexed column

The database acquires a record lock and a gap lock on the ENTIRE table.

As a result, querying a record using a non-indexed column prevents other transactions from modifying any records in the table.

Örnek - non-clustered index

Elimizde şöyle bir tablo olsun. Burada non-clustered index kullanıldığı için bir önceli ve bir sonraki kayıt ta kilitlendi

/* Sample table */
+---------+---------+------+-------------------+
| id      | user_id | age  | email             |
+---------+---------+------+-------------------+
| 1000076 |     101 |   12 | peter@hotmail.com |
| 1000080 |     104 |   17 | dan@hotmail.com   |
| 1000079 |     107 |   19 | lim@hotmail.com   |
| 1000081 |     109 |   20 | chris@hotmail.com |
+---------+---------+------+-------------------+

Tabloya select for update yapalım

/* Session 1 */
mysql> begin;

mysql> select * from user_tab where user_id=107 for update;
+---------+---------+------+-----------------+
| id      | user_id | age  | email           |
+---------+---------+------+-----------------+
| 1000079 |     107 |   19 | lim@hotmail.com |
+---------+---------+------+-----------------+

/* Session 2 */
mysql> begin;

mysql> insert into user_tab (user_id, age, email) 
       values (106, 10, 'lee@gmail.com');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into user_tab (user_id, age, email) 
       values (108, 10, 'lee@gmail.com');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into user_tab (user_id, age, email) 
       values (110, 10, 'lee@gmail.com');
Query OK, 1 row affected (0.00 sec)

Açıklaması şöyle

In the example above, the first transaction queried a record using an xLock. The database locks the record and also the gaps from user_id=104 to user_id=109.

Hence, the second transaction was blocked when trying to insert a record within the gap user_id=104-109. When trying to insert a record outside of the gap user_id=110, the statement succeeded.