Bug #120715 Unexpected deadlock when one of the two transactions does not acquire any locks

• Submitted: 17 Jun 8:28
• Modified: 18 Jun 2:12
• Reporter: Ryan Yang
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 9.7.0
• OS: Any
• CPU Architecture: Any
• Tags: deadlock, transaction

[17 Jun 8:28] Ryan Yang

Description:
Isolation Level: Repeatable Read & Serializable
DELETE statement does not acquire any locks, but deadlock occurs when "UPDATE t SET c1=1 WHERE 3;" executes, resulting in incorrect final database state.

How to repeat:
/*init*/ CREATE TABLE t(c1 INT PRIMARY KEY);
/*init*/ INSERT INTO t(c1) VALUES (3);

/* t1 */ BEGIN;
/* t1 */ UPDATE t SET c1=2 WHERE -5;
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t WHERE -3;  -- blocked
/* t1 */ UPDATE t SET c1=1 WHERE 3;
/* t2 */ Error 1213: Deadlock found when trying to get lock; try restarting transaction
/* t1 */ COMMIT;
/* t2 */ COMMIT;
/* t2 */ SELECT * FROM t;  -- [(1)]

We expect deadlock not to occur, and the final database state to be [ ].

[17 Jun 14:47] Jean-François Gagné

Care about unusual SQL in Description and How to repeat.

> UPDATE t SET c1=1 WHERE 3
> UPDATE t SET c1=2 WHERE -5
> DELETE FROM t WHERE -3
> UPDATE t SET c1=1 WHERE 3

In above, we see "WHERE 3" (and others) while we might expect "WHERE c1=3".  If I am not wrong, "WHERE 3" (and others) match all rows, so the query above are equivalent to below.

- UPDATE t SET c1=1
- UPDATE t SET c1=2
- DELETE FROM t
- UPDATE t SET c1=1

Unclear if above details are changing the expectations of the original poster.

[18 Jun 2:12] Ryan Yang

Sorry, I didn't simplify the test case to its minimum. The simplified test case is:

/*init*/ CREATE TABLE t(c1 INT PRIMARY KEY);
/*init*/ INSERT INTO t(c1) VALUES (3);

/* t1 */ BEGIN;
/* t1 */ UPDATE t SET c1=2;
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t;  -- blocked
/* t1 */ UPDATE t SET c1=1;
/* t2 */ Error 1213: Deadlock found when trying to get lock; try restarting transaction
/* t1 */ COMMIT;
/* t2 */ COMMIT;
/* t2 */ SELECT * FROM t;  -- [(1)]