为什么此SQL Server查询死锁？ [英] Why is this SQL Server query deadlocking?

查看：76
本文介绍了为什么此SQL Server查询死锁？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

有人可以告诉我以下SQL Server查询为何死锁，以及解决该问题的解决方案是什么？

 <死锁列表> 
<死锁受害者= process88b5b8> 
< process-list> 
< process id = process88b5b8 taskpriority = 0 logused = 76132 waitresource = RID：32：1：151867：174 waittime = 5093 ownerId = 65554098 transactionguid = 0xedf3314c05f1124cbe8d480cd092e03e transactionname = DTCXact lasttranstarted = 2011-09-02T19：00：29.690 XDES = 0x1029e040 lockMode = S schedulerid = 1 kpid = 5108 status = suspended spid = 118 sbid =  0 ecid = 0优先级= 0 transcount = 2 lastbatchstarted = 2011-09-02T19：00：31.317 lastbatchcompleted = 2011-09-02T19：00：31.300 hostname = MELWFPL382S hostpid = 0登录名= MM4隔离级别=可重复读取（3） xactid = 65554098 currentdb = 32 lockTimeout = 4294967295 clientoption1 = 671088672 clientoption2 = 128056> 
< executionStack> 
< frame procname = adhoc line = 1 stmtstart = 78 sqlhandle = 0x020000004b4b0a0d63e1040095143cbaa0174ffc3e076067>从其中PARTYEXTERNALREF = @ P0和ISCOUNTERPARTY ='N'和PARTYID的PARTIES中删除（从NAB_PARTY_EXTEND中选择PARTYID（无锁），其中PARTYTYPE = @ P1）< / frame> 
< frame procname =未知 line = 1 sqlhandle = 0x000000000000000000000000000000000000000000000000000000>未知< / frame> 
< / executionStack> 
< inputbuf>（@ P0 nvarchar（4000），@ P1 nvarchar（4000））从其中PARTYEXTERNALREF = @ P0和ISCOUNTERPARTY ='N'和PARTYID的PARTIES中删除（从NAB_PARTY_EXTEND（nolock）中选择PARTYID，其中PARTYTYPE = @ P1）< / inputbuf> 
< / process> 
< process id = process9196a8 taskpriority = 0 logused = 132612 waitresource = RID：32：1：140302：31 waittime = 5046 ownerId = 65554657 transactionguid = 0x7313c78fecc8914dac3ed821cd7c21fe transactionname = DTCXact lasttranstarted = 2011-09-02T19：00：34.100 XDES = 0x12835778 lockMode = S schedulerid = 2 kpid = 3692 status = suspended spid = 94 sbid =  0 ecid = 0优先级= 0 transcount = 2 lastbatchstarted = 2011-09-02T19：00：35.690 lastbatchcompleted = 2011-09-02T19：00：35.687 hostname = MELWFPL382S hostpid = 0登录名= MM4隔离级别=可重复读取（3） xactid = 65554657 currentdb = 32 lockTimeout = 4294967295 clientoption1 = 671088672 clientoption2 = 128056> 
< executionStack> 
< frame procname = adhoc line = 1 stmtstart = 78 sqlhandle = 0x020000004b4b0a0d63e1040095143cbaa0174ffc3e076067>从PARTIES中删除，其中PARTYEXTERNALREF = @ P0和ISCOUNTERPARTY ='N'和PARTYID来自（ NAB_PARTY_EXTEND（无锁），其中PARTYTYPE = @ P1）< / frame> 
< frame procname =未知 line = 1 sqlhandle = 0x000000000000000000000000000000000000000000000000000000>未知< / frame> 
< / executionStack> 
< inputbuf>（@ P0 nvarchar（4000），@ P1 nvarchar（4000））从其中PARTYEXTERNALREF = @ P0和ISCOUNTERPARTY ='N'和PARTYID的PARTIES中删除（从NAB_PARTY_EXTEND（nolock）中选择PARTYID，其中PARTYTYPE = @ P1）< / inputbuf> 
< / process> 
< / process-list> 
< resource-list> 
< ridlock fileid = 1 pageid = 140302 dbid = 32 objectname = mm4_melwfpl382s.dbo.COUNTERPARTYSSI id = lock170fa500 mode = X relatedObjectId = 72057595803336704> 
< owner-list> 
<所有者id = process88b5b8模式= X /> 
< / owner-list> 
< waiter-list> 
< waiter id = process9196a8 mode = S requestType = wait /> 
< / waiter-list> 
< / ridlock> 
< ridlock fileid = 1 pageid = 151867 dbid = 32 objectname = mm4_melwfpl382s.dbo.COUNTERPARTYSSI id = lock20e65d80 mode = X relatedObjectId = 72057595803336704> 
< owner-list> 
< owner id = process9196a8 mode = X /> 
< / owner-list> 
< waiter-list> 
< waiter id = process88b5b8 mode = S requestType = wait /> 
< / waiter-list> 
< / ridlock> 
< / resource-list> 
< / deadlock> 
< / deadlock-list>

我不了解的是两个进程如何在同一个对象上拥有排他锁。 / p>

在PARTIES表上有一个索引（IDX_NC_PARTIES_PARTYEXTERNALREF_ISCOUNTERPARTY_PARTYID），数据库已设置为读取已提交的快照。



谢谢，



韦恩。

解决方案


  • 进程9196a8在X模式下具有页面151867插槽174，并希望在S模式下显示页面140302插槽31

  • 进程88b5b8在X模式下具有页面140302的插槽31，想要页面151867插槽174处于S模式下

  • 这两个删除操作在 isolationlevel = repeatable read（3）

下运行

因此死锁发生在表的基本堆上（RID锁而不是键锁意味着堆而不是Btree）。高隔离级别（可能是由DTC引起的，从xact名称来看）使RCSI设置无关紧要。



PARTYEXTERNALREF和PARTYTYPE列是什么类型？传入的参数为NVARCHAR（即Unicode），如果列为VARCHAR（即Ascii），则由于数据类型优先级不会使用NC索引。由于涉及表扫描以及使用中的高隔离级别，因此几乎无法避免死锁。



解决方案是对@ P0和@ P1使用VARCHAR类型的参数，以便利用NC索引来避免表扫描。



如果参数已经是VARCHAR类型，并且您可以从执行计划中确认使用了NC搜索，那么我的第一个问题是 else 是

BTW，您只提供NC索引的名称，但我假设它位于（PARTYEXTERNALREF，ISCOUNTERPARTY ，PARTYID）。



Update



您的评论说列是 NVARCHAR，则表扫描假设可能是错误的。还有三种可能导致需要研究的僵局的可能性：




  • 在DELETE之前事务运行的任何其他语句（这是最

  • 与死锁相关的两个DELETE语句选择的行中的任何重叠

  • 哈希冲突



对于前两个假设，您现在只能执行任何操作（请检查它们是否正确）。对于最后一个，我可以告诉您如何进行验证，但这并不简单。这不太可能发生，并且很难证明，但是有可能。由于您知道死锁情况（附加的XML），因此可以将其用作调查依据：




  • 还原以下项的时间点副本：数据库停止在2011-09-02T19：00：29.690

  • 运行 DBCC TRACEON（3604，-1）

  • 使用DBCC PAGE（<还原的数据库ID>，1、151867、3） 检查插槽174中​​的值

  • 使用DBCC PAGE（，1，140302，3）`检查插槽31处的值

  • 运行 SELECT %% lockres％来自PARTYEXTERNALREF = ... AND ISCOUNTERPARTY ='N'和PARTYID = ... 的各方的百分比，并传递上面读取的值

  • 比较结果锁哈希值，如果它们匹配，那么您将发生哈希冲突，这会导致死锁。


Can someone tell me why the following SQL Server queries is deadlocking and what is the solution to fixing it?

<deadlock-list>
  <deadlock victim="process88b5b8">
    <process-list>
      <process id="process88b5b8" taskpriority="0" logused="76132" waitresource="RID: 32:1:151867:174" waittime="5093" ownerId="65554098" transactionguid="0xedf3314c05f1124cbe8d480cd092e03e" transactionname="DTCXact" lasttranstarted="2011-09-02T19:00:29.690" XDES="0x1029e040" lockMode="S" schedulerid="1" kpid="5108" status="suspended" spid="118" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-09-02T19:00:31.317" lastbatchcompleted="2011-09-02T19:00:31.300" hostname="MELWFPL382S" hostpid="0" loginname="MM4" isolationlevel="repeatable read (3)" xactid="65554098" currentdb="32" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
          <frame procname="adhoc" line="1" stmtstart="78" sqlhandle="0x020000004b4b0a0d63e1040095143cbaa0174ffc3e076067"> delete from PARTIES where PARTYEXTERNALREF=@P0 and ISCOUNTERPARTY='N' and PARTYID in (select PARTYID from NAB_PARTY_EXTEND (nolock) where PARTYTYPE=@P1)     </frame>
          <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown     </frame>
        </executionStack>
        <inputbuf>(@P0 nvarchar(4000),@P1 nvarchar(4000))delete from PARTIES where PARTYEXTERNALREF=@P0 and ISCOUNTERPARTY='N' and PARTYID in (select PARTYID from NAB_PARTY_EXTEND (nolock) where PARTYTYPE=@P1)                    </inputbuf>
      </process>
      <process id="process9196a8" taskpriority="0" logused="132612" waitresource="RID: 32:1:140302:31" waittime="5046" ownerId="65554657" transactionguid="0x7313c78fecc8914dac3ed821cd7c21fe" transactionname="DTCXact" lasttranstarted="2011-09-02T19:00:34.100" XDES="0x12835778" lockMode="S" schedulerid="2" kpid="3692" status="suspended" spid="94" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-09-02T19:00:35.690" lastbatchcompleted="2011-09-02T19:00:35.687" hostname="MELWFPL382S" hostpid="0" loginname="MM4" isolationlevel="repeatable read (3)" xactid="65554657" currentdb="32" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
          <frame procname="adhoc" line="1" stmtstart="78" sqlhandle="0x020000004b4b0a0d63e1040095143cbaa0174ffc3e076067">delete from PARTIES where PARTYEXTERNALREF=@P0 and ISCOUNTERPARTY='N' and PARTYID in (select PARTYID from NAB_PARTY_EXTEND (nolock) where PARTYTYPE=@P1)     </frame>
          <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown     </frame>
        </executionStack>
        <inputbuf>(@P0 nvarchar(4000),@P1 nvarchar(4000))delete from PARTIES where PARTYEXTERNALREF=@P0 and ISCOUNTERPARTY='N' and PARTYID in (select PARTYID from NAB_PARTY_EXTEND (nolock) where PARTYTYPE=@P1)                    </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <ridlock fileid="1" pageid="140302" dbid="32" objectname="mm4_melwfpl382s.dbo.COUNTERPARTYSSI" id="lock170fa500" mode="X" associatedObjectId="72057595803336704">
        <owner-list>
          <owner id="process88b5b8" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process9196a8" mode="S" requestType="wait" />
        </waiter-list>
      </ridlock>
      <ridlock fileid="1" pageid="151867" dbid="32" objectname="mm4_melwfpl382s.dbo.COUNTERPARTYSSI" id="lock20e65d80" mode="X" associatedObjectId="72057595803336704">
        <owner-list>
          <owner id="process9196a8" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process88b5b8" mode="S" requestType="wait" />
        </waiter-list>
      </ridlock>
    </resource-list>
  </deadlock>
</deadlock-list>

What I don't understand is how two process can have an exclusive lock on the same object.

There is an index (IDX_NC_PARTIES_PARTYEXTERNALREF_ISCOUNTERPARTY_PARTYID) on the PARTIES table and the database is set to read committed snapshot.

Thanks,

Wayne.

解决方案

  • process 9196a8 has page 151867 slot 174 in X mode and wants page 140302 slot 31 in S mode
  • process 88b5b8 has page 140302 slot 31 in X mode and wants page 151867 slot 174 in S mode
  • the two deletes run under isolationlevel="repeatable read (3)"

So the deadlock occur on the base heap of the table (RID locks instead of key locks implies a heap not a Btree). The high isolation level (likely caused by DTC, judging from the xact name) makes the RCSI setting irrelevant.

What type are the columns PARTYEXTERNALREF and PARTYTYPE? The parameters passed in are NVARCHAR (ie. Unicode) and if the columns are VARCHAR (ie. Ascii) then due to the rules of data type precedence the NC index would not be used. Because of the table scan involved, together with of the high isolation level in use, a deadlock is almost unavoidable.

The solution would be to use VARCHAR type parameters for @P0 and @P1 so the NC index would be leveraged to avoid the table scan.

If the parameters are already of type VARCHAR and you can confirm from the execution plan that a seek on the NC is used, the my first question would be what else is the transaction doing, other than the delete statements?

BTW, you only give the name of the NC index but I assume is on (PARTYEXTERNALREF, ISCOUNTERPARTY, PARTYID).

Update

Since your comment say that the columns are NVARCHAR then the tables scan hypothesis is probably wrong. There are three more possibilities to cause a deadlock that need investigation:

  • any other statement run by the transaction before the DELETE (this is the most likely)
  • any overlap in the rows selected by two DELETE statements involved in the deadlock
  • hash collision

For the first two hypotheses only you can do anything right now (investigate if they are correct). For the last one I can tell you how to verify it, but is not trivial. It is unlikely to happen and a bit difficult to prove, but it is possible. Since you know of as deadlock case (the attached XML), use it as investigation base:

  • restore a point-in-time copy of the database with stop at 2011-09-02T19:00:29.690
  • run DBCC TRACEON(3604,-1)
  • using DBCC PAGE (<restored db id>, 1, 151867, 3) inspect the values in slot 174
  • using DBCC PAGE(, 1, 140302, 3)` inspect the values at slot 31
  • run SELECT %%lockres%% FROM PARTIES WHERE PARTYEXTERNALREF = ... AND ISCOUNTERPARTY='N' and PARTYID=... and pass in the values read above
  • compars the resulted lock hash values, if they match then you have a hash collision and this caused the deadlock.

这篇关于为什么此SQL Server查询死锁？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆