'Deadlock found when trying to get lock; try restarting transaction' in AWS RDS MySQL 8.0.36 after upgrading from MySQL 5.7.42, Using Django v4.1.6

Question

We recently upgraded our AWS RDS MySQL database from version 5.7.42 to 8.0.36. We are using Django version 4.1.6. Since the upgrade, we've been encountering frequent deadlock issues, even during read operations, which were not present with MySQL 5.7.

The error message we're encountering is:

"Deadlock found when trying to get lock; try restarting transaction".

Our application primarily performs read operations, and we haven't made significant changes to the database schema or application logic that would explain this sudden increase in deadlock occurrences.

Additionally, we use generic relations in some of our models and have asynchronous jobs running. I'm not sure if these factors could be contributing to the issue, but I thought it's worth mentioning.

We're seeking guidance on how to troubleshoot and mitigate these deadlock issues in our MySQL 8.0.36 and Django 4.1.6 environment and assistance to determine if any parameters or database settings introduced in MySQL 8.0 might be contributing to this issue, which wasn't present in version 5.7. Any insights, suggestions, or best practices would be greatly appreciated.

I attempted to identify deadlock transactions by querying the SHOW INNODB STATUS command and inspecting waiting transactions in performance_schema.locks. As these deadlocks occur unpredictably across various queries, modifying the queries or code isn't feasible.

Answer

The sudden increase in deadlock occurrences after upgrading to `MySQL 8.0.36` suggests that there might be some compatibility issues or changes in default settings that could be contributing to this problem.

#### What you can do:
- MySQL 8.0 introduced some changes in transaction isolation levels, and the default isolation level is now `REPEATABLE READ`.
> 💡 This isolation level can increase the chances of deadlocks, especially in applications with a high degree of concurrency.

**Action:** You can try changing the isolation level to `READ COMMITTED` or `READ UNCOMMITTED` (if your application can tolerate some anomalies) to see if it helps reduce the deadlock occurrences.

---

- Long-running queries can increase the likelihood of deadlocks by holding locks for an extended period.

**Action:** You can use the `SHOW PROCESSLIST` statement or the `performance_schema.events_statements_current` table to identify and optimize slow queries.

---

- MySQL 8.0 introduced some new settings and changes to existing settings that can impact concurrency and deadlock behavior.

**Action:** You can review the [MySQL documentation](https://dev.mysql.com/doc/refman/8.3/en/innodb-deadlocks.html) and consider adjusting settings like `innodb_lock_wait_timeout`, `innodb_deadlock_detect`, and `innodb_spin_wait_delay` to see if they help mitigate the deadlock issues.

'Deadlock found when trying to get lock; try restarting transaction' in AWS RDS MySQL 8.0.36 after upgrading from MySQL 5.7.42, Using Django v4.1.6

What you can do:

Relevant content