3

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.

8
  • 1
    Can you just run your select under snapshot isolation, so that it does not embrace in deadlocks ever?
    – A-K
    Jan 29 '13 at 16:51
  • Please edit the question to include the query plans and the deadlock graph XML.
    – Jon Seigel
    Jan 29 '13 at 18:03
  • And the CREATE TABLE statement including indexes. You can't have two clustered indexes... Jan 29 '13 at 22:39
  • Ok, fixed up the question to indicate that the 2nd Index is a non-clustered index Jan 30 '13 at 6:34
  • what isolation level are these running in? Jan 30 '13 at 7:12
4

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.

1

In this case yes. The first lock is a keylock so it's basically on the row. The second lock is a page lock, so it affects all the rows on the page. So in this case both pages that you are touching are on the same page.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.