Good reasons to use SELECT... WITH XLOCK?

Question

I am facing some re-occurring deadlocks, one of which is a Keylock and contains a SELECT query with XLOCK hint that becomes the deadlock victim. The other statement is an INSERT into one of the tables that is part of the view of the first query.

View:

create view dbo.viewE
 as
    select * from dbo.E  
    where myValue > 13000 

Select Query:

select * from dbo.viewE with (XLOCK) where A > GETUTCDATE() 

INSERT Statement:

INSERT INTO [dbo].[E] (myValue,A) VALUES (10,GetDate())

The underlying table dbo.E is holding about 3 million rows in about 20 columns, some of them are ntext.

Taking the queries out and simulating it manually with two transactions, the behaviour is re-producable. The behaviour changes if XLOCK is removed from the select.

Deadlock Graph:

<deadlock-list>
 <deadlock victim="process222222221">
  <process-list>
   <process id="process222222221" taskpriority="0" logused="0" waitresource="KEY: 5:72057604035644444 (ccdf51accc0c)" waittime="2522" ownerId="27202256401" transactionname="SELECT" lasttranstarted="2015-09-14T16:32:36.160" XDES="0x2f1ec5ca0" lockMode="RangeX-X" schedulerid="15" kpid="12936" status="suspended" spid="359" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2015-09-14T16:32:36.160" lastbatchcompleted="2015-09-14T16:32:36.160" clientapp="x" hostname="x" hostpid="14536" loginname="x" isolationlevel="serializable (4)" xactid="27202256401" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="48" sqlhandle="0x02000000611e4523142b2318c47c87313a9b2ba587ff3130">
        SELECT * FROM viewE WITH (XLOCK) WHERE A &lt; GetUtcDate()      </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@UICulture nvarchar(5))SELECT * FROM viewE WITH (XLOCK) WHERE A &lt; GetUtcDate()    </inputbuf>
   </process>
   <process id="process6022222" taskpriority="0" logused="161152" waitresource="KEY: 5:72057604035644444 (cd874c2ba438)" waittime="1370" ownerId="27202248438" transactionguid="0x8de5ccd6eeef67469c6234af59e44ca5" transactionname="DTCXact" lasttranstarted="2015-09-14T16:32:34.767" XDES="0x4aa0bf950" lockMode="RangeI-N" schedulerid="14" kpid="6636" status="suspended" spid="329" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-09-14T16:32:37.300" lastbatchcompleted="2015-09-14T16:32:37.300" clientapp="x" hostname="x" hostpid="14536" loginname="x" isolationlevel="read uncommitted (1)" xactid="27202248438" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="936" sqlhandle="0x020000004853462f09790a4ddedc0d574c2afa539aef1c0e">
     INSERT INTO [E] ([a], [b], [c],...) VALUES (@aDate, @bDate, @c, ...)
     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>INSERT INTO [E] ([a], [b], [c],...) VALUES (@aDate, @bDate, @c, ...)
    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057604035644444" dbid="5" objectname="db.dbo.E" indexname="IX_index1" id="lock258b6dc80" mode="X" associatedObjectId="72057604035644444">
    <owner-list>
     <owner id="process6022222" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process222222221" mode="RangeX-X" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057604035644444" dbid="5" objectname="db.dbo.E" indexname="IX_index1" id="lock7b145c400" mode="RangeX-X" associatedObjectId="72057604035644444">
    <owner-list>
     <owner id="process222222221" mode="RangeX-X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6022222" mode="RangeI-N" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

As far I understand this, I am looking at a KEYLOCK deadlock basically caused by an uncovered index query that uses a nonclustered and a clustered index in order to collect the required values, right?

My questions:

1. I can't create a covering index because of the required NTEXT columns involved. Will drastically reducing the number of rows help here?
2. Is there any good reason I just don't know the SELECT is executed with the XLOCK? Would the deadlock also happen without XLOCK?

Answer 1

As far I understand this, I am looking at a KEYLOCK deadlock basically caused by an uncovered index query that uses a nonclustered and a clustered index in order to collect the required values, right?

Essentially, yes. The read operation (select) accesses the nonclustered index first, then the clustered index (lookup). The write operation (insert) accesses the clustered index first, then the nonclustered index. Accessing the same resources in a different order holding incompatible locks can lead to deadlock.

Will drastically reducing the number of rows help here?

It might, because fewer resources are locked and the operation will tend to complete more quickly. If it does help, it may reduce deadlocks, but most likely not eliminate them (but read on).

Is there any good reason I just don't know the SELECT is executed with the XLOCK?

Not really. Locking hints like this are often introduced by people without a full understanding of how isolation, locking, and deadlocks work, in a desperate attempt to reduce or eliminate a problem.

Would the deadlock also happen without XLOCK?

No, if the select actually runs at read uncommitted isolation because incompatible locks won't be taken (and held) in a different order.

Yes, if a locking isolation level is used, and incompatible locks are taken and held in an inconsistent order, for example shared (S) on the nonclustered, then S on the clustered when reading. How likely a deadlock is in this scenario depends on how many locks are taken, and for how long they are held.

Advice

The thing that really stands out (on review) is that the select transaction is running under serializable isolation. That could be being set by your framework, or due to the use of the DTC (Distributed Transaction Coordinator) - see transactionname="DTCXact" in the deadlock graph. You should look into the reasons for this, and look to change it if possible.

Without this escalation to serializable, the chances are very good that this deadlock would not occur, assuming the XLOCK hint is removed. That said, you would be reading under read uncommitted isolation, which comes with very few consistency guarantees.

If your application and SQL Server code can tolerate reading versions of rows, changing to read committed snapshot isolation (RCSI) or snapshot isolation (SI) for the reads would also avoid the deadlock (XLOCK removed!), while presenting a consistent, point-in-time view of the committed data. This also assumes you can avoid the serializable isolation, of course.

Ultimately, the XLOCK hint is counter-productive, but you really need to look into the reason for the use of the serializable isolation level. The trancount = 2 is also interesting - perhaps you are unintentionally nesting transactions here. Something else to check.

Answer 2

1. Drastically reducing the number of rows will reduce the likelihood of getting the deadlock, but it won't go away completely.

In simple terms the select is first using the index to determine the rows to select, then fetching the rows, while the insert is inserting a row, then trying to update the (XLOCKED) index.

2. Application developers tend to use XLOCK if in the same transaction they want to later do an update on the data. This ensures no one can update the data under them. I would investigate what the application is doing to see if the XLOCK is required.

Having said this, removing the XLOCK probably won't resolve the issue. The SELECT will still take out a shared lock on the index, and the INSERT will want an XLOCK to update it. A shared lock and an XLOCK can't exist on the object together, so you will still get a deadlock. IX_Index1 must be either MyValue or A, or both.

This type of deadlock often occurs because of poorly designed indexes and/or too many indexes. Or poorly written code. Your best option is to see if there is some way the select can be re-written to use another index.