DAS lock analysis - Database Autonomy Service - Alibaba Cloud Documentation Center

When a database experiences lock wait issues, you can use the lock analysis feature to visually analyze deadlocks, transaction blocking, and metadata lock waits. This feature helps you quickly locate and resolve these issues to ensure database stability.

Prerequisites

The target database runs one of the following database engines:
- ApsaraDB RDS for MySQL
- Self-managed MySQL
- PolarDB for MySQL
- PolarDB-X 2.0
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.

Precautions

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

Features

The lock analysis feature provides multi-dimensional analysis of deadlocks, transaction blocking, and metadata lock waits 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 database instances. To use these features, enable Alibaba Cloud Managed Services (formerly Economy Edition). Note that Alibaba Cloud Managed Services (formerly Economy Edition) is available only in specific regions. For more information, see Supported databases and regions. To enable this service, see Manage Alibaba Cloud Managed Services (formerly Economy Edition).

Recent deadlock analysis: DAS analyzes the most recent deadlock log returned by the SHOW ENGINE INNODB STATUS command. If multiple deadlocks occur, DAS analyzes only the most recent one. For more information, see Recent deadlock analysis.
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 specific time range and the details of each deadlock. For more information, see Full deadlock analysis.
Other lock analysis: DAS analyzes metadata locks and blocked transactions in the current sessions on your database instance in real time using data from information_schema and performance_schema. For more information, see Other lock analysis.
- Metadata lock analysis: DAS infers the lock wait relationships and generates a corresponding graph based on data from tables such as information_schema.processlist.
- Transaction blocking analysis: DAS analyzes the transaction blocking relationships and generates a corresponding 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).
  Note
  The transaction blocking analysis feature is not supported for PolarDB for MySQL 5.6 instances.

Database instance parameter restrictions

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

Lock analysis feature	Related Database Instance Parameters
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. For an ApsaraDB RDS for MySQL instance, also set the `log_error_verbosity` parameter to 3.
Transaction blocking analysis in other lock analysis	For an ApsaraDB RDS for MySQL 8.0 instance or a PolarDB for MySQL 8.0 instance, set the `performance_schema` parameter to ON.

To modify database instance parameters:

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

References

What to do next

When a deadlock or transaction blocking occurs on your database instance, you can use information such as thread IDs from the lock analysis to analyze the execution of related transactions in SQL Explorer and Audit. This helps identify the cause of lock waits. For example, after a transaction starts and updates a number of data rows, those rows are locked by the transaction. If the transaction runs for a long time and is not committed or rolled back (either explicitly or implicitly), subsequent sessions or transactions that try to update the same data rows will enter a LOCK WAIT state.