Description:
Description:
I found a possible InnoDB deadlock-detection issue in MySQL 8.4.9.
In the following two-transaction test case, Transaction 1 updates the only row in the table 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, this statement can execute successfully. However, under REPEATABLE READ and SERIALIZABLE, a deadlock is reported when this second UPDATE is executed.
I think this behavior is suspicious. The table initially contains only one row, (9). Transaction 1 updates this row first and keeps holding the row lock. Transaction 2 starts later and is only waiting for the lock; it should not have acquired the row lock. Therefore, Transaction 1 should be able to continue updating the row it already locked. I would not expect a deadlock to be reported in this schedule.
This issue is reproducible under REPEATABLE READ and SERIALIZABLE, but not under READ COMMITTED.
How to repeat:
How to repeat:
Tested on MySQL 8.4.9 with InnoDB.
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 and SERIALIZABLE, a deadlock is reported when [1-2] is executed:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Expected result:
I expected [1-2] to execute successfully under REPEATABLE READ and SERIALIZABLE as well.
Transaction 1 is the transaction that already updated and locked the only row. Transaction 2 only starts later and waits on SELECT ... FOR UPDATE. Since Transaction 2 has not acquired the row lock, its waiting lock request should not prevent Transaction 1 from updating its own locked row again.
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 resumes or returns an appropriate error if the row-change check requires it.
Returning a deadlock here seems unexpected because there does not appear to be a real circular dependency: Transaction 2 is waiting for Transaction 1, while Transaction 1 should already own the relevant row lock.
© 2026, Oracle Corporation and/or its affiliates