Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
12.2.2
-
MariaDB Server 12.2.2
Storage engine: InnoDB
Isolation level: SERIALIZABLE
Table engine: InnoDB
innodb_snapshot_isolation = ON
-
Not for Release Notes
Description
I found a behavior difference under SERIALIZABLE isolation with concurrent UPDATE statements.
In this case, Transaction 1 updates a non-primary-key column of all rows, while Transaction 2 concurrently updates the primary key of one of the rows. Transaction 2 waits for Transaction 1 to commit, but in MariaDB 12.2.2 it may fail with:
```text
ERROR 1020 (HY000): Record has changed since last read in table 't0'
```
Minimal reproducible test case:
```sql
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(
c0 INT,
c1 INT,
c2 INT PRIMARY KEY
);
INSERT IGNORE INTO t0(c0, c1, c2) VALUES (-1, 0, 1);
INSERT IGNORE INTO t0(c0, c1, c2) VALUES (2, 3, 4);
```
Connection 1:
```sql
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
UPDATE t0 SET c0 = 6 WHERE 4;
```
Connection 2:
```sql
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
UPDATE t0 SET c2 = 2 WHERE c2 = 1;
```
Then in Connection 1:
```sql
COMMIT;
```
Observed behavior in MariaDB 12.2.2:
After Connection 1 commits, the UPDATE in Connection 2 fails with:
```text
ERROR 1020 (HY000): Record has changed since last read in table 't0'
```
Expected behavior:
The UPDATE in Connection 2 should wait until Connection 1 commits, then continue execution and update the row whose primary key is `c2 = 1` to `c2 = 2`.
The expected final table state is:
```text
c0 c1 c2
6 0 2
6 3 4
```
Schedule:
```text
T1 BEGIN
T2 BEGIN
T1 UPDATE t0 SET c0 = 6 WHERE 4
T2 UPDATE t0 SET c2 = 2 WHERE c2 = 1
T1 COMMIT
T2 COMMIT
```
This concurrent execution is equivalent to the serial order:
```text
T1 -> T2
```
because Transaction 1 only changes `c0`, while Transaction 2 changes the primary key `c2` of the row where `c2 = 1` after Transaction 1 commits.