Parsing raw InnoDB logs to diagnose deadlocks is time-consuming and requires deep familiarity with the InnoDB lock system. Database Autonomy Service (DAS) automates this process through its lock analysis feature, which visualizes deadlock relationships and traces the full transaction history without manual log parsing. DAS provides two modes: recent deadlock analysis, which captures the latest deadlock on demand, and full deadlock analysis, which continuously collects deadlock history from the instance error log.
The data shown in the screenshots is for demonstration only. Use your actual thread IDs and SQL statements when working with your instances.
Prerequisites
Before you begin, make sure that:
The target instance runs a supported database engine:
Mode Supported engines Recent deadlock analysis RDS MySQL, self-managed MySQL, PolarDB for MySQL, PolarDB-X 2.0 Full deadlock analysis RDS MySQL, PolarDB for MySQL The instance is connected to DAS with a connection status of Normal Access. See Connect a database instance to DAS.
Alibaba Cloud Managed Services (formerly DAS Economy Edition) is enabled for the instance.
Alibaba Cloud Managed Services (formerly DAS Economy Edition) is available only in some regions. For region availability, see Supported databases and regions by edition. To enable the service, see Manage Alibaba Cloud Managed Services (formerly DAS Economy Edition).
Recent deadlock analysis
Recent deadlock analysis captures the most recent deadlock log from SHOW ENGINE INNODB STATUS output. Each time you trigger an analysis, DAS processes the latest deadlock and saves the result. If multiple deadlocks have occurred since the last analysis, only the most recent one is captured.
Required parameters
Enable innodb_deadlock_detect on the instance before running an analysis.
This parameter is enabled by default on most instance types. Confirm with SHOW VARIABLES LIKE 'innodb_deadlock_detect'. On some instance types, this parameter cannot be changed.To modify instance parameters:
RDS MySQL: Set instance parameters
PolarDB for MySQL: Set cluster and node parameters
Run a recent deadlock analysis
Log on to the DAS console.
In the left navigation pane, click Intelligent O&M Center > Instance Monitoring.
Click the target instance ID to go to the instance details page.
In the left navigation pane, click Lock Analysis, then click the Recent Deadlock Analysis tab.
Click Create Analysis Task.
View deadlock details
Select a time range to filter the diagnostic task list.
In the task list, click View Details in the Details column.
A panel opens with three tabs:
View: A visual graph of the deadlock relationship. Click a transaction tile to see that transaction's details below the graph.
List: A side-by-side comparison of the details of lock-holding transactions.
Log: The raw
LATEST DETECTED DEADLOCKsection fromSHOW ENGINE INNODB STATUS, useful for low-level debugging.
Trace the full transaction history with SQL Explorer and Audit
Use the thread IDs from the deadlock analysis to look up every SQL statement the transactions executed — not just the statements involved in the lock conflict.
In the left navigation pane of the instance, click SQL Explorer and Audit. On the Audit page, click Enable Advanced Query.
Set the time range to cover the period when the deadlock occurred, then search by the thread ID from the recent deadlock analysis.
Transaction 1:
Transaction 2 (rolled back):
Full deadlock analysis
Full deadlock analysis parses deadlock events from the instance error log every hour, building a historical trend of deadlock counts and preserving the details of each individual deadlock.
Required parameters
Enable the following parameters before using full deadlock analysis:
| Parameter | Required for |
|---|---|
innodb_deadlock_detect | All supported engines |
innodb_print_all_deadlocks | All supported engines |
log_error_verbosity = 3 | RDS MySQL only |
innodb_deadlock_detectis enabled by default on most instance types. Confirm withSHOW VARIABLES LIKE 'innodb_deadlock_detect'. On some instance types, this parameter cannot be changed.
To modify instance parameters:
RDS MySQL: Set instance parameters
PolarDB for MySQL: Set cluster and node parameters
Cooldown period: If Alibaba Cloud Managed Services is enabled but the required parameters are not, DAS pauses full deadlock analysis for a 12-hour cooldown period. After the cooldown expires, DAS retries the analysis. If the parameters are still disabled, another 12-hour cooldown begins.
For example: an analysis task is scheduled at 11:12. DAS detects that a required parameter is disabled and starts a 12-hour cooldown. At 23:12, DAS retries. If the parameter is now enabled, analysis runs at 23:12 and continues every hour until the parameter is disabled or Alibaba Cloud Managed Services is turned off for the instance.
Full deadlock analysis does not support deadlocks that contain the message: "TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION".
View the deadlock trend
Log on to the DAS console.
In the left navigation pane, click Intelligent O&M Center > Instance Monitoring.
Click the target instance ID to go to the instance details page.
In the left navigation pane, click Lock Analysis, then click the Full Deadlock Analysis tab.
Select a time range to view the Deadlock Trend chart. The chart plots the deadlock count for the selected period.
View deadlock details
In the deadlock list, click View Details in the Details column.
A panel opens with the same View, List, and Log tabs as in recent deadlock analysis. The Log tab contains error logs related to the deadlock. For details on reading each tab, see View deadlock details in the recent deadlock analysis section.
Trace the full transaction history with SQL Explorer and Audit
The procedure is the same as for recent deadlock analysis. See Trace the full transaction history with SQL Explorer and Audit in the recent deadlock analysis section.