MySQL Bugs: #115640: Read a value that should be deleted

Bug #115640	Read a value that should be deleted
Submitted:	18 Jul 2024 12:29	Modified:	19 Jul 2024 4:34
Reporter:	Huicong Xu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.36	OS:	Any (22.04)
Assigned to:		CPU Architecture:	Any

Description:
Hi.
When I perform a delete operation on two transactions concurrently (T1,T2), one of them will be blocked (T2), and when the other transaction commits (T1), the blocked delete should continue to execute (T2), but after the delete, the data that should have been deleted can still be read (T2).

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 INT primary key);
INSERT INTO t0 VALUES (1);
INSERT INTO t0 VALUES (-1);

/* T1 */ BEGIN;
/* T2 */ BEGIN;
/* T1 */ SELECT * FROM t0;
+------+
| c0   |
+------+
|    1 |
|   -1 |
+------+
2 rows in set (0.00 sec)
/* T2 */ SELECT * FROM t0;
+------+
| c0   |
+------+
|    1 |
|   -1 |
+------+
2 rows in set (0.00 sec)
/* T1 */ Delete From t0 where c0 > 0;
Query OK, 1 row affected (0.00 sec)
/* T2 */ Delete From t0;
Query OK, 1 row affected (9.11 sec)
/* T1 */ SELECT * FROM t0;
+----+
| c0 |
+----+
| -1 |
+----+
1 row in set (0.00 sec)
/* T1 */ COMMIT;
/* T2 */ SELECT * FROM t0;
+----+
| c0 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
/* T2 */ COMMIT;

Hi Mr. Xu,

Thank you very much for your bug report.

What you describe is a designed behaviour for the default isolation level , REPEATABLE_READ.

If you wish that concurrent transaction, which is still running,  sees the committed rows, you should use some other isolation level.

Not a bug.

Hi.
Thank you very much for your reply.
This is indeed under the RR isolation level.But here's another thing that puzzles me. Take a look at this case. 
After T3 is committed, we excute the delete operation, and no other data will be found here. Why would transaction blocking cause a transaction to read data that it shouldn't ?
/* T3 */ BEGIN;
/* T4 */ BEGIN;
/* T3 */ SELECT * FROM t0;
+------+
| c0   |
+------+
|    1 |
|   -1 |
+------+
2 rows in set (0.00 sec)
/* T4 */ SELECT * FROM t0;
+------+
| c0   |
+------+
|    1 |
|   -1 |
+------+
2 rows in set (0.00 sec)
/* T3 */ Delete From t0 where c0 > 0;
Query OK, 1 row affected (0.00 sec)
/* T3 */ SELECT * FROM t0;
+----+
| c0 |
+----+
| -1 |
+----+
1 row in set (0.00 sec)
/* T3 */ COMMIT;
/* T4 */ Delete From t0;
Query OK, 1 row affected (0.00 sec)
/* T4 */ SELECT * FROM t0;
Empty set (0.00 sec)
/* T4 */ COMMIT;

Hi Mr. Xu,

That is all expected behaviour that is fully described in our Reference Manual.