Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 29 '13 at 15:34

This question came from our site for professional and enthusiast programmers.

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... –  Martin Smith Jan 29 '13 at 22:39
    
Ok, fixed up the question to indicate that the 2nd Index is a non-clustered index –  Peter PitLock Jan 30 '13 at 6:34
    
what isolation level are these running in? –  StrayCatDBA Jan 30 '13 at 7:12

2 Answers 2

up vote 3 down vote accepted

usually deadlock happens when:

  1. Query 1 tries to do an update - but before updating it needs to d 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 set a READ lock (S - shared lock) on some of these pages.
  3. Query 1 no tries to do an update - so it tries to change READ lock to and 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 are not 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.

share|improve this answer

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 effects all the rows on the page. So in this case both pages that you are touching are on the same page.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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