MySQL Forums
Forum List  »  InnoDB

MySQL still add gap lock on unique index when delete
Posted by: Jacky Jacky
Date: July 20, 2020 02:56AM

Recently, I have encountered a deadlock issue with below steps (MySQL 5.7, Repeatable Read isolation level)


1 Create a new table

CREATE TABLE `t` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `p_name` (`name`)
) ENGINE=InnoDB CHARSET=utf8;

2 Prepare 3 records

insert into t (name) value ('A'), ('C'), ('D');

3.1 Session A:
begin; delete from t where name = 'C';

3.2 Session B:
begin; delete from t where name = 'C';

3.3 Session A:
insert into t (name) values ('B');

3.4 Session B:
Deadlock found when trying to get lock;


The result of `show engine innodb status`: session B is waiting next-key lock **C**, and session A hold a record lock **C** and waiting gap lock on **C**;


2020-07-20 13:49:22 0x700003fac000
*** (1) TRANSACTION:
TRANSACTION 19394, ACTIVE 16 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 68, OS thread handle 123145369354240, query id 3692 localhost root updating
delete from t where name = 'C'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 86 page no 4 n bits 80 index p_name of table `jacky`.`t` trx id 19394 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 1; hex 43; asc C;;
1: len 8; hex 8000000000000003; asc ;;

*** (2) TRANSACTION:
TRANSACTION 19393, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 67, OS thread handle 123145369075712, query id 3693 localhost root update
insert into t (name) values ('B')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 86 page no 4 n bits 80 index p_name of table `jacky`.`t` trx id 19393 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 1; hex 43; asc C;;
1: len 8; hex 8000000000000003; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 86 page no 4 n bits 80 index p_name of table `jacky`.`t` trx id 19393 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 1; hex 43; asc C;;
1: len 8; hex 8000000000000003; asc ;;


However, in official doc, it says that

> DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

I do not understand why the delete would still add gap lock even though `p_name` is a unique index?

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL still add gap lock on unique index when delete
111
July 20, 2020 02:56AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.