Got a deadlock on a server and I'm having trouble to understand the deadlock XML.
I do know that is something related to the jiraissue table, but I'm trying to figure out the condition that caused the deadlock.
<deadlock-list>
<deadlock victim="processb1f0c1498">
<process-list>
<process id="processb1f0c1498" taskpriority="0" logused="2168" waitresource="RID: 27:1:30449:23" waittime="4232" ownerId="20896443558" transactionname="implicit_transaction" lasttranstarted="2018-03-09T16:57:57.037" XDES="0xf225183a8" lockMode="U" schedulerid="8" kpid="9736" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-09T16:57:57.077" lastbatchcompleted="2018-03-09T16:57:57.077" lastattention="1900-01-01T00:00:00.077" clientapp="jTDS" hostname="JIRASVR" hostpid="123" loginname="JiraApp" isolationlevel="read committed (2)" xactid="20896443558" currentdb="27" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="88" sqlhandle="0x02000000bf7aec1fb39ea87c02d404222bd6af21bd14f0820000000000000000000000000000000000000000">
UPDATE dbo.jiraissue SET DESCRIPTION= @P0 , UPDATED= @P1 WHERE ID= @P2
</frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 datetime,@P2 bigint)UPDATE dbo.jiraissue SET DESCRIPTION= @P0 , UPDATED= @P1 WHERE ID= @P2
</inputbuf>
</process>
<process id="process1baf46d0c8" taskpriority="0" logused="6364" waitresource="RID: 27:1:6966:18" waittime="4185" ownerId="20896443443" transactionname="implicit_transaction" lasttranstarted="2018-03-09T16:57:56.527" XDES="0xb18f58d28" lockMode="U" schedulerid="11" kpid="5188" status="suspended" spid="75" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-09T16:57:57.133" lastbatchcompleted="2018-03-09T16:57:57.133" lastattention="1900-01-01T00:00:00.133" clientapp="jTDS" hostname="JIRASVR" hostpid="123" loginname="JiraApp" isolationlevel="read committed (2)" xactid="20896443443" currentdb="27" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="902" sqlhandle="0x020000007e528313f10b169994e266259a2c9a5bd6df6ee60000000000000000000000000000000000000000">
UPDATE dbo.jiraissue SET pkey= @P0 , issuenum= @P1 , PROJECT= @P2 , REPORTER= @P3 , ASSIGNEE= @P4 , CREATOR= @P5 , issuetype= @P6 , SUMMARY= @P7 , DESCRIPTION= @P8 , ENVIRONMENT= @P9 , PRIORITY= @P10 , RESOLUTION= @P11 , issuestatus= @P12 , CREATED= @P13 , UPDATED= @P14 , DUEDATE= @P15 , RESOLUTIONDATE= @P16 , VOTES= @P17 , WATCHES= @P18 , TIMEORIGINALESTIMATE= @P19 , TIMEESTIMATE= @P20 , TIMESPENT= @P21 , WORKFLOW_ID= @P22 , SECURITY= @P23 , FIXFOR= @P24 , COMPONENT= @P25 WHERE ID= @P26
</frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 bigint,@P2 bigint,@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 nvarchar(4000),@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 datetime,@P14 datetime,@P15 datetime,@P16 datetime,@P17 bigint,@P18 bigint,@P19 decimal(38,10),@P20 decimal(38,10),@P21 decimal(38,10),@P22 bigint,@P23 bigint,@P24 decimal(38,10),@P25 decimal(38,10),@P26 bigint)UPDATE dbo.jiraissue SET pkey= @P0 , issuenum= @P1 , PROJECT= @P2 , REPORTER= @P3 , ASSIGNEE= @P4 , CREATOR= @P5 , issuetype= @P6 , SUMMARY= @P7 , DESCRIPTION= @P8 , ENVIRONMENT= @P9 , PRIORITY= @P10 , RESOLUTION= @P11 , issuestatus= @P12 , CREATED= @P13 , UPDATED= @P14 , DUEDATE= @P15 , RESOLUTIONDATE= @P16 , VOTES= @P17 , WATCHES= @P18 , TIMEORIGINALESTIMATE= @P19 , TIMEESTIMATE= @P20 , TIMESPENT= @P21 , WORKFLOW_ID= @P22 , SECURITY= @P23 , FIXFOR= @P24 , COMPONENT= @P25 WHERE ID= @P26
</inputbuf>
</process>
</process-list>
<resource-list>
<ridlock fileid="1" pageid="30449" dbid="27" objectname="JIRA.dbo.jiraissue" id="lockb954c5980" mode="X" associatedObjectId="72057594054443008">
<owner-list>
<owner id="process1baf46d0c8" mode="X"></owner>
</owner-list>
<waiter-list>
<waiter id="processb1f0c1498" mode="U" requestType="wait"></waiter>
</waiter-list>
</ridlock>
<ridlock fileid="1" pageid="6966" dbid="27" objectname="JIRA.dbo.jiraissue" id="lockc8b55ae00" mode="X" associatedObjectId="72057594054443008">
<owner-list>
<owner id="processb1f0c1498" mode="X"></owner>
</owner-list>
<waiter-list>
<waiter id="process1baf46d0c8" mode="U" requestType="wait"></waiter>
</waiter-list>
</ridlock>
</resource-list>
</deadlock>
</deadlock-list>
Best Answer
Looks to me like Jira is trying to update the same row (in a heap!) from two different connections, and that one or both updates is likely part of a larger transaction (otherwise, one would just win, and the other would update the same row immediately afterward). Without understanding what else is happening in these transactions, or how one of the updates escalated to an XLOCK, there's not much your peers can do to help.
Have you changed any deadlock priority or optimistic locking properties at the jTDS level or within the JIRA application? Have you contacted JIRA support?