When a deadlock or lock contention event occurs on your PolarDB for MySQL cluster, deadlock analysis helps you identify the root cause without manually parsing InnoDB logs. It integrates with Database Autonomy Service (DAS) to analyze deadlocks, transaction blocking, and metadata locks in real time or from historical error logs.
How it works
Deadlock analysis provides three modes:
| Mode | Data source | What you get |
|---|---|---|
| Recent deadlock analysis | SHOW ENGINE INNODB STATUS output | Analysis of the most recent deadlock event. If multiple deadlocks occurred, only the latest is analyzed. |
| Full deadlock analysis | Error logs scanned at regular intervals | Deadlock trends over a time range, plus details for each event. |
| Other lock analysis | information_schema and performance_schema data, queried in real time | Metadata lock dependency diagram and transaction blocking diagram for current sessions. |
Metadata lock analysis (part of other lock analysis) deduces lock wait relationships and generates the corresponding diagram based on data in the information_schema.processlist table.
Transaction blocking analysis (part of other lock analysis) analyzes transaction blocking relationships and generates the corresponding diagram based on data in the information_schema.processlist, information_schema.innodb_trx, and the following version-specific tables:
MySQL 5.6 or 5.7:
information_schema.innodb_lock_waitsMySQL 8.0:
performance_schema.data_lock_waits
Transaction blocking analysis is not supported for PolarDB for MySQL clusters running MySQL 5.6.
Prerequisites
Before you begin, make sure that:
Your cluster is a PolarDB for MySQL cluster.
(Required for full deadlock analysis and other lock analysis) DAS Cost-efficient Edition or DAS Enterprise Edition is enabled for your cluster. These editions are available only in some regions. For supported regions, see Supported databases and regions. To enable either edition, see Enable and manage DAS Economy Edition and DAS Enterprise Edition.
Parameter settings
Configure the following parameters before running an analysis. For instructions, see Configure cluster and node parameters.
| Analysis mode | Required parameter settings |
|---|---|
| Recent deadlock analysis | Set innodb_deadlock_detect to ON. |
| Full deadlock analysis | Set innodb_deadlock_detect to ON. Set innodb_print_all_deadlocks to ON and set log_error_verbosity to 3. |
| Transaction blocking analysis (other lock analysis) | Set performance_schema to ON. (MySQL 8.0 only) |
Run a deadlock diagnosis
Log on to the PolarDB console. In the left-side navigation pane, click Clusters. In the upper-left corner, select the region of your cluster, then click the cluster ID to go to the Basic Information page.
In the left-side navigation pane, choose Diagnostics and Optimization > Quick Diagnostics.
Click the Deadlock Analysis tab.
On the Deadlock Analysis page, select your cluster from the Current Node drop-down list and click Diagnose.
In the results list, find the most recent entry where Deadlock Detected shows Yes, then click View Details in the Details column.
In the Deadlock Analysis dialog box, review the analysis results. Click View Deadlock Log to see the raw deadlock log.
FAQ
Why do UPDATE statements cause deadlocks, and how do I fix them?
This happens when two transactions each hold an exclusive lock (X lock) on a different row and each waits for the lock held by the other. For example:
Transaction 1 runs
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;, acquiring an X lock onuser_id = 1.Transaction 2 runs
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;, acquiring an X lock onuser_id = 2.
If transaction 1 then tries to lock user_id = 2 while transaction 2 tries to lock user_id = 1, neither can proceed — a deadlock.
To prevent this:
Update rows in a consistent order across all transactions (for example, always lock the row with the smaller ID first).
Combine multiple updates into a single batch statement to reduce the lock contention window.