Next-Key LOCK in MySQL

InnoDB engine in MySQL has a myriad of locking options often leads to confusion. This article tries to explore next-key lock to better understand the concept.

Example table

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.

-- 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.

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

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

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

	use testdb;
	drop table gaplock_test;
	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;
	INSERT INTO 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');
	INSERT INTO gaplock_test (id, emp_div, emp_id, start_date, end_date, div_id, code) VALUES (2, 0, 200, '1909-12-31 15:00:00', '2382-12-31 00:00:00', 2, 'testB');
	INSERT INTO gaplock_test (id, emp_div, emp_id, start_date, end_date, div_id, code) VALUES (3, 0, 300, '1909-12-31 15:00:00', '2382-12-31 00:00:00', 2, 'testC');
	INSERT INTO gaplock_test (id, emp_div, emp_id, start_date, end_date, div_id, code) VALUES (4, 0, 250, '1909-12-31 15:00:00', '2382-12-31 00:00:00', 2, 'testD');

view raw create.sql hosted with ❤ by GitHub

	-- INNODB_TRX
	select * from information_schema.INNODB_TRX;
	-- INNODB_LOCKS
	select * from information_schema.INNODB_LOCKS;
	-- TABLE
	select * from testdb.gaplock_test;

view raw debug.sql hosted with ❤ by GitHub

	version: '3.3'
	services:
	db:
	image: mysql:5.7
	volumes:
	- db_data:/var/lib/mysql
	restart: always
	environment:
	MYSQL_ROOT_PASSWORD: root
	MYSQL_DATABASE: testdb
	MYSQL_USER: test_user
	MYSQL_PASSWORD: test_password
	ports:
	- 44444:3306
	volumes:
	db_data: {}

view raw docker-compose.yml hosted with ❤ by GitHub

	-- 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');
	-- 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;

view raw transaction.sql hosted with ❤ by GitHub