Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.2.2
-
None
-
None
Description
I found a possible false positive ERROR 1020 in MariaDB 12.2.2 under SERIALIZABLE isolation with innodb_snapshot_isolation=ON.
In this test case, Transaction 1 first executes a no-op UPDATE with WHERE FALSE, which matches and changes no rows. Then Transaction 2 inserts a new row and commits. After that, Transaction 1 executes another UPDATE on an existing row, but it fails with:
ERROR 1020 (HY000): Record has changed since last read in table 't0'
I think this behavior is unexpected because the first UPDATE in Transaction 1 does not read or modify any row, and the concurrent INSERT in Transaction 2 does not conflict with the row updated by Transaction 1. The final state can be serialized.
Here is the test case:
/* init */ DROP TABLE IF EXISTS t0;
/* init */ CREATE TABLE t0(c0 INT);
/* init */ INSERT INTO t0(c0) VALUES(1);
/* init */ INSERT INTO t0(c0) VALUES(2);
/* t1 */ BEGIN;
/* t1 */ UPDATE t0 SET c0=-1 WHERE false;
/* t2 */ BEGIN;
/* t2 */ INSERT INTO t0(c0) VALUES(3);
/* t2 */ COMMIT;
/* t1 */ UPDATE t0 SET c0=3 WHERE c0=1;
ERROR 1020 (HY000): Record has changed since last read in table 't'
/* t1 */ COMMIT;
Expected result:
Transaction 1 should not fail with ERROR 1020.The first UPDATE in Transaction 1 is a no-op and does not modify any row. Transaction 2 only inserts a new row. There is no unique constraint on c0, so duplicate values are allowed.
A valid serial execution exists. For example, if Transaction 2 is serialized before Transaction 1:
Initial state:
{1, 2}After Transaction 2:
{1, 2, 3}After Transaction 1:
{3, 2, 3}So the expected final result should be:
c0
–
2
3
3
The observed ERROR 1020 seems to be a false positive. If this behavior is expected under SERIALIZABLE with innodb_snapshot_isolation=ON, please clarify why a no-op UPDATE ... WHERE FALSE can cause a later UPDATE to fail after a concurrent non-conflicting INSERT.