Next-Key LOCK in MySQL
Tags: mysql, locking, innodb, gaplock
Published: 2021-01-25 07:53:03

InnoDB engine in MySQL has a myriad of [locking](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html) options often leads to confusion.  This article tries to explore [next-key](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-next-key-locks) lock to better understand the concept.

## Example table

```sql
CREATE TABLE `gaplock_test` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `emp_div` INT(11) NOT NULL DEFAULT '0',
    `emp_id` BIGINT(20) NOT NULL DEFAULT '0',
    `start_date` DATETIME(3) NOT NULL,
    `end_date` DATETIME(3) DEFAULT NULL,
    `div_id` BIGINT(20) NOT NULL DEFAULT '0',
    `code` VARCHAR(50) COLLATE UTF8_UNICODE_CI DEFAULT NULL,
    PRIMARY KEY (`id` , `start_date`),
    UNIQUE KEY `uidx_gaplock_01` (`emp_id` , `start_date` , `div_id` , `emp_div`),
    KEY `idx_gaplock_01` (`div_id` , `code` , `start_date`)
)  ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=UTF8 COLLATE = UTF8_UNICODE_CI ROW_FORMAT=DYNAMIC;
```
InnoDB has a concept of Record lock defined as 

> A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes.

`uidx_gaplock_01` is a unique index on the table. Hence this is the target for record locking.

## Initial data

| ID | EMP_DIV | EMP_ID | START_DATE          | END_DATE           | DIV_ID | CODE  |
|----|---------|--------|---------------------|--------------------|--------|-------|
| 1  | 0       | 100    | 1909/12/31 15:00:00 | 2382/12/31 0:00:00 | 2      | testA |
| 2  | 0       | 200    | 1909/12/31 15:00:00 | 2382/12/31 0:00:00 | 2      | testB |
| 3  | 0       | 300    | 1909/12/31 15:00:00 | 2382/12/31 0:00:00 | 2      | testC |
| 4  | 0       | 250    | 1909/12/31 15:00:00 | 2382/12/31 0:00:00 | 2      | testD |

So ideally when we operate on a certain row, only the index record corresponding to that row should be locked.

But InnoDB has concept of next-key lock. 
> A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

So  if we operate on `EMP_ID` *100*, we should expect to see record *200* locked **sometimes**.

According to the manual, one such case is duplicate-key checking. 
> Gap locking is used only for foreign-key constraint checking and duplicate-key checking.

So if we delete and insert the same record, we will trigger gap locking.

## Experiment with Transactions
Consider a transaction TR1 that operates on record 100.
``` SQL
-- TR1 
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
DELETE FROM testdb.gaplock_test WHERE div_id = 2 AND emp_id = 100 AND emp_div = 0;
INSERT INTO testdb.gaplock_test (id, emp_div, emp_id, start_date, end_date, div_id, code) VALUES (1, 0, 100, '1909-12-31 15:00:00', '2382-12-31 00:00:00', 2, 'testA');

```
Consider another transaction TR2 that operates on record 200.

```SQL
-- TR2
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
DELETE FROM testdb.gaplock_test WHERE div_id = 2 AND emp_id = 200 AND emp_div = 0;
```

In this case, Transaction 2 waits for Transaction 1 to complete.

## Results
```SQL
-- INNODB_TRX
select * from  information_schema.INNODB_TRX;
```
| trx_id | trx_state|   trx_started | trx_query
|--|--|--|--|
2418|	LOCK WAIT|	2021-01-25 06:13:03| 'DELETE FROM testdb.gaplock_test WHERE div_id = 2 AND emp_id = 200 AND emp_div = 0'
2402|	RUNNING	|2021-01-25 06:02:21| 


Let's check locked contents
```SQL
-- INNODB_LOCKS 
select * from  information_schema.INNODB_LOCKS;
```
|lock_id |lock_trx_id| lock_mode |lock_type| lock_table| lock_index| lock_data
|--|--|--|--|--|--|--|
|2418:27:4:3	|2418	|X	|RECORD	|`testdb`.`gaplock_test`	|uidx_gaplock_01	|	200, 0x983F7EF0000000, 2, 0
|2402:27:4:3|	2402|	S	|RECORD	|`testdb`.`gaplock_test`|	uidx_gaplock_01	|200, 0x983F7EF0000000, 2, 0|

## Conclusion
As we can see, even though transaction only operates on record `100`, record `200` is locked too.

## Code
All the code is here {%gist https://gist.github.com/chaitan64arun/d5924cceab38fcfcf38cadd7c338ec49 %}