Are Deadlocks becoming a thing of the past?
Unless I missed it, I didn’t see his example
There was a blog post [1] awhile back by super-stealth DBA dude Brad McGehee that described the notion of using the new Try/Catch syntax for handling deadlocks. Great idea and good blog posting! The only problem that I found, however, was that he never gave an example that actually showed the benefit of using such. So I thought that I’d go ahead and create a scenario that shows a deadlock, and then use the feature that he mentions to handle one. After I wrote mine, I peeked in Books Online (BOL) to find that they did a somewhat similar example, and per usual, I liked mine better than theirs; easier, simpler, and more straightforward IMHO, so I hope you find same. Let’s look at the code.
Set up the tables and data
Run the following in SQL Server Management Studio (SSMS)
DROP TABLE ##test1, ##test2
go
CREATE TABLE ##test1 (id int PRIMARY KEY CLUSTERED)
GO
CREATE TABLE ##test2 (id int PRIMARY KEY CLUSTERED)
GO
INSERT INTO ##test1 VALUES (1)
INSERT INTO ##test2 VALUES (1)
GO
Instructions next up. Please follow carefully or suffer severe flogging
Here are the very simple and easy-to-understand instructions for all of the below code examples. Please read them – the order of execution is important, and if you don’t execute them in this order using the particular window as directed, the demos won’t work for you. When doing our own testing, either commit or rollback transactions often, or the code also won’t work.
- Run SCRIPT 1 in an SSMS window
- Run SCRIPT 2 in another SSMS window
- Run SCRIPT 3 back in the window that you ran in step (1)
Lunch is served at 1205 today
The example, a variation from WardyIT’s blog [2], is an example of a “Hold and Wait” type of deadlock, where one process is waiting on another while that process attempts to gain access to a held process. Run example 1 (using the instructions given) and you should get a 1205 deadlock error. Remember – Script 1-> window 1, script 2 –> window 2, script 3 –> window 1. Awesome! Great job.
--EXAMPLE 1
--SCRIPT 1 in window 1
BEGIN TRAN
UPDATE ##test1 SET id = 1
--SCRIPT 2 in window 2
BEGIN TRAN
UPDATE ##test2 SET id = 2
UPDATE ##test1 SET id = 2
--SCRIPT 3 in window 1
BEGIN TRAN
UPDATE ##test2 SET id = 1
Msg 1205, Level 13, State 51, Line 3
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
TRY/CATCH is catching!
Thank goodness for TRY/CATCH in TSQL; we now have a facility to handle errors thoroughly. Run Example 2 now in order to see that this logic does not work with the old syntax; I have deliberately commented the code to simple BEGIN/END blocks. You’ll notice that SQL Server punts when it receives an error, and what is happening is that the constructs cannot trap errors and essentially no exception handling is occurring.
--EXAMPLE 2
--SCRIPT 1
BEGIN TRAN
UPDATE ##test1 SET id = 1
--SCRIPT 2
DECLARE @err int
BEGIN TRAN
Retry:
IF (@@TRANCOUNT=0)
BEGIN TRAN
BEGIN --TRY
UPDATE ##test2
SET id = 2
UPDATE ##test1
SET id = 2
END --TRY
BEGIN --CATCH
SET @err = @@ERROR
IF (@err = 1205)
BEGIN
PRINT 'RETRY OCCURRED!'
ROLLBACK
SET @err = 0
GOTO Retry
END
END --CATCH
COMMIT
--SCRIPT 3
DECLARE @err int
BEGIN TRAN
Retry:
IF (@@TRANCOUNT=0)
BEGIN TRAN
BEGIN --TRY
UPDATE ##test2
SET id = 1
END --TRY
BEGIN --CATCH
SET @err = @@ERROR
IF (@err = 1205)
BEGIN
PRINT 'RETRY OCCURRED!'
ROLLBACK
SET @err = 0
GOTO Retry
END
END --CATCH
COMMIT
Deadlocks – a dying breed
Run example three now and check out the results. Notice that I use a GOTO. If you don’t like one, don’t use one. For me, though, this made sense. You’ll see that BOL created a cheesy loop for their transactions…think I’ll stay clear of all that, plus mine is shorter anyway. BOL also says that “GOTO statements cannot be used to enter a TRY or CATCH block”, but I noticed that it didn’t say a darn thing about exiting them, which is exactly what we do here.
--EXAMPLE 3
--SCRIPT 1
BEGIN TRAN
UPDATE ##test1 SET id = 1
--SCRIPT 2
DECLARE @err int
BEGIN TRAN
Retry:
IF (@@TRANCOUNT=0)
BEGIN TRAN
BEGIN TRY
UPDATE ##test2
SET id = 2
UPDATE ##test1
SET id = 2
END TRY
BEGIN CATCH
SET @err = ERROR_NUMBER()
IF (@err = 1205)
BEGIN
PRINT 'RETRY OCCURRED!'
ROLLBACK
SET @err = 0
GOTO Retry
END
END CATCH
COMMIT
--SCRIPT 3
DECLARE @err int
BEGIN TRAN
Retry:
IF (@@TRANCOUNT=0)
BEGIN TRAN
BEGIN TRY
UPDATE ##test2
SET id = 1
END TRY
BEGIN CATCH
SET @err = ERROR_NUMBER()
IF (@err = 1205)
BEGIN
PRINT 'RETRY OCCURRED!'
ROLLBACK
SET @err = 0
GOTO Retry
END
END CATCH
COMMIT
Conclusion. Did you catch all of this, there buddy, or did you even try?
Deadlocks can be handled a number of different ways. There are four different types of deadlocks, and we looked at and resolved a common “Hold and Wait” type in this blog posting. Here I showed a code snippet that created a deadlock, and then another one that took the same code and solved the deadlock problem using a retry label for a GOTO statement and a TRY/CATCH block to handle the error. In previous versions of SQL Server you couldn’t “trap” the error – SQL Server simply raised an internal error before your code could kick in, and one or more of the threads became a deadlock victim. The beauty of TRY/CATCH is that these constructs serve as a true mechanism for error handling, and allow the SQL dev to create robust code for trapping and handling errors.
What I did here was a “proof of concept” in tech speak – I am not at a client who is at this point suffering from deadlocks, so I cannot fully test this code. However, give this concept a shot if you’re dying from the dreaded deadlock; some variation of this might help you out.
Lee Everest
----------------------------
No, deadlocks are not a popular hairstyle of many NFL and NCAA football players.
[1]. McGehee, B. (2005). Using TRY/CATCH to Resolve a Deadlock in SQL Server 2005. Retrieved on 9/22/2009 from http://www.sql-server-performance.com/articles/per/deadlock_sql_2005_p1.aspx
[2]. WardyIT (2005). Creating a deadlock. Retrieved on 9/22/09 from http://wardyit.com/blog//blog/archive/2005/12/12/65.aspx
5d3c90a0-3fb8-4eb5-bc54-0cb8729c7c45|0|.0