Unexpected deadlock under RR/SER after blocked SELECT FOR UPDATE with innodb_snapshot_isolation=ON

Description

I found a possible InnoDB locking-read issue in MariaDB 12.2.2 with `innodb_snapshot_isolation=ON`.
In the following test case, the table initially contains only one row:

(9)

Transaction 1 updates this row from `9` to `4` and keeps the transaction open. Then Transaction 2 executes:

SELECT * FROM t0 WHERE TRUE FOR UPDATE;

This statement is blocked because Transaction 1 still holds the lock on the row.

After that, Transaction 1 executes another update on the same row:

UPDATE t0 SET c0=1 WHERE TRUE;

Under `READ COMMITTED`, the statement can execute successfully. However, under `REPEATABLE READ`, a deadlock is reported after Transaction 1 executes the second update. I also observed the same deadlock behavior under `SERIALIZABLE`.

I think this behavior is suspicious. Since the only row in the table has been locked by Transaction 1 since `[1-1]`, Transaction 2 is only waiting and should not have acquired the row lock. Therefore, Transaction 1 should be able to continue updating its own locked row. I would not expect a deadlock to be reported at `[1-2]`.

The same phenomenon can also be observed when the statement `[2-1]` is replaced by an `UPDATE` statement or by `SELECT ... LOCK IN SHARE MODE`.

How to repeat

Create and initialize the table:

DROP TABLE IF EXISTS t0;

CREATE TABLE t0(

c0 INT PRIMARY KEY

);

INSERT INTO t0(c0) VALUES (9);

Use two sessions.

For the `READ COMMITTED` test, set both sessions to:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

For the `REPEATABLE READ` test, set both sessions to:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

For the `SERIALIZABLE` test, set both sessions to:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Transaction 1:

[1-0] BEGIN;

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

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

[1-3] COMMIT;

Transaction 2:

[2-0] BEGIN;

[2-1] SELECT * FROM t0 WHERE TRUE FOR UPDATE;

[2-2] COMMIT;

Execute the statements in the following order:

[1-0] BEGIN;

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

[2-0] BEGIN;

[2-1] SELECT * FROM t0 WHERE TRUE FOR UPDATE;

-- blocked

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

-- deadlock under REPEATABLE READ / SERIALIZABLE

[1-3] COMMIT;

[2-2] COMMIT;

Actual result

Under `READ COMMITTED`, `[1-2]` can execute successfully.
Under `REPEATABLE READ`, `[1-2]` reports a deadlock after `[2-1]` is blocked:

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

The same deadlock behavior is also observed under `SERIALIZABLE`.

Expected result

I expected Transaction 1 to continue successfully at `[1-2]`.

The table contains only one row initially, `(9)`. Transaction 1 updates this row to `(4)` at `[1-1]` and keeps holding the lock. Transaction 2 starts `[2-1] SELECT ... FOR UPDATE` later and becomes blocked. Since Transaction 2 is only waiting and should not have acquired the row lock, it should not prevent Transaction 1 from updating its own locked row again.

Therefore, `[1-2] UPDATE t0 SET c0=1 WHERE TRUE;` should not report a deadlock merely because Transaction 2 is waiting on the row.

A more reasonable behavior would be:

1. Transaction 1 executes `[1-2]` successfully;
2. Transaction 2 remains blocked until Transaction 1 commits;
3. after Transaction 1 commits, Transaction 2 either resumes according to the snapshot-isolation rules or reports an appropriate consistency error such as `ERROR 1020`, if the row-change check applies.

Additional observations

The same issue can also be reproduced when `[2-1]` is replaced by an `UPDATE` statement or by a `SELECT ... LOCK IN SHARE MODE` statement.

This suggests that the issue may be related to the lock-wait / deadlock-detection path when `innodb_snapshot_isolation=ON` under `REPEATABLE READ` and `SERIALIZABLE`.

The key point is that Transaction 1 is the transaction that already holds the lock on the only row. Transaction 2 is only waiting. Therefore, I do not expect Transaction 2's waiting lock request to create a deadlock that prevents Transaction 1 from updating the same row again.

If this is expected behavior, could you please explain why a waiting `SELECT ... FOR UPDATE` under `REPEATABLE READ` / `SERIALIZABLE` can cause the blocking transaction to deadlock when it updates its own locked row again?