When lock waits occur during your database queries, you can use lock wait source analysis to quickly identify the lock-holding SQL statement based on SQL audit data. This feature shortens lock troubleshooting time and removes the need to manually go through large volumes of audit logs.
Applicable scope
The database instance is an ApsaraDB RDS for MySQL or PolarDB for MySQL instance, and the default storage engine is InnoDB.
The database instance is registered with DAS, and its connection status is Normal Access. For more information, see Connect a database instance to DAS.
The version and region of the database instance support DAS Enterprise Edition V3 or the audit log feature. For more information, see DAS editions and supported features.
Hot storage of DAS Enterprise Edition V3 or the audit log feature is enabled for the database instance. For more information, see DAS Enterprise Edition.
Feature overview
Lock wait source analysis is based on the hot storage of DAS Enterprise Edition V3 or the audit log feature. It analyzes which SQL statements in the same instance may have held the lock while the target SQL was waiting, and presents suspected blocking sources and their lock wait relationships with the target SQL in both a graph and a list.
The analysis infers relationships only from information such as time windows, table names, and transactions in the audit logs. The results are suspected blocking sources rather than definitive conclusions, and are provided for reference only.
Limits
Supported SQL types: Only the following types of blocked SQL can be analyzed: UPDATE, DELETE, INSERT, SELECT (including SELECT ... FOR UPDATE and LOCK IN SHARE MODE), ALTER, FLUSH TABLES, and LOCK TABLES. Other SQL types such as SET, SHOW, USE, and DESCRIBE are not supported.
Lock wait duration threshold: The lock wait duration of the blocked SQL must be at least 3 seconds. A lock wait shorter than this threshold cannot trigger analysis.
Analysis time window: Only the hot-storage audit data within 2 hours before the execution time of the target SQL is scanned. SQL statements outside this window are not included. In addition, the audit record of the blocked SQL must be within the hot storage retention period. If the audit data has expired or been cleared, the analysis task cannot be created.
Task concurrency: Only one lock wait source analysis task can run at a time for the same instance. You must wait for the previous task to complete before you start a new one.
Transaction ID availability: When the transaction ID is unavailable (Audit), the analysis uses an inference mode based on thread IDs. In this mode, transaction boundary inference may not be fully precise.
Procedure
Create an analysis task
Log on to the DAS console.
Find the target instance and click its instance ID to go to the instance details page.
In the left-side navigation pane, choose . Then, on the page that appears, click the Audit tab.
Set the search conditions, click Query, and find the target SQL in the log list.
In the Actions column of the target SQL, click Lock Wait Analysis to start the analysis.
View the analysis results
You can view the analysis results in the following two ways:
Method 1: View directly on the Audit page
After you trigger the analysis on the Audit tab, wait for the analysis to complete and then view the results.
Method 2: View from the historical task list
On the page, go to the Lock Wait Analysis section on the SQL Analysis tab to view the list of historical analysis tasks. In the Actions column of the target task, click View Details to open the results.
NoteThe historical task list displays tasks created within the last month.
You can view the analysis result details in three modes:
Graph: Displays the lock wait relationships between suspected blocking sources and the target SQL as a diagram. The suspected blocking source nodes that hold locks are at the top, with arrows pointing to the target SQL that waits for the locks. Each node shows information such as the SQL execution start time, its own lock-holding duration (for a blocking source) or lock wait duration (for the target SQL), the thread, and the transaction status.
List: Displays the details of suspected blocking sources in a table, including fields such as SQL ID, SQL text, transaction ID, thread ID, start time, and lock-holding duration.
AI Interpretation: On the result details page, click AI Interpretation, or click the
icon in the Actions column of the historical analysis task list, to view the DAS Agent's interpretation of the lock wait analysis results.
What to do next
After you obtain the details of a suspected blocking source, you can perform the following operations:
On the SQL Explorer and Audit page, click the ️SQL Explorer tab. In the Full Request Statistics section, analyze the execution frequency of the blocking source SQL by SQL ID to confirm whether that SQL frequently appears together with the target SQL. If lock waits occur frequently, optimize the transaction logic in your business promptly to reduce lock conflicts at the source.
If a lock wait is occurring in real time, use the Other lock analysis to analyze MDL or transaction blocking.