Sql-server – Can 2 tables lock each other if concurrent read/update do not affect same rows? pagelocks off

deadlocksql serversql-server-2005

Can 2 tables cause a deadlock, if the pagelocks are off, and the rows are not related.
E.g.

Query 1 runs for a few seconds

Select * from Orders where CustomerID = 1 and DateOrdered between @D1 and @D2

Query 2 starts to run, and ends, before Query 1 is finished, but with a different customerID

Update Orders set Quantity = Quantity + 10 where OrderID = 20 and CustomerID = 2

(These are just examples to prove a point please)

Now there is a PK_Orders which is a clustered index on the OrderID.
There is also a separate index (ix_Order_Customer) which is a non-clustered index, and contains CustomerID as one of the fields to index. This index has pagelocks enabled.

What I don't understand is how SQL (with traceID 1222) showed me in SQL Server logging that the PK_Orders caused a deadlock when the query which was executed did not even affect the same OrderIDs which were updated.

Extract from Log File Viewer:

 keylock hobtid=720229632 dbid=5 objectname=myDB.dbo.Orders
 indexname=PK_Orders id=lock1b0b23c0 mode=X associatedObjectId=720229632

and

 pagelock fileid=1 pageid=195848 dbid=5 objectname=myDB.dbo.Orders id=lock24442a40
 mode=S associatedObjectId=720229274

Can someone help explain how this is possible please, should the Deadlock not only kick in if there were intersecting rows here? I copied the 2 queries and ran them at the same time, yet I cannot recreate the error and in SQL Server Management Studio this error does not occur – the update goes through.

The error in SQL Event Log Viewer specifically says that PK_Orders is the cause here.

Best Answer

Usually deadlock happens when:

  1. Query 1 tries to do an update - but before updating it needs to do a select. So first it applies READ lock (S - shared lock). Let's say it managed to lock a couple of pages.
  2. Query 2 tries to do a select and sets a READ lock (S - shared lock) on some of these pages.
  3. Query 1 now tries to do an update - so it tries to change READ lock to a WRITE lock (i.e. S (shared) to an X (exclusive) lock). It manages to do it on certain pages but can't do it on all of them since some of the pages are locked by Query 2.
  4. Query 2 in turn tries to obtain more pages - but they now have a X lock (WRITE lock).

In the end both queries are waiting for each other so SQL Server decides to kill one query with DEADLOCK exception.

By the way - issue is not Primary Key Index here.

Related Question