A deadlock graph in the SQL server profiler shows a deadlock on the same cluster key

Question

I am trying to determine the cause of a deadlock using SQL Server Profiler. Here's the deadlock schedule: Both operators: inserts followed by select scope_identity(); In fact, a have two parallel processes that repeat insert-select_identity in a loop.

What I would expect is that the insert takes an exclusive lock on the clustered index, and then selects the general lock on the non-clustered index, and then they wait for each other to release their respective indexes.

What I see is that both processes are waiting for the release of the same resource - the clustered index. How can it be? A particular regression should belong to either one process or another. What am I missing here? thanks to everyone in advance.

Edited: Yes, isolation level Serializible. PS: maybe my assumption about a general nonclustered index blocking was wrong, as my choice does not contain a where statement

Edit2: here is the xml part:

  <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> 

Accordingly, I think it is a range scan caused by isolating SERIALIZABLE (googled that). But still I do not understand how this happens and what is the recommended remedy.

Answer 1

Consider the following code, which is called from two concurrent transactions (T1 and T2) that access the same record.

 Read LastRow Insert AtLastRow 

Suppose a context switch occurs in Read . Thus, the sequence of operations

 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! 

The above record will take Range SS . Paste finally also requires Range IN , which is not compatible with the existing Range SS lock held by other transactions. Therefore, he is waiting.

There are several ways to solve this problem.

• Use the read value as the general level of isolation, not serializable. This will prevent Range locking.
• Reading with update lock (UPDLOCK). This will require an exclusive upgrade of the castle in 1st place. Consequently, another transaction will wait for the Read itself.
• Avoid reading and pasting / updating patterns . Just straight ahead with insert / update and won't work.

Let me know if you have any questions.