Bug #120684 Inconsistent deadlock of UPDATE and SELECT FOR UPDATE with the same WHERE clause

• Submitted: 15 Jun 3:22
• Modified: 15 Jun 3:28
• Reporter: Ryan Yang
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any
• Tags: transaction

[15 Jun 3:22] Ryan Yang

Description:
Isolation Level: Read Uncommitted.
UPDATE statement is not blocked by the DELETE statement of another transaction, while SELECT FOR UPDATE statement that has the same WHERE clause as the UPDATE statement is blocked by the same DELETE statement of another transaction and reports deadlock.

How to repeat:
Test Case 1:

/* init */ CREATE TABLE t (c1 NUMERIC);
/* init */ INSERT INTO t VALUES (1);

/* t1 */ BEGIN;
/* t1 */ INSERT INTO t (c1) VALUES (2);
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t;  -- blocked
/* t1 */ UPDATE t SET c1 = 3;
/* t1 */ COMMIT;  -- t2 unblocked
/* t2 */ COMMIT;

Test Case 2:

/* init */ CREATE TABLE t (c1 NUMERIC);
/* init */ INSERT INTO t VALUES (1);

/* t1 */ BEGIN;
/* t1 */ INSERT INTO t (c1) VALUES (2);
/* t2 */ BEGIN;
/* t2 */ DELETE FROM t;  -- blocked
/* t1 */ SELECT c1 FROM t FOR UPDATE;  -- deadlock
/* t2 unblocked */ DELETE FROM t;
/* t2 */ COMMIT;

We expect UPDATE statement in test case 1 to report deadlock.

[15 Jun 3:28] Ryan Yang

Isolation Level: Read Uncommitted & Read Committed & Repeatable Read & Serializable.
This situation applies to all four isolation levels.