Given two known statements, how can I replicate deadlock?

Question

I have two statements (an update against every row in one table (call it table A) and a delete on another table that looks up rows in table A) that I know are causing occasional deadlocks. It seems that there is an X lock and a U lock on the same primary key index of table A.

I have been trying, and failing, to replicate the deadlock in SQL Server Management Studio. Should I be able to?

Separately, the delete statement is very inefficient and I think I can fix the issue by creating a covering index that means that the primary key index mentioned above is no longer included in the actual execution plan of the delete statement. Given that ultimately the same rows are required by both statements will this guarantee no deadlocks or simply reduce the chance of it happening by giving SQL Server a different path to the data?

I finally managed to get a deadlock graph as shown below:

If I am reading this correctly, the process node on the right has an exclusive lock on PK_LoanFacility and is requesting another one (maybe this is because there are two updates in the statement associated with this process node. The first sets a field to NULL for all rows and the second then updates a subset with values pulled from another database). The process node on the left has an update lock on PK_LoanFacility and is requesting a shared lock. The statement on the left is deleting a single row from a child table and is finding the parent id via a WHERE clause. i.e. DELETE FROM table where ForeignID = (SELECT ID FROM ParentTable WHERE x = y). I am unsure as to why this would require an update lock on PK_LoanFacility (the PK of the parent table). I am guessing that whenever you delete a row from a table, all the foreign keys are inspected and updates to the primary key indexes of all parent tables are made..?

As previously mentioned, I am fairly sure that I can eliminate the need for the requested shared lock by adding some proper indexes, but I am still interested to understand what is happening.

Answer 1

Nothing can prevent deadlocks in a RDBMS. However, you can greatly reduce the chance of the occurring by following a few simple rules:

1. Make sure your queries are tuned well, using appropriate indexes.
2. If you have to take concurrent locks on multiple tables, all your statements and procedures should access those tables in the same order.
3. Keep your transactions as short as possible.

That will make it a lot less likely for you to encounter deadlocks. However, you cannot completely avoid them, so your code should be resilient and just automatically wait a random time interval (a few milliseconds) and then try again. Only if after three or so retries you still cant get the statement executed log the error.

Now to your question, of how to reproduce the deadlock: There are several types of deadlocks. The simplest one is the deadlock on two resources:

1. connection A takes lock on resource 1
2. connection B takes lock on resource 2
3. connection A tries to take conflicting lock on resource 2 and has to wait
4. connection B tries to take conflicting lock on resource 1 and has to wait

Now both connections wait for each other causing a deadlock. This type of deadlock can be fairly easily reproduced by stepping through the code one statement at a time on two connections. Other deadlocks are not always as easy to reproduce because e.g. they might only occur on memory pressure situations or similar.

The easiest way to get more information about deadlocks is, to set up a deadlock-graph trace. The deadlock graph contains a lot of information that can be helpful understanding the captured deadlock. As your system (hopefully) has deadlock only relatively infrequent, you might have to run the trace for a while. But if you are capturing only that one event, it won't use a lot of resources. Once you captured the deadlock graph look at it as XML. The graphical representation given by the profiler does reveal only a limited amount of information, but it is a good starting point.

Answer 2

As Sebastian said there is no way to completely eliminate deadlocks in SQL Server. The best you can do is reduce your changes, usually by shortening your transactions.

You can probably reproduce your deadlock by putting transactions around each statement.

1. In connection 1 BEGIN TRANSACTION
2. In connection 1 run your delete script
3. In connection 2 BEGIN TRANSACTION
4. In connection 2 run your other script

If that order doesn't work try the other way. The point here is to open the transactions but not close them. This forces SQL to keep the locks open.

You can also take a look at historical deadlocks by viewing the deadlock graph (again as Sebastian already said). This article "Retrieving Deadlock Graphs with SQL Server 2008 Extended Events" by Jonathan Kehayias gives some fairly straightforward instructions on how to find and view the deadlock graph.