INSERT returns deadlock instead of duplicate-key error after SELECT ... LOCK IN SHARE MODE is released under RR/SER with innodb_snapshot_isolation=ON

Description

I found a possible issue in MariaDB 12.2.2 and 12.3 with `innodb_snapshot_isolation=ON` under both `REPEATABLE READ` and `SERIALIZABLE`.

In the following test case, Transaction 2 first executes `SELECT ... LOCK IN SHARE MODE` and holds shared locks on the existing rows. Then Transaction 1 executes `DELETE FROM t0 WHERE TRUE` and becomes blocked by Transaction 2. After that, Transaction 3 executes an `INSERT` that conflicts with an existing unique key and is also blocked.

When Transaction 2 commits, both Transaction 1 and Transaction 3 are unblocked. However, I observed that statement `[3-1]` returns a deadlock error:

MariaDB [test]>  INSERT INTO t0 VALUES (0.63668, '');

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

I think the expected behavior should be different. Since `[1-1]` was blocked before `[3-1]`, after Transaction 2 commits, `[1-1]` should be resumed first. Then `[3-1]` should later succeeds after transaction 1 is committed.

Interestingly, if `[1-1]` is changed from:

DELETE FROM t0 WHERE TRUE;

to:

DELETE FROM t0 WHERE TRUE ORDER BY c0;

then the execution order behaves as expected.

I also observed a similar phenomenon in MySQL 8.4.9.

Minimal test case

CREATE TABLE t0(

    c0 REAL UNSIGNED UNIQUE NOT NULL,

    c1 CHAR(100) UNIQUE NOT NULL,

    PRIMARY KEY(c1, c0)

) ENGINE=InnoDB;

INSERT INTO t0 VALUES (0.35864, '');

INSERT INTO t0 VALUES (0.70547, '0.7054672067864869');

INSERT INTO t0 VALUES (0.65337, '-234246463');

Please run the test with:

SET SESSION innodb_snapshot_isolation=ON;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

The same behavior can also be reproduced under:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Transactions

– Transaction 1, with statements:

[1-0] START TRANSACTION WITH CONSISTENT SNAPSHOT;

[1-1] DELETE FROM t0 WHERE TRUE;

[1-2] COMMIT;

– Transaction 2, with statements:

[2-0] START TRANSACTION WITH CONSISTENT SNAPSHOT;

[2-1] SELECT t0.c1 FROM t0 WHERE TRUE LOCK IN SHARE MODE;

[2-2] COMMIT;

– Transaction 3, with statements:

[3-0] START TRANSACTION WITH CONSISTENT SNAPSHOT;

[3-1] INSERT INTO t0 VALUES (0.63668, '');

[3-2] INSERT INTO t0 VALUES (0.70547, '-1425360482');

[3-3] COMMIT;

Execute the transaction statements in the following order:

[3-0] START TRANSACTION WITH CONSISTENT SNAPSHOT;

[1-0] START TRANSACTION WITH CONSISTENT SNAPSHOT;

[2-0] START TRANSACTION WITH CONSISTENT SNAPSHOT;

[2-1] SELECT t0.c1 FROM t0 WHERE TRUE LOCK IN SHARE MODE;

[1-1] DELETE FROM t0 WHERE TRUE; -- blocked by Transaction 2

[3-1] INSERT INTO t0 VALUES (0.63668, ''); -- blocked by Transaction 2

[3-2] INSERT INTO t0 VALUES (0.70547, '-1425360482');

[2-2] COMMIT

[3-1] INSERT INTO t0 VALUES (0.63668, ''); -- [3-1] resumed and report ERROR 1213:deadlock

[1-1] DELETE FROM t0 WHERE TRUE; -- [1-1] resumed

[1-2] COMMIT;

[3-3] COMMIT;

Expected result

After Transaction 2 commits and releases its shared locks, I expected `[1-1]` to be resumed first, because it was blocked before `[3-1]`.

Actual result

After Transaction 2 commits, `[3-1]` is resumed and returns:

MariaDB [test]>  INSERT INTO t0 VALUES (0.63668, '');

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

This seems unexpected to me, because `[1-1]` was blocked first, and `[3-1]` appears to be a duplicate-key conflict.

Additional observation

If `[1-1]` is changed from:

DELETE FROM t0 WHERE TRUE;

to:

DELETE FROM t0 WHERE TRUE ORDER BY c0;

then the execution order appears to follow the expected behavior: `[1-1]` is resumed first, and `[3-1]` later succeeds after transaction 1 is committed.

Could you please help verify whether this deadlock is expected behavior? If it is expected, I would appreciate an explanation of why `[3-1]` can be selected as the deadlock victim instead of simply returning a duplicate-key error.