Why concurrent "Delete...Insert" statements cause a deadlock?

Question

Consider the following schema in mysql:

create table foo(
  id int not null primary key auto_increment,
  name varchar(32) not null,
  unique key(name)
);

And there is a record with name "abc" in the table.

I have a transaction (RC):

start transaction;
delete from foo where name = "abc";
insert into foo(name) values("abc");
commit;

If there are two concurrent transactions, the dead lock will happen.

       |        TX A         |             TX B
---------------------------------------------------------------------
Step 1 | start transaction;  | 
       | delete name="abc";  |
---------------------------------------------------------------------
Step 2 |                     | start transaction;
       |                     | delete name="abc";
       |                     | <wait for lock>
---------------------------------------------------------------------
Step 3 | insert name="abc";  | <deadlock detected, exit>
---------------------------------------------------------------------
Step 4 | commit;             |
---------------------------------------------------------------------

I'm wondering why this sequence causes the deadlock.

In the mysql doc says (https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html)

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.

I suppose when transaction A runs the "delete" statement, it has acquired the X lock of the record "abc". When the "insert" statement executes, it tries to acquire the S lock due to the "duplicate key error". Shouldn't it get the S lock since it has got the X lock of the same record? Why deadlock happens here?

with delete .... i assume you always meandelete from foo where name = "abc"; and with insert ... i assume you always mean insert into foo(name) values("abc"); — Raymond Nijland
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. — Raymond Nijland
TX A holds because of DELETE FROM ... WHERE ... a unique index lock on the value "abc" .. TX B also tryes to delete with "abc" but transaction TX A "invalidates" TX B delete transaction state when TX A does that insert with "abc" whats why TX B deadlocks as TX A holds the rights of "abc" (more or less as it is simplified a bit) — Raymond Nijland

lzhongzh lzhongzh · Accepted Answer · 2019-10-22T09:51:29

I reproduced the deadlock, and got the innoDB status log as follow:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-10-18 18:35:14 0x7f1dfc738700
*** (1) TRANSACTION:
TRANSACTION 26547965, ACTIVE 6 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
/* ApplicationName=DataGrip 2019.1.1 */ delete from foo where name='abc'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3011 page no 4 n bits 224 index IDX_NAME of table `foo` trx id 26547965 lock_mode X locks rec but not gap waiting
Record lock, heap no 153 PHYSICAL RECORD: n_fields 2; ....

*** (2) TRANSACTION:
TRANSACTION 26547960, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
/* ApplicationName=DataGrip 2019.1.1 */ INSERT INTO foo(id, name)
VALUES (1, 'abc')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3011 page no 4 n bits 224 index IDX_NAME of table `foo` trx id 26547960 lock_mode X locks rec but not gap
Record lock, heap no 153 PHYSICAL RECORD: ...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3011 page no 4 n bits 224 index IDX_NAME of table `foo` trx id 26547960 lock mode S waiting
Record lock, heap no 153 PHYSICAL RECORD: ....

*** WE ROLL BACK TRANSACTION (1)

The log explains the reason clearly, TX B waiting X lock held by TX A, at the same time, TX A waiting S lock which is block by TX B's lock request.

According to Mysql doc:

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. "

Insert statement does acquire S lock at some point, so the reason for why deadlock happens is very clear.

But problem is:

according to the mysql doc, insert statement will acquire a S lock if a duplicate-key error occurs, which is not happened in current case we discuss
why insert statement still acquire a S lock when current transaction already holds the X lock, X lock is enough for doing a current read to check the duplicate key error. so what does it use for ?

Why concurrent "Delete...Insert" statements cause a deadlock?

1 Answers