Database Autonomy Service:Deadlock analysis

• Parameter settings: The instance must have the innodb_deadlock_detect parameter enabled.

  Note

  This parameter is typically enabled by default. To confirm this, run SHOW VARIABLES LIKE 'innodb_deadlock_detect'. Note that you cannot change the value of this parameter for some instance types.

  • To modify instance parameters:

    • For RDS MySQL, see Set instance parameters.

    • For PolarDB for MySQL, see Set cluster and node parameters.

• Analyze data: Analyze the most recent deadlock log returned by the SHOW ENGINE INNODB STATUS command.

Procedure

1. Log on to the DAS console.

2. In the navigation pane on the left, click Intelligent O&M Center > Instance Monitoring .

3. Find the target instance and click the instance ID to open the instance details page.

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

5. Click Create Analysis to start a recent deadlock analysis.

   After the analysis is complete, the results table on the Recent Deadlock Analysis tab shows the generation time, occurrence time, and whether a deadlock was found. Click View Details to see detailed information about the deadlock.

View details

1. Select a time period to view the results of analysis tasks triggered within that period.

2. In the list of analysis tasks for recent deadlock analysis, click View Details in the Details column.

3. A panel appears, displaying the deadlock details in three tabs:

   • View: Displays a visual graph of the deadlock relationship, including nodes for the transactions and record locks involved. Arrows indicating lock granted and waiting show the lock possession and waiting relationships, forming a deadlock cycle. Rolled-back transactions are marked as "Rolled back". Click a transaction node to expand its details, such as Session ID, thread ID, request type, transaction ID, involved table, waiting lock information, and the name of the index on the waiting lock.

   • List: This tab displays a side-by-side comparison of the two deadlocked transactions, with rolled-back transactions marked. The table rows include Session ID, thread ID, request type, transaction ID, involved table, waiting lock (index name and lock type, such as X locks rec but not gap waiting), held lock (index name and lock type), and transaction SQL. At the bottom, you can use the thread ID or transaction ID to search for related transaction details in SQL Explorer and Audit.

   • Log: Displays the raw deadlock log details from the LATEST DETECTED DEADLOCK section of the SHOW ENGINE INNODB STATUS output. This includes the time the deadlock occurred, the SQL statements of the involved transactions, held lock information (HOLDS THE LOCK), and waiting lock information (WAITING FOR THIS LOCK TO BE GRANTED).

Analyze with SQL Explorer and Audit

1. In the left-side navigation pane for the target instance, click SQL Explorer and Audit. On the Audit page, click Enable Advanced Query.

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

   1. For Transaction 1 in the example, the query results show that the thread executed three SQL statements: update students set name=UUID() where id = 2 (execution time: 2,938.377 ms, lock wait time: 1 ms), update students set name=UUID() where id = 3 (execution time: 0.002 ms), and begin. The unusually long execution time of the first update statement indicates a lock wait.

   2. For Transaction 2 (rolled back): On the Audit page, set the Time range, set the Thread ID to 195662, and then click Query. The log list shows the SQL execution record for Transaction 2: update students set name=UUID() where id = 3 failed with error code 1213 (deadlock) and took 1.40 ms, with a lock wait time of 1.152 ms. update students set name=UUID() where id = 2 succeeded and took 0.20 ms. begin succeeded and took 0.18 ms.

• You must enable the innodb_deadlock_detect and innodb_print_all_deadlocks parameters. For an RDS MySQL instance, you must set the log_error_verbosity parameter to 3.

  Note

  The innodb_deadlock_detect parameter is usually enabled by default. You can run SHOW VARIABLES LIKE 'innodb_deadlock_detect' to confirm this. Note that you cannot change the value of this parameter for some instance types.

  • To modify instance parameters:

    • For RDS MySQL, see Set instance parameters.

    • For PolarDB for MySQL, see Set cluster and node parameters.

• Data source: This feature parses the deadlock information printed in the instance's error log every hour to generate deadlock count trends and compile the details of each deadlock.

• Cooldown period: If Alibaba Cloud Managed Services is enabled and DAS detects that the required parameters are not enabled, DAS enters a 12-hour cooldown period and does not run analysis tasks. If you enable the required parameters during the cooldown period, DAS waits for the period to end before rescheduling the analysis. For example:

  • An analysis task at 11:12 detects that a required parameter is not enabled and initiates a 12-hour cooldown. After 23:12, full deadlock analysis reschedules the task. If the parameter is still not enabled, another 12-hour cooldown begins. If you enable the parameter before 23:12, the cooldown ends at 23:12. Afterward, the analysis runs hourly, provided the required parameter and Alibaba Cloud Managed Services remain enabled for the instance.

Procedure

1. Log on to the DAS console.

2. In the navigation pane on the left, click Intelligent O&M Center > Instance Monitoring .

3. Find the target instance and click the instance ID to open the instance details page.

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

5. Each hour, full deadlock analysis automatically analyzes the instance's error logs, counts the deadlocks, and plots the data on the Deadlock Trend chart at the top of the page. Select a time period to view the deadlock trend for that period.

   The top of the page provides quick-select buttons for Last 1 Day, Last 3 Days, and Last 7 Days, as well as a custom date range selector. Click the View button to refresh the trend chart. Below the chart is a list of deadlock events that shows the Deadlock Time for each. Click View Details for a specific event to see more information.

View details

1. In the deadlock list of the full deadlock analysis, click View Details in the Details column.

2. A panel appears, displaying the deadlock details in three tabs: View, List, and Log. The Log tab contains the error log content related to the deadlock. The View and List tabs are the same as those in recent deadlock analysis. For more information, see Recent deadlock analysis - View details.

Analyze with SQL Explorer and Audit

The procedure is the same as for recent deadlock analysis. For more information, see Recent deadlock analysis - Analyze with SQL Explorer and Audit.