locked
Deadlock on 2 simple Updates trying to update the same row RRS feed

  • Question

  • Hi, 

    I am having problems with 2 Simple Update statements (which are from the same SP) deadlocking.

    I do not not understand one of the updates just doesn't wait for the other to finish before trying to update?

    Many thanks,

    Richard


    Here is the Deadlock Graph

    Deadlock graph

    xml_report <deadlock>
    <victim-list>
    <victimProcess id="process116139468"/>
    </victim-list>
    <process-list>
    <process id="process116139468" taskpriority="0" logused="16376" waitresource="KEY: 5:72057918342234112 (8194443284a0)" waittime="2796" ownerId="1072882" transactionname="user_transaction" lasttranstarted="2017-07-26T14:56:44.517" XDES="0x1329c6860" lockMode="X" schedulerid="2" kpid="20652" status="suspended" spid="67" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-07-26T14:56:46.373" lastbatchcompleted="2017-07-26T14:56:46.357" lastattention="1900-01-01T00:00:00.357" clientapp="Aptify" hostname="RCBLAP2" hostpid="26724" loginname="WebService" isolationlevel="read committed (2)" xactid="1072882" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
    <frame procname="Aptify.dbo.spPTUpdateNextInRangeCRMIdentifier" line="10" stmtstart="276" stmtend="476" sqlhandle="0x0300050037b7912fb32bf600bca7000001000000000000000000000000000000000000000000000000000000">  UPDATE dbo.PTLookupCRMCodeAllocations   SET NextInRange = @NextInRange   WHERE ID = @CodeAllocationI    </frame>
    </executionStack>
    <inputbuf>  Proc [Database Id = 5 Object Id = 798078775]   </inputbuf>
    </process>
    <process id="process12d72f088" taskpriority="0" logused="16856" waitresource="KEY: 5:72057918342234112 (8194443284a0)" waittime="2792" ownerId="1072799" transactionname="user_transaction" lasttranstarted="2017-07-26T14:56:44.387" XDES="0x139ab8820" lockMode="X" schedulerid="4" kpid="27432" status="suspended" spid="65" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-07-26T14:56:46.377" lastbatchcompleted="2017-07-26T14:56:46.357" lastattention="1900-01-01T00:00:00.357" clientapp="Aptify" hostname="RCBLAP2" hostpid="26724" loginname="WebService" isolationlevel="read committed (2)" xactid="1072799" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
    <frame procname="Aptify.dbo.spPTUpdateNextInRangeCRMIdentifier" line="10" stmtstart="276" stmtend="476" sqlhandle="0x0300050037b7912fb32bf600bca7000001000000000000000000000000000000000000000000000000000000">  UPDATE dbo.PTLookupCRMCodeAllocations   SET NextInRange = @NextInRange   WHERE ID = @CodeAllocationI    </frame>
    </executionStack>
    <inputbuf>  Proc [Database Id = 5 Object Id = 798078775]   </inputbuf>
    </process>
    </process-list>
    <resource-list>
    <keylock hobtid="72057918342234112" dbid="5" objectname="Aptify.dbo.PTLookupCRMCodeAllocations" indexname="pkey_PTLookupCRMCodeAllocations" id="lock12160ee80" mode="S" associatedObjectId="72057918342234112">
    <owner-list>
    <owner id="process12d72f088" mode="S"/>
    <owner id="process12d72f088" mode="X" requestType="convert"/>
    </owner-list>
    <waiter-list>
    <waiter id="process116139468" mode="X" requestType="convert"/>
    </waiter-list>
    </keylock>
    <keylock hobtid="72057918342234112" dbid="5" objectname="Aptify.dbo.PTLookupCRMCodeAllocations" indexname="pkey_PTLookupCRMCodeAllocations" id="lock12160ee80" mode="S" associatedObjectId="72057918342234112">
    <owner-list>
    <owner id="process116139468" mode="S"/>
    <owner id="process116139468" mode="X" requestType="convert"/>
    </owner-list>
    <waiter-list>
    <waiter id="process12d72f088" mode="X" requestType="convert"/>
    </waiter-list>
    </keylock>
    </resource-list>
    </deadlock>  

    Wednesday, July 26, 2017 2:05 PM

Answers

  • Judging from the locks and the waits, it seems that these processes have previously both read this row with isolation level REPEATABLE READ, despite that the overall isolation level is READ COMMITTED. Maybe the programmer first reads the value to compute the next in range, and don't want the value change in the table in the meanwhile and therefore slapped on a hint. But the code should use the UPDLOCK hint instead. Since only one process at a time can have an UPDLOCK a resource, the processes will be serialised when reading and not deadlock.

    • Marked as answer by Rich Baker Thursday, July 27, 2017 12:17 PM
    Wednesday, July 26, 2017 9:23 PM

All replies

  • Just two quick questions.  First, want to confirm that the index pkey_PTLookupCRMCodeAllocations is the primary key on the field ID, and second whether or not these statements are in transactions along with other updates.

    Thanks,

    Josh

    Wednesday, July 26, 2017 2:29 PM
  • Hi, 

    Yes it is the primary ID and Clustered.

    ALTER TABLE [dbo].[PTLookupCRMCodeAllocations] ADD  CONSTRAINT [pkey_PTLookupCRMCodeAllocations] PRIMARY KEY CLUSTERED 
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    The SP is called from within a Transaction originating in C# code. There will be other updates in the transaction to different tables.

    Thanks

    Richard

    this is the SP

    ALTER PROCEDURE [dbo].[spPTUpdateNextInRangeCRMIdentifier] (
    @CodeAllocationID INT
    ,@NextInRange NVARCHAR(20)
    )
    AS
    BEGIN
    UPDATE dbo.PTLookupCRMCodeAllocations
    SET NextInRange = @NextInRange
    FROM dbo.PTLookupCRMCodeAllocations 

    WHERE ID = @CodeAllocationID;
    END;


    • Edited by Rich Baker Wednesday, July 26, 2017 2:53 PM
    • Marked as answer by Rich Baker Thursday, July 27, 2017 12:16 PM
    • Unmarked as answer by Rich Baker Thursday, July 27, 2017 12:16 PM
    Wednesday, July 26, 2017 2:41 PM
  • Judging from the locks and the waits, it seems that these processes have previously both read this row with isolation level REPEATABLE READ, despite that the overall isolation level is READ COMMITTED. Maybe the programmer first reads the value to compute the next in range, and don't want the value change in the table in the meanwhile and therefore slapped on a hint. But the code should use the UPDLOCK hint instead. Since only one process at a time can have an UPDLOCK a resource, the processes will be serialised when reading and not deadlock.

    • Marked as answer by Rich Baker Thursday, July 27, 2017 12:17 PM
    Wednesday, July 26, 2017 9:23 PM
  • But is the call to the SP in a larger transaction that includes other updates?

    (or selects with lock hints)

    Josh

    Thursday, July 27, 2017 1:38 AM
  • Hi,

    There was a SELECT previously that had HOLDLOCK and ROWLOCK on an inner join

    --        INNER JOIN                dbo.PTLookupCRMCodeAllocations AS ca WITH (HOLDLOCK, ROWLOCK)

    When I replaced them with UPDLOCK the deadlocks seem to have disappeared.

    Many thanks everyone for your help it was driving me nuts,

    Richard

    Thursday, July 27, 2017 12:16 PM