View deadlock statistics - ApsaraDB RDS - Alibaba Cloud Documentation Center

This topic describes how to view the deadlock statistics of an ApsaraDB RDS for SQL Server instance in the ApsaraDB RDS console.

Prerequisites

Your RDS instance does not run SQL Server 2008 R2 with cloud disks.

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. You can quickly find different types of deadlocks that occur in the database. 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

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, choose Autonomy Services > Lock Optimization.

Introduction to the Deadlock tab

Numbers of deadlocks
This section displays the number of deadlocks over recent time ranges.
Deadlock Trend
This section displays different types of deadlocks that occurred over a specific time range. You can perform the following operations to query the deadlock information:
- 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 24 Hours, Last 7 Days, or Last Month to view the trend of deadlocks that occurred over the previous 1 day, previous 7 days, or previous 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 to change the display style of the trend chart and download the trend chart as an image.
Deadlock Details
This section displays the details of 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 provides 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. The SELECT statement can be rolled back at a lower cost than the UPDATE statement. In this case, SQL Server terminates the session that executes the SELECT 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.
You can 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 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 Download SQL Server Management Studio (SSMS).