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.

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. This situation triggers a deadlock. Deadlock

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

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 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

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, choose CloudDBA > Lock Optimization.

Introduction to the Deadlock tab

  • Number of deadlocksNumber of deadlocks

    This section displays the number of deadlocks over recent time ranges.

  • Deadlock TrendDeadlock Trend
    This 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 Icon icons. These icons allow you to change the display style of the trend chart and download the trend chart as an image.
  • Deadlock DetailsDeadlock Details
    This section displays the details about deadlocks. You can click the Plus sign 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 DiagramDeadlock 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).