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 it. This helps you quickly locate and resolve the deadlock and ensure that your database remains stable.

Prerequisites

The target database instance runs one of the following database engines:
- ApsaraDB RDS for MySQL
- A self-managed MySQL database
The target database instance is connected to Database Autonomy Service (DAS) and its 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 a database instance.

Important

The full deadlock analysis and other lock analysis features require Alibaba Cloud Managed Services (formerly Economy Edition). This service is available only for specific databases and regions. For more 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 occur, DAS analyzes only the most recent one.
Full deadlock analysis: DAS periodically analyzes error logs, parses deadlock information, 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 blockages in the current sessions of your database instance in real time based on data from information_schema and performance_schema.
- Metadata lock analysis: DAS determines lock wait relationships and generates a relationship graph based on data from tables such as information_schema.processlist.
- Transaction blocking analysis: DAS analyzes transaction blocking relationships and generates a 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 limitations

You must set specific parameters for the target database instance to use certain lock analysis features.

Lock analysis feature	Required database instance parameters
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 the `log_error_verbosity` parameter to 3.
Transaction blocking analysis in Other lock analysis	For ApsaraDB RDS for MySQL 8.0 instances, enable the `performance_schema` parameter.

For more information about how to modify database instance parameters, see Set instance 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, you can view or diagnose recent deadlocks in the database instance.
- Recent deadlock analysis
  - On the Recent Deadlock Analysis tab, click Recent Deadlock Analysis. DAS then analyzes the most recent deadlock log from the output of the SHOW ENGINE INNODB STATUS statement.
  - Select a time range to view the deadlock diagnostic results for that period. In the Details column for a result, click View Details to view the detailed diagnostic results.
- Full deadlock analysis
  On the Full Deadlock Analysis tab, you can view deadlock trends and the details of each deadlock within a specified time range.
- Other lock analysis
  - On the Other Lock Analysis section, click Create Analysis Task. DAS then analyzes metadata locks and transaction blockages in the current sessions of the database instance in real time based on data from information_schema and performance_schema.
  - Select a time range to view the diagnostic results.
    1. Click the icon next to a diagnostic result to view the lock analysis statistics.
    2. In the Actions column of the statistics, click View Details to view the detailed results and the lock analysis relationship graph.
      Hover over a session to view its associated lock wait relationships. 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.