READ UNCOMMITTED UPDATE skips a row locked by uncommitted DELETE with innodb_snapshot_isolation=ON

Description:

I found a possible inconsistency in MariaDB 12.2.2 when innodb_snapshot_isolation=ON.

In the following concurrent transaction case, Transaction 268 first deletes the only row in the table but does not commit. Then Transaction 267 executes an UPDATE with WHERE TRUE.

Under READ UNCOMMITTED, the UPDATE in Transaction 267 does not block. It succeeds immediately. However, under READ COMMITTED, the same UPDATE is blocked as expected.

This looks suspicious because the UPDATE statement is a write statement and should need to acquire a lock on the row that is currently locked by the uncommitted DELETE. The difference seems specific to READ UNCOMMITTED with innodb_snapshot_isolation=ON.

How to repeat:
Please enable innodb_snapshot_isolation = ON.

Schema initialization:

CREATE TABLE t0(

    c0 REAL UNIQUE NOT NULL,

    c1 BOOLEAN NOT NULL,

    c2 REAL UNIQUE PRIMARY KEY NOT NULL

) ;

INSERT INTO t0 VALUES (0.35578, true, 0.38297);

CREATE INDEX ic0 USING BTREE ON t0(c0 ASC);

Initial table state:

c0          c1    c2

0.35578  1     0.38297

– Transaction 267, with statements:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

[267-0] BEGIN;

[267-1] UPDATE t0 SET c0=0.58097 WHERE TRUE;

[267-2] COMMIT;

– Transaction 268, with statements:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

[268-0] BEGIN;

[268-1] DELETE FROM t0 WHERE TRUE;

[268-2] COMMIT;

Execute the statements in the following order:

[267-0] BEGIN;

[268-0] BEGIN;

[268-1] DELETE FROM t0 WHERE TRUE;

        -- Query OK, 1 row affected

[267-1] UPDATE t0 SET c0 = 0.58097 WHERE TRUE; [ expected: blocked, actual: unblocked]

        -- Query OK, 0 rows affected (0.000 sec)   

            Rows matched: 0  Changed: 0  Warnings: 0

[268-2] COMMIT;

[267-2] COMMIT;

Observed result under READ COMMITTED:
Using the same schedule with:
[267-1] is blocked by the uncommitted DELETE in Transaction 268.
This behavior seems expected, because Transaction 268 holds the lock on the row deleted by:

[268-1] DELETE FROM t0 WHERE TRUE;

Additional observations:
The behavior also changes if the statements are slightly modified under READ UNCOMMITTED with the same execution schedule.

1. If [267-1] is changed from UPDATE to SELECT ... FOR UPDATE, it blocks as expected:

SELECT * FROM t0 WHERE TRUE FOR UPDATE;

2. If [267-1] is changed to SELECT ... LOCK IN SHARE MODE, it also blocks as expected:

SELECT * FROM t0 WHERE TRUE LOCK IN SHARE MODE;

3. If [268-1] is changed from DELETE to UPDATE, then [267-1] also blocks as expected:

UPDATE t0 SET c0 = c0 WHERE TRUE;

Why this looks suspicious:
READ UNCOMMITTED may allow dirty reads for ordinary SELECT statements, but this case involves an UPDATE statement. UPDATE is a write statement and should acquire locks for rows it updates. Under the same schedule, READ COMMITTED blocks correctly. Also, replacing the UPDATE with locking reads causes blocking, and replacing the concurrent DELETE with UPDATE also causes blocking.