Database Autonomy Service (DAS) provides the lock optimization feature for ApsaraDB RDS for SQL Server. You can use this feature to view the deadlock statistics of an ApsaraDB RDS for SQL Server instance.
Usage note
The RDS instance that runs SQL Server 2008 R2 with standard SSDs or enhanced SSDs (ESSDs) is not supported.
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.
In most cases, a deadlock occurs when multiple transactions compete for the same resource. In the following example, when Transaction A is modifying Resource A, it also initiates a request to modify Resource B, which is being modified by Transaction B. In this case, a deadlock occurs. 
To resolve the deadlock issue, 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 start time of the blocking and blocked transactions, the IDs of the blocking and blocked sessions, the details about 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 deadlock issues.
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
ApsaraDB RDS for SQL Server locks resources by using different lock modes that determine how the resources are accessed by concurrent transactions. The lock mode that is used to lock the resource accessed by a transaction varies based on the type of operation required by the transaction. ApsaraDB RDS for SQL Server supports the following 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 latter 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 former transaction releases the exclusive lock.
For more information about lock modes, see Lock modes.
Procedure
Log on to the DAS console.
In the left-side navigation pane, click Instance Monitoring.
On the page that appears, find the database instance that you want to manage and click the instance ID. The instance details page appears.
In the left-side navigation pane, click Lock Optimization. On the Lock Optimization page, click the DeadLock tab.
On the DeadLock tab, view the deadlock statistics of the ApsaraDB RDS for SQL Server instance.
Information on the DeadLock tab
Numbers of deadlocks
This section displays the numbers of deadlocks that occurred during different periods.
Deadlock Trend
This section displays different types of deadlocks that occurred during a specific period of time. You can perform the following operations to query the deadlock information:
Specify the start time and end time, and click Search to view the trend of deadlocks that occurred within the specified time range.
NoteThe interval between the specified start time and end time cannot exceed 30 days.
Click Last 24 Hours, Last 7 Days, or Last Month to view the trend of deadlocks that occurred in the previous day, previous seven days, or previous month.
Move the pointer over a specific point in time in the trend chart 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 the
icons based on your business requirements. You can change the display style of the trend chart or download the trend chart.
Deadlock Details
This section displays the details of deadlocks. You can click the
icon on the left of a deadlock record to view the details of the blocking and blocked sessions. The details include the following information:LastTranStarted: the point in time at which the transaction was started in the session.
SPID: the session ID.
IsVictim: indicates whether the session is terminated.
NoteDAS uses an internal deadlock monitor thread to periodically detect deadlocks in ApsaraDB RDS for SQL Server instances. If a deadlock is detected, DAS evaluates the blocking and blocked sessions related to the deadlock 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. The SELECT statement can be rolled back at a lower cost than the UPDATE statement. In this case, DAS terminates the session that executes the SELECT statement.
LogUsed: the size of logs that were generated in the session. Unit: bytes.
LockMode: the lock mode of the deadlock. For more information, see the Lock modes section of this topic.
WaitResourceDesc: the details of the resource for which the transaction is waiting in the session.
ObjectOwned: the object that is locked in the session.
ObjectRequested: the object that the transaction requests to lock in the session.
WaitResource: the name of the resource for which the transaction is waiting in the session.
HostName: the name of the host on which the transaction in the session is run.
LoginName: the username of the account that is used to run the transaction in the session.
Status: the status of the transaction in the session.
ClientApp: the name of the client that initiates the transaction in the session.
SQLText: the details about the SQL statement that is executed in the session.
NoteYou can click the SQL statement to open the SQL statement window and copy the SQL statement in the window for subsequent tracing and troubleshooting.
Click a deadlock record to 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 of locked resources in a deadlock. You can click Download to download the diagram as an XDL file. The XDL file contains the details of the deadlock. You can open and view the file in the SQL Server Management Studio (SSMS) client. For more information, see Download SQL Server Management Studio (SSMS).