0

I am literally new to this topic.

Here is my deadlock graph contents. could someone help me figure out what is the cause and what options are there for me to resolve this deadlock ?

<deadlock-list>
 <deadlock victim="process29b0cf8">
  <process-list>
   <process id="process29b0cf8" taskpriority="0" logused="5616" waitresource="PAGE: 999:1:161763 " waittime="3370" ownerId="87774966" transactionname="INSERT" lasttranstarted="2017-09-07T15:58:27.860" XDES="0x7f75663a8" lockMode="IU" schedulerid="8" kpid="2568" status="suspended" spid="185" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-09-07T15:58:27.867" lastbatchcompleted="2017-09-07T15:55:39.737" lastattention="2017-09-07T15:55:39.647" hostpid="0" loginname="testuser" isolationlevel="read committed (2)" xactid="87774966" currentdb="999" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
    <executionStack>
     <frame procname="Database2.dbo.Process_Workdays_Update" line="115" stmtstart="8774" stmtend="10390" sqlhandle="0x03003100439685498bcdb800e5a7000001000000000000000000000000000000000000000000000000000000">
UPDATE dbo.Chords
        SET REC_DEPT = @REC_DEPT
            ,REC_TITLE = @MatNum
            ,REC_DESC = @MatNam
            ,REC_DT_FROM = @MatOpDt
        WHERE REC_FILE_ID IN (
                SELECT FID.Value ChordId
                FROM @JsonTbl J
                INNER JOIN @JsonTbl FID ON FID.Parent = J.Id
                WHERE J.NAME = &apos;FolderId&apos;
                )     </frame>
     <frame procname="Database2.dbo.TRG_Inbound_Database2Inbound_Ins" line="214" stmtstart="12568" stmtend="12694" sqlhandle="0x0300310053138f1707cf0001e5a7000000000000000000000000000000000000000000000000000000000000">
EXEC [Process_Workdays_Update] @JsonTbl 
            ,@InboundMsgId     </frame>
     <frame procname="Database2.dbo.TRG_InboundTodo_Database2InboundTodo_Ins" line="63" stmtstart="4016" stmtend="4838" sqlhandle="0x030031001aef9a1632fcbc00e5a7000000000000000000000000000000000000000000000000000000000000">
INSERT INTO [dbo].[Database2Inbound] (
        [MessageId]
        ,[Message]
        ,[MessageType]
        ,[Status]
        ,[CreatedOn]
        ,[LastModifiedOn]
        )
    SELECT I.MessageId
        ,@Message
        ,I.MessageType
        ,I.[Status]
        ,GETDATE()
        ,GETDATE()
    FROM Inserted I     </frame>
     <frame procname="Database2.dbo.InsertDatabase2InboundTodo" line="12" stmtstart="430" stmtend="950" sqlhandle="0x0300310044153f468515e000dfa7000001000000000000000000000000000000000000000000000000000000">
INSERT INTO dbo.Database2InboundTodo (
        MessageId
        ,MessageType
        ,[Status]       
        ,[Message]
        ,CreatedOn
        ,LastModifiedOn
        )
    VALUES (
        @MessageId      
        ,@MessageType
        ,@Status
        ,@Message
        ,GETDATE()
        ,GETDATE()
        )     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 999 Object Id = 1178539332]    </inputbuf>
   </process>
   <process id="process482ecf8" taskpriority="0" logused="6328" waitresource="KEY: 99972057594361151488 (c1430c090b5d)" waittime="2497" ownerId="87777001" transactionname="INSERT" lasttranstarted="2017-09-07T15:58:28.140" XDES="0x8c6a083a8" lockMode="U" schedulerid="12" kpid="6008" status="suspended" spid="184" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-09-07T15:58:28.143" lastbatchcompleted="2017-09-07T15:55:39.107" lastattention="2017-09-07T15:55:38.967" hostpid="0" loginname="testuser" isolationlevel="read committed (2)" xactid="87777001" currentdb="999" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
    <executionStack>
     <frame procname="Database2.dbo.Process_Holidays_Update" line="234" stmtstart="15368" stmtend="15838" sqlhandle="0x03003100d14d9d472b0ddb00e7a7000001000000000000000000000000000000000000000000000000000000">
UPDATE dbo.Database2Inbound
            SET STATUS = @CompletionStatus
                ,Error = CASE 
                    WHEN @CompletionStatus = &apos;E&apos;
                        THEN @LogMsg
                    ELSE &apos;&apos;
                    END
            WHERE MessageId = @InboundMsgId

            -- no need to retry anymore     </frame>
     <frame procname="Database2.dbo.TRG_Inbound_Database2Inbound_Ins" line="199" stmtstart="11692" stmtend="11818" sqlhandle="0x0300310053138f1707cf0001e5a7000000000000000000000000000000000000000000000000000000000000">
EXEC [Process_Holidays_Update] @JsonTbl 
            ,@InboundMsgId     </frame>
     <frame procname="Database2.dbo.TRG_InboundTodo_Database2InboundTodo_Ins" line="63" stmtstart="4016" stmtend="4838" sqlhandle="0x030031001aef9a1632fcbc00e5a7000000000000000000000000000000000000000000000000000000000000">
INSERT INTO [dbo].[Database2Inbound] (
        [MessageId]
        ,[Message]
        ,[MessageType]
        ,[Status]
        ,[CreatedOn]
        ,[LastModifiedOn]
        )
    SELECT I.MessageId
        ,@Message
        ,I.MessageType
        ,I.[Status]
        ,GETDATE()
        ,GETDATE()
    FROM Inserted I     </frame>
     <frame procname="Database2.dbo.InsertDatabase2InboundTodo" line="12" stmtstart="430" stmtend="950" sqlhandle="0x0300310044153f468515e000dfa7000001000000000000000000000000000000000000000000000000000000">
INSERT INTO dbo.Database2InboundTodo (
        MessageId
        ,MessageType
        ,[Status]       
        ,[Message]
        ,CreatedOn
        ,LastModifiedOn
        )
    VALUES (
        @MessageId      
        ,@MessageType
        ,@Status
        ,@Message
        ,GETDATE()
        ,GETDATE()
        )     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 999 Object Id = 1178539332]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <pagelock fileid="1" pageid="161763" dbid="999" subresource="FULL" objectname="Database2.dbo.Chords" id="lock808b6ba00" mode="UIX" associatedObjectId="72057594064797696">
    <owner-list>
     <owner id="process482ecf8" mode="UIX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process29b0cf8" mode="IU" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <keylock hobtid="72057594361151488" dbid="999" objectname="Database2.dbo.Database2Inbound" indexname="PK_Database2Inbound" id="lock4ef4c4880" mode="X" associatedObjectId="72057594361151488">
    <owner-list>
     <owner id="process29b0cf8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process482ecf8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

Update:

The order of events is as follows:

The process victim: (Proc1)                                            | The process survivor: (Proc2)
-------------------------------------------------------------------------------------------------------------------
INSERT INTO dbo.Database2InboundTodo                                   | INSERT INTO dbo.Database2InboundTodo
                                                                       |
- INSERT INTO [dbo].[Database2Inbound]                                 | - INSERT INTO [dbo].[Database2Inbound]
                                                                       |
  - Process_Workdays_Update                                            |   - Process_Holidays_Update
                                                                       |
    - UPDATE dbo.Chords (primary responsibility of Proc1)              |      - UPDATE dbo.Chords (primary responsibility of Proc2)            
    - UPDATE dbo.Database2Inbound (secondary responsibility of Proc1)  |      - UPDATE dbo.Database2Inbound (secondary responsibility of Proc2)
1

The first process starts updating dbo.Chords. While that is updating, the second process starts updating dbo.Database2Inbound. Now the first process finishes the update which fires a trigger to insert into Database2Inbound. It has to wait, though, because the update from the second process is blocking it with the uncommitted update. The second process also fires the trigger which also needs Database2Inbound for an insert but the first process is still on hold ahead of it.

Now the first process is waiting on the second one to let go of Database2Inbound so it can do its insert and the second one is waiting on the first one to let go of the same table so it can do its own insert. Neither one can complete unless the other finishes or is killed and this makes a deadlock. Modern databases are smart enough to recognize this and will pick one of them to be the "deadlock victim" and kill it.

How do you prevent this? Since the second process is calling Database2Inbound and then asking for more of Database2Inbound that process will need to escalate the lock to prevent process 1 from putting a hold on it in the middle. Since it's not doing it automatically you will need to manually tell it to do so.

Helpful info about locking and escalation: https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx

Info about deadlocks: https://technet.microsoft.com/en-us/library/ms177433(v=sql.105).aspx

5
  • couple of questions as i am trying to better understand.. in which order should i interpret/read the <frames>? top-down or bottom-up.? which part of the deadlock graph instructs that "Now the first process finishes the update which fires a trigger to insert into Database2Inbound".. I just want to make sure if I have a trigger that inserts into Database2Inbound when the first process finishes..
    – blogs4t
    Sep 10 '17 at 16:49
  • Here is an abstract of what happens in my programs: Process_Workdays_Update & Process_Holidays_Update are two different use cases. both are triggered by inserts into [Database2Inbound].. Process1's primary task is to update the Chords table. It also updates [Database2Inbound] for the result at the end. Process 2 does the same thing. I could not connect the dots how the update on Chords conflicts with update on Database2Inbound.. as per the deadlock graph.. really appreciate if you could throw some light.
    – blogs4t
    Sep 10 '17 at 17:19
  • By the way, my above interpretation of the <executionStack> and of the <frames> is bottom-up.. I have updated the question by providing the order of execution of the two processes..
    – blogs4t
    Sep 10 '17 at 17:35
  • The second insert into Database2Inbound cannot complete until the first one does. But the first insert can't complete because the update on the table is still locking it. But the second insert and the update are on the same process_id. You can tell it fires the trigger because they are contained in the same process id tag
    – indiri
    Sep 11 '17 at 15:33
  • I solved this by removing the UPDATE dbo.Database2Inbound statements in both the procedures. thanks for the help.
    – blogs4t
    Sep 27 '17 at 16:53

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.