Use lock analysis to diagnose deadlocks and lock wait issues in your MySQL database instances. Database Autonomy Service (DAS) provides three analysis modes—from quick deadlock snapshots to real-time metadata lock and transaction blocking analysis—so you can identify the blocking source and restore database stability.
Prerequisites
Before you begin, ensure that you have:
A database instance running one of the following engines:
ApsaraDB RDS for MySQL
A self-managed MySQL database
Connected the instance to DAS with its status showing Normal Access. For details, see Connect a database instance to DAS
How it works
DAS offers three lock analysis modes:
Recent deadlock analysis — Parses the most recent deadlock log from
SHOW ENGINE INNODB STATUS. When multiple deadlocks occur in sequence, only the most recent one is analyzed.Full deadlock analysis — Periodically parses error logs to extract all deadlock events. View deadlock trends over a time range and drill into the details of each event.
Other lock analysis — Analyzes metadata locks and transaction blocking in real time, using data from
information_schemaandperformance_schema. Includes two sub-analyses:Metadata lock analysis — Determines lock wait relationships and generates a relationship graph from
information_schema.processlist.Transaction blocking analysis — Maps transaction blocking chains from
information_schema.processlist,information_schema.innodb_trx, andinformation_schema.innodb_lock_waits(MySQL 5.6 and 5.7) orperformance_schema.data_lock_waits(MySQL 8.0).
Full deadlock analysis and Other lock analysis require Alibaba Cloud Managed Services (formerly Economy Edition). This service is available only for specific databases and regions. To enable it, see Manage Alibaba Cloud Managed Services (formerly Economy Edition).
When to use each analysis mode
| Symptom | Recommended analysis |
|---|---|
| A deadlock just occurred; need to identify which transactions conflicted | Recent deadlock analysis |
| Deadlocks happen repeatedly; need to see the trend and full history | Full deadlock analysis |
| Sessions are blocked and not progressing; suspect a long-running transaction or DDL operation | Other lock analysis — Transaction blocking |
| Schema changes (ALTER TABLE) are stalled; suspect a metadata lock | Other lock analysis — Metadata lock |
Common causes of lock issues
Deadlocks typically occur when:
Two or more transactions update the same rows in opposite order, causing a circular lock dependency.
A transaction holds locks for too long without committing or rolling back.
Metadata lock waits typically occur when:
A long-running transaction holds a table lock while a DDL operation (such as
ALTER TABLE) waits for it to release.An idle transaction remains open without being committed or rolled back.
An application issues explicit
LOCK TABLEstatements that are not released promptly.
Transaction blocking typically occurs when:
A transaction updates rows and does not commit or roll back for an extended period, causing subsequent transactions that touch the same rows to enter the LOCK WAIT state.
Required parameters
Configure the following parameters on your database instance before using the corresponding analysis feature. For instructions, see Set instance parameters.
| Feature | Required parameters |
|---|---|
| Recent deadlock analysis | Enable innodb_deadlock_detect |
| Full deadlock analysis | Enable innodb_deadlock_detect; enable innodb_print_all_deadlocks; set log_error_verbosity to 3 |
| Transaction blocking analysis (Other lock analysis) | For ApsaraDB RDS for MySQL 8.0: enable performance_schema |
Limitations
Deadlock analysis cannot process deadlock events that contain the following message:
TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTIONAnalyze locks
Log on to the DAS console.
In the left navigation pane, click Intelligent O&M Center > Instance Monitoring.
Click the ID of the target instance to open its details page.
In the left navigation pane, click Lock Analysis.
Run the analysis based on your scenario:
Recent deadlock analysis
On the Recent Deadlock Analysis tab, click Recent Deadlock Analysis. DAS parses the most recent deadlock log from
SHOW ENGINE INNODB STATUS.Select a time range to filter diagnostic results. In the Details column, click View Details to see the full diagnostic output for a specific result.
Full deadlock analysis
On the Full Deadlock Analysis tab, view deadlock trends and the details of each deadlock event within a specified time range.
Other lock analysis
In the Other Lock Analysis section, click Create Analysis Task. DAS analyzes metadata locks and transaction blocking in the current sessions in real time.
Select a time range to filter the diagnostic results.
Click the
icon next to a result to view lock analysis statistics.In the Actions column, click View Details to open the detailed results and lock relationship graph.
Hover over a session node to view its associated lock wait relationships. Click the node to view session details.
What's next
If a deadlock or blocking event has already occurred
Use the thread IDs from the lock analysis results to trace transaction execution on the SQL Explorer and Audit page. This helps you identify which statements caused the lock contention.
For example: if a transaction updates specific rows and then remains open without committing or rolling back, those rows stay locked. Any subsequent transaction that updates the same rows enters the LOCK WAIT state until the first transaction completes.
Reduce future lock issues
Apply the following practices to minimize deadlocks and lock waits:
Keep transactions short. Commit or roll back as soon as the required operations are complete.
Access tables and rows in a consistent order across transactions to avoid circular dependencies.
Avoid issuing DDL statements (such as
ALTER TABLE) on busy tables during peak hours.Monitor long-running transactions regularly and terminate idle ones that are blocking other sessions.