Lock analysis - - Alibaba Cloud Documentation Center

Database Autonomy Service (DAS) provides the lock analysis feature that allows you to view and analyze deadlocks, transaction blocking, and metadata locks in a database. This helps you identify and troubleshoot lock issues with ease and ensure database stability.

Prerequisites

Your database instance runs one of the following database engines:
- ApsaraDB RDS for MySQL
- Self-managed MySQL
- PolarDB for MySQL
- PolarDB-X 2.0
Your database instance is connected to DAS and is in the Normal Access state. For more information, see Connect a database instance to DAS.

Features

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

Important

The full deadlock analysis and other lock analysis features are supported only for ApsaraDB RDS for MySQL and PolarDB for MySQL instances. To use these features for a database instance, you must activate DAS Cost-efficient Edition or DAS Enterprise Edition for the database instance. DAS Cost-efficient Edition and DAS Enterprise Edition are available only in specific regions. For more information, see the Supported databases and regions section of the "Editions and supported features" topic. For more information about how to activate DAS Cost-efficient Edition or DAS Enterprise Edition, see the Enable DAS Cost-efficient Edition and DAS Enterprise Edition section of the "Enable and manage DAS Cost-efficient Edition and DAS Enterprise Edition" topic.

Recent deadlock analysis: DAS analyzes the most recent deadlock logs in the returned results of the SHOW ENGINE INNODB STATUS statement. If multiple deadlocks have occurred, DAS analyzes only the most recent deadlock.
Full deadlock analysis: DAS analyzes error logs on a regular basis, parses the deadlock information, and then performs comprehensive deadlock analysis. DAS also allows you to view the deadlock trends within a specific time range and view the details of each deadlock.
Other lock analysis: DAS analyzes the metadata locks and blocked transactions in the current sessions of your database instance in real time based on the data in the information_schema and performance_schema databases.
- 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 database instances that run MySQL 5.6 or MySQL 5.7, and data in the performance_schema.data_lock_waits table is used for database instances that run MySQL 8.0.
  Note
  The transaction blocking analysis feature does not support PolarDB for MySQL clusters that run MySQL 5.6.

Parameter settings

To use a lock analysis feature for a database instance, you must specify the corresponding parameters of the database instance. The following table describes the parameter settings required for different lock analysis features.

Lock analysis feature	Required parameter settings
Recent deadlock analysis	Set the `innodb_deadlock_detect` parameter to ON.
Full deadlock analysis	Set the `innodb_deadlock_detect` parameter to ON. Set the `innodb_print_all_deadlocks` parameter to ON. If the database instance is an ApsaraDB RDS for MySQL instance, you must also set the `log_error_verbosity` parameter to 3.
Transaction blocking analysis in other lock analysis	If the database instance is an ApsaraDB RDS for MySQL 8.0 instance or a PolarDB for MySQL 8.0 cluster, you must set the `performance_schema` parameter to ON.

For more information about how to modify the parameters of a database instance, see one of the following topics based on the database engine of your database instance:

ApsaraDB RDS for MySQL: Modify instance parameters
PolarDB for MySQL: Configure cluster and node parameters

Usage notes

The lock analysis feature cannot be used to analyze deadlocks that contain the following information: TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION.

Procedure

Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
In the left-side navigation pane of the instance details page, click Deadlock Analysis.
On the Deadlock Analysis tab, view or diagnose the detected deadlocks.
- Recent Deadlock Analysis
  - On the Recent Deadlock Analysis tab, click Create Analysis Task. DAS analyzes the most recent deadlock logs in the returned results of the SHOW ENGINE INNODB STATUS statement.
  - Specify a time range to view the diagnostics results within the specified time range. Find a task and click View Details in the Details column to view the details of the diagnostics results.
- Full Deadlock Analysis
  On the Full Deadlock Analysis tab, view the deadlock trends and the details of each deadlock within the specified time range.
- Other Deadlock Analysis
  - In the Other Deadlock Analysis section, click Create Analysis Task. DAS analyzes the metadata locks and blocked transactions in the current sessions of your database instance in real time based on the data in the information_schema and performance_schema databases.
  - Specify a time range to view the diagnostics results within the specified time range.
    1. Click the icon to the left of the diagnostic result to view the statistics.
    2. Click View Details in the Actions column to view the details of the diagnostics results and the lock analysis diagram.
      Move the pointer over a session. You can view the lock wait diagram of the session. Click the session to view the session details.

What to do next

If a deadlock occurs in a database instance, you can use the information that you obtain by using the lock analysis feature, such as the thread ID of the transaction, to analyze the transaction on the SQL Explorer tab. This helps you identify the cause of the deadlock. For example, after a transaction is started and specific data rows are updated, the data rows are locked. If the execution of the transaction lasts for an extended period of time and the transaction is not explicitly or implicitly committed or rolled back, subsequent sessions or transactions that update the same data rows will enter the LOCK WAIT state.