Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Incomplete
-
10.6.17, 10.6.22
-
None
-
Ubuntu 20.04
Description
When I tested Repeatable Read isolation levels on Mariadb, I found a like lost update exception.
In a transaction, I used a table-wide delete statement, but was still able to read data from the table, which should have been deleted. If I use this result to modify the data, it will result in a serious error.
A simplified test case with result is as follows.
In the last query of session 2, I should see an empty set.Therefore, it seems to me that this is a logical bug for the isolation level.
--- session 0 ---
|
CREATE TABLE t0(c0 INT primary key); |
INSERT INTO t0 VALUES (1); |
INSERT INTO t0 VALUES (-1); |
 |
--- session 1 ---
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
BEGIN; |
SELECT * FROM t0; |
+----+ |
| c0 |
|
+----+ |
| -1 |
|
| 1 |
|
+----+ |
2 rows in set (0.00 sec) |
--- session 2 ---
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
BEGIN; |
SELECT * FROM t0; |
+----+ |
| c0 |
|
+----+ |
| -1 |
|
| 1 |
|
+----+ |
2 rows in set (0.00 sec) |
--- session 1 ---
|
UPDATE t0 SET c0 = 2 where c0 = 1; |
Query OK, 1 row affected (0.00 sec)
|
Rows matched: 1 Changed: 1 Warnings: 0 |
SELECT * FROM t0; |
+----+ |
| c0 |
|
+----+ |
| -1 |
|
| 2 |
|
+----+ |
2 rows in set (0.00 sec) |
COMMIT; |
--- session 2 ---
|
DELETE FROM t0; |
Query OK, 2 rows affected (0.00 sec) |
SELECT * FROM t0; |
+----+ |
| c0 |
|
+----+ |
| 1 |
|
+----+ |
1 row in set (0.00 sec) |
COMMIT; |
Expect to see:
..
|
--- session 2 ---
|
DELETE FROM t0;
|
Query OK, 2 rows affected (0.00 sec)
|
SELECT * FROM t0;
|
Empty set (0.00 sec)
|
