Sql-server – Differences between SQL Server 2005 and SQL Server 2008 deadlock handling

concurrencysql serversql-server-2008

We recently upgraded a server to SQL Server 2008 R2. The server had been running SQL Server 2005. We are using

  • Transactional Replication
  • Management Data Warehouse and Data Collector on the Production server

We are starting to see deadlock errors in the application log. This did not happen in SQL 2005.

On the reporting database I can replicate a deadlock using an "ad-hoc" query with an inner join and table scan on an active table. However, I can run reporting procs that take minutes to complete with no problems.

Is sql 2008 more sensitive to deadlocks to sql 2005? I am going to start undoing some of the changes we have implemented and tested. Before I go that route I wanted to see if anyone else has noticed increased deadlocks when upgrading to 2008 R2.

Best Answer

"Sensitivity to deadlocks" is not version-specific. It is related to concurrency and contention. Please see this BOL reference on more information regarding deadlocking.

There are certain things that influence deadlocking, such as transaction isolation levels, and lock escalation. These are configurable settings that impact if and when deadlocking can/will happen in certain situations.

It will take a bit of investigation on your part to find out why you're seeing more deadlocking happening. But to answer your question, it isn't specific to the version of SQL Server.

As for the deadlock process, when SQL Server runs into a deadlock it will choose a victim so that the unresolvable concurrency situation can complete.

Related Question