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:
CREATE OR ALTER PROC dbo.Turkey_Sandwich AS
SET XACT_ABORT ON; /* THIS IS NEW */
INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE());
WAITFOR DELAY '00:00:30';
INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE());
IF @@TRANCOUNT > 0 ROLLBACK; /* THIS IS NEW */
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:
TRUNCATE TABLE dbo.Parent;
TRUNCATE TABLE dbo.Child;
/* Let that run for a few seconds, then cancel it. Then: */
SELECT * FROM dbo.Parent;
SELECT * FROM dbo.Child;
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.