This topic describes how to view the deadlock statistics of an ApsaraDB RDS for SQL Server instance by using CloudDBA in the ApsaraDB RDS console.
Prerequisites
The RDS instance does not run SQL Server 2008 R2 with standard SSDs or enhanced SSDs (ESSDs).
Background information
When you run a transaction to modify a specific resource, the resource is locked to prevent modifications from other concurrent transactions. This ensures data consistency.

To resolve the deadlock, SQL Server terminates the transaction that can be rolled back at a lower cost than the other transaction. If you want to complete the task in the terminated transaction, you must run the transaction again.
ApsaraDB RDS provides the statistics of various deadlocks in the ApsaraDB RDS console. The deadlock statistics include the details about the start time of the blocking and blocked transactions, the IDs of the blocking and blocked sessions, the locked resources, and the types of deadlocks that occur. You can identify the problem SQL statements and other exceptions that cause the deadlocks and optimize your RDS instance to resolve the deadlocks.
Deadlock types
ApsaraDB RDS can analyze the following types of deadlocks:
- KeyDeadlock
- ObjectDeadLock
- RIDDeadlock
- PageDeadlock
- ComplieDeadlock
For more information about each type of deadlock, see Lock Granularity and Hierarchies.
Lock modes
- Shared (S): After a transaction acquires a shared lock on a resource, the resource can only be read but cannot be modified until the transaction releases the shared lock.
- Update (U): After a transaction acquires an update lock on a resource, the resource cannot be modified by another transaction until the transaction acquires an exclusive lock on the resource.
- Exclusive (X): After a transaction acquires an exclusive lock on a resource, the resource cannot be accessed by another transaction until the transaction releases the exclusive lock.
For more information about lock modes, see Lock Modes.
Procedure
- Visit the RDS instance list, select a region above, and click the target instance ID.
- In the left-side navigation pane, choose .
Introduction to the Deadlock tab
- Number of deadlocks
This section displays the number of deadlocks over recent time ranges.
- Deadlock TrendThis section displays different types of deadlocks that occurred over a specific time range. You can perform the following operations:
- Specify the Start time and End time. Then, click Search to view the trend of deadlocks that occurred over the specified time range.
Note The interval between the start time and end time cannot exceed 30 days.
- Click Last 1 Day, Last 7 Days, or Last 30 Days to view the trend of deadlocks that occurred over the last 1 day, last 7 days, or last 30 days.
- Move the pointer over a specific point in time to view the types and numbers of deadlocks that occurred at that point in time.
- In the upper-right corner of the trend chart, click one of the
icons. These icons allow you to change the display style of the trend chart and download the trend chart as an image.
- Specify the Start time and End time. Then, click Search to view the trend of deadlocks that occurred over the specified time range.
- Deadlock DetailsThis section displays the details about deadlocks. You can click the
icon to the left of a deadlock record to view the details about the blocking and blocked sessions. The details include the following information:
- LastTranStarted: indicates the time when the transaction was started in the session.
- SPID: indicates the ID of the session.
- IsVictim: indicates whether the session was terminated.
Note SQL Server comes with a deadlock monitor thread that periodically checks for deadlocks. If a deadlock is detected, SQL Server evaluates the blocking and blocked sessions and terminates the session in which the transaction can be rolled back at a lower cost than the other session. For example, a deadlock occurs between a session that executes the SELECT statement and a session that executes the UPDATE statement. In this case, SQL Server terminates the session that executes the SELECT statement, because the SELECT statement can be rolled back at a lower cost than the UPDATE statement.
- LogUsed: indicates the size of logs that were generated in the session. Unit: bytes.
- LockMode: indicates the lock mode of the deadlock. For more information, see Lock modes.
- WaitResourceDesc: indicates the details about the resource for which the transaction is waiting in the session.
- ObjectOwned: indicates the object that is locked in the session.
- ObjectRequested: indicates the object that the transaction requests to lock in the session.
- WaitResource: indicates the name of the resource for which the transaction is waiting in the session.
- HostName: indicates the name of the host on which the transaction in the session is run.
- LoginName: indicates the username of the account that is used to run the transaction in the session.
- Status: indicates the status of the transaction in the session.
- ClientApp: indicates the name of the client that initiates the transaction in the session.
- SQLText: indicates the details about the SQL statement that is executed in the session.
Note You can click the SQL statement to copy and further analyze the SQL statement.
Click a deadlock record. Then, view the diagram of the deadlock in the Deadlock Diagram section.
- Deadlock Diagram
This section displays the relationships between the blocking and blocked sessions. This section also displays the details about the locked resources. You can click Download to download the diagram as an XDL file. This file contains the details about the deadlock. You can open and view this file by using SQL Server Management Studio (SSMS). For more information, see SQL Server Management Studio (SSMS).