All Products
Search

This Product

    :Overview of lock analysis

    Document Center

    :Overview of lock analysis

    Last Updated:Dec 05, 2024

    Database Autonomy Service (DAS) provides the lock analysis feature that allows you to view and analyze deadlocks, transaction blocking, and metadata lock waits in a database. This helps you identify and troubleshoot lock wait 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 database

      • 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.

    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.

    Features

    The lock analysis feature allows you to perform multi-dimensional analysis on 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 instances and PolarDB for MySQL clusters. To use these features for a database instance, you must enable DAS Economy Edition or DAS Enterprise Edition for the database instance. DAS Economy 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 enable DAS Economy Edition or DAS Enterprise Edition, see the Enable DAS Economy Edition and DAS Enterprise Edition section of the "Enable and manage DAS Economy Edition and DAS Enterprise Edition" topic.

    • Recent deadlock analysis: DAS analyzes the most recent deadlock log in the output of the SHOW ENGINE INNODB STATUS statement. If multiple deadlocks have occurred, DAS analyzes only the most recent deadlock. For more information, see the Recent deadlock analysis section of the "Deadlock analysis" topic.

    • 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. For more information, see the Full deadlock analysis section of the "Deadlock analysis" topic.

    • 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. For more information, see Other lock analysis.

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

      • Transaction blocking analysis: DAS analyzes the transaction blocking relationship 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 information_schema.innodb_lock_waits table is used for database instances 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 a deadlock 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 deadlock analysis features.

    Lock analysis feature

    Required parameter setting

    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:

    References

    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.