Resolving SQL Server 2005 deadlock using the Try/catch statement

This article sample source code or material download

Deadlocks are hard to avoid for today's RDBMS architectures-and are most prevalent in high-volume OLTP environments. It is because of the presence of the. NET Common Language Runtime (CLR) that SQL Server 2005 can provide developers with a new method of error handling. In this month's column, Ron Talmage describes how to use the Try/catch statement to solve a deadlock problem.

One example deadlock

Let's start with an example of this, which can cause deadlocks in SQL Server 2000 and 2005. In this article, I use the latest CTP (Community Technology Preview, Community Technology Preview) version of SQL Server 2005, and the same applies to SQL Server, Beta 2 (released in July). If you do not have a Beta 2 or the latest CTP version, download the latest version of SQL Server Express, and use it to experiment.

There are a number of possible deadlocks, but the most interesting and subtle are the deadlocks that are blocking each other from readers and writers. The following code produces such a deadlock in the pubs database. (You can run this code in the two Query Analyzer windows of SQL Server 2000 or in two Management Studio queries in SQL Server 2005.) Add the following statement before the body of the code in one of the windows:

-- Window 1 header
DECLARE @au_id varchar(11), @au_lname varchar(40)
SELECT @au_id = '111-11-1111', @au_lname = 'test1'

Add the following statement to the second window for a second connection:

-- Window 2 header
DECLARE @au_id varchar(11), @au_lname varchar(40)
SELECT @au_id = '111-11-1112', @au_lname = 'test2'

Use the following statements as the body of code in two windows:

-- Body for both connections:
BEGIN TRANSACTION
INSERT Authors VALUES
　(@au_id, @au_lname, '', '', '', '', '', '11111', 0)
WAITFOR DELAY '00:00:05'
SELECT *
　FROM authors
　WHERE au_lname LIKE 'Test%'
COMMIT

Run the following statement in the third window to ensure that there are no data in the authors table that contains the following IDs: