SELECT ... FOR UPDATE returns empty set instead of ER_CHECKREAD 1020 after concurrent DELETE commits with innodb_snapshot_isolation=ON under RR/SER

Description

I found a possible inconsistency 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 starts with `START TRANSACTION WITH CONSISTENT SNAPSHOT`. Then Transaction 1 deletes all rows but does not commit immediately. When Transaction 2 executes `SELECT ... FOR UPDATE`, it is blocked by Transaction 1. After Transaction 1 commits, I would expect Transaction 2 to report:

SELECT t0.c0, t0.c1, t0.c2 FROM t0 WHERE TRUE FOR UPDATE;

ERROR 1020 (HY000): Record has changed since last read in table 't0'

However, in MariaDB 12.2.2 and 12.3, after the blocking is released, Transaction 2 returns an empty result set instead.

This seems inconsistent with the expected behavior of START TRANSACTION WITH CONSISTENT SNAPSHOT together with innodb_snapshot_isolation=ON, because the rows were visible in Transaction 2's transaction snapshot, but were deleted and committed by another transaction before Transaction 2 could lock them.

Minimal test case

CREATE TABLE t0(

    c0 VARCHAR(100) UNIQUE NOT NULL,

    c1 INT NOT NULL,

    c2 VARCHAR(100) UNIQUE,

    PRIMARY KEY(c0, c1, c2)

) ENGINE=InnoDB;

INSERT INTO t0 VALUES ('', -1337292163, '');

INSERT INTO t0 VALUES ('1064601816', 2103997766, 'u|');

CREATE INDEX ic0 ON t0(c2 DESC, c0 ASC, c1);

Please run the following 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;

– 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.c0, t0.c1, t0.c2 FROM t0 WHERE TRUE FOR UPDATE;

[2-2] COMMIT;

Execute the transaction statements in the following order:

[2-0] START TRANSACTION WITH CONSISTENT SNAPSHOT;

[1-0] START TRANSACTION WITH CONSISTENT SNAPSHOT;

[1-1] DELETE FROM t0 WHERE TRUE;

[2-1] SELECT t0.c0, t0.c1, t0.c2 FROM t0 WHERE TRUE FOR UPDATE; -- blocked

[1-2] COMMIT;

[2-2] COMMIT;

Expected result

After Transaction 1 commits, statement `[2-1]` should fail with:

MariaDB [test]> SELECT t0.c0, t0.c1, t0.c2 FROM t0 WHERE TRUE FOR UPDATE;

ERROR 1020 (HY000): Record has changed since last read in table 't0'

This is because Transaction 2 started with a consistent snapshot before Transaction 1 committed the DELETE. The rows were visible in Transaction 2's snapshot, but were deleted and committed by Transaction 1 before Transaction 2 could acquire the locks.

Therefore, after the blocking is released, the locking read should detect that the records have changed since the snapshot read and report ER_CHECKREAD 1020.

Actual result

In MariaDB 12.2.2 and 12.3, after Transaction 1 commits, statement `[2-1]` does not report `ERROR 1020`.

Instead, it returns an empty result set:

MariaDB [test]> SELECT t0.c0, t0.c1, t0.c2 FROM t0 WHERE TRUE FOR UPDATE;

Empty set

So the committed DELETE is silently reflected as an empty result, instead of being reported through `ER_CHECKREAD`.

Additional observations

I tried several variants of this test case on MariaDB 12.2.2 and 12.3 under both REPEATABLE READ and SERIALIZABLE with innodb_snapshot_isolation=ON. The conclusions are as follows:

1. If START TRANSACTION WITH CONSISTENT SNAPSHOT is replaced by BEGIN, statement `[2-1]` still does not report ERROR 1020. It returns an empty result set after the blocking is released.

2. If the statement in Transaction 1 is changed from DELETE FROM t0 WHERE TRUE to an INSERT or UPDATE ... WHERE TRUE, or if statement `[2-1]` is changed from SELECT ... FOR UPDATE to UPDATE ... WHERE TRUE or DELETE ... WHERE TRUE, then the blocked statement can correctly report ERROR 1020.

3. If statement `[2-1]` is changed from SELECT ... WHERE TRUE FOR UPDATE to SELECT ... WHERE TRUE LOCK IN SHARE MODE, the behavior is the same as SELECT ... WHERE TRUE FOR UPDATE: after the blocking is released, it returns an empty result set instead of reporting `ERROR 1020`.

Therefore, the issue seems specific to the case where a locking read, such as SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MOD, waits for a concurrent DELETE to commit.

This may also be related to the behavior discussed in MDEV-39992. In that report, a similar issue was unintentionally involved, but I did not notice this specific pattern at that time. Based on the discussion there, it seems possible that MariaDB 11.4 and later versions behave differently from earlier versions: earlier versions report ER_CHECKREAD, while MariaDB 11.4+ silently returns an empty result set.