Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
12.3.2
-
OS: Ubuntu-20.04 LTS
-
Not for Release Notes
Description
Isolation Level: Read Uncommitted & Read Committed & Repeatable Read & Serializable.
DELETE statement is blocked by the SELECT FOR UPDATE statement of another transaction, which has an impossible WHERE.
/* init */ CREATE TABLE t (c1 INT UNIQUE, c2 INT); |
/* init */ INSERT INTO t VALUES (1, 1), (2, 2), (3, 3); |
 |
/* t1 */ BEGIN; |
/* t1 */ SELECT * FROM t WHERE c1 = 1 and c2 = 3 FOR UPDATE; |
/* t2 */ BEGIN; |
/* t2 */ DELETE FROM t WHERE c1 = 1; -- blocked |
/* t1 */ COMMIT; |
/* t2 */ COMMIT; |
SELECT FOR UPDATE statement has an impossible WHERE:
mysql> explain SELECT * FROM t WHERE c1 = 1 and c2 = 3 FOR UPDATE; |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
1 row in set (0.00 sec) |
We are unsure whether the predicate in the SELECT FOR UPDATE statement is an impossible WHERE. If it is, we expect DELETE statement not to be blocked.