Bug #114565 UPDATE with subqueries read uncommitted version of rows under Read Uncommitted

• Submitted: 7 Apr 2024 9:42
• Modified: 8 Apr 2024 9:56
• Reporter: Dai Dinary
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.3.0
• OS: Any
• CPU Architecture: Any
• Tags: read uncommitted, subquery, transaction

[7 Apr 2024 9:42] Dai Dinary

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? 

How to repeat:
/* 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.

[8 Apr 2024 9:56] MySQL Verification Team

Hi Mr. Dinary,

Thank you for your bug report.

However, this is not a bug.

Read Uncommitted works exactly as specified in the SQL standard. This is also explained in our Reference Manual.

If you are interested in the details of MVCC, you can read the bug "Transaction Processing", which we recommend warmly.

Not a bug.