All Products
Search

This Product

    Database Autonomy Service:Deadlock analysis

    Document Center

    Database Autonomy Service:Deadlock analysis

    Last Updated:Dec 09, 2024

    This topic describes how to use the lock analysis feature of Database Autonomy Service (DAS) to analyze database deadlocks. Deadlock analysis covers recent deadlock analysis and full deadlock analysis.

    Note

    In this topic, the data presented in figures is used as test data and is for reference only. In real-world scenarios, parameters such as thread IDs and SQL statements vary based on the actual database environments.

    Prerequisites

    • Your database instance runs one of the following database engines:

      • Recent deadlock analysis:

        • ApsaraDB RDS for MySQL

        • Self-managed MySQL database

        • PolarDB for MySQL

        • PolarDB-X 2.0

      • Full deadlock analysis:

        • ApsaraDB RDS for MySQL

        • PolarDB for MySQL

    • Your database instance is connected to DAS and is in the Normal Access state. For more information, see Connect a database instance to DAS.

    • DAS Economy Edition or DAS Enterprise Edition is enabled.

      Important

      • 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 "DAS 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

    • Parameter settings: You must set the innodb_deadlock_detect parameter to ON.

    • Data analysis: DAS analyzes the most recent deadlock log in the output of the SHOW ENGINE INNODB STATUS statement.

    Important

    If multiple deadlocks have occurred, DAS analyzes only the most recent deadlock and retains the deadlock data.

    Procedure

    1. Log on to the DAS console.

    2. In the left-side navigation pane, click Instance Monitoring.

    3. On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.

    4. In the left-side navigation pane, click Lock Analysis. On the Lock Analysis page, click the Recent Deadlock Analysis tab.

    5. Then, click Create Analysis Task to analyze the most recent deadlock.

      image.png

    View details

    1. Specify a time range to view deadlock analysis results.

    2. On the Recent Deadlock Analysis tab, click View Details in the Details column of the analysis task. image.png

    3. You can view the deadlock details by using one of the following methods:

      • View: displays the deadlock details by using the visualized deadlock relationship. You can click the corresponding transaction tile to view the transaction details at the bottom. image.png

      • List: compares the details of lock-holding transactions in a list. image.png

      • Log: displays the original deadlock log (LATEST DETECTED DEADLOCK) in the output of the SHOW ENGINE INNODB STATUS statement. image.png

    Deadlock analysis by using SQL Explorer and Audit

    1. In the left-side navigation pane, click SQL Explorer and Audit. On the Audit tab, click Enable Advanced Query. image.png

    2. Select a time range that covers the time when the deadlock occurred and enter the thread ID identified from the recent deadlock analysis to query the SQL statements executed by the transaction.

      1. Transaction 1 for the preceding sample image.png

      2. Transaction 2 for the preceding sample (rolled back) image.png

    Full deadlock analysis

    • Parameter settings: You must set the innodb_deadlock_detect and innodb_print_all_deadlocks parameters to ON. If you use an ApsaraDB RDS for MySQL instance, set the log_error_verbosity parameter to 3.

    • Data analysis: DAS parses the deadlock information that is displayed in the instance error log on an hourly basis, and collects statistics on the trend of the number of deadlocks and the details of each deadlock.

    • Cooldown period: If DAS Economy Edition or DAS Enterprise Edition is enabled for an instance but the required parameters are disabled, the instance is cooled down for 12 hours. During the cooldown period, DAS does not perform deadlock analysis. If you enable the required parameters during the cooldown period, DAS reschedules the analysis task after the cooldown period. Example:

      • If DAS detects that a required parameter is disabled for the instance that has an analysis task scheduled at 11:12, the task is cooled down for 12 hours. DAS reschedules the full deadlock analysis task for the instance at 23:12. If the parameter is still disabled after the first cooldown period, the scheduled task continues to be cooled down for another 12 hours. If the parameter is enabled before 23:12, the current cooldown period becomes invalid after 23:12. Then, the scheduled task executes the full deadlock analysis on an hourly basis until the parameter is disabled for the instance or DAS Economy Edition or DAS Enterprise Edition is disabled for the instance.

    Important

    You cannot use the full deadlock analysis feature 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

    1. Log on to the DAS console.

    2. In the left-side navigation pane, click Instance Monitoring.

    3. On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.

    4. In the left-side navigation pane, click Lock Analysis. On the Lock Analysis page, click the Full Deadlock Analysis tab.

    5. The full deadlock analysis feature automatically analyzes the deadlock information displayed in the instance error log on an hourly basis, collects statistics on the number of deadlocks, and plots the Deadlock Trend chart at the top of the page. Specify a time range to view the deadlock trend.

      image.png

    View details

    1. On the Full Deadlock Analysis tab, click View Details in the Details column of the deadlock analysis task.

      image.png

    2. You can view the deadlock details by using one of the following methods: view, list, and log. The log contains the deadlock error information. Views and lists for full deadlock analysis are displayed in the same formats as views and lists for recent deadlock analysis. For more information, see the "View details" subsection of the Recent deadlock analysis section in this topic. image

    Deadlock analysis by using SQL Explorer and Audit

    The procedure of using SQL Explorer and Audit for full deadlock analysis is the same as the procedure of using SQL Explorer and Audit for recent deadlock analysis. For more information, see the "Deadlock analysis by using SQL Explorer and Audit" subsection of the Recent deadlock analysis section in this topic.

    References