CAN YOU BELIEVE THAT HOT MESS IN YESTERDAY’S POST?!?
I know, right? You thought that by combining try/catch with a transaction, you’d get robust error handling. Instead, you ended up with half the tables populated, and a leftover open transaction. You’d already forgotten Tuesday’s post in which I pointed out that TRY/CATCH doesn’t catch low severity or high severity errors.
TRY/CATCH and transactions are turkeys.
And two turkeys don’t make an eagle.
For a better experience, you need the Turkey Sandwich approach to transaction and error handling. You have to surround those turkeys with two key pieces of code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE OR ALTER PROC dbo.Turkey_Sandwich AS BEGIN SET XACT_ABORT ON; /* THIS IS NEW */ BEGIN TRY BEGIN TRANSACTION; INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE()); WAITFOR DELAY '00:00:30'; INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE()); COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK; END CATCH IF @@TRANCOUNT > 0 ROLLBACK; /* THIS IS NEW */ END GO |
We start with the XACT_ABORT option which increases our odds that entire transactions are rolled back or forwards together. However, that alone still may not be enough – remember Tuesday’s post where I talked about how some catastrophic errors skip the CATCH block altogether. We have to finish our turkey sandwich with an additional line to check to see if any transactions still remain, and if so, roll them back.
We’ll do yet another round of clearing the tables, running our proc, waiting a few seconds, canceling it, and then checking the table contents:
|
1 2 3 4 5 6 7 8 9 10 |
TRUNCATE TABLE dbo.Parent; TRUNCATE TABLE dbo.Child; GO EXEC dbo.Turkey_Sandwich; /* Let that run for a few seconds, then cancel it. Then: */ SELECT * FROM dbo.Parent; SELECT * FROM dbo.Child; GO |
FINALLY, we achieve our goal: when the proc is canceled or fails, neither of our tables have any rows in them.
You are not a chef.
You just made your first Turkey Sandwich.
This is not the end of your culinary journey. You’ve merely learned that TRY/CATCH and transactions are turkeys, and you have to make a turkey sandwich by framing them with additional options and code. There is way more work involved.
The next step in your learning journey is to read Erland Sommarskog’s Error and Transaction Handling in SQL Server: Part 1, and then Part 2, and then Part 3. My blog posts this week have not even covered everything in Part 1! My job this week was really just to challenge what you thought you knew about error and transaction handling, and convince you that you need to dig into Erland’s posts.
Also, put down that jar of NOLOCK. That’s not mayonnaise.
29 Comments. Leave new
Seems that the link for ‘TRY/CATCH doesn’t catch low severity or high severity errors.’ is broken. Btw. Great stuff again. Will you also cover nested stored procedures? I believe that you could save the state of a transaction in some way to fix issues with that.
I’m not covering those in this series, but make sure to read the more info links that I put in the post.
I was actually right about XACT_ABORT?
Mirabile dictu.
Awwww yeah!
I appreciate the series. I’d like to propose an alternate title: “You keep using [TRY/CATCH]. I do not think it [does] what you think it [does].”
I also wanted to thank you that it drove me to learn something new about the scope of SET statements in stored procedures. I was worried that i’d have to turn XACT_ABORT back off at the end of the statement, but after some independent study, it appears you do not.
Thank you for continuing to encourage us to get in there and try these things out and not just read a blog and move on.
You’re welcome! That’s awesome to hear. That’s my goal, getting folks to jump in and test their expectations.
Interesting. I have had issues in the past with IF @@TRANCOUNT > 0 ROLLBACK; where it appeared that @@TRANCOUNT wasn’t reliable and it sometimes would throw the error that there’s nothing to rollback, or it just would leave an open transaction. But then again, this was when the IF @@TRANCOUNT > 0 ROLLBACK; was inside the catch, not outside the try/catch block. Seems I have more future testing to do.
I believe the issue you were encountering there was that you weren’t hitting the CATCH block because of the limitations around “small” and “big” exceptions
Does having this in the CATCH block help, or does setting XACT_ABORT ON make it unnecessary?
IF XACT_STATE() 0 BEGIN
ROLLBACK;
END;
I’m looking at https://www.brentozar.com/archive/2016/05/try-catch-doesnt-handle.
Give it a shot and test, by all means. That’s why I do all this work in the open – so y’all can take this and run with it to answer your own questions quickly.
Just great stuff Brent, thank you so much. You know I came to SQL Server from Oracle, and I just carried on using transactions the same as I did there. Looks like I’ve been doing it wrong for years now, you know this could explain some of those occasional “funnies” over the years I could never get to the bottom of and always had to go and sort out the damage manually afterwards? Brilliant, many thanks.
You’re welcome!
I wish T-SQL allowed you to alias words so that I could use SELECT…WITH (NOMAYO).
Feel free to fork Postgres and implement that feature btw
Great series. thanks, Brent!
You’re welcome! Glad you enjoyed it.
Thanks Brent for all you done for the community. My first post to you!
An MS Technet article says using XACT_ABORT solves the open transaction issue but at the cost of the logging implemented in the CATCH block and is not needed if you have QA in place prior to deployment in production, (my paraphrasing)
See conclusion in https://social.technet.microsoft.com/wiki/contents/articles/40078.sql-server-set-xact-abort-vs-try-catch.aspx
Any comments?
Glad you liked it. I think the demos in this week’s blog post speak well for themselves and I don’t have anything to add.
Great writeup. That is nasty. And very unexpected behavior. If I’m understanding correctly, this wouldn’t exist if T-SQL’s CATCH actually caught *everything*?
I wear both the DBA hat and the .NET developer hat at my org. All of our multi-table stuff is client-side. Assuming we are using a language that has exception handling that catches *everything*, this won’t be a problem, right? Or will it?
Our code follows this pattern:
Try
Start Transaction
Try
Insert into table 1
Insert into table 2
Commit
Catch
Rollback
End Try
Finally
Close Transaction
End Try
I spent some more time thinking about this yesterday and am stumbling on something. How is line 17 executed when we cancel the request while it’s executing line 8? When i put other statements below the Catch statement they do not appear to be executed, but that rollback does?
What am i missing?
KC, a friend of mine told me that you are not missing anything. The CATCH block and the ROLLBACK block are not executed in the steps Brent outlined. The canceling of the query during the WAITFOR DELAY stops the execution of the stored procedure. The XACT_ABORT ON takes care of the ROLLBACK.
A friend of mine says you can test it. You can run this changed version of the code, where the TRY and CATCH and Test for TRANCOUNT > 0 are commented out.
CREATE OR ALTER PROC dbo.Turkey_Sandwich AS
BEGIN
SET XACT_ABORT ON; /* THIS IS NEW */
/* BEGIN TRY */
BEGIN TRANSACTION;
INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE());
WAITFOR DELAY ’00:00:30′;
INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE());
COMMIT TRANSACTION;
/* END TRY
BEGIN CATCH
ROLLBACK;
Select ‘Catch Block’;
END CATCH
Select ‘How about the rollback test’
IF @@TRANCOUNT > 0 ROLLBACK; /* THIS IS NEW */
*/
END
GO
INSERT dbo.Parent (DateAdded)
SELECT DateAdded
FROM (INSERT dbo.Child(DateAdded)
OUTPUT Inserted.DateAdded
SELECT GETDATE()
) SQ
It’d be so awesome if we could boil every transaction down to just one insert statement, right? Unfortunately, in the real world, that isn’t the case. I’m using a simple pair of inserts here in order to illustrate the problem of error handling in transactions, and typically it’s a lot bigger than a pair of insert statements. Thanks though!
SET XACT_ABORT ON automatically aborts the batch and rolls everything back anyway.
So why would you bother with a CATCH just to do a rollback? Just let the batch abort, the server will automatically rollback
Hi, Charlie, thanks for stopping by.
As I mention in big print at the end of the post, this is not the finish line. This is only the starting line, and there’s much more you can do with CATCH. Make sure to do that homework that I gave you in the last section – there’s a lot more to cooking than just a turkey sandwich.
Cheers!
I’ve dug into Erland’s posts many times, and the conclusion I came to was always the same:
There is no point using CATCH if you are not actually handling the error. Catching only to rollback and re-throw is pointless.
So yes, if you want to *handle* the error by logging or retrying or whatever, yes use it.
But in 99% of T-SQL code I have written, I catch all errrors in the client, and for that all you need is XACT_ABORT. I just don’t get why you would ever want to catch an error you are not handling.
The most egregious examples of CATCH that I’ve seen use SELECT @@ERROR … which returns an unexpected resultset to the client (which assumes no error has happened as it’s been swallowed), and in the case when there are multiple errors you only see one. This version is absolutely the wrong way to do error-handling, even if TechNet and BOL seem to love it.
Charlie – it’s fantastic that you handle errors 99% of the time in your code. That’s great! That’s 99% more than most folks.
Besides, I’m sure the 1% won’t produce errors, and it won’t matter if things crash, data is lost, or transactions are left open. You’ll probably be fine.
Have a great week!
Sorry think you misunderstood what I was saying: I’m both developer & DBA, so 99% of code I write is run from a client app (regular DML etc), where it’s *essential* whatever you do to handle/log errors anyway on the client. There are errors which cannot be caught by T-SQL such as Attention or a broken connection.
Furthermore triggers should *never* have an explicit rollback in them, because otherwise you will get spurious Transaction ended in the trigger” error messages.
Don’t forget that Agent Jobs for example also have error logging built-in, so we don’t need to catch that either. We do use XEvents to log T-SQL errors anyway, and XACT_ABORT will sort out rollback and batch-abortion.
My point is that 99% of regular T-SQL code shouldn’t be handling errors on the server, rather they should handle them on the client. There is simply no point catching errors server-side that you have no intention of handling.
The other 1% are the sort of code that you would be writing: complex DBA code such as bulk index rebuilds, or bulk DDL changes, sp_Blitz_whatever. Yes, there you need TRY CATCH, yes that is where I would use it, because you want to *handle* the error and take some action. You still need XACT_ABORT though.
Do you have an example of code that *needs* TRY CATCH even when there is no handling done other than ROLLBACK? I don’t know, perhaps there are some edge cases where XACT_ABORT will not roll back, but I’m not aware of any. (Note that syntax errors are not relevant here, because a syntax error would prevent TRY CATCH from compiling also, and if BEGIN TRAN is in the same batch then the transaction doesn’t even start.)
Just to clarify: this is a great series, and I’m not attacking it. It’s important for all to understand the idiosyncrasities o SQL errors. I’m just trying to understand why TRY CATCH would be recommended if XACT_ABORT ON and no error handling done.
Charlie – I’ll be quick and to the point because your time is valuable, as is mine.
Most of the time, when people tell me, “Oh yeah, we handle errors on the app side,” and I go look at the app code – all they’re doing is discarding the error.
I’m sure you’re different, and your client-side error handling has no bugs, and is robustly implemented. 😉