SQL Server 2005: Key-Range Locks in Read Committed Transaction Isolation Level?

Question

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

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

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>

Answer 1

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

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.

Answer 2

You are using READ COMMITTED, as you expect.

Deadlocks like this can occur if an UPDATE acquires an eXclusive key lock on the clustered index and modifies a row, and that lock blocks a SELECT’s bookmark lookup on the clustered index.

Locks of this nature can often be eliminated by creating a covering non-clustered index.

Another option available to you is to set READ_COMMITTED_SNAPSHOT ON for the database. This changes how the way SELECT statements read committed data; instead of taking shared locks, they read prior versions (a snapshot) of any data changed by transactions that began at the start of the SELECT statement. This doesn't come entirely for free though; the cost is increased activity in tempDB. [There is also the potential for issues with triggers in READ COMMITTED SNAPSHOT mode.]

Answer 3

Another common explanation for this may be related to UNIQUE indexes with the IGNORE_DUP_KEY option set ON.

From BOL - This option specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

ON A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.

OFF An error message will occur when duplicate key values are inserted into a unique index. The entire INSERT operation will be rolled back.

What they fail to mention here is that SERIALIZABLE isolation is enforced during INSERTS when this option is enabled. I personally have not grasped the inherent requirement to do so as some inserted rows may be discarded and nothing else, but it is what it is. Queue the SQL dev team to chime in here...

This behaviour is easily demonstrated;

First create a new table with a typical PK:

CREATE TABLE [dbo].[Test_RC_TIL_RangeLocks](
    [RID] [int] IDENTITY(1,1) NOT NULL,
    [Col1] [int] NOT NULL,
    [Col2] [int] NOT NULL,
    [Col3] [int] NOT NULL
) ON [PRIMARY]

Next we want to add a UNIQUE index on Col1 and Col2 with the IGNORE_DUP_KEY ON:

CREATE UNIQUE NONCLUSTERED INDEX [UIX_Test_RC_TIL_RangeLocks] ON [dbo].[Test_RC_TIL_RangeLocks](
    [Col1] ASC,
    [Col2] ASC
)WITH (
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    SORT_IN_TEMPDB = OFF, 
    IGNORE_DUP_KEY = ON, --<<**THE OFFENDER**>>
    DROP_EXISTING = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]

Next we'll add 5 rows and see what happens...

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @C int
SELECT @C=5
BEGIN TRANSACTION
WHILE @C>0
BEGIN
    INSERT Test_RC_TIL_RangeLocks(Col1,Col2,Col3)
    VALUES (@C,@C+1,2*@C + 100)
    SET @C=@C-1
END
SELECT * FROM Test_RC_TIL_RangeLocks
EXEC sp_lock @@SPID
COMMIT

As expected we have added five rows:

RID Col1    Col2    Col3
1   5       6       110
2   4       5       108
3   3       4       106
4   2       3       104
5   1       2       102

And the locks we're interested in are:

sid ObjId     IndId Type Resource                  Mode
53  0             0 DB                             S
53  0             0 DB                             S
53  402100473     1 KEY (8194443284a0)             X
53  402100473     2 KEY (550e0a2a4b96)             RangeX-X
53  402100473     2 KEY (ffffffffffff)             RangeS-U
53  1131151075    0 TAB                            IS
53  402100473     1 PAG 1:744                      IX
53  402100473     2 PAG 1:748                      IX
53  402100473     1 KEY (98ec012aa510)             X
53  402100473     1 KEY (a0c936a3c965)             X
53  402100473     2 KEY (ec04ac4bee1f)             RangeX-X
53  402100473     0 TAB                            IX
53  402100473     2 KEY (0207a0a08e23)             RangeX-X
53  402100473     2 KEY (7112ec63c430)             RangeX-X
53  402100473     1 KEY (59855d342c69)             X
53  402100473     1 KEY (61a06abd401c)             X
53  338100245     0 TAB                            IX

Ah, the dreaded SERIALIZABLE Key Range Locks invoked under a READ COMMITTED isolation level on a simple insert!

So when BOL says; Before key-range locking can occur, the following conditions must be satisfied:

• The transaction-isolation level must be set to SERIALIZABLE.

Remember, that is NOT always true...

p.s. As a design note, blindly eliminating duplicate keys that may have unique row values is generally bad practice. A better practice is to make sure you are not attempting to insert duplicate keys as part of your INSERT statement...

Cheers...

Answer 4

According to SQL Docs, somehow, your transaction (or some other one) was running at level serializable.

Docs for key-range locking here. First sentence reads:

Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level.