1

I have a multi-threaded application that does an insert into a table using a stored procedure, and it is causing deadlocks in the database. I ran a trace on my database, and the deadlock processes are calling the same stored procedure.

CREATE PROC dbo.MyTable_Insert
@parm1 INT,
@parm2 INT, ..... and so on for the parms (around 20 of them)
AS
INSERT INTO MyTable
(Column1, Column2, .....)
VALUES
(@parm1, @parm2, ....)

The stored procedure doesn't reference any other tables. It just takes the parms passed in, and inserts into the table.

Here is a sample of the deadlock log.

<deadlock victim="process3f0868">
<process-list>
    <process id="process34f978" taskpriority="0" logused="476" waitresource="OBJECT: 16:2089826557:0 " waittime="1421" ownerId="194136667" transactionname="user_transaction" lasttranstarted="2017-08-13T21:09:16.750" XDES="0x275dd57d0" lockMode="X" schedulerid="1" kpid="4352" status="suspended" spid="93" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2017-08-13T21:09:16.977" lastbatchcompleted="2017-08-13T21:09:16.910" clientapp=".Net SqlClient Data Provider" hostname="MYDB" hostpid="7272" loginname="myUser" isolationlevel="read committed (2)" xactid="194136667" currentdb="16" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
            <frame procname="MYDB.dbo.MyTable" line="51" stmtstart="3448" sqlhandle="0x03001000a31b0d7bdca06a0185a700000100000000000000">
                INSERT  INTO MyTable
                ( Column1,
                Column2 , </frame>
        </executionStack>
        <inputbuf>
            Proc [Database Id = 16 Object Id = 2064456611]    </inputbuf>
    </process>
    <process id="process3f0868" taskpriority="0" logused="476" waitresource="OBJECT: 16:2089826557:0 " waittime="1421" ownerId="194136678" transactionname="user_transaction" lasttranstarted="2017-08-13T21:09:16.750" XDES="0x25af8d670" lockMode="X" schedulerid="4" kpid="4660" status="suspended" spid="86" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2017-08-13T21:09:16.977" lastbatchcompleted="2017-08-13T21:09:16.910" clientapp=".Net SqlClient Data Provider" hostname="MYDB" hostpid="7272" loginname="myUser" isolationlevel="read committed (2)" xactid="194136678" currentdb="16" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
            <frame procname="MYDB.dbo.MyTable" line="51" stmtstart="3448" sqlhandle="0x03001000a31b0d7bdca06a0185a700000100000000000000">
                INSERT  INTO MyTable
                ( Column1,
                  Column2 , </frame>
        </executionStack>
        <inputbuf>
            Proc [Database Id = 16 Object Id = 2064456611]    </inputbuf>
    </process>
</process-list>
<resource-list>
    <objectlock lockPartition="0" objid="2089826557" subresource="FULL" dbid="16" objectname="MYDB.dbo.MyTable" id="lock224663700" mode="IX" associatedObjectId="2089826557">
        <owner-list>
            <owner id="process34f978" mode="IX"/>
            <owner id="process3f0868" mode="IX"/>
        </owner-list>
        <waiter-list>
            <waiter id="process34f978" mode="X" requestType="convert"/>
            <waiter id="process3f0868" mode="X" requestType="convert"/>
        </waiter-list>
    </objectlock>
</resource-list>

NOTE: Sensitive information was redacted.

  • Do you have an indexed view on the table? – Tom V Aug 14 '17 at 9:16
  • @TomV, I have a few views using this table, but none of them have indexes. – dburgener Aug 14 '17 at 15:19

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.