Search code examples
sql-serversql-server-2000deadlock

SQL Server 2000 Deadlock

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?

Solution

  • 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.