0

In our application we are seeing a deadlock between two stored procedures where one does a Select and the other is doing a delete. The piece that makes this interesting is that they appear to be locking the same clustered index somehow. There is not a non-clustered index involved so it doesn't appear to be a "bookmark loookup deadlock". The Select appears to have a shared lock while the Delete has an Exclusive lock which seems impossible on the same index. How is this possible? Below is the SQL Profiler Deadlock graph:

enter image description here

DeviantSpark
  • 80
  • 1
  • 9
  • 1
    The locks will be for individual rows (unless the X lock escalates to a table lock). Presumably the two queries are processing the rows in different orders, so that the delete takes an X on row 1 while the select takes an s on row 10. Then the delete tries for an X on row 10 and starts waiting for the S to clear. While that's happening the select tries to take an S on row 1 and boom - deadlock. – simon at rcl Apr 06 '16 at 18:00
  • Interesting, that would make sense. This seems like it would be a fairly common scenario, is there a typical resolution you know of? I saw one option is to use NOLOCK on the Select to avoid taking out a shared lock. – DeviantSpark Apr 06 '16 at 19:32
  • 1
    NOLOCK is quite often a bad idea. It would prevent this problem (it would be worth using it to check that the problem doesn't happen!) but it does mean that the select would get possibly inconistent reads - i.e. uncommitted data included with already committed data. This is not always a problem - you alone can decide whether it is.A second solution is to try to make the queries access the data in the same order. For this you need to look at the query plans and change the queries so that they use the same indexes in the same ways. – simon at rcl Apr 06 '16 at 20:17
  • PS Have a look at this: http://stackoverflow.com/questions/20047/diagnosing-deadlocks-in-sql-server-2005?rq=1 Ironically, it's a problem on Stack Exchange! The accepted answer could help. – simon at rcl Apr 06 '16 at 20:37
  • Thanks for your assistance. In our tests we found that the delete transaction was doing up to 100 deletes so we changed the application code to do only 1 delete per transaction and the deadlock doesn't occur anymore that we can see. – DeviantSpark Apr 11 '16 at 19:49

0 Answers0