Transaction analysis lets you retrieve the type, count, and details of transactions for a specific thread within a selected time range. Use it to identify the root cause of deadlocks, lock waits, and other transaction-related performance problems.
How it works
Transaction analysis reads from the hot storage of DAS Enterprise Edition V3 or Compliance Audit Edition. For a selected thread and time range, it runs a statistical analysis and plots a trend chart showing the count of each transaction type.
DAS classifies each transaction into one or more of the following types:
| Type | Definition |
|---|---|
| Normal transaction | A standard transaction |
| Implicitly submitted transaction | A transaction submitted implicitly (autocommit) |
| Large transaction | Updates more than 10,000 rows |
| Long-running transaction | Lasts more than 10 minutes |
| Transaction with a long pause | Paused for more than 1 minute |
| Transaction with too many statements | Contains more than 100 SQL statements that involve updates (INSERT, UPDATE, DELETE, MERGE, CREATE, ALTER, DROP, SET, REPLACE, or RENAME) |
| Boundary transaction | A transaction whose end statement falls outside the analysis time range |
A transaction can match multiple types simultaneously. For example, a transaction can be both an implicitly submitted transaction and a long-running transaction.
When to use transaction analysis
Use transaction analysis when you encounter any of the following problems:
Deadlock: Examine the transactions involved in a deadlock event. Combine results with Deadlock Analysis from the lock analysis feature for a full picture.
Lock wait degradation: Trace which transactions are blocking others when lock waits are hurting database performance. Combine results with transaction blocking analysis from the lock analysis feature.
SQL thread investigation: When a specific SQL statement is causing issues, examine all transactions on that SQL's thread. Get the thread ID from audit logs.
Prerequisites
Before you begin, make sure that:
The database engine is ApsaraDB RDS for MySQL or PolarDB for MySQL
The database instance is connected to DAS with a status of Accessed
The database version and region support DAS Enterprise Edition V3 or Compliance Audit Edition — see Editions and supported features
DAS Enterprise Edition V3 or Compliance Audit Edition is enabled for the instance — see DAS Enterprise Edition
Limitations
| Limitation | Details | Workaround |
|---|---|---|
| Data range | Analyzes only SQL data within the hot storage time range. Hot storage incurs additional fees; see Billing details. | To extend the coverage window, adjust the hot storage duration on the SQL Explorer and Audit page — click Service Settings in the upper-right corner. |
| Concurrent tasks | Only one transaction analysis task can run per instance at a time. | Wait for the current task to complete before creating a new one. |
| Threads per task | Each task supports up to two threads. | To analyze more threads, run separate tasks sequentially. |
| Time range per task | Each task covers a maximum of 30 minutes. | For longer time ranges, run multiple tasks covering consecutive 30-minute windows. |
Run a transaction analysis
Create a task
Log on to the DAS console.
In the left navigation pane, click Intelligent O&M Center > Instance Monitoring.
Find the target instance and click its ID to open the instance details page.
In the left navigation pane, click Request Analysis > SQL Explorer and Audit. On the page that appears, click the SQL Explorer tab, then click the Transaction Analysis tab.
Click Create Analysis Task. Select a time range (up to 30 minutes), enter a thread ID, and confirm to create the task. The task appears in the Transaction Analysis List.
Enter a valid thread ID. Get thread IDs from Lock Analysis Overview, the audit log list, or Instance Sessions.
Interpret the results
In the Transaction Analysis List, find the completed task and click View Details in the Actions column. The details page shows:
A trend chart of transaction counts by type over the selected time range.
A transaction list below the chart.
In the Transaction Overview section, click a bar in the column chart to filter the transaction list to transactions that started within that minute.
Click View Details in the transaction details column to inspect the SQL statements within a specific transaction.
ImportantSQL details are available only for data within the hot storage time range.
What's next
Lock analysis — investigate deadlocks and transaction blocking in detail
Audit logs — search SQL statements and retrieve thread IDs
Instance Sessions — view active sessions and retrieve thread IDs