Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.0
Description
Isolation Level: Read Uncommitted.
The behaviors of UPDATE statement using subqueries are weird. I speculate that dirty read performed by subqueries caused the problem with the UPDATE statements.
Test case:
/* init */ DROP TABLE IF EXISTS t0; |
/* init */ DROP TABLE IF EXISTS t1; |
/* init */ CREATE TABLE t0 (pkey INT PRIMARY KEY, value INT); |
/* init */ INSERT INTO t0 VALUES (1,20),(2,56); |
/* init */ CREATE TABLE t1 (pkey INT PRIMARY KEY, value INT, c0 INT); |
/* init */ INSERT INTO t1 VALUES (2,43,8),(3,57,53); |
|
|
/* t1 */ BEGIN; |
/* t1 */ UPDATE t0 SET value=162; |
/* t1 */ UPDATE t1 set c0=200 WHERE pkey=3; |
/* t2 */ BEGIN; |
/* t2 */ SELECT value FROM t1 WHERE c0 <= (SELECT MIN(value) FROM t0); -- [(43)] |
/* t2 */ UPDATE t1 SET value=100 WHERE c0 <= (SELECT MIN(value) FROM t0); -- blocked |
/* t1 */ COMMIT; -- t2 unblocked |
/* t2 */ COMMIT; |
UPDATE statement in transaction 2 updates the first row, but is blocked by the lock added to the second row.
However, under Read Committed, UPDATE statement in transaction 2 is not blocked.