PolarDB:Deadlock analysis

Overview

The deadlock analysis feature allows you to perform multi-dimensional analysis on deadlocks, transaction blocking, and metadata locks on a cluster.

• Recent deadlock analysis: DAS analyzes the most recent deadlock logs in the returned results of the SHOW ENGINE INNODB STATUS statement. If multiple deadlocks occur, DAS analyzes only the most recent deadlock.

• Full deadlock analysis: DAS analyzes error logs at regular intervals, parses the deadlock information, and performs comprehensive deadlock analysis. DAS also allows you to view the deadlock trends within a specified time range and view details of each deadlock.

• Other lock analysis: DAS analyzes the metadata lock and transaction blocking in the current sessions of your cluster in real time based on the data in information_schema and performance_schema.

  • Metadata lock analysis: DAS deduces the lock wait relationships and generates the corresponding diagram based on the data in tables such as the information_schema.processlist table.

  • Transaction blocking analysis: DAS analyzes the transaction blocking relationships and generates the corresponding diagram based on the data in the information_schema.processlist, information_schema.innodb_trx, and information_schema.innodb_lock_waits or performance_schema.data_lock_waits tables. Data in the information_schema.innodb_lock_waits table is used for clusters that run MySQL 5.6 or MySQL 5.7, and data in the performance_schema.data_lock_waits table is used for clusters that run MySQL 8.0.

    Note

    The transaction blocking analysis feature is not supported for PolarDB for MySQL clusters that run MySQL 5.6.

Parameter settings

To use the deadlock analysis feature on a cluster, you must configure the corresponding parameters of the cluster. The following table describes the parameter settings required for different deadlock analysis features.

For information about how to modify the parameters of a PolarDB for MySQL cluster, see Configure cluster and node parameters.

Procedure

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. In the upper-left corner, select the region of the cluster. In the cluster list, click the cluster ID to go to the Basic Information page.

2. In the left-side navigation pane, choose Diagnostics and Optimization > Quick Diagnostics.

3. Click the Deadlock Analysis tab.

4. On the Deadlock Analysis page, select the target cluster ID from the Current Node drop-down list and click Diagnose.

5. Click View Details in the Details column for the most recent deadlock that is detected.

   Note

   The View Details button is available only when the Deadlock Detected column for the same item appears in the Yes state.

6. In the Deadlock Analysis dialog box, view the details of the deadlock analysis result. You can click View Deadlock Log to view the details of the most recent deadlock log.

FAQ

Why does executing UPDATE statements cause a deadlock? How can it be resolved?

A deadlock occurs when two transactions hold exclusive locks (X locks) on corresponding records and simultaneously try to acquire the lock held by the other. For example:

• Transaction 1 executes the UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; statement, which acquires an exclusive lock on user A's account (id=1).

• Transaction 2 executes the UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;, which acquires an exclusive lock on user B's account (id=2).

Solution: Ensure that all transactions update records in the same order, such as always updating records with smaller IDs first. Alternatively, you can combine multiple update operations into a single batch update, reducing lock contention.