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

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

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

2. A good resource for 2000:

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

(somehow my 1 2 3 4 become 1 2 1 2 ;))

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

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

Answer 2

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.

Answer 3

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 sProcs are accessing the same tables in transactions but in a different order.

To address this, I would first examine the sProcs, and make sure the the modifiations statments have the indexes that they need. note: this applies to bothe 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 sprocs. Unlike batch or bulk operations, most user querirs & DMLs worl on small subsets of the tbale rows and so should not be locking the entire table,

Then secondly, I would schek the sprocs to insire that all data acess in a sproc is being donfe in a consistent order (Partet -> Child is usually preferred).

Answer 4

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.