In the MySQL manual, in the section Deadlocks in InnoDB, it states:

The possibility of deadlocks is not affected by the isolation level, because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations.

But this is contradicted in the sub-section How to Minimize and Handle Deadlocks, which says:

If you use locking reads...try using a lower isolation level such as READ COMMITTED.

So which is true? That deadlocks are not affected by the isolation level, or that a lower isolation level might help to minimize deadlocks?

I know the subject of RDBMS deadlocks is notoriously complicated and widely misunderstood. But having unclear information in the official docs doesn't help at all...

Would any MySQL experts be willing to clear up my confusion?

In the MySQL manual, in the section Deadlocks in InnoDB, it states:

The possibility of deadlocks is not affected by the isolation level, because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations.

But this is contradicted in the sub-section How to Minimize and Handle Deadlocks, which says:

If you use locking reads...try using a lower isolation level such as READ COMMITTED.

So which is true? That deadlocks are not affected by the isolation level, or that a lower isolation level might help to minimize deadlocks?

I know the subject of RDBMS deadlocks is notoriously complicated and widely misunderstood. But having unclear information in the official docs doesn't help at all...

Would any MySQL experts be willing to clear up my confusion?

• mysql
• concurrency
• transactions
• database-deadlocks

1 Answer 1

That's a great point out.
I think the possibility if deadlocks is certainly affected by transaction levels.

because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations.

Is a categorically incorrect statement IMHO.
To give a counter example, BEGIN; UPDATE TABLE_A SET B=5; COMMIT; will take a gap-lock (given that column B is not a unique index.) if the isolation level is REPEATABLE_READ. However, if isolation level is READ_COMMITTED it would not take a gap lock https://dev.mysql/doc/refman/8.4/en/innodb-locking.html#innodb-gap-locks

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

There are also other effects of using the READ COMMITTED isolation level. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. For UPDATE statements, InnoDB does a “semi-consistent” read, such that it returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE.

Gap lock certainly does affect the possibility of deadlocks.