How to use Quick Diagnostics and deadlock analysis-PolarDB(PolarDB)-阿里云帮助中心

Overview

The deadlock analysis feature supports multi-dimensional analysis of deadlocks, transaction blocking, and metadata lock waits.

Important

The full deadlock analysis and other lock analysis features have the following requirements:

Your database instance must be a PolarDB for MySQL instance.
You must enable DAS Economy Edition or DAS Enterprise Edition. For a list of supported regions, see Supported databases and regions for each edition. To learn how to enable an edition, see Manage DAS audit logs and O&M services (formerly Economy Edition).

Recent deadlock analysis: DAS analyzes the most recent deadlock log returned by SHOW ENGINE INNODB STATUS. If multiple deadlocks occur, DAS analyzes only the latest one.
Full deadlock analysis: DAS periodically analyzes error logs to parse deadlock information. You can also view deadlock trends over a specified time range and see details for each deadlock.
Other lock analysis: Based on data from information_schema and performance_schema, DAS analyzes metadata locks and transaction blocking in the current database session in real time.
- Metadata lock analysis: DAS infers lock wait relationships and generates a relationship graph based on data from sources such as information_schema.processlist.
- Transaction blocking analysis: DAS analyzes transaction blocking relationships and generates a corresponding relationship graph based on information_schema.processlist, information_schema.innodb_trx, and the following version-specific tables: information_schema.innodb_lock_waits (for MySQL 5.6 and 5.7) and performance_schema.data_lock_waits (for MySQL 8.0).
  
  Note
  Transaction blocking analysis is not supported for PolarDB for MySQL 5.6 instances.

Parameter requirements

To use these analysis features, you must set the required parameters.

Feature	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 `log_error_verbosity=3`.
Transaction blocking analysis (under other lock analysis)	For PolarDB for MySQL 8.0 instances, you must enable the `performance_schema` parameter.

To modify database instance parameters:

For PolarDB for MySQL, see Set cluster and node parameters.

Procedure

Log on to the PolarDB console. In the left-side navigation pane, click Clusters, select the region of your cluster, and then click the target cluster's ID to open its details page.
In the left-side navigation pane, choose Diagnostics and Optimization > Quick Diagnostics.
Click the Deadlock Analytics tab.
On the Deadlock Analytics page, from the Current Node list, select the ID of the target instance, and click Diagnose.
In the generated list of diagnostic results, click View Details in the Details column.

Note
You can click View Details only when Deadlock Detected is Yes.
In the Deadlock Analytics dialog box, review the detailed diagnostic results. You can also click View Deadlock Log to see the log for the most recent deadlock.

FAQ

Q: Why does an UPDATE statement cause a deadlock, and how can I resolve it?

A: A deadlock can occur when two transactions each hold an exclusive lock (X lock) on a record and each attempts to acquire the other's lock, creating a circular wait. For example:

Transaction 1: UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; tries to acquire an exclusive lock on User A’s account (id=1).
Transaction 2: UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; tries to acquire an exclusive lock on User B’s account (id=2).

Note

Transaction 1 then attempts to lock the record held by Transaction 2 (where user_id=2) and must wait. Concurrently, Transaction 2 attempts to lock the record held by Transaction 1 (where user_id=1) and must also wait. This mutual blocking creates a circular wait, resulting in a deadlock.

Solution: Update records in a consistent order across all transactions (for example, by always updating the record with the lower ID first). You can also combine multiple update operations into a single batch update to reduce lock contention.