SQLServer2000 Deadlock Analaysis

Introduction

SQLServer deadlocks are not rare , most of the complex systems will have some or other sort of deadlocks. It's practically impossible to avoid deadlock and that's why we have the deadlock detection built in to SQLServer, SQLserver resolve the deadlock by terminating one of the transaction involved and the usual victim is the less resource intensive one. Too many deadlocks adversely affect application performance and scalability.

What's a Deadlock

Deadlock's a special kind of blocking scenario, where two or more threads are blocked against each other where none can proceed. Consider two transactions T1 and T2 using resource R1 and R2 . T1 holds and exclusive lock to R1 and T2 have an exclusive lock on R2 . At any point of time in the execution, if T1 require an exclusive lock to R2 and T2 to R1 , both T1 and T2 will be deadlocked. There are lot many articles on net explaining deadlocks and how to handle them. Here i try to explain how a real time deadlock was tracked down and it's solution, of course the solution is simple once we know the cause.

Problem Definition

The application in question is a simple remoting server servicing about 300 concurrent users. The application is well written and follows most of the best practices. Enabling the sql server tracing revealed large number of deadlock and resulting transaction failures. To turn on flag 1204 use DBCC TRACEON (1222, -1) . SQLServer 2005 introduces a new and improved version of the tried and tested T1024. If you are using 2005 use T1222 flag. Below is the deadlock information (truncated for clarity) printed by T1024 flag.

// Any Deadlock encountered .... Printing deadlock information




2007-09-13 16:54:47.15 spid4     



2007-09-13 16:54:47.15 spid4     Wait-for graph



2007-09-13 16:54:47.15 spid4     



2007-09-13 16:54:47.15 spid4     Node:1



2007-09-13 16:54:47.15 spid4     KEY: 7:645577338:1 (12014f0bec4f) 
                                 CleanCnt:2 Mode: Range-S-S Flags: 0x0



2007-09-13 16:54:47.15 spid4      Grant List::



2007-09-13 16:54:47.15 spid4        Owner:0x1a3cc3c0 Mode: Range-S-S Flg:0x0
                                    Ref:2 Life:02000000 SPID:56 ECID:0



2007-09-13 16:54:47.15 spid4        SPID: 56 ECID: 0 Statement 
                                    Type: INSERT Line #: 1



2007-09-13 16:54:47.15 spid4        Input Buf: Language 
                                    Event: Insert from t_cash_folder 
                                    (ADDED_DT ,



2007-09-13 16:54:47.15 spid4      Requested By: 



2007-09-13 16:54:47.15 spid4        ResType:LockOwner Stype:'OR' 
                                    Mode: Range-Insert-Null SPID:51 
                                    ECID:0 Ec:(0x1a4a9570) 



                                    Value:0x25af8760 Cost:(0/D0)



2007-09-13 16:54:47.15 spid4     



2007-09-13 16:54:47.15 spid4     Node:2



2007-09-13 16:54:47.15 spid4     KEY: 7:645577338:1 (12014f0bec4f) 
                                 CleanCnt:2 Mode: Range-S-S Flags: 0x0



2007-09-13 16:54:47.15 spid4      Grant List::



2007-09-13 16:54:47.15 spid4        Owner:0x1a3c4e80 Mode: Range-S-S 
                                    Flg:0x0 Ref:2 Life:02000000 
                                    SPID:51 ECID:0



2007-09-13 16:54:47.15 spid4        SPID: 51 ECID: 0 Statement 
                                    Type: INSERT Line #: 1



2007-09-13 16:54:47.15 spid4        Input Buf: Language 
                                    Event: Insert into t_cash_folder (...



2007-09-13 16:54:47.15 spid4      Requested By: 



2007-09-13 16:54:47.15 spid4        ResType:LockOwner Stype:'OR' 
                                    Mode: Range-Insert-Null SPID:56 ECID:0 
                                    Ec:(0x1e4f5570) 

                                    Value:0x1a3c56e0 Cost:(0/D0)



2007-09-13 16:54:47.15 spid4     Victim Resource Owner:



2007-09-13 16:54:47.15 spid4      ResType:LockOwner Stype:'OR' 
                                  Mode: Range-Insert-Null SPID:56 ECID:0 
                                  Ec:(0x1e4f5570) 



                                 Value:0x1a3c56e0 Cost:(0/D0)



2007-09-13 16:54:52.15 spid4     



//

Analyzing T1024 Output

I will try to make the analysis as simple are possible. Each Node in the deadlock information represent the resources involved. Under each node we have 3 sections KEY, GrantList and Requested By.

KEY: 7:645577338:1 (12014f0bec4f) CleanCnt:2 Mode: Range-S-S Flags: 0x0

KEY is followed by the ID of the resource as it appear in system tables (systables and sysindexes) The cryptic resourceID KEY have 3 parts {KEY : DatabaseID : TableID: IndexType }, where the IndexdType is 1 for clustered Index and greater than 1 for non-clustered. 7:645577338:1 (12014f0bec4f) represent a "Clustured index (hash=12014f0bec4f) on table with id 645577338 in Database 7.

Another significant point to be noted here is the lock mode, the Mode attribute on KEY is the highest lock mode granted for the resource. Other attributes of KEY can be ignored.

GrantList

GrantList as the name suggest list all the process which have been granted some kind of lock on the resource in question. It lists the SPID which is holding the lock, lock mode and the SQL being executed. Owner:0x1a3cc3c0 Mode: Range-S-S Flg:0x0 Ref:2 Life:02000000 SPID:56 ECID:0 spid4 SPID: 56 ECID: 0 Statement Type: INSERT Line #: 1 spid4 Input Buf: Language Event: Insert into t_cash_folder (ADDED_DT

SPID 56 is holding Range-S-S lock on resource identified by hash 12014f0bec4f and is trying to execute an Insert. Now the question is why the insert statement is holding a Range-S-S lock since we know that Range-S-S is a serialized range scan which is usually triggered by SELECT in serialized isolation mode. Also remember that the insert is not the current statement being executed but the one which initially requested for the lock. So it follows that there's a SELECT statement executed in serialized isolation context which require a RangeS-S lock .

Requested By

This section list the SPID waiting to acquire a lock on the resource .SPID 51 is waiting to acquire a Range-Insert-Null lock on the same resource. Range-Insert-Null locks are required to test for insertability before the actual insert is made. Requested By: 2007-09-13 16:54:47.15 spid4 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:51 ECID:0 Ec :(0x1a4a9570) Value:0x25af8760 Cost:(0/D0) Now we know what's happening. We have SPID 56 holding Range-S-S lock and is waiting for Range-Insert-Null on the same resource , while SPID 51 too hold Range-S-S lock(Range S-S lock is compatible with itself) and waiting for Range-Insert-Null .Now the sequence which lead to the deadlock

• SPID 56 acquired Range-S-S lock on a clustered index B+ tree branch.
• SPID 51 acquired Range-S-S lock on the same clustered index B+ tree branch.
• SPID 56 require Range-Insert-Null lock on the branch and is waiting for SPID 51 to release it's RangeS-S lock.
• SPID 51 require Range-Insert-Null lock on the branch and is waiting for SPID 56 to release it's RangeS-S lock.

Solution

Range-S-S lock held by both the SPID's suggest that the application is using serialized isolation mode as this locking mode is used when SELECT is used within serialized isolation mode. serialized isolation is the highest possible isolation mode and eliminates phantom reads and guarantee repeatable reads. Repeatable read guarantee require the RangeS-S lock to be held for the scope of the transaction.

You can try changing the isolation mode if serialized isolation mode is not really what the application requires. If you prefer to use System.Transaction instead of the ADO.NET transaction remember that the former use serialized isolation mode as default. Another idea is to think of the possibility of moving the select statement outside the transaction. This will eliminate the need for RangeS-S lock and of course the deadlock.

Conclusion

SQL Server is designed to handle deadlock and it has a dedicated agent which wakes up now and then to look for deadlock and resolve it. Your code should be written to handle possible deadlock and resubmit the same transaction if required. Apart from handling deadlock in code, simple code rearrangement and consistent database access across application can avoid many deadlocks. Even though you can't avoid deadlocks altogether it can be kept to minimum by following some of the best practices. Remember that deadlocks are scalability killer of your application.