三个事务，insert获取锁失败问题记录

问题描述

最近遇到一个比较奇怪的问题，有三个事务，同时向一个表里面插入数据，删除数据。一个事务拿到行锁插入删除之后提交事务，另外两个等待的事物，其中一个会拿到锁，成功插入，另外一个会报死锁，而不是继续等待。

表结构

create table user
(
    id          bigint unsigned auto_increment comment 'id' primary key,
    name        varchar(32) not null comment '名字'
    constraint name unique (name)
);

SQL语句

insert into user(name) values('aaa');

delete
from user
where name = 'aaa';

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-01-04 14:44:50 140330894620416
*** (1) TRANSACTION:
TRANSACTION 23500431, ACTIVE 19 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 253, OS thread handle 140332134795008, query id 1691791 39.170.101.162 root update
/* ApplicationName=DataGrip 2021.3.1 */ insert into user(name) values('lhc2')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 7 page no 5 n bits 80 index name of table `demo`.`user` trx id 23500431 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 14 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 6c686332; asc lhc2;;
 1: len 8; hex 0000000000000016; asc         ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 5 n bits 80 index name of table `demo`.`user` trx id 23500431 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) TRANSACTION:
TRANSACTION 23500508, ACTIVE 14 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 254, OS thread handle 140332135859968, query id 1691841 39.170.101.162 root update
/* ApplicationName=DataGrip 2021.3.1 */ insert into user(name) values('lhc2')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7 page no 5 n bits 80 index name of table `demo`.`user` trx id 23500508 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 14 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 6c686332; asc lhc2;;
 1: len 8; hex 0000000000000016; asc         ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 5 n bits 80 index name of table `demo`.`user` trx id 23500508 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)