Use the deadlock analysis feature for an ApsaraDB RDS for MySQL instance - ApsaraDB RDS

If your ApsaraDB RDS for MySQL instance is locked, you can use the deadlock analysis feature to view and analyze the latest deadlock of the RDS instance. The deadlock analysis feature helps locate and resolve the deadlock issue in an efficient manner and ensures the stability of your RDS instance.

Prerequisites

Your RDS instance runs one of the following MySQL versions and RDS editions:
- MySQL 8.0 on RDS High-availability Edition, RDS Enterprise Edition, or RDS Cluster Edition
- MySQL 5.7 on RDS High-availability Edition, RDS Enterprise Edition, or RDS Cluster Edition
- MySQL 5.6 on RDS High-availability Edition
- MySQL 5.5 on RDS High-availability Edition

The value of the innodb_deadlock_detect parameter is set to ON. For more information about how to modify the parameters of an RDS instance, see Modify instance parameters.

Introduction

The deadlock analysis feature allows you to perform multi-dimensional deadlock analysis on an RDS instance.

Important

If you want to enable the full deadlock analysis and other deadlock analysis features for your RDS instance, you must enable Database Autonomy Service (DAS) Cost-efficient Edition or DAS Enterprise Edition. DAS Cost-efficient Edition and DAS Enterprise Edition are available only in some regions. For more information, see Supported databases and regions. For more information about how to enable DAS Cost-efficient Edition or DAS Enterprise Edition, see Enable DAS Cost-efficient Edition and DAS Enterprise Edition.

Recent deadlock analysis: DAS analyzes the latest deadlock log that is displayed in the output of the SHOW ENGINE INNODB STATUS statement. If multiple deadlocks occur, DAS analyzes only the latest deadlock.
Full deadlock analysis: DAS analyzes error logs at regular intervals, parses the deadlock information, and performs comprehensive deadlock analysis. DAS also allows you to view the deadlock trends within a specified time range and view details of each deadlock.
Important
To use the full deadlock analysis feature, you must enable the innodb_print_all_deadlocks parameter and set the log_error_verbosity parameter to 3. For more information about how to modify the parameters of an RDS instance, see Modify instance parameters.
Other lock analysis: DAS analyzes the metadata lock and transaction blocking in the current sessions of your RDS instance in real time based on the data in information_schema and performance_schema.
- Metadata lock analysis: DAS deduces the lock wait relationship and generates the required diagram based on data in tables such as information_schema.processlist.
- Transaction blocking analysis: DAS analyzes the transaction blocking relationship and generates the required diagram based on the data in the information_schema.processlist and information_schema.innodb_trx tables and the configuration of the information_schema.innodb_lock_waits and performance_schema.data_lock_waits parameters. The information_schema.innodb_lock_waits parameter is used for MySQL 5.6 and MySQL 5.7, and the information_schema.innodb_lock_waits parameter is used for MySQL 8.0.

Usage notes

The deadlock analysis feature cannot be used to analyze deadlocks that contain the following information: TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION.

Procedure

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, choose Autonomy Services > Diagnostics.
Click the Deadlock Analysis tab.
On the Deadlock Analysis tab, click Diagnose in the upper-left corner.
Find the generated deadlock entry and click View Details in the Details column.
On the Deadlock Analysis page, view or diagnose the latest deadlocks of the RDS instance.
- Recent Deadlock Analysis
  - On the Recent Deadlock Analysis tab, click Create Analysis Task. DAS performs the analysis task based on the latest deadlock log that is displayed in the output of the SHOW ENGINE INNODB STATUS statement.
  - Specify a time range to view the diagnostic result. Click View Details in the Details column to view the details of the diagnostic result.
- Full Deadlock Analysis
  On the Full Deadlock Analysis tab, view the deadlock trends and details of each deadlock within a specified time range.
- Other Deadlock Analysis
  - On the Other Deadlock Analysis tab, click Create Analysis Task. DAS analyzes the metadata locks and transaction blocking of the current sessions of your RDS instance in real time based on the data in information_schema and performance_schema.
  - Specify a time range to view the diagnostic result.
    1. Click the icon to the left of the diagnostic result to view the statistics.
    2. Click View Details in the Actions column to view the details of the diagnostic result and the lock analysis diagram.
      When you move the pointer over a session, you can view the lock wait diagram of the session. You can click the session to view session details.

What to do next

When a deadlock occurs on your RDS instance, you can use the information such as the thread IDs obtained after the deadlock analysis to analyze the execution of related transactions on the SQL Explorer and Audit page. This helps identify the cause of the deadlock. For example, after a transaction is started and a specific number of data rows are updated, the data rows are locked. If the execution of the transaction lasts for a long period of time and the transaction is not explicitly or implicitly committed or rolled back, subsequent sessions or transactions will enter the LOCK WAIT state when the same data rows are updated.