READ UNCOMMITTED unexpectedly avoids a deadlock that occurs under READ COMMITTED with innodb_snapshot_isolation=ON

I found a possible inconsistency between READ COMMITTED and READ UNCOMMITTED in MariaDB 12.2.2 when innodb_snapshot_isolation is enabled.

In the following concurrent transaction schedule, READ COMMITTED reports a deadlock when executing the UPDATE statement in Transaction 103, while READ UNCOMMITTED successfully executes the same UPDATE statement.

This is unexpected because the statements involved are DELETE, INSERT, SELECT ... LOCK IN SHARE MODE, and UPDATE. The difference between READ COMMITTED and READ UNCOMMITTED should mainly affect ordinary non-locking SELECT visibility. Locking reads and write statements should still respect row locks and should not bypass conflicting uncommitted writes. In this case, both isolation levels appear to have the same lock dependency structure and therefore should both report a deadlock.

Reproduction:

SET GLOBAL innodb_snapshot_isolation = ON;

Reconnect the sessions after setting the global variable, or ensure that the session uses innodb_snapshot_isolation=ON.

Schema initialization:

CREATE TABLE t0(

    c0 BOOLEAN UNIQUE,

    c1 REAL ZEROFILL NOT NULL,

    c2 BIGINT,

    c3 VARCHAR(100) UNIQUE NOT NULL

) ENGINE=InnoDB;

INSERT INTO t0 VALUES (NULL, 0.71535, 807633510, '');

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

Transaction 102:

[102-0]BEGIN;

[102-1]DELETE FROM t0 WHERE TRUE;

[102-2]SELECT t0.c3, t0.c0 FROM t0 WHERE TRUE LOCK IN SHARE MODE;

[102-3]COMMIT;

Transaction 103:

[103-0]BEGIN;

[103-1]INSERT INTO t0 VALUES (1, 0.12098, 1600742384, '0.5007103978381064');

[103-2]UPDATE t0 SET c2 = DEFAULT, c0 = DEFAULT WHERE TRUE;

[103-3]COMMIT;

The statements are executed according to this schedule:

[102-0] BEGIN;

[102-1] DELETE FROM t0 WHERE TRUE;

[103-0] BEGIN;

[103-1] INSERT INTO t0 VALUES (1, 0.12098, 1600742384, '0.5007103978381064');

[102-2] SELECT t0.c3, t0.c0 FROM t0 WHERE TRUE LOCK IN SHARE MODE;  -- [102-2] is blocked

[103-2] UPDATE t0 SET c2 = DEFAULT, c0 = DEFAULT WHERE TRUE; 

[103-3] COMMIT;

[102-3] COMMIT;

Observed result under READ COMMITTED:

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

The error occurs when executing:

UPDATE t0 SET c2 = DEFAULT, c0 = DEFAULT WHERE TRUE;

Observed result under READ UNCOMMITTED:

UPDATE t0 SET c2 = DEFAULT, c0 = DEFAULT WHERE TRUE;

Query OK, 1 row affected (0.000 sec)

Rows matched: 1  Changed: 1  Warnings: 0

Expected result:

READ UNCOMMITTED should not avoid the deadlock in this case. The same UPDATE should also encounter a deadlock, or MariaDB should document why READ UNCOMMITTED is allowed to bypass this lock dependency when innodb_snapshot_isolation=ON.

Reasoning:
The expected lock dependency is as follows:

Transaction 102 executes:

DELETE FROM t0 WHERE TRUE;

This deletes the initial row and holds an exclusive lock on it until Transaction 102 commits.

Transaction 103 executes:

INSERT INTO t0 VALUES (1, 0.12098, 1600742384, '0.5007103978381064');

This inserts a new row and holds locks on the uncommitted inserted row.

Transaction 102 then executes:

SELECT t0.c3, t0.c0 FROM t0 WHERE TRUE LOCK IN SHARE MODE;

This is a locking read. It should need to lock matching rows, including the row inserted by Transaction 103, so Transaction 102 waits for Transaction 103.

Transaction 103 then executes:

UPDATE t0 SET c2 = DEFAULT, c0 = DEFAULT WHERE TRUE;

This UPDATE should process the row deleted but not yet committed by Transaction 102. Therefore Transaction 103 should wait for Transaction 102.

This creates the following wait cycle:
Transaction 102 waits for Transaction 103
Transaction 103 waits for Transaction 102

Therefore a deadlock is expected.

Comparison with MySQL:

I also tested the same case on MySQL 8.0.25. Under both READ COMMITTED and READ UNCOMMITTED, MySQL reports:

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

This suggests that MariaDB 12.2.2 may be handling READ UNCOMMITTED locking reads or UPDATE differently when innodb_snapshot_isolation=ON.

Actual problematic behavior:
In MariaDB 12.2.2 with innodb_snapshot_isolation=ON, READ COMMITTED reports the expected deadlock, but READ UNCOMMITTED allows the UPDATE to succeed:

UPDATE t0 SET c2 = DEFAULT, c0 = DEFAULT WHERE TRUE;

This may indicate that READ UNCOMMITTED incorrectly bypasses a lock conflict for locking reads or UPDATE statements under innodb_snapshot_isolation=ON.