Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 10.6.27, 12.2.2
-
MariaDB Server 10.6, 12.2.2, and 12.3
Storage engine: InnoDB
Isolation levels tested: READ UNCOMMITTED and READ COMMITTED
innodb_snapshot_isolation = ON
Description
I found a possible inconsistency between READ UNCOMMITTED and READ COMMITTED.
In the following concurrent transaction case, the statement:
UPDATE IGNORE t0 SET c0 = 3.96717278E8 WHERE TRUE ORDER BY c0 ASC; |
updates a different row under READ UNCOMMITTED than under READ COMMITTED.
How to repeat:
CREATE TABLE t0( |
c0 REAL PRIMARY KEY NOT NULL, |
c1 REAL UNIQUE NOT NULL, |
c2 VARCHAR(100) |
) ENGINE=InnoDB;
|
INSERT INTO t0 VALUES (0.39029, 0.54545, ''); |
INSERT INTO t0 VALUES (0.74901, 0.90606, '8!'); |
INSERT INTO t0 VALUES (0.62655, 0.74793, '0.5454512604377426'); |
INSERT INTO t0 VALUES (0.91403, 0.02027, '8!'); |
Transaction 1:
[1-0] BEGIN; |
[1-1] SELECT t0.c2, t0.c0, t0.c1 FROM t0 WHERE TRUE FOR UPDATE; |
[1-2] DELETE FROM t0 WHERE TRUE LIMIT 1034361946; |
[1-3] COMMIT; |
Transaction 2:
[2-0] BEGIN; |
[2-1] INSERT INTO t0 VALUES (0.16242, 0.38532, NULL); |
[2-2] UPDATE t0 SET c0=0.01693 WHERE TRUE; |
[2-3] INSERT INTO t0 VALUES (0.25908, 0.44895, ''); |
[2-4] COMMIT; |
Transaction 3:
[3-0] BEGIN; |
[3-1] UPDATE IGNORE t0 SET c0=3.96717278E8 WHERE TRUE ORDER BY c0 ASC; |
[3-2] SELECT t0.c0, t0.c2 FROM t0 WHERE TRUE FOR UPDATE; |
[3-3] COMMIT; |
Execute the transaction statements in the following order:
-- session 1
|
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
[1-0] BEGIN; |
 |
-- session 2
|
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
[2-0] BEGIN; |
 |
-- session 3
|
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
[3-0] BEGIN; |
 |
-- session 1
|
[1-1] SELECT t0.c2, t0.c0, t0.c1 FROM t0 WHERE TRUE FOR UPDATE; |
[1-2] DELETE FROM t0 WHERE TRUE LIMIT 1034361946; |
 |
-- session 2
|
[2-1] INSERT INTO t0 VALUES (0.16242, 0.38532, NULL); |
[2-2] UPDATE t0 SET c0=0.01693 WHERE TRUE; -- [2-2] is blocked |
 |
-- session 2
|
[3-1] UPDATE IGNORE t0 SET c0=3.96717278E8 WHERE TRUE ORDER BY c0 ASC; -- [3-1] is blocked |
 |
-- session 1
|
[1-3] COMMIT; |
 |
-- session 2
|
[2-2] UPDATE t0 SET c0=0.01693 WHERE TRUE; -- [2-2] is resumed |
[2-3] INSERT INTO t0 VALUES (0.25908, 0.44895, ''); |
[2-4] COMMIT; |
 |
-- session 3
|
[3-1] UPDATE IGNORE t0 SET c0=3.96717278E8 WHERE TRUE ORDER BY c0 ASC; -- [3-1] is resumed |
[3-2] SELECT t0.c0, t0.c2 FROM t0 WHERE TRUE FOR UPDATE; |
[3-3] COMMIT; |
Then repeat the same schedule under READ UNCOMMITTED.
Observed Result:
Under READ COMMITTED :
MariaDB [test]> SELECT t0.c0, t0.c2 FROM t0 WHERE TRUE FOR UPDATE; |
+-----------+------+ |
| c0 | c2 |
|
+-----------+------+ |
| 0.01693 | NULL | |
| 396717278 | |
|
+-----------+------+ |
Under READ UNCOMMITTED :
MariaDB [test]> SELECT t0.c0, t0.c2 FROM t0 WHERE TRUE FOR UPDATE; |
+-----------+------+ |
| c0 | c2 |
|
+-----------+------+ |
| 0.25908 | |
|
| 396717278 | NULL | |
+-----------+------+ |
Expected result:
READ UNCOMMITTED and READ COMMITTED should update the same row for:
UPDATE IGNORE t0 SET c0 = 3.96717278E8 WHERE TRUE ORDER BY c0 ASC; |
Therefore, the final result should be consistent between READ UNCOMMITTED and READ COMMITTED. In MySQL, this case returns the same result as MariaDB READ COMMITTED.
Why this looks suspicious:
The difference suggests that READ UNCOMMITTED and READ COMMITTED choose different target rows for the same UPDATE IGNORE ... ORDER BY statement under the same concurrent schedule.
Since this is an UPDATE statement, the row selected and updated by ORDER BY c0 ASC should be deterministic and should not differ between READ UNCOMMITTED and READ COMMITTED in this way.