By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

'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

0

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.

Topics
Database
Tags
DatabaseMySQL
Language
English
karan
asked a year ago670 views
1 Answer
1

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 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.

profile picture
EXPERT
Osvaldo Marte
answered a year ago
  • karan
    a year ago

    Thanks for the insights. The isolation level is same in both 5.7 and 8.0. There are no slow queries. The deadlock is not getting captured in SHOW INNODB ENGINE STATUS; logs.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content