个人中心
gpt4 book ai didi

sql - 死锁拼图 : Victim doesn't own any resource, 用kill来解决死锁

转载 作者：行者123 更新时间：2023-12-04 06:25:47 26 4
gpt4 key购买 nike

我有奇怪的死锁图，其中 MSSQL Server 2008 选择的受害者不是死锁循环的一部分。
这个死锁在 select 和 insert 之间。
死锁资源是一个表，其中所有 select 都需要 waitresource = "KEY: 6:72057594098810880 (ffffffffffff)"
Question1 :这里的 ffffffffffff 是否意味着他们想要对整个表进行全范围锁定？还是整个键范围？或者是其他东西？

我们遵循一个规则，其中表永远不会有主键 id = 0 的行。
我们做这种检查的地方很少
select foo from bar where @someId = 0 OR SomeId = @someId 。

我也开始知道 SQL 不会使表达式短路。因此，如果我通过 @someId = 0 并不能保证不会评估其他部分。所以 SQL 有可能在运行时执行 SomeId = @someId 。

Question2 :由于无法在 SomeId 中找到 0，SQL 将获取整个表(或行)的范围锁，因此其他人不会插入 0 id。对？

考虑到这个假设，我将 where 子句更改为

(CASE
       WHEN @someId = 0 THEN 1
       WHEN SomeId = @someId THEN 1
       ELSE 0
END = 1)

希望这将强制评估顺序。但我错了。我又陷入僵局了。
我附上了下面的死锁图。我重命名了涉及的表和过程(公司政策)

Question3 : 你知道我在这里遗漏了什么吗？

enter image description here 
  <deadlock-list>
 <deadlock victim="process722c508">
  <process-list>
   <process id="process722c508" taskpriority="0" logused="0" waitresource="KEY: 6:72057594098810880 (ffffffffffff)" waittime="6217" ownerId="24219001" transactionname="SELECT" lasttranstarted="2011-05-17T03:29:16.033" XDES="0x80073a40" lockMode="RangeS-S" schedulerid="13" kpid="20436" status="suspended" spid="91" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-17T03:29:16.033" lastbatchcompleted="2011-05-17T03:29:16.033" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24219001" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="fnGetTableResultAByBId" line="44" stmtstart="2246" stmtend="3566" sqlhandle="0x03000600800d7f0bda124000d99e00000000000000000000">
INSERT INTO @ReturnTable
    SELECT Foo, Bar
    FROM TheOneTable 
    WHERE ZId = @zId 
    AND (CASE
            WHEN @yId = 0 THEN 1
            WHEN YId = @yId THEN 1
            ELSE 0
        END = 1)
    AND (CASE
            WHEN @xId = 0 THEN 1
            WHEN XId = @xId THEN 1
            ELSE 0
        END = 1)     </frame>
     <frame procname="GetViewCByDId" line="9" stmtstart="272" stmtend="2984" sqlhandle="0x03000600c21629025d8f3f00d99e00000100000000000000">
    </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 36247234]    </inputbuf>
   </process>
   <process id="process7185048" taskpriority="0" logused="0" waitresource="KEY: 6:72057594098810880 (ffffffffffff)" waittime="6217" ownerId="24218992" transactionname="SELECT" lasttranstarted="2011-05-17T03:29:16.030" XDES="0x179980430" lockMode="RangeS-S" schedulerid="13" kpid="30616" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-17T03:29:16.030" lastbatchcompleted="2011-05-17T03:29:16.030" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24218992" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="fnGetTableResultAByBId" line="44" stmtstart="2246" stmtend="3566" sqlhandle="0x03000600800d7f0bda124000d99e00000000000000000000">
INSERT INTO @ReturnTable
    SELECT Foo, Bar
    FROM TheOneTable 
    WHERE ZId = @zId 
    AND (CASE
            WHEN @yId = 0 THEN 1
            WHEN YId = @yId THEN 1
            ELSE 0
        END = 1)
    AND (CASE
            WHEN @xId = 0 THEN 1
            WHEN XId = @xId THEN 1
            ELSE 0
        END = 1)     </frame>
     <frame procname="GetViewCByDId" line="9" stmtstart="272" stmtend="2984" sqlhandle="0x03000600c21629025d8f3f00d99e00000100000000000000">
</frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 36247234]    </inputbuf>
   </process>
   <process id="process7223048" taskpriority="0" logused="0" waitresource="KEY: 6:72057594098810880 (ffffffffffff)" waittime="5330" ownerId="24235090" transactionname="SELECT" lasttranstarted="2011-05-17T03:29:16.927" XDES="0x840d3b30" lockMode="RangeS-S" schedulerid="15" kpid="23452" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-17T03:29:16.927" lastbatchcompleted="2011-05-17T03:29:16.927" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24235090" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="GetOneRowEByFId" line="11" stmtstart="260" stmtend="2456" sqlhandle="0x03000600db082c08ba823f00d99e00000100000000000000">
         SELECT TOP 1
         Col1, Col2, Col3

         FROM The2ndTable
         INNER JOIN [dbo].[TheOneTable] ON [dbo].[TheOneTable].[LinkBetweenOneAndTwoId]=[The2ndTable].[LinkBetweenOneAndTwoId]
         WHERE [dbo].[TheOneTable].ZId= @ActivityId and
         [TheOneTable].[n
     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 137103579]    </inputbuf>
   </process>
   <process id="process6334088" taskpriority="0" logused="0" waitresource="KEY: 6:72057594098810880 (ffffffffffff)" waittime="5668" ownerId="24229434" transactionname="SELECT" lasttranstarted="2011-05-17T03:29:16.587" XDES="0x17ea9ac90" lockMode="RangeS-S" schedulerid="12" kpid="5104" status="suspended" spid="86" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-17T03:29:16.587" lastbatchcompleted="2011-05-17T03:29:16.587" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24229434" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="GetOneRowEByFId" line="11" stmtstart="260" stmtend="2456" sqlhandle="0x03000600db082c08ba823f00d99e00000100000000000000">
SELECT TOP 1 
    Col1, Col2, Col3

    FROM The2ndTable
    INNER JOIN [dbo].[TheOneTable] ON [dbo].[TheOneTable].[LinkBetweenOneAndTwoId]=[The2ndTable].[LinkBetweenOneAndTwoId]
    WHERE [dbo].[TheOneTable].ZId= @ActivityId and
        [TheOneTable].[n</frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 137103579]    </inputbuf>
   </process>
   <process id="process8808e08" taskpriority="0" logused="0" waitresource="KEY: 6:72057594098810880 (ffffffffffff)" waittime="6652" ownerId="24217112" transactionname="SELECT" lasttranstarted="2011-05-17T03:29:15.610" XDES="0x833b5ca0" lockMode="RangeS-S" schedulerid="1" kpid="19752" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-05-17T03:29:15.610" lastbatchcompleted="2011-05-17T03:29:15.610" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24217112" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="GetOneRowEByFId" line="11" stmtstart="260" stmtend="2456" sqlhandle="0x03000600db082c08ba823f00d99e00000100000000000000">
         SELECT TOP 1
         Col1, Col2, Col3

         FROM The2ndTable
         INNER JOIN [dbo].[TheOneTable] ON [dbo].[TheOneTable].[LinkBetweenOneAndTwoId]=[The2ndTable].[LinkBetweenOneAndTwoId]
         WHERE [dbo].[TheOneTable].ZId= @ActivityId and
         [TheOneTable].[n
     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 137103579]    </inputbuf>
   </process>
   <process id="process5c08988" taskpriority="0" logused="1644" waitresource="KEY: 6:72057594098810880 (91a0638558d2)" waittime="4889" ownerId="24214248" transactionname="user_transaction" lasttranstarted="2011-05-17T03:29:15.327" XDES="0x186609470" lockMode="RangeI-N" schedulerid="9" kpid="9000" status="suspended" spid="102" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-05-17T03:29:15.330" lastbatchcompleted="2011-05-17T03:29:15.330" clientapp=".Net SqlClient Data Provider" hostname="SOMEHOST" hostpid="28820" loginname="someloginname" isolationlevel="serializable (4)" xactid="24214248" currentdb="6" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="InsertIntoTheOneTable" line="25" stmtstart="1334" stmtend="2608" sqlhandle="0x03000600bbbacb5d25883f00d99e00000100000000000000">
INSERT INTO [dbo].[TheOneTable] (Some,Col,Here)
    VALUES (@some,@col,@here)     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 1573632699]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6b17a00" mode="RangeI-N" associatedObjectId="72057594098810880">
    <owner-list />
    <waiter-list>
     <waiter id="process722c508" mode="RangeS-S" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6b17a00" mode="RangeI-N" associatedObjectId="72057594098810880">
    <owner-list />
    <waiter-list>
     <waiter id="process7185048" mode="RangeS-S" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6b17a00" mode="RangeI-N" associatedObjectId="72057594098810880">
    <owner-list />
    <waiter-list>
     <waiter id="process7223048" mode="RangeS-S" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6b17a00" mode="RangeI-N" associatedObjectId="72057594098810880">
    <owner-list />
    <waiter-list>
     <waiter id="process6334088" mode="RangeS-S" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6b17a00" mode="RangeI-N" associatedObjectId="72057594098810880">
    <owner-list>
     <owner id="process5c08988" mode="RangeI-N" />
    </owner-list>
    <waiter-list>
     <waiter id="process8808e08" mode="RangeS-S" requestType="wait" />
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594098810880" dbid="6" objectname="TheOneTable" indexname="PK_TheOneTable" id="lock6372e80" mode="RangeS-S" associatedObjectId="72057594098810880">
    <owner-list>
     <owner id="process7223048" mode="RangeS-S" />
     <owner id="process6334088" mode="RangeS-S" />
    </owner-list>
    <waiter-list>
     <waiter id="process5c08988" mode="RangeI-N" requestType="wait" />
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

最佳答案

在锁定的上下文中，表及其相关索引是独立的实体。有时，死锁发生在表与其索引之间，而不是发生在两个单独的表之间。

当在索引上获取锁然后在相关表(即 bar)上获取另一个锁以进行数据查找时，最有可能出现问题。在插入期间，这将以相反的顺序发生。首先，表(即 bar)被锁定和更新，然后索引被锁定。

select foo 
from bar 
where @someId = 0 OR SomeId = @someId

您是否/可以添加一个包含 SomeId 和 foo 的覆盖索引(以帮助选择)？这样，您将完全避免查找并阻止问题发生。

你能发布查询计划而不是死锁帧吗？

关于sql - 死锁拼图 : Victim doesn't own any resource, 用kill来解决死锁，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6070364/

26 4 0
文章推荐： Java小程序: How to tell the browser (Firefox) where to look for libraries (jars)
文章推荐： ASP.NET MVC 3 AntiForgeryToken 和自定义 MachineKey 配置
文章推荐： php - 是否可以使用 TinyButStrong 和 OpenTBS 插件输出 .docx 和 .odt 以外的格式
文章推荐： php - 从 Flash 保存图像，将其发送到 PHP 并将 URL 字符串返回给 Flash
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com