This topic describes how to use the DAS lock analysis feature to perform metadata lock (MDL) wait analysis and transaction blocking analysis on your database.
The data shown in the images in this document is from test cases. In actual use, rely on your real data parameters, such as thread ID and SQL statements.
Prerequisites
-
Target database engine:
-
RDS MySQL
-
PolarDB for MySQL
-
-
The target database instance is connected to DAS, and its accessed state appears as Normal Access. For details about how to connect a database instance, see Connect a database instance.
-
You have enabled Alibaba Cloud Managed Services (formerly DAS Economy Edition).
Important-
Alibaba Cloud Managed Services (formerly DAS Economy Edition) is available only in specific regions. For details, see Supported databases and regions by edition.
-
To enable Alibaba Cloud Managed Services (formerly DAS Economy Edition), see Manage Alibaba Cloud Managed Services (formerly DAS Economy Edition).
-
Lock types
-
Metadata lock (MDL) analysis
-
Analytical data: Uses data from tables such as
information_schema.processlistto infer lock wait relationships and generate a relationship graph.
-
-
Transaction blocking analysis
ImportantTransaction blocking analysis is not supported for PolarDB for MySQL 5.6 instances.
-
Analytical data: Uses data from
information_schema.processlist,information_schema.innodb_trx, and the following tables to analyze transaction blocking relationships and generate a relationship graph.-
RDS MySQL 5.6 and 5.7:
information_schema.innodb_lock_waits -
PolarDB for MySQL 5.7:
information_schema.innodb_lock_waits -
RDS MySQL and PolarDB for MySQL 8.0:
performance_schema.data_lock_waits
-
-
Parameter settings: For RDS MySQL 8.0 and PolarDB for MySQL 8.0 instances, you must enable the
performance_schemaparameter. To modify instance parameters:-
For RDS MySQL, see Set instance parameters.
-
For PolarDB for MySQL, see Set cluster and node parameters.
-
-
Trigger methods
-
Lock analysis – On-demand analysis: On the Lock Analysis page, manually trigger other lock analysis.
-
Instance sessions – Automatic analysis: Other lock analysis runs automatically when you refresh the Instance Sessions page.
Lock analysis – On-demand analysis
Create an analysis
Log on to the DAS console.
In the navigation pane on the left, click .
Find the target instance and click the instance ID to open the instance details page.
-
In the navigation pane on the left, click Lock Analysis. On the Lock Analysis page, under Other Lock Analysis, click Create Analysis to start an analysis.
View details
Click the arrow icon (
) next to a diagnosis result to view details about metadata lock waits and transaction blocking. On the Other Lock Analysis page, after expanding a diagnosis record, you can see lock type details, including Metadata Lock (2 blocked sessions) and Transaction Lock (2 blocked sessions). Click View Details next to a lock type to see specific blocking information.
Transaction lock details
Click View Details in the Actions column for a transaction lock in the list to see two ways to view detailed transaction lock information:
-
List: The transaction lock details list shows information about blocked transactions in table form, including columns for ID, user, host, database name, command, and blocked transaction. Click the View Details link in the Blocked Transaction column to open a pop-up window showing detailed information about the blocked transaction, including ID, user, host, database name, command, running time (in seconds), status, and SQL statement.
-
View: The View tab displays transaction lock dependencies as a visual graph. Sessions holding locks appear at the top, with LOCK WAIT connectors pointing to sessions waiting for locks. Click a session node to expand a details panel showing ID, Transaction Resolution, Rows Locked by Transaction, Tables Locked by Transaction, and Rows Updated by Transaction.
Metadata lock details
Click View Details in the Actions column for a metadata lock in the list to see a visual representation of the metadata lock details. In the view, click a session block to see its detailed information below. You can also kill related sessions from this page. The metadata lock diagnosis page shows a lock relationship graph with three nodes: Suspicious Session (1) Metadata Lock, Table students DDL Session (1), and Table students Other Session (1). Arrows labeled "Waiting for table metadata lock" indicate wait relationships. Expanding the suspicious session shows: ID 197889, user das, partially masked host address, database das, command Sleep, and running time of 176 seconds. At the bottom, three action buttons help resolve metadata lock issues: Kill Selected Session, Kill All Suspicious Sessions, and Kill All DDL Statements.
Combine with SQL Explorer and audit analysis
A suspicious session in Sleep state may not show SQL information because it started a transaction, executed SQL statements, but has not yet committed or rolled back. In this case, in the navigation pane on the left of the target instance, click SQL Explorer and Audit. On the Audit page, click Enable Advanced Query, enter the suspicious session’s thread ID, and query the SQL statements executed by that session. For example, an uncommitted transaction caused lock waits. On the Audit tab, expand the Set Query Conditions advanced query form, set the time range from November 4, 2024 14:34:49 to November 4, 2024 16:49:49, enter 197889 in Thread ID, and click Query. The Log List below shows SQL records executed by this thread during the specified period, including update students set name=UUID() where id = 3 (executed at 2024-11-04 16:27:22.426) and begin (executed at 2024-11-04 16:27:21.322).
Instance sessions – Automatic analysis
Procedure
Log on to the DAS console.
In the navigation pane on the left, click .
Find the target instance and click the instance ID to open the instance details page.
-
In the navigation pane on the left, click Instance Sessions. On the Session Management page, other lock analysis runs automatically, and results appear in the upper-right corner. On the instance details Sessions page, check the Lock Wait Exists status in the top metrics area. If it shows No, no lock waits exist. Click View Details to analyze lock waits further.
View details on the Instance Sessions page
On the Instance Sessions page, if lock waits exist, click View Details to see transaction locks (if any) and metadata locks (if any) in a drawer. On the DAS instance Sessions page, the top metrics area shows 6 active sessions, 0.3% CPU usage, 0.1% connection usage, 4 abnormal sessions, and Lock Wait Exists as Yes. In the sessions list below, some sessions show the status "Waiting for table metadata lock," indicating lock wait blocking. Click View Details to analyze lock wait relationships further.
Transaction lock details
Click View Details in the Actions column for a transaction lock in the list to see two viewing options (List and View), which work the same as in on-demand analysis. For details, see On-demand analysis – Transaction lock details.
In the Lock Wait pop-up window, the Transaction Lock tab displays a table showing Lock Type and Blocked Sessions.
Metadata lock details
Click View Details in the Actions column for a metadata lock in the list to see a visual representation of the metadata lock details, using the same method as in on-demand analysis. For details, see On-demand analysis – Metadata lock details.
In the Lock Wait pop-up window, the Metadata Lock tab shows a visual graph of lock holding and waiting relationships.
View details on the Lock Analysis page
On the Lock Analysis page, click the arrow icon (
) next to a diagnosis result to view historical statistics from automatically triggered other lock analyses on the Instance Sessions page. The viewing method matches that of on-demand analysis. For details, see On-demand analysis – View details. On the Other Lock Analysis page, select a time range, click View, find a diagnosis record where Lock Found is Yes, expand it, and view lock types (metadata lock, transaction lock) and their blocked session counts. Click View Details to go to the lock details page.