lock analysis - Database Autonomy Service - Alibaba Cloud Documentation Center

If a deadlock occurs in your database, you can use the lock analysis feature to view and analyze the deadlock information. This helps you quickly locate and resolve the issue to ensure stable database operation.

Prerequisites

The target database engine is PolarDB for MySQL.
The target database instance is connected to Database Autonomy Service (DAS), and its connection status is Normal Access. For more information, see Connect a database instance to DAS.

Feature description

The lock analysis feature lets you perform multi-dimensional deadlock analysis on database instances.

Important

The Full deadlock analysis and Other lock analysis features require you to enable Alibaba Cloud Managed Services (formerly Economy Edition) for DAS. Alibaba Cloud Managed Services (formerly Economy Edition) is available only for specific databases and regions. For information about how to enable the service, see Manage Alibaba Cloud Managed Services (formerly Economy Edition).

Recent deadlock analysis: DAS analyzes the most recent deadlock log from the output of the SHOW ENGINE INNODB STATUS statement. If multiple deadlocks have occurred, DAS analyzes only the most recent one.
Full deadlock analysis: DAS periodically analyzes error logs, parses deadlock information from the logs, and performs a comprehensive deadlock analysis. DAS also lets you view deadlock trends within a specified time range and the details of each deadlock.
Other lock analysis: DAS analyzes metadata locks and transaction blocks in the current database instance sessions in real time using data from information_schema and performance_schema.
- Metadata lock analysis: DAS determines lock wait relationships and generates a corresponding relationship graph based on data from tables such as information_schema.processlist.
- Transaction blocking analysis: DAS analyzes transaction blocking relationships and generates a corresponding relationship graph based on data from information_schema.processlist, information_schema.innodb_trx, and either information_schema.innodb_lock_waits for MySQL 5.6 and 5.7 or performance_schema.data_lock_waits for MySQL 8.0.

Database instance parameter restrictions

To use a lock analysis feature, you must set the corresponding parameters for the target database instance.

Lock analysis feature	Required database instance 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.
Transaction blocking analysis in Other lock analysis	For a PolarDB for MySQL 8.0 instance, enable the `performance_schema` parameter.

To modify database instance parameters, see Set cluster and node parameters.

Precautions

The deadlock 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 navigation pane on the left, click Intelligent O&M Center > Instance Monitoring.
Find the target instance, click the instance ID, and then go to the instance details page.
In the navigation pane on the left, click Lock Analysis.
On the Lock Analysis page, diagnose recent deadlocks in the database instance.
- Recent deadlock analysis
  - On the Recent Deadlock Analysis tab, click Create Analysis Task. DAS analyzes the most recent deadlock log from the output of the SHOW ENGINE INNODB STATUS statement.
  - Select a time range to view the deadlock diagnosis results for that period. In the Details column of a diagnosis result, click View Details to view the details.
- Full deadlock analysis
  On the Full Deadlock Analysis tab, view deadlock trends and the details of each deadlock within a specified time range.
- Other lock analysis
  - On the Other Lock Analysis tab, click Create Analysis Task. DAS analyzes metadata locks and transaction blocks in the current database instance sessions in real time using data from information_schema and performance_schema.
  - Select a time range to view the diagnosis results for that period.
    1. Click the icon next to a diagnosis result to view the lock analysis statistics.
    2. In the Actions column, click View Details to view the detailed diagnosis result and the lock analysis relationship graph.
      Hover over a session to view the associated lock wait relationship. Click the session to view its details.

What to do next

When a deadlock or transaction blocking occurs on your database instance, you can use the information such as the thread IDs obtained after the lock analysis to analyze the execution of related transactions on the SQL Explorer and Audit page. This helps identify the cause of lock waits. 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.