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

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.