Let’s say we have two tables, Parent and Child, and we need to guarantee that they both get populated at once. We’ll write a single stored procedure to do both inserts:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DROP TABLE IF EXISTS dbo.Parent; DROP TABLE IF EXISTS dbo.Child; CREATE TABLE dbo.Parent (ID INT IDENTITY(1,1), DateAdded DATETIME2); CREATE TABLE dbo.Child (ID INT IDENTITY(1,1), DateAdded DATETIME2); GO CREATE OR ALTER PROC dbo.Insert_Two AS BEGIN INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE()); WAITFOR DELAY '00:00:30'; INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE()); END GO |
I put a WAITFOR in there, but that isn’t the problem – I’m just using that to demonstrate why the code isn’t production-ready. If I execute the stored procedure, wait a few moments, and then click cancel – what happens?
|
1 2 3 4 5 6 |
EXEC dbo.Insert_Two; /* Let that run for a few seconds, then cancel it. Then: */ SELECT * FROM dbo.Parent; SELECT * FROM dbo.Child; GO |
The results show that a row was added in the Parent table – but not in the Child table:
Well, that’s not good: the business has asked us to guarantee that we either insert both rows, or neither. Our code can’t be doing a half-*ss job. We either gotta have all *ss or no *ss in our back end systems.
This isn’t just about canceling queries.
This problem pops up in many scenarios, like these:
- Our query loses a deadlock
- Our query gets killed because it’s blocking others
- The server runs out of drive space
- Our application calls multiple statements (rather than a proc) and the app fails midway through
Foreign keys don’t fix this kind of problem, either – in fact, in many situations, foreign keys even cause problems. That’s outside of the scope of this week’s posts, though: this week, I need to teach you why and how to make a turkey sandwich.
Let’s try a TRY/CATCH.
According to the TRY/CATCH documentation – don’t worry, I know it’s a long page, but this important part is right up there at the top of the page so you can ignore it more quickly:
A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.
Let’s implement it in our code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR ALTER PROC dbo.Insert_TryCatch AS BEGIN BEGIN TRY INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE()); WAITFOR DELAY '00:00:30'; INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE()); END TRY BEGIN CATCH ROLLBACK; END CATCH END GO |
And then try the same trick – run it, wait a few seconds, then cancel it, and see what’s inside the tables:
|
1 2 3 4 5 6 |
EXEC dbo.Insert_TryCatch; /* Let that run for a few seconds, then cancel it. Then: */ SELECT * FROM dbo.Parent; SELECT * FROM dbo.Child; GO |
I’m not going to show you what happens, dear reader, because this is a short demo, and I want you to run it yourself to see what happens. Guess what’ll happen before you run it:
- We’ll have no new rows in either Parent or Child, or
- We’ll have a new row in Parent, but not Child, or
- We’ll have new rows in both Parent and Child
Then run the query yourself to see what actually happens. In this next post post, we’ll continue the experiments.
34 Comments. Leave new
2
2- a new row only in parent, catch block is not executed. so same execution as first example
And why isn’t the catch block executed?
Because there is no error occurred
Messages with severity from 11 to 19 are trappable with TRY-CATCH, whereas messages with severity 0 to 9 are always passed to the client, and you cannot access them in SQL Server. Nor can you trap errors with severity ? 20, since when this happens you are no longer alive
Bingo! CATCH is about as effective as a single-finger catcher’s mitt.
severity >= 20.
Extract from http://www.sommarskog.se/error_handling
2 – unless SET IMPLICIT TRANSACTIONS ON is set.
By default, each statement is run as a separate transaction unless you explicitly use “BEGIN TRANSACTION” to wrap a series of statements in a Transaction and then use “COMMIT” or “ROLLBACK” to complete or discard the entire set of statements.
Thus, the first statement is successfully executed as a complete transaction and a row written in the first table. There is no transaction in progress when you terminate the procedure, so the second Insert doesn’t execute and the rollback in the catch block does nothing.
If you use “SET IMPLICIT TRANSACTIONS ON”, then this example would result in no rows in either table. Of course, if you did set that on, you would need to include an explicit “COMMIT” statement after the second Insert.
Alternately, wrap both inserts in a block with “BEGIN TRANSACTION” and a “COMMIT” and you would get the desired behavior of both inserts either completing or being rolled back.
Kim – try running a ROLLBACK without typing BEGIN TRAN. What happens?
In light of that, does that change anything about your answer?
Hmm… I think this is an artifact of running the stored procedure inside SSMS, possibly related to the WAITFOR. Try to close the SSMS tab and note that you have uncommitted transactions. Also – scatter a bunch of “print ‘ ‘ + isnull(convert(varchar(32), @@TRANCOUNT),’NULL’);” and you can see the transaction counter incrementing with each run in the same tab.
IRL I have never had a problem with BEGIN TRANSACTION … BEGIN TRY blocks leaving orphan records, but I have never tried (intentionally) to interrupt one in an interactive SSMS session.
That might be an artifact of you changing the demo. Close SSMS entirely and follow the instructions in the scripts, exactly as is.
Yes – the uncommitted transactions message occurred after I added a BEGIN TRANSACTION … COMMIT block around the Inserts, which implies to me that SSMS is maintaining the session state of the tab and the “transaction” is neither completed nor rolled back entirely until you try to close the tab.
Looking forward to tomorrow’s analysis.
Interesting.. I expected that the catch block will not be executed on Abort, so only parent row gets added (2).
But I get the same result even after adding BEGIN TRANSACTION; .. COMMIT TRANSACTION inside SP, it seems to always commit open transactions on abort?
That is indeed part of the fun this week! Simply adding a transaction doesn’t fix the problem either. Stay tuned!
Yeah, I thought it was just a missing “begin transaction” at the beginning and a “commit” after the second INSERT. Still doesn’t work though. I’m curious to see the solution.
Um, I get a row in both tables with either sproc. The parent time stamp is 30s prior to the child time stamp. I’m running the code without change on my local SQL Server 2019.
You’re missing the second paragraph of the post. Read that one carefully.
Yep, missed the ‘cancel’ part, lol
That was fun. Thank you for prompting us to think about things like this. I learned something new today!
Very nice puzzle – I would have thought it would have worked as written – I think to work it needs a “SET XACT ABORT ON” and an explicit transaction around the code in the TRY section.
You got it!!
Looking forward to part two…
Wow this is a real head scratcher! Looking forward to the next post 😀
Thanks for posting this code-along. I think I inadvertently figured things out sort of, but I’m very interested to see the next part to make sure I fully understand.
Got it 🙂
…
SET XACT_ABORT ON;
BEGIN TRAN
…
COMMIT
…
And even that may fail, as you’ll see this week! Enjoy the series.
I’m curious to see when/how that would fail, because that would have been my approach. Thanks for the puzzle!
Interesting one! I’m looking forward to seeing your solution.
a little above my head but
would the XACT_STATE or the TRANCOUNT have something to do with it?
a better TRY/CATCH construct like the following would help the “all or nothing” for your two insert statements:
https://dba.stackexchange.com/questions/134129/transaction-in-a-stored-procedure
Close, but not quite! Check the next post in the series.
But how will it ever get to the CATCH (and rollback) if no error was ever raised?
The link to the next post in the series is broken.
Hi Brent,
The link to the next post in the series is broken. I will search for them individually, but wanted to let you know about it.
All the best,
Jamie
Sorry about that! Fixed.