主页 / dba / 问题 / 205245
Accepted
GriGrim
Asked: 2018-04-29 03:39:30 +0800 CST

解释 SQL Server 死锁图模式

  • 772

我遇到了这样的僵局： 短暂的僵局

我看到/process/@lockMode等于/owner/@mode。但为什么/keylock/@mode不等于/owner/@mode？是什么/keylock/@mode意思？

正如我从整个死锁中理解的那样，processVictim 既不应该使用 U 锁也不应该使用 X 锁。

这是整个僵局：

<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>

它的可视化： dl_pic

sql-server deadlock

1 个回答

  1. Best Answer

    两个会话都锁定了第二个密钥。读取器有一个 S，写入器有一个 U，并且想要转换为 X。考虑将数据库更改为使用 READ_COMMITTED_SNAPSHOT 模式，或者在 UPDATE 中添加一个 XLOCK 提示，这样它就不会使用 U 锁读取然后转换为 X。

    这些模式是什么。

    我认为这是资源上当前持有的最严格的锁，而不是请求的锁。这通常是与阻塞会话的请求不兼容的锁，但并非总是如此。

    为什么这个模式不是S。

    因为当前在资源上持有的最严格的锁是写入会话持有的 U 锁，并且它已请求将其从 U“转换”为 X。

    • 4

相关问题