SQL Server Deadlock – Explain Deadlock Graph Modes

deadlocksql server

I got such a deadlock:
short deadlock

I see that /process/@lockMode is equal to /owner/@mode.
But why /keylock/@mode is not equal to /owner/@mode?
What does /keylock/@mode mean?

As I understand from the entire deadlock, processVictim should neither use U lock nor X lock.

Here is the entire deadlock:

<deadlock-list>
 <deadlock victim="processVictim">

  <process-list>
   <process id="processVictim" taskpriority="0" logused="0" waitresource="KEY: 10:72057602742812672 (3100aad8604d)" waittime="2982" ownerId="222541347" transactionname="user_transaction" lasttranstarted="2018-04-28T00:17:29.680" XDES="0x7d6dcf990" lockMode="S" schedulerid="13" kpid="14052" status="background" spid="25" sbid="0" ecid="0" priority="0" trancount="1">
    <executionStack>
     <frame procname="Db1.notify.pRuleActivated" line="71" stmtstart="3296" stmtend="3508" sqlhandle="0x03000e00d9368812ce87f700aaa800000100000000000000">
if exists(select 1 from DbRepl.dbo.vRule where RuleId = @nRuleId_ and CloseDate is not null)     </frame>
     <frame procname="Db1.notify.pRuleActivatedHandler" line="21" stmtstart="928" sqlhandle="0x03000e000f89bc79375d210165a100000100000000000000">
EXEC notify.pRuleActivated
    @xmlMsg = @xmlMsg     </frame>
     <frame procname="Db1.notify.pQppQueueProcNotificationProcessingQueue" line="105" stmtstart="5906" stmtend="6154" sqlhandle="0x03000e0020de2a3dd505d00096a100000100000000000000">
exec @proc_ @xmlMsg = @msg_, @uuidConversationGroup = @cgid_, @uuidConversationHandle = @dh_, @nServiceId = @service_id_;     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>

   <process id="processSurvivor" taskpriority="0" logused="149676" waitresource="KEY: 10:72057602742681600 (460029689516)" waittime="2904" ownerId="222536438" transactionname="user_transaction" lasttranstarted="2018-04-28T00:16:28.673" XDES="0x17ea1c780" lockMode="X" schedulerid="12" kpid="9864" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-04-28T00:17:34.753" lastbatchcompleted="2018-04-28T00:17:34.710" isolationlevel="read committed (2)" xactid="222536438" currentdb="10" lockTimeout="4294967295" clientoption1="671156320" clientoption2="128056">
    <executionStack>
     <frame procname="DbRepl.dbo._pReplUpd_dbo_tRule" line="34" stmtstart="1514" stmtend="6938" sqlhandle="0x03000a0067d8b8712176bf00c0a800000100000000000000">
update [dbo].[tRule] set
        [Id] = case substring(@bitmap,1,1) &amp; 2 when 2 then @c2 else [Id] end,
        [TypeId] = case substring(@bitmap,1,1) &amp; 4 when 4 then @c3 else [TypeId] end,
        [EventId] = case substring(@bitmap,1,1) &amp; 8 when 8 then @c4 else [EventId] end
where [RuleId] = @pkc1     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 10 Object Id = 1907939431]    </inputbuf>
   </process>
  </process-list>

  <resource-list>
   <keylock hobtid="72057602742812672" dbid="10" objectname="DbRepl.dbo.tEvent" indexname="PK_tEvent" id="lock261441400" mode="X" associatedObjectId="72057602742812672">
    <owner-list>
     <owner id="processSurvivor" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processVictim" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>

   <keylock hobtid="72057602742681600" dbid="10" objectname="DbRepl.dbo.tRule" indexname="PK_tRule" id="lock476f3d680" mode="U" associatedObjectId="72057602742681600">
    <owner-list>
     <owner id="processVictim" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processSurvivor" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>

 </deadlock>
</deadlock-list>

And it's visualization:
dl_pic

Best Answer

Both sessions have locks on the second key. The reader has an S and the writer has a U and wants to convert to X. Consider changing the database to use READ_COMMITTED_SNAPSHOT mode, or add an XLOCK hint to the UPDATE so it doesn't read with U locks and then convert to X.

What are these modes.

I think that is the most restrictive lock locks currently held on the resource, as opposed to the locks requested. This is often the lock that is incompatible with the blocked session's request, but not always.

Why is this mode not S.

Because the most restrictive lock currently held on the resource is the U lock held by the writer session, and which it has requested to "convert" from U to X.

Related Question