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, transaction blocking, and metadata lock on the RDS instance. By using this feature, you can efficiently locate the deadlock issue and improve 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

Note

You can view the version of your RDS instance on the Basic Information page of the ApsaraDB RDS console. To use the deadlock analysis feature, the instance edition indicated by Type and Edition must be High-availability Edition, Enterprise Edition, or Cluster Edition, and the value of Database Engine must be a MySQL version.

The deadlock analysis feature is not supported by RDS Basic Edition instances.

Feature description

The deadlock analysis feature allows you to perform multi-dimensional analysis on deadlocks, transaction blocking, and metadata locks 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.
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.

Limits

You must configure required parameters to use the different capabilities of the deadlock analysis feature.

Feature	Required parameter
Recent Deadlock Analysis	Enable the `innodb_deadlock_detect` parameter.
Full Deadlock Analysis	Enable the `innodb_deadlock_detect` parameter. Enable the `innodb_print_all_deadlocks` parameter and set the `log_error_verbosity` parameter to 3.
Transaction blocking analysis in Other lock analysis	Enable the `performance_schema` parameter if your RDS instance runs MySQL 8.0.

For more information about how to modify the parameters of an RDS instance, see Modify instance parameters.

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 Lock 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. Find a task and click View Details in the Details column to view the details of the diagnostics results.
- 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 diagnostics results and the deadlock 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 specific 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.