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.
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.
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 //
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 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 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 .
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
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.
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.
| You must Sign In to use this message board. | |||||||||||
|
|||||||||||
|
|||||||||||
|
|||||||||||
