MySQL Bugs: #106655: Unexpected Deadlock Happened When Two transaction Execute Concurrently

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:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	SERIALIZABLE, transaction

Description:
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;

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.

Hi John,

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

Thanks for your interest in MySQL

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.

The improvement in the behaviour has already been defined precisely in this feature request:

https://bugs.mysql.com/bug.php?id=21356

At this moment in time, we do not know when will it be implemented.

Thank you for your reply and I agree with it.

The pleasure is ours.

The pleasure is ours.