Bug #106655 Unexpected Deadlock Happened When Two transaction Execute Concurrently
Submitted: 7 Mar 2022 12:26 Modified: 22 Mar 2022 13:25
Reporter: John Jove Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: SERIALIZABLE, transaction

[7 Mar 2022 12:26] John Jove
Isolation level: Serializable
Unexpected deadlock occurs in this case.

How to repeat:
/* init */ DROP TABLE IF EXISTS t;
/* init */ CREATE TABLE t(c1 INT PRIMARY KEY, c2 INT);
/* init */ INSERT INTO t(c1, c2) VALUES (1, 1), (2, 2);

/* t1 */ BEGIN;
/* t1 */ SELECT * FROM t
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t WHERE c1 = 1; -- blocked
/* t1 */ DELETE FROM t; -- t2 deadlock
/* t1 */ COMMIT;
[8 Mar 2022 4:25] Justin Swanhart
When you do a SELECT * in SERIALIAZABLE /SHARED/ locks are taken on each row, not exclusive locks.  The deadlock happens when MySQL tries to update a SHARED lock to an exclusive lock.  To avoid this execute the SELECT SQL statement with the FOR UPDATE clause.
[9 Mar 2022 11:39] MySQL Verification Team
Hi John,

Not a bug but expected behavior. 
Serializable isolation is prone to deadlocks and performance penalty.

Thanks for your interest in MySQL
[14 Mar 2022 14:16] MySQL Verification Team
Hi Mr. Jove,

This is the expected behaviour.

This is caused by the escalation of the locks.

We have already a feature request for solving this problem, but the scheduling of feature requests is not known yet.
[14 Mar 2022 14:20] MySQL Verification Team
The improvement in the behaviour has already been defined precisely in this feature request:


At this moment in time, we do not know when will it be implemented.
[22 Mar 2022 13:25] John Jove
Thank you for your reply and I agree with it.
[22 Mar 2022 13:50] MySQL Verification Team
The pleasure is ours.
[22 Mar 2022 13:50] MySQL Verification Team
The pleasure is ours.