Using TRY/CATCH to Resolve a Deadlock in SQL Server 2005

A deadlock is an inevitable situation in the RDBMS architecture and very common in high-volume OLTP environments. A deadlock situation is when at least two transactions are waiting for each other to complete. The Common Language Runtime (CLR) of .NET lets SQL Server 2005 provide developers with the latest way to deal with error handling. In case of a deadlock, the TRY/CATCH method is powerful enough to handle the exceptions encountered in your code irrespective of how deeply nested the application is in a stored procedure.

This article will acquaint you with how to use TRY/CATCH blocks in your code to handle deadlocks. Exception handling offers a powerful mechanism for controlling complex programs that have many dynamic runtime characteristics. As the article progresses, it includes the syntax for new TRY/CATCH exception handling methods that help improve your code.

While this article focuses on how to use TRY/CATCH blocks to handle deadlocks, it can also be used to deal with many different types of SQL Server exception handling. See the SQL Server 2005 Books Online for more on how TRY/CATCH can be used.

Occurrence of Deadlock and Transaction Handling

We will begin with an example that causes a deadlock in SQL Server 2005. A deadlock is a situation wherein two transactions wait for each other to give up their respective locks.

Let us take an example of a publishing house whose inventory database uses two tables, say "Titles" and "Authors." The Titles table stores the information about the books published, whereas the Authors table stores the list of authors.

In a certain scenario, Transaction1 might lock the rows in the Titles table and need to update some rows in the Authors table to complete. In a similar manner, Transaction2 holds a lock on the same rows in the Titles table, but needs to update the rows held by the Authors table in transaction1. As a result,, neither of the transactions reaches completion as Transaction1 has a lock on the Authors table and Transaction2 has a lock on the Titles table. This brings the ongoing process to a halt. This process resumes only when SQL Server 2005 detects the deadlock and aborts one of the transactions.

Each transaction has an exclusive lock on the inserted data. Consequently, any attempts to read each other's inserted data using a SELECT statement will be blocked and result in a deadlock. This deadlock is then detected by SQL Server's lock manager, which cancels the transaction that caused the halt and rolls back the other transaction releasing its locks to reach completion. The transaction that is generally aborted is the once that has consumed the least amount of resources up to this point in time.

How Using TRY/CATCH Helps to Resolve Deadlocks

This section explains how using a TRY/CATCH block helps to write structured and well-designed deadlock-resolving code, capable of trapping errors that were not uncovered in previous versions of SQL Server.

TRY/CATCH lets you separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.

As per our deadlock example above, SQL Server 2005 returns the value 1205 [Invalid Deal Sequence number in table] as a result of the transaction getting deadlocked with another process. The catch block then catches the 1205 deadlock error and rolls back the transaction until Transaction1 becomes unlocked as shown in the code below. The following code serves as an example of how to release a deadlock.