SQL Server 2000

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

SQL Essentials: Using TRY/CATCH to Resolve Deadlocks in SQL Server 2005

Ron Talmage

Deadlocks are unavoidable artifacts of today's RDBMS architecture–and all too common in high-volume OLTP environments. But thanks to .NET's Common Language Runtime (CLR), SQL Server 2005 provides developers with a new way to deal with error handling. In this month's column, Ron Talmage shows you how to harness TRY/CATCH to help resolve a deadlock.

T-SQL is great at delivering the message, but not so good at providing us with tools to intercept the error. I suspect that almost all DBAs are familiar with the 1205 "deadlock victim" error message:

  Transaction (Process ID 52) was deadlocked on lock 
resources with another process and has been chosen as 
the deadlock victim. Rerun the transaction.

	When a deadlock occurs in your code, it doesn't matter how deeply nested the application is in stored procedures; the victim spid of the deadlock will have its batch aborted and error 1205 will be returned to the client. Despite the error message encouraging you to retry the transaction, you can't do it within T-SQL code; the retry must be done from the calling application. The error simply isn't trappable, and @@ERROR is useless. It's not a happy situation.

A sample deadlock
Let's start with an example that will cause a deadlock in both SQL Server 2000 and 2005. For this article, I've been using the latest CTP (Community Technology Preview) of SQL Server 2005, but SQL Server 2005 Beta 2 (released last July) will do just as well. If you don't have access to either Beta 2 or one of the recent CTPs, you can download the latest version of SQL Server 2005 Express and run the experiment with it.

	There are many kinds of deadlocks possible [see https://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_3xrf.asp and subsequent articles in the deadlocking article tree–Ed.], but the most interesting and subtle ones involve cases where readers and writers block each other. The following code shows one such deadlock in the pubs database. (You can run this code side-by-side in two Query Analyzer windows in SQL Server 2000 or in two Management Studio queries in SQL Server 2005.) In one window, prefix the body of the code with the following:

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

	In a second window with a second connection, use the following:

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

	Then in both windows, use the following as the body of the code:

  -- 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

	Make sure that the authors table doesn't already have any data with those ids by running the following from a third window:

  DELETE FROM authors WHERE au_id = '111-11-1111'
DELETE FROM authors WHERE au_id = '111-11-1112'

	Now run both Window 1 and Window 2 within five seconds of each other. Each connection will finish its INSERT and, because each waits five seconds before issuing its SELECT statement, you're guaranteed that both INSERTs will have completed before the SELECTs are issued. Each SELECT statement attempts to read all data in the authors table that has an au_lname value that is LIKE 'Test%'. Therefore, each SELECT statement will try to read its own connection's inserted data–as well as the other connection's inserted data.

	The READ COMMITTED isolation level guarantees that the SELECT statements will never read uncommitted data, by issuing shared locks. Shared locks aren't compatible with exclusive locks on the same resource, and the requestor must wait until the exclusive locks are released before the shared locks can be issued. Each connection already has an exclusive lock on the inserted data, so the SELECT statements that attempt to read each other's inserted data will attempt to take out shared locks on it and will be blocked. Both connections mutually block each other, forming a deadlock. SQL Server's lock manager detects the deadlock, aborts one of the batches, and rolls back its transaction, releasing its blocking locks so that the other transaction may complete. The deadlock victim's transaction will be rolled back, and the other transaction will succeed.

How TRY/CATCH helps
Now let's modify the body of the code and use TRY/CATCH. (For this example, you'll need to run the code in a version of SQL Server 2005.) When you use TRY/CATCH, you separate your action code from error handling code. You put code for an action in a TRY block and place your error handling code in the CATCH block. If the code in the TRY block fails, the code execution will jump to the CATCH block. (This works for almost all errors except those that keep the entire batch from running, such as missing objects.)

	Here's an example of TRY/CATCH using the same code from before. Use the same headers, but change the body of the code:

  BEGIN TRANSACTION
BEGIN TRY
  INSERT Authors VALUES 
  (@au_id, @au_lname, '', '', '', '', '', '11111', 0)
  WAITFOR DELAY '00:00:05'
  SELECT COUNT(*)  FROM Authors 
  COMMIT
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrorNumber
  ROLLBACK
END CATCH;
SELECT @@TRANCOUNT AS '@@Trancount'

	Now, run these in side-by-side windows connected to SQL Server 2005, making sure you deleted any data in the authors table that would prevent the inserts; you can use the preceding DELETE statements.

	Both windows will return an @@TRANCOUNT level of 0. A deadlock will still occur, but now TRY/CATCH has trapped it. The victim's batch is no longer aborted, and you can see the error in the output of the deadlock victim:

  ErrorNumber
-----------
1205

@@Trancount
-----------
0

	You should now be seeing the power that TRY/CATCH gives you. Because even deadlock errors can be trapped in the CATCH block, the batch is no longer aborted and T-SQL code can continue to work. For the deadlock victim, the deadlock error 1205 puts the code into the CATCH block–where you can explore it with new error handling functions. The preceding code only uses ERROR_NUMBER() in place of @@ERROR, but you can also use ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_SEVERITY(), and ERROR_STATE(). These functions are self-explanatory, but they still give us much more than we used to have.

	Notice that the preceding CATCH block code contains a ROLLBACK. The reason is that even though the deadlock error is trapped, the transaction isn't rolled back. The transaction is still doomed, but now it's your responsibility to roll it back within the TRY/CATCH. So what's different? Although you can't continue the transaction, you can retry it!

Retries in TRY/CATCH
The dismaying aspect of error 1205 in SQL Server 2000's T-SQL is the suggestion, "Rerun the transaction." The problem is, you can't–at least not in SQL Server 2000's T-SQL. But because SQL Server 2005's TRY/CATCH gives us the means of trapping the deadlock error, it's now possible to retry the transaction.

	The following code body shows one way of executing a retry. Use it again with the same headers shown earlier:

  DECLARE @Tries tinyint
SET @Tries = 1
WHILE @Tries <= 3
BEGIN
  BEGIN TRANSACTION
  BEGIN TRY
    INSERT Authors VALUES 
      (@au_id, @au_lname, '', '', '', '', '', 
'11111', 0)
    WAITFOR DELAY '00:00:05'
    SELECT * FROM authors WHERE au_lname LIKE 'Test%'
    COMMIT
    BREAK
  END TRY
  BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
    ROLLBACK
    SET @Tries = @Tries + 1
    CONTINUE
  END CATCH;
END

&#9;What this code does is add a retry using a WHILE loop. I set the number of retries to three, but that's configurable. The bottom line is that now we have a way to retry a deadlock victim's code from within T-SQL–something we've never been able to do before.

&#9;Notice, though, that the entire transaction is within the WHILE loop–not outside it. So within each loop, as it runs, not only does the transaction start, but it also ends–either with a COMMIT if the TRY block executes to completion or with a ROLLBACK if the CATCH block executes. The TRY block ends with a BREAK command to exit the WHILE loop if the TRY is successful. The CATCH block increments the retries counter and ends with a CONTINUE command that will re-execute the WHILE loop. You have code that does, in fact, retry the transaction–just like error 1205 tells us to do. But now the retry is done completely within T-SQL.

&#9;SQL Server 2005 has other methods of helping resolve deadlocks, such as the SNAPSHOT ISOLATION level and the new option for READ COMMITTED, called READ COMMITTED SNAPSHOT. However, just the fact that with SQL Server 2005 you can now code your transactions and trap even deadlock errors (and retry them) means that you have a considerably more powerful tool at your disposal.

Download Click the Download button for the code (505RON.SQL)

To find out more about SQL Server Professsional and Pinnacle Publishing, visit their Web site at http://www.pinpub.com/

Note: This is not a Microsoft Corporation Web site. Microsoft is not responsible for its content.

This article is reproduced from the May 2005 issue of SQL Server Professsional. Copyright 2005, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professsional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.