1

I am testing a scenario or recurrent request that ends up in executing the following stored procedure simultaneously

ALTER PROCEDURE [dbo].[clear_class]( @classID [int])
AS
BEGIN 
  DELETE FROM [dbo].[class_action]
  WHERE class_id=@classID;

  DELETE FROM [dbo].[page_item_image] 
  WHERE class_id=@classID;

  DELETE pis
  FROM [dbo].[page_item_spec] as pis 
  JOIN page_item AS pit ON (pis.pi_id=pit.pi_id AND pit.pi_owner=@classID) 

  DELETE FROM [dbo].[page_item] 
  WHERE pi_owner=@classID;

  DELETE FROM [dbo].[page] 
  WHERE page_owner=@classID;

  DELETE FROM [dbo].[image] 
  WHERE img_owner=@classID;

  DELETE FROM [dbo].[class_style] 
  WHERE class_id=@classID;

  DELETE FROM [dbo].[style] 
  WHERE sty_owner=@classID;

  DELETE FROM [dbo].[class_text] 
  WHERE class_id=@classID;

  DELETE FROM [dbo].[text] 
  WHERE text_owner=@classID;

  DELETE FROM [dbo].[action] 
  WHERE act_owner=@classID;
END

and I get deadlock exceptions.

The deadlock graph is the following:

<deadlock-list>
 <deadlock victim="process2f7f61848">
  <process-list>
   <process id="process2f7f61848" taskpriority="0" logused="55572" waitresource="KEY: 5:72057594104315904 (81110f5858a7)" waittime="2947" ownerId="161655" transactionname="user_transaction" lasttranstarted="2015-04-02T15:36:12.427" XDES="0x2effb83b0" lockMode="U" schedulerid="1" kpid="9908" status="suspended" spid="59" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-04-02T15:36:12.427" lastbatchcompleted="2015-04-02T15:36:12.417" lastattention="1900-01-01T00:00:00.417" clientapp="EntityFramework" hostname="MVERG" hostpid="8084" loginname="menelaos" isolationlevel="read committed (2)" xactid="161655" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="db.dbo.p_clear_class" line="15" stmtstart="722" stmtend="874" sqlhandle="0x03000500dfae3842b1f7fa006ea4000001000000000000000000000000000000000000000000000000000000">
DELETE FROM [dbo].[page_item_image] with(rowlock)
    WHERE class_id=@classI     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 5 Object Id = 1111011039]    </inputbuf>
   </process>
   <process id="process2f53ff468" taskpriority="0" logused="425956" waitresource="KEY: 5:72057594104119296 (cc50c8bdfc5d)" waittime="2921" ownerId="161454" transactionname="user_transaction" lasttranstarted="2015-04-02T15:36:12.303" XDES="0x2eed31000" lockMode="S" schedulerid="1" kpid="8516" status="suspended" spid="61" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-04-02T15:36:12.303" lastbatchcompleted="2015-04-02T15:36:12.303" lastattention="1900-01-01T00:00:00.303" clientapp="EntityFramework" hostname="MVERG" hostpid="8084" loginname="menelaos" isolationlevel="read committed (2)" xactid="161454" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="db.dbo.p_clear_class" line="40" stmtstart="2108" stmtend="2244" sqlhandle="0x03000500dfae3842b1f7fa006ea4000001000000000000000000000000000000000000000000000000000000">
DELETE FROM [dbo].[text] with(rowlock)
    WHERE text_owner=@classI     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 5 Object Id = 1111011039]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594104315904" dbid="5" objectname="db.dbo.page_item_image" indexname="PK_page_item_image" id="lock2ef2e4900" mode="X" associatedObjectId="72057594104315904">
    <owner-list>
     <owner id="process2f53ff468" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2f7f61848" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594104119296" dbid="5" objectname="db.dbo.page_item" indexname="PK_page_item" id="lock2f02ee900" mode="X" associatedObjectId="72057594104119296">
    <owner-list>
     <owner id="process2f7f61848" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2f53ff468" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

I see that the problem has something to do with the lock mode, the table is lock mode 's' and need lock mode 'x' but the other process has already lock the table with 'u'...

What do I have to do the solve this deadlock? Is it because of execution order? Does IX lock solves anything and if so how I can set that?

  • Is there a foreign key between text and page_item? Presumably currently these statements are all running in a transaction. Can you remove that so they run in separate transactions? – Martin Smith Apr 3 '15 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.