Deadlock Troubleshooting in Percona Server for MySQL 5.7

Disclaimer: the following script only works for Percona Server for MySQL 5.7, and relies on enabling performance schema (PS) instrumentation which can add overhead on high concurrent systems, and is not intended for continuous production usage as it’s a POC (proof of concept).

Introduction

Deadlock Troubleshooting MySQLIn Percona Support, we frequently receive tickets related to deadlocks and even though the deadlock concept is simple, troubleshooting might not be in all cases. 

As explained in How to Deal with MySQL Deadlocks, a deadlock occurs when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. MySQL will detect deadlocks and kill one of the transactions (making it rollback), and the deadlock will be printed in SEIS (show engine innodb status). Limitations of using this approach are that 1) Only one (latest) deadlock will be printed -potentially missing many of the deadlocks that occur if you are not actively checking- (unless enabling innodb_print_all_deadlocks), and 2) only the last executed DML is printed, which might not shed enough light to identify the offending transaction for complex deadlocks.

Using pt-deadlock-logger will overcome the difficulty from number one, as it will log all deadlocks occurring, but what about number two?

For Percona Server for MySQL 5.7, I have developed the following script (that you can find in our support snippets repo) that prints SQL history for two transactions involved in a deadlock, that can help in troubleshooting some complex deadlock cases. Note that most of the time, a deadlock will consist of two transactions, but other times there can be more transactions involved, in which case the script will be partially useful.

Installation

Executing the script will show the following prompts:

Then, the operating system prompt won’t be returning, as the script will keep running, scanning for deadlocks. (You can execute with “&” to run in the background.)

Note: deadlock_logger script will start logging deadlocks occurring only AFTER the script execution.

Generating a Deadlock

You need to open two sessions and execute the following interleaved:

Checking “deadlock_logger.sh” output we can see the history of transactions for above threads (you need to read from bottom to the top):

It can be seen that the entire history transaction is being fetched from Performance Schema tables, which helps identify deadlocks in complex transactions.

Limitations of the Script

  • Currently only works for Percona Server for MySQL 5.7
  • Enabling Performance Schema instrumentation will add some overhead to MySQL
  • The script works by tailing the error.log, so modifying the location or content at runtime can have unexpected results
  • The script relies on “performance_schema.events_statements_history” whose max rows are limited by  performance_schema_events_statements_history_long_size and performance_schema_events_statements_history_size. For high activity servers, the history table might rotate before the deadlock occurs, resulting in a partially incomplete print of statements, instead of printing the entire life of the thread.

Conclusion

Troubleshooting deadlocks can be hard, but after checking the entire history of statements executed for the threads involved in the deadlock, it can be easier to understand why a deadlock happened. Test the script and performance degradation before going to high activity and/or critical production systems!


Our solution brief “Get Up and Running with Percona Server for MySQL” outlines setting up a MySQL® database on-premises using Percona Server for MySQL. It includes failover and basic business continuity components.

Download PDF

Share this post

Leave a Reply