DocsICP FilingConsole
官方文档
首页

Lock analysis

更新时间：
复制 MD 格式
产品详情
我的收藏

When your database encounters deadlocks, use the lock analysis feature to visualize and analyze them. This helps you quickly identify and resolve these issues to ensure database stability.

Prerequisites

  • The target database instance runs one of the following engines:

    • ApsaraDB RDS for MySQL

    • self-managed MySQL

  • The target database instance is connected to DAS and its status is Normal Access. For more information, see Connect a database instance to DAS.

Overview

The lock analysis feature supports multi-dimensional deadlock analysis for database instances.

Important

The full deadlock analysis and other lock analysis features require Alibaba Cloud Managed Services. Alibaba Cloud Managed Services is currently available only for specific databases and regions. To enable this service, see Manage Alibaba Cloud Managed Services.

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

  • full deadlock analysis: DAS periodically analyzes error logs, parses the deadlock information, and performs comprehensive deadlock analysis. You can also view deadlock trends and deadlock details within a specified time range.

  • other lock analysis: DAS uses data from information_schema and performance_schema to perform real-time analysis of metadata locks and transaction blocking for current database sessions.

    • Metadata lock analysis: DAS infers lock-wait relationships from data in tables such as information_schema.processlist and generates a relationship graph.

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

Parameter requirements

To use the lock analysis features, you must configure specific parameters on your database instance.

Feature

Required 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 an ApsaraDB RDS for MySQL 8.0 instance, you must enable the performance_schema parameter.

To learn 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

  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 navigation pane, click Lock Analysis.

  5. On the Lock Analysis page, diagnose recent deadlocks.

    • Recent Deadlock Analysis

      • On the Recent Deadlock Analysis tab, click Create Analysis Task. DAS then analyzes the most recent deadlock log from the SHOW ENGINE INNODB STATUS command.

      • Select a time range to view the diagnostic results. In the Details column of a result, click View Details to view the full diagnostic report.

    • Full Deadlock Analysis

      On the Full Deadlock Analysis tab, view deadlock trends and deadlock details within a specified time range.

    • Other Lock Analysis

      • On the Other Lock Analysis tab, click Create Analysis Task. DAS then performs real-time analysis of metadata locks and transaction blocking in current sessions by using data from information_schema and performance_schema.

      • Select a time range to view the diagnostic results.

        1. Click the image icon next to a diagnostic result to view its analysis statistics.

        2. In the Actions column of the statistics, click View Details to view the detailed results and the lock relationship graph.

          Hover over a session node in the graph to view its associated lock-wait relationships. Click the node to view the session 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.

Previous：Capacity assessmentNext：Instance alerts