Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.0
Description
Isolation Level: Read Uncommitted.
UPDATE statements using subqueries read uncommitted version of rows and modify them under Read Uncommitted isolation level. I am curious about the lock mechanism of the UPDATE statements with subqueries under Read Uncommitted. Does the UPDATE statement modify uncommitted version of rows because its subquery reads these uncommitted version? Could you please explain it briefly at the implementation level?
/* 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,60); |
/* init */ CREATE TABLE t1 (pkey INT PRIMARY KEY, value INT, c0 INT); |
/* init */ INSERT INTO t1 VALUES (1,32,8),(2,45,50); |
|
|
/* t1 */ SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
/* t2 */ SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
/* t1 */ BEGIN; |
/* t1 */ UPDATE t0 SET value=100; |
/* t2 */ BEGIN; |
/* t2 */ SELECT * FROM t1 WHERE c0 <= (SELECT MIN(value) FROM t0); -- [(1,32,8),(2,45,50)] |
/* t2 */ UPDATE t1 SET value=200 WHERE c0 <= (SELECT MIN(value) FROM t0); |
-- Query OK, 2 rows affected (0.00 sec)
|
-- Rows matched: 2 Changed: 2 Warnings: 0
|
/* t1 */ ROLLBACK; |
/* t2 */ COMMIT; |
/* t2 */ SELECT * FROM t1; -- [(1,200,8),(2,200,50)] |
Without transaction t1, the UPDATE statement in t2 only modifies the first row. However, in this test case, although transaction t1 aborts, the UPDATE statement in t2 modifies two rows.