4

I am trying to identify the reason of deadlock using SQL Server Profiler. Here's the deadlock graph: Deadlock graph Both statements are inserts followed by select scope_identity(); Actually a have 2 concurrent processes that repeatedly do insert-select_identity in a cycle.

What would i expect is that insert takes exclusive lock over the clustered index and select takes a shared lock of the non-clustered index, and then they wait for each other to release their respective indeces.

What i see is that both processes wait for the same resource to be released - the clustered index. How can this be? The particular recourse should belong either to one process or to another. What do i miss here? thanks to all in advance.

Edited: yes, isolation level is Serializible. PS: probably, my assumption about the shared lock on non-clustered index was wrong as far as my select does not contain a where statement

Edit2: here is the part of xml:

 <resource-list>
   <keylock hobtid="72057594148028416" dbid="29" objectname="<confidential>" indexname="PK_WP_Inbound_StockTransactionLine" id="lock9641700" mode="RangeS-S" associatedObjectId="72057594148028416">
    <owner-list>
     <owner id="process8e09288" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process991ce08" mode="RangeI-N" requestType="convert"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594148028416" dbid="29" objectname="<confidential>" indexname="PK_WP_Inbound_StockTransactionLine" id="lock9641700" mode="RangeS-S" associatedObjectId="72057594148028416">
    <owner-list>
     <owner id="process991ce08" mode="RangeS-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process8e09288" mode="RangeI-N" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>

According to this, i thinks that it is Range scans caused by SERIALIZABLE isolation (googled that). But still, i don't understand how does this happen and what is the recommended remedy.

Artur Udod
  • 4,465
  • 1
  • 29
  • 58

1 Answers1

6

Consider following code being called from two parallel transactions(T1 and T2) accessing same record.

Read LastRow
Insert AtLastRow

Lets say context switch happens at Read. So sequence of operations is

T1 Read LastRow
T2 Read LastRow
T2 Insert AtLastRow // This will wait for T1 to finish.
T1 Insert AtLastRow // This will wait for T2 to finish. Hence deadlock!

Above read will take Range S-S lock. Insert at last also needs Range I-N which is not compatable with existing Range S-S lock held by other transactions. Hence it waits.

There can be multiple ways to solve this problem.

  1. Use read committed as overall isolation level and not serializable. This will prevent taking Range locks.
  2. Read with update lock (UPDLOCK). This will take exclusive update lock in 1st place. Hence other transaction will wait at Read itself.
  3. Avoid the read and insert/update pattern. Just straight go ahead with insert/update and let it fail.

Let me know if you have any questions.

Ankush
  • 2,454
  • 2
  • 21
  • 27
  • 3. Avoid the read and insert/update pattern. Then how do i handle the fails? ie. i need to do smth else (say, display a specific message) in case when the record already exists. Should i just w8 for exception? and then what? parse the exception data to identify its exact reason? Besides, why should insert/update fail? Consider a situation when i need to check business logic constraints, not the data-model constraints. Probably, the solution is also to move the read op-n out of transaction. But in our case this is 2much refactoing, so i'll change the isolation lvl as a workaround. thank you =) – Artur Udod Sep 26 '12 at 08:52
  • @ArturUdod lets say you read a row with key = 10 and increment value. You can either read the value and then update it or go ahead and update. If the key doesn't exist, above update will fail. Now if you detect failure, you insert key 10 with value 1. Does this help? – Ankush Sep 26 '12 at 08:58
  • yeah, i got it. But, as i said: Let us assume that i only want to increment a value if it is less then 100. If it is already 100, then i want to display a message. Off cource, there can be a "check" constraint, but that's an easy example. I might have a far more complex validation business logic and i realy need to perform a read before modifying data. – Artur Udod Sep 26 '12 at 09:34
  • @ArturUdod in that case, go with 2nd option. "Read with update lock (UPDLOCK)." – Ankush Sep 26 '12 at 09:41
  • Actually, in my case there will be no danger if i switch the isolation level to read-commited just for this particular case. Phantom reads are unlikely to happen (just because the server logic prevents this). Thank you for your response. – Artur Udod Sep 26 '12 at 13:07