Description:
Hello,
When I tested the database isolation levels, I found a problem.
As shown in the use case below, I set the isolation level for read committed. However, a dirty read exception still occurs in the actual execution.
--- I see
--- Initial state of the database
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DROP TABLE tYv10enE;
CREATE TABLE tYv10enE (c0 INT PRIMARY KEY);
INSERT INTO tYv10enE (c0) VALUES (10989748);
INSERT INTO tYv10enE (c0) VALUES (-1404643822);
--- session 1 ---
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--- session 2 ---
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--- session 1 ---
BEGIN;
--- session 2 ---
BEGIN;
--- session 1 ---
UPDATE tYv10enE SET c0 = 0 WHERE c0 = 10989748;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--- session 2 ---
SELECT * FROM tYv10enE WHERE ( c0 >= 0 );
+----+
| c0 |
+----+
| 0 |
+----+
1 row in set (0.00 sec)
--- session 1 ---
ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
--- session 2 ---
COMMIT;
Query OK, 0 rows affected (0.00 sec)
--- Expected to see （MariaDB show）
...
SELECT * FROM tYv10enE WHERE ( c0 >= 0 );
+----------+
| c0 |
+----------+
| 10989748 |
+----------+
1 row in set (0.00 sec)
...
How to repeat:
--- Initial state of the database
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DROP TABLE tYv10enE;
CREATE TABLE tYv10enE (c0 INT PRIMARY KEY);
INSERT INTO tYv10enE (c0) VALUES (10989748);
INSERT INTO tYv10enE (c0) VALUES (-1404643822);
--- session 1 ---
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--- session 2 ---
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--- session 1 ---
BEGIN;
--- session 2 ---
BEGIN;
--- session 1 ---
UPDATE tYv10enE SET c0 = 0 WHERE c0 = 10989748;
--- session 2 ---
SELECT * FROM tYv10enE WHERE ( c0 >= 0 );
--- session 1 ---
ROLLBACK;
--- session 2 ---
COMMIT;
Suggested fix:
I'm guessing the isolation level setting isn't working.
I had reported in MariaDB and its been fixed also see https://jira.mariadb.org/browse/MDEV-35335?filter=-2.
