3

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

  • ProcessVictim is calling 3 stored procedure.Out of those 3 is there any code to insert/update/delete any data with in DbRepl.dbo.tRule? – SqlWorldWide Apr 28 '18 at 12:10
  • No. There are only selects from this object. It is object in replicated database, and we don't change it. – GriGrim Apr 28 '18 at 12:32
4

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.

  • Please tell me if I understand correctry what is going on. 1. ProcessSurvivor handles X lock on DbRepl.dbo.tEvent. 2. ProcessVictim handles S lock on DbRepl.dbo.tRule. 3. ProcessVictim waits for DbRepl.dbo.tEvent to be released and wants to get S lock. 4. ProcessSurvivor already got U lock on DbRepl.dbo.tRule, wants to convert it to X lock, but waits until ProcessVictim releases it's S lock. Is that correct? – GriGrim Apr 28 '18 at 12:57
  • 1
    @GriGrim that sounds about right. Here is how SentryOne Plan Explorer (free) visualizes it: i.stack.imgur.com/kU8WW.png – Aaron Bertrand Apr 28 '18 at 14:24
  • @AaronBertrand it's strange that there is no U-lock on SentryOne plan visualization, whereas on SQL Profiler there is one. – GriGrim Apr 28 '18 at 14:37
  • @GriGrim We show the mode of the victim and survivor processes, not of the lock itself. – Aaron Bertrand Apr 28 '18 at 15:24
  • 3
    And notice that the SSMS visualization (incorrectly) takes the "Owner Mode" from the keylock/@mode element instead of the keylock/ownerlist/owner/@mode. The SentryOne visualization is more accurate, as the U lock isn't owned by the victim. – David Browne - Microsoft Apr 28 '18 at 16:09

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.