Resolve SQL server2005 deadlock with Try/catch

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 deadlock situations, [see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_3xrf.asp and subsequent articles in the deadlock story tree.] -Editors, but the most interesting and subtle are the deadlocks that are blocking the reader and the writer. 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 (one), @au_lname varchar
SELECT @au_id = ' 111-11-1111 ', @au_lname = ' t Est1 '

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

--Window 2 header
DECLARE @au_id varchar (one), @au_lname varchar
SELECT @au_id = ' 111-11-1112 ', @au_lname = ' t Est2 '

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:

Delete from authors where au_id = ' 111-11-1111 '
deletes from authors where au_id = ' 111-11-1112 '

Both Windows 1 and window 2 are executed within 5 seconds. Because each window waits for at least 5 seconds to issue a SELECT statement, all of the connections complete the insert operation, which ensures that the insert operation in two Windows is complete before the individual SELECT statement is published. The SELECT statement in each window attempts to read all the data in the authors table, looking for data similar to the "test%" format in the Au_lname field value. Therefore, the SELECT statements in the two windows will attempt to read the inserted data in their connection-and also read the insertion data from the other connection.

The Read Committed isolation level ensures that the SELECT statement never reads the uncommitted data by publishing the shared lock. For the same resource, the shared lock is incompatible with the exclusive lock, and the requestor must wait for the exclusive lock release before publishing the shared lock. Each connection has an exclusive lock on the data that is inserted, so a SELECT statement that attempts to read each other's inserted data will attempt to unlock the shared lock that was inserted into the data, but it will be blocked. Two connections will block each other, thereby forming a deadlock. When the lock manager for SQL Server detects a deadlock, one of the batches is aborted, its transaction is rolled back, and its blocking locks are freed so that other transactions can be completed. The transaction that is the deadlock victim is rolled back, and the other transactions are completed successfully.