Analyze RDS MySQL deadlocks - ApsaraDB RDS - Alibaba Cloud Documentation Center

When deadlocks occur on your ApsaraDB RDS for MySQL instance, you can use the Lock Analysis feature to visually analyze recent deadlocks, transaction blocking, and metadata lock waits. This helps you quickly identify the root cause of deadlocks and improve database stability.

Prerequisites

Your instance must be one of the following editions:

RDS MySQL 8.4 High-availability Edition or Cluster Edition
RDS MySQL 8.0 High-availability Edition, Three-node Enterprise Edition, or Cluster Edition
RDS MySQL 5.7 High-availability Edition, Three-node Enterprise Edition, or Cluster Edition
RDS MySQL 5.6 High-availability Edition
RDS MySQL 5.5 High-availability Edition

Note

On the Basic Information page of the instance, verify that the Type and Edition is High-availability Edition, Three-node Enterprise Edition, or Cluster Edition, and that the Database Engine is MySQL.

Basic Edition instances do not support Lock Analysis.

Features

The Lock Analysis feature provides multi-dimensional analysis of deadlocks, transaction blocking, and metadata lock waits on your database instance.

Important

The Full Deadlock Analysis and Other Lock Analysis features require DAS Professional Edition or DAS Enterprise Edition, which are available only in specific regions. For more information, see Databases and regions supported by different DAS editions. To enable DAS Professional Edition or DAS Enterprise Edition, see Manage DAS audit logs and O&M services (formerly Professional Edition).

Last Deadlock Analysis: DAS analyzes the most recent deadlock log returned by the SHOW ENGINE INNODB STATUS command.
Full Deadlock Analysis: DAS periodically scans the error log to parse deadlock information and perform a comprehensive analysis. You can also view deadlock trends and the details of each deadlock within a specified time range.
Other Lock Analysis: DAS uses data from information_schema and performance_schema to perform real-time analysis of metadata locks and transaction blocking within the current sessions of your database instance.
- Metadata lock analysis: DAS infers lock-wait relationships from data in tables such as information_schema.processlist and visualizes the relationships as a graph.
- Transaction blocking analysis: DAS analyzes transaction blocking relationships and visualizes them as a graph. It uses data from information_schema.processlist, information_schema.innodb_trx, and either information_schema.innodb_lock_waits (for MySQL 5.6 and 5.7) or performance_schema.data_lock_waits (for MySQL 8.0).

Instance parameter requirements

To use the Lock Analysis features, you must configure the required parameters for the target database instance.

Feature	Required parameters
Last 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	For RDS MySQL 8.0 instances, you must enable the `performance_schema` parameter.

To modify instance parameters, see Configure instance parameters.

Usage notes

Lock Analysis currently does not support the analysis of deadlocks that contain the description "TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION".

Procedure

Go to the RDS Instance List page, select a region at the top, and then click the ID of the target instance.
In the left-side navigation pane, choose Autonomy Services > Diagnostics.
Click the Lock Analysis tab.
On the Lock Analysis page, click Create Analysis in the left-side pane.
(Optional) If a deadlock is found, find the new entry in the diagnosis list and click View Details in the Details column.
On the Lock Analysis page, view and diagnose recent deadlocks on the database instance.
- Last Deadlock Analysis
  - On the Last Deadlock Analysis tab, click Create Analysis to analyze the most recent deadlock log from the SHOW ENGINE INNODB STATUS command.
  - Select a time range to view the diagnosis results. In the Details column of a result, click View Details to view the detailed diagnostic report.
- Full Deadlock Analysis
  On the Full Deadlock Analysis tab, view deadlock trends and the details of each deadlock for a specified time range.
- Other Lock Analysis
  - On the Other Lock Analysis tab, click Create Analysis. DAS analyzes metadata locks and transaction blocking in the current session of the database instance in real time based on data from information_schema and performance_schema.
  - Select a time range to view the diagnosis results.
    1. Click the icon next to a diagnosis result entry to view its lock analysis statistics.
    2. In the Actions column of the statistics, click View Details to view the detailed diagnostic report and the lock analysis graph.
      Hover over a session in the graph to view its lock-wait relationships. Click a session to view its details.

What to do next

When a deadlock occurs on your database instance, use information from the lock analysis, such as the transaction thread ID, in SQL Explorer and Audit to analyze the execution of related transactions and identify the cause of the deadlock. For example, after a transaction starts and updates a number of data rows, those rows are locked. If the transaction remains open for a long time without being committed or rolled back (including implicit commits and rollbacks), subsequent sessions or transactions that try to update the same rows will enter a LOCK WAIT state.