:How to handle deadlocks in RDS SQL Server

Problem description

When an application frequently reads or writes a table or resource, it is prone to deadlock. In case of a deadlock, SQL Server chooses to terminate one of the transactions and sends the following error message to the client that initiated the transaction.

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. Use a client to connect to an instance. For more information, see connect instances.

2. Monitoring 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

      The [$Time] loop gap can be customized, for example, 00:00:01.

      The following command output is returned.

      Note

      in the monitoring result, the value of the blocked column is the ID of the blocked Source session, and the waitresource is the resource that the blocked session is waiting for. As can be seen from the above results, 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

      The following command output is returned.

      Note

      • DBName:request_session_id database.

      • request_session_id: the ID of the blocked session.

      • blocking_session_id: the ID of the blocked Source session.

      • BlockedObjectName: the object of blocked session operations.

      • resource_type: the type of resources to wait for.

      • RequestingText: the statement that has been executed for the current session.

      • BlockingText: the statement that blocks the source session.

      • request_mode: the lock mode requested by the current session.

   3. If you use RDS SQL Server 2012, you can use SQL Server Profiler to monitor and capture the deadlock graph, as shown in the following figure. The following figure shows the deadlock graph.

3. Perform optimization based on the actual situation.

   • Close the blocked Source session to help quickly remove the blocking.

   • Check whether there are transactions that have not been submitted for a long time, and submit the transactions in time.

   • Use with(nolock) for query.

     Note

     If an S lock is involved in the deadlock and the application allows dirty reading, you can use the with(nolock) command to avoid requesting a lock by the query statement, thus avoiding the deadlock, as shown in the following SQL statement. select * from table with(nolock)

   • Check the application logic and access a resource in sequence.

Application scope

• ApsaraDB RDS for SQL Server