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 = 'FolderId'
) </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 = 'E'
THEN @LogMsg
ELSE ''
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)
Best Answer
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