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

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

By : Brad McGehee Nov 18, 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.

 

BEGIN TRANSACTION
BEGIN TRY
INSERT Title VALUES (@Title_ID, Title_Name, ' ', ' ', ' ', ' ', 1112, 0)
WAITFOR DELAY '00:00:05'
SELECT COUNT (*) FROM Authors
COMMIT
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ER_Num
ROLLBACK
END CATCH;
SELECT @@TRANCOUNT AS '@@TCount

Run these snippets of code simultaneously in two Management Studio windows connected to SQL Server 2005, and make sure to delete the data that would prevent the inserts in the "Titles" table. Once done, both windows will return an @@TCOUNT level of 0. There will still be a deadlock, but this time TRY/CATCH will trap it. The victim's transaction will no longer be aborted and you can see the error in the output of the deadlock victim.

ER_Num
--------------------
1205

@@TCOUNT
---------------------
0

This example indicates the power that TRY/CATCH offers us, as transactions no longer get aborted, and deadlock errors are trapped using the catch blocks. For deadlock victims, error 1205 puts the code into the catch block where it can be explored with new error handling functions.

Notice that in the TRY/CATCH block code given above, the error functions are used to capture error information. The code also contains ROLLBACK. The reason is that though the error has been trapped, it leaves the transaction at a standstill. Therefore, it is your responsibility to get it rolled back within the TRY/CATCH block. This will let you continue with the transaction from the beginning.

If no errors are encountered until the last statement of the TRY block code, control jumps to the statement immediately after the associated END CATCH statement. If an error is encountered within the TRY block, control passes to the first statement in the respective catch block. If the END CATCH statement is last in a stored procedure, control jumps back to the statements that invoked the stored procedure.

Use the following system functions to acquire information about errors within the CATCH block. These functions, if called outside the CATCH block, will return NULL.

• ERROR_NUMBER() returns the number of the error.
• ERROR_SEVERITY() returns the severity.
• ERROR_STATE() returns the error state number.
• ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
• ERROR_LINE() returns the line number inside the routine that caused the error.
• ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names or times.

Conclusion

We have seen how a deadlock situation arises when two transactions are processed simultaneously. We have incorporated the use of the TRY/CATCH block, which helps to resolve the situation by releasing the locks that block the transactions so that you are able to retrieve the data you want without any halts or delays. The examples above show that a TRY/CATCH block lends itself to writing structured and well-designed deadlock-resolving code capable of trapping errors that were not uncovered before. In addition to this, you can even investigate the error information enclosed within the CATCH block by calling the functions that show you the ID, message text, state, severity and transaction state of an error. Therefore, SQL Server 2005 gives you a robust means to resolve deadlocks using T-SQL.