SQL Server 2005:读取提交的事务隔离级别中的键范围锁定? [英] SQL Server 2005: Key-Range Locks in Read Committed Transaction Isolation Level?

问题描述

我正在帮助解决使用SQL Server 2005的.NET应用程序中的某些死锁.我从下面的跟踪中获得了XML数据.

I'm helping troubleshoot some deadlocking in a .NET application that uses SQL Server 2005. I have the XML data from the trace below.

真正使我感到困惑的是，当事务隔离级别被读取为已提交时，PK_Exp_Experience_PriorFirm上的RangeX-X锁定.

What really puzzles me is the RangeX-X lock on PK_Exp_Experience_PriorFirm when the transaction isolation level is read committed.

我读过的所有内容都表明，使用事务隔离级别可序列化"的操作只会得到一个键范围锁.到目前为止，我在应用程序中找不到将隔离级别设置为除已读提交以外的任何位置的位置，并且下面的XML还指示我们正在使用已读提交.

Everything I've read indicates that you only get a key-range lock of you are using the transaction isolation level "serializable". So far, I can't find any places in our application where we set the isolation level to anything other than read committed, and the XML below also indicates that we're using read committed.

但是，如果我们使用的是提交读操作，我将不了解跟踪如何显示存在键范围锁定.有没有人知道如何发生这种情况?

But if we're using read-committed, I don't understand how the trace shows there being a key-range lock. Does anyone have ideas about how that could be happening?

<deadlock-list>
  <deadlock victim="processc2f438">
    <process-list>
      <process id="processc2f438" taskpriority="0" logused="13488" waitresource="KEY: 120:72057594583646208 (8201498b6efe)" waittime="484" ownerId="693258089" transactionname="user_transaction" lasttranstarted="2009-01-06T16:33:27.817" XDES="0xa71ce370" lockMode="U" schedulerid="1" kpid="9112" status="suspended" spid="53" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-01-06T16:33:27.863" lastbatchcompleted="2009-01-06T16:33:27.863" clientapp=".Net SqlClient Data Provider" hostname="CHQAPT3" hostpid="6464" loginname="AppUser" isolationlevel="read committed (2)" xactid="693258089" currentdb="120" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
          <frame procname="adhoc" line="1" stmtstart="108" sqlhandle="0x0200000015d9962978fc6206b09e4c872150511b455e8923">
            UPDATE Exp_Experience_PriorFirm SET RelatedGuid = @newGuid WHERE RelatedGuid = @oldGuid
          </frame>
          <frame procname="mssqlsystemresource.sys.sp_executesql" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000">
            sp_executesql
          </frame>
          <frame procname="MyDb.dbo.Contact_MergeRelationships" line="74" stmtstart="4754" stmtend="4976" sqlhandle="0x0300780036a608461ed8af00669b00000100000000000000">
            EXEC sp_executesql @sql,
            N'@oldGuid uniqueidentifier, @newGuid uniqueidentifier',
            @oldGuid, @newGuid
          </frame>
          <frame procname="MyDb.dbo.Contact_Company_MergeRelationships" line="8" stmtstart="312" sqlhandle="0x03007800b271a129c8ccaf00669b00000100000000000000">
            EXEC Contact_MergeRelationships @oldGuid, @newGuid, 'Contact_Company', @excludedTableNames
          </frame>
        </executionStack>
        <inputbuf>
          Proc [Database Id = 120 Object Id = 698446258]
        </inputbuf>
      </process>
      <process id="processeb5d68" taskpriority="0" logused="14212" waitresource="KEY: 120:72057594594066432 (7c02a3a5890e)" waittime="2312" ownerId="693243114" transactionname="user_transaction" lasttranstarted="2009-01-06T16:33:20.957" XDES="0x8cdb9450" lockMode="S" schedulerid="2" kpid="9000" status="suspended" spid="73" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2009-01-06T16:33:29.770" lastbatchcompleted="2009-01-06T16:33:29.770" clientapp=".Net SqlClient Data Provider" hostname="CHQAPT3" hostpid="6464" loginname="AppUser" isolationlevel="read committed (2)" xactid="693243114" currentdb="120" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
          <frame procname="MyDb.dbo.Contact_Company_Delete" line="27" stmtstart="1128" sqlhandle="0x03007800b0e5761877cbaf00669b00000100000000000000">
            DELETE FROM Contact WHERE GUID = @Guid;
          </frame>
        </executionStack>
        <inputbuf>
          Proc [Database Id = 120 Object Id = 410445232]
        </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <keylock hobtid="72057594583646208" dbid="120" objectname="MyDb.dbo.Exp_Experience_PriorFirm" indexname="PK_Exp_Experience_PriorFirm" id="lockd1d43f80" mode="RangeX-X" associatedObjectId="72057594583646208">
        <owner-list>
          <owner id="processeb5d68" mode="RangeX-X"/>
        </owner-list>
        <waiter-list>
          <waiter id="processc2f438" mode="U" requestType="wait"/>
        </waiter-list>
      </keylock>
      <keylock hobtid="72057594594066432" dbid="120" objectname="MyDb.dbo.Contact_PersonCompanyLocation" indexname="PK_Contact_PersonCompanyLocation" id="lockd20c4380" mode="X" associatedObjectId="72057594594066432">
        <owner-list>
          <owner id="processc2f438" mode="X"/>
        </owner-list>
        <waiter-list>
          <waiter id="processeb5d68" mode="S" requestType="wait"/>
        </waiter-list>
      </keylock>
    </resource-list>
  </deadlock>
</deadlock-list>

推荐答案

PK_Exp_Experience_PriorFirm上的RangeX-X锁已被用作级联删除的一部分.

The RangeX-X lock on PK_Exp_Experience_PriorFirm was being taken as part of a cascading delete.

对于某些操作(例如级联删除)，SQL Server会自动将隔离级别升级为可序列化.

SQL Server automatically upgrades the isolation level to serializable for certain operations, such as cascading deletes.

此处对此进行了更详细的描述:

This is described in more detail here: Conor vs. Isolation Level Upgrade on UPDATE/DELETE Cascading RI.

这篇关于SQL Server 2005:读取提交的事务隔离级别中的键范围锁定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！