SQL Server 2000 Deadlock

Question

We are experiencing some very annoying deadlock situations in a production SQL Server 2000 database.

The main setup is the following:

• SQL Server 2000 Enterprise Edition.
• Server is coded in C++ using ATL OLE Database.
• All database objects are being accessed trough stored procedures.
• All UPDATE/INSERT stored procedures wrap their internal operations in a BEGIN TRANS ... COMMIT TRANS block.

I collected some initial traces with SQL Profiler following several articles on the Internet like this one (ignore it is referring to SQL Server 2005 tools, the same principles apply). From the traces it appears to be a deadlock between two UPDATE queries.

We have taken some measures that may have reduced the likelihood of the problem from happening as:

• SELECT WITH (NOLOCK). We have changed all the SELECT queries in the stored procedures to use WITH (NOLOCK). We understand the implications of having dirty reads but the data being queried is not that important since we do a lot of automatic refreshes and under normal conditions the UI will have the right values.
• READ UNCOMMITTED. We have changed the transaction isolation level on the server code to be READ UNCOMMITED.
• Reduced transaction scope. We have reduced the time a transaction is being held in order to minimize the probabilities of a database deadlock to take place.

We are also questioning the fact that we have a transaction inside the majority of the stored procedures (BEGIN TRANS ... COMMIT TRANS block). In this situation my guess is that the transaction isolation level is SERIALIZABLE, right? And what about if we also have a transaction isolation level specified in the source code that calls the stored procedure, which one applies?

This is a processing intensive application and we are hitting the database a lot for reads (bigger percentage) and some writes.

If this were a SQL Server 2005 database I could go with Geoff Dalgas answer on an deadlock issue concerning Stack Overflow, if that is even applicable for the issue I am running into. But upgrading to SQL Server 2005 is not, at the present time, a viable option.

As these initial attempts failed my question is: How would you go from here? What steps would you take to reduce or even avoid the deadlock from happening, or what commands/tools should I use to better expose the problem?

Answer 1

My guess would be that you are experiencing deadlocks, either:

1. Because your DML(Updates probably) statements are getting escalations to table-locks, or
2. Different stored procedures are accessing the same tables in transactions but in a different order.

To address this, I would first examine the stored procedures, and make sure the the modifications statements have the indexes that they need.

Note: this applies to both the target tables and the source tables (despite NOLOCK, an UPDATE's source tables will get locks also. Check the query plans for scans on user stored procedures. Unlike batch or bulk operations, most user queries & DMLs work on a small subsets of the table rows and so should not be locking the entire table.

Then secondly, I would check the stored procedures to ensure that all data access in a stored procedure is being done in a consistent order (Parent -> Child is usually preferred).

Answer 2

A few comments:

1. The isolation level explicitly specified in your stored procedure overrides isolatlation level of the caller.

2. If sp_getapplock is available on 2000, I'd use it:

   http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx

3. In many cases serializable isolation level increases the chance you get a deadlock.

4. A good resource for 2000:

   http://www.code-magazine.com/article.aspx?quickid=0309101&page=1

Also some of Bart Duncan's advice might be applicable:

http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx

Answer 3

The reason for the deadlocks in my setup scenario was after all the indexes. We were using (generated by default) non clustered indexes for the primary keys of the tables. Changing to clustered indexes fixed the problem.

Answer 4

In addition to Alex's answer:

• Eyeball the code to see if tables are being accessed in the same order. We did this recently and reordered code to alway to parent then child. The system had grown, code and features were more complex, more user: we simply started getting deadlocks.

- See if transactions can be shortened (eg start later, finish earlier, less processing)

• Identify which code you'd like not to fail and use SET DEADLOCK PRIORITY LOW in the other We've used this (SQL 2005 has more options here) to make sure that some code will never be deadlocked and sacrificed other code.

• If you have SELECT at the start of the transaction to prepare some stuff, consider HOLDLOCK (maybe UPDLOCK) to keep this locked for the duration. We use this occasionally so stop writes on this table by other processes.