ApsaraDB RDS:How to handle deadlocks in ApsaraDB RDS for SQL Server

Problem description

When an application frequently reads from or writes to the same table or resource, deadlocks can occur. When one occurs, SQL Server terminates one of the transactions and returns an error message to the client, such as the following:

Error Message: Msg 1205, Level 13, State 47, Line 1Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Solution

1. Connect to the instance from a client. For more information, see Connect to an ApsaraDB RDS for SQL Server instance.

2. Monitor the relevant views.

   1. Run the following SQL statement to periodically monitor SYS.SYSPROCESSES.

      WHILE 1 = 1
      BEGIN
      SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
      WAITFOR DELAY '[$Time]';
      END;

      Note

      This example uses [$Time]. You can customize the polling interval, for example, 00:00:01.

      In the results from multiple iterations, SPID 53 is waiting for a lock of type LCK_M_X, and SPID 56 is waiting for a lock of type LCK_M_S. The blocked value for SPID 53 is 56, and the blocked value for SPID 56 is 53, which indicates that they are blocking each other. The waittime for both sessions increases with each loop.

      Note

      In the monitoring results, the blocked column shows the session ID of the blocking session, and the waitresource column shows the resource that the blocked session is waiting for. The results indicate that SPID 53 and SPID 56 are blocking each other, forming a deadlock.

   2. Run the following SQL statement to periodically monitor views such as sys.dm_tran_locks and sys.dm_os_waiting_tasks.

      while 1=1
          Begin
          SELECT
          db.name DBName,
          tl.request_session_id,
          wt.blocking_session_id,
          OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
          tl.resource_type,
          h1.TEXT AS RequestingText,
          h2.TEXT AS BlockingText,
          tl.request_mode
          FROM sys.dm_tran_locks AS tl
          INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
          INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
          INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
          INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
          INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
          CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
          CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
          waitfor delay '[$Time]'
          End

      Two sessions are blocking each other, creating a deadlock: request_session_id 56 is blocked by blocking_session_id 62 on the object Tbl2, with a request_mode of X. Concurrently, session 62 is blocked by session 56 on the object Tbl1, with a request_mode of X. The RequestingText for both sessions shows INSERT statements, confirming a cyclical blocking relationship that results in a deadlock.

      The following table describes the returned columns.

      Parameter	Description
      DBName	The database that the requesting session is accessing.
      request_session_id	The ID of the blocked session.
      blocking_session_id	The ID of the blocking session.
      BlockedObjectName	The object that the blocked session is accessing.
      resource_type	The type of resource being waited for.
      RequestingText	The statement executed by the blocked session.
      BlockingText	The statement being executed by the blocking session.
      request_mode	The lock mode requested by the blocked session.

   3. If your instance runs ApsaraDB RDS for SQL Server 2012, you can also use SQL Server Profiler to monitor and capture a deadlock graph.

      In the Trace Properties dialog box, on the Events Selection tab, expand the Locks category and select the Deadlock graph and Lock:Deadlock events.

      Captured deadlock graph:

      The SQL Server Profiler trace results show two Lock:Deadlock Chain events: SPID 52 holds a 5-X (exclusive) lock on the test_shrink database (transaction 1374753), while SPID 55 holds a 3-S (shared) lock (transaction 1374762). This subsequently triggers a Lock:Deadlock event. The deadlock graph shows that SPID 52 and SPID 55 are locking key lock resources on the index i of test_shrink.dbo.Th12 and test_shrink.dbo.Th11, respectively. The two processes request the locks held by each other (Request Mode is X/S), creating a circular dependency that results in a deadlock.

Recommendations

Use the following recommendations to resolve and prevent deadlocks.

• Terminate the blocking session to quickly resolve the deadlock.

• Check for long-running transactions and commit them promptly to release locks.

• If the deadlock is caused by a shared lock and your application can tolerate a dirty read, use the WITH (NOLOCK) query hint. For example, add SELECT * FROM table WITH (NOLOCK); to your query. This allows the query to execute without requesting a lock, which helps prevent a deadlock.

• Review the application logic to ensure that resources are accessed in a consistent order.

Related operations

You can view deadlock details for your ApsaraDB RDS for SQL Server instance in the ApsaraDB RDS console. For more information, see View deadlock statistics.