In yesterday’s post, we tried a TRY/CATCH, and those of us who actually ran the demo scripts were disappointed. We found that a TRY/CATCH alone doesn’t fix it: we end up getting rows in the Parent table, but no rows in the Child table. It turns out there are some gotchas in Books Online’s summary of what TRY/CATCH does:
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.
The devil is in the details. If you scroll further down in the documentation – look, I’m not saying you’re actually going to do that, because we both know damn well that you don’t read the documentation – there are gotchas like:
TRY…CATCH constructs do not trap the following conditions:
- Warnings or informational messages that have a severity of 10 or lower.
- Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
- Attentions, such as client-interrupt requests or broken client connections.
- When the session is ended by a system administrator by using the KILL statement.
So to recap, TRY/CATCH doesn’t catch small problems or big problems – only medium-sized problems.
Okay, that’s a bit of a turkey.
In our scenario this week, I’m causing the error by canceling the query (a client-interrupt request) – but no, we can’t just say, “Never abort your queries.” I’m pro-choice: people deserve the right to end their queries at any stage of their development. I also believe in the death penalty: DBAs deserve the right to kill your queries when they’re causing terrible harm to society at large.
The business is asking us to make sure that the data gets into both the Parent and Child tables, or neither. We’re gonna need something more powerful than TRY/CATCH.
Let’s try a transaction instead.
Microsoft’s documentation on SQL Server transactions starts out with the most important stuff:
A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
That, not try/catch, is probably a better fit for what we’re trying to do. Let’s frame our two insert statements with transactional code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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_Transaction AS BEGIN BEGIN TRANSACTION; /* THIS IS NEW */ INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE()); WAITFOR DELAY '00:00:30'; INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE()); COMMIT TRANSACTION; /* THIS IS NEW */ END GO |
And as before, run the proc, wait a few seconds, cancel it, and then see what we’ve got:
|
1 2 3 4 5 |
EXEC dbo.Insert_Transaction; /* Let that run for a few seconds, then cancel it. Then: */ SELECT * FROM dbo.Parent; SELECT * FROM dbo.Child; |
As with yesterday’s demo, I’m not going to show you the results here. Instead, 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
And then run it yourself to see whether your hunch was correct. Just as a reminder, only do these experimentations in a development environment, not production, especially if you happen to have RCSI or SI enabled in production.
To understand what’s happening and why, check out the next post in this series.
33 Comments. Leave new
Would have been interesting to have been a fly on the wall at the design meeting when this “feature” was introduced!
As always you sum up the complex with a memorable phrase “TRY/CATCH doesn’t catch small problems or big problems – only medium-sized problems.”
Thanks! It was either that or, “CATCH is about as useful as a catcher’s mitt with only one finger.”
“2. We’ll have a new row in Parent, but not Child” but… but transaction will be opened. If it will be rolled back than will be “1. We’ll have no new rows in either Parent or Child”, if it will be commited – “2. We’ll have a new row in Parent, but not Child”
Anyway open transaction is really bad.
Loving these posts Brent, keep ’em coming!
My pleasure!
I know where this is going but I don’t wanna spoil it! This is a common best practice for us in our company when it comes to stored procedures that perform DML operations. The hint here is that the transaction needs to be either committed or rolled back depending on the scenario. The question to consider is: how do you determine whether to commit or roll back? This is something to think about when trying to figure out what to do with your code.
Ooo ooo ooh open transactions ??
Awwww yeah
Thank you for formulating these quiz/challenges… the thinking cap is getting a workout.
Love your work.
You’re welcome, my pleasure! It’s been so fun to see people surprised.
This is a template I have used for years and so far it has worked without problems, even if there is no initial transaction or if there is a transactions (initiated from SSMS or from the app). It is hard to find a recipe by googling, so I just want to share….
CREATE PROC sp_xxx
AS
DECLARE @TranCount INT = @@TRANCOUNT;
BEGIN TRY
IF @TranCount = 0
BEGIN TRANSACTION
ELSE
SAVE TRANSACTION tran_xxx;
/* DO THE WORK … */
IF @TranCount = 0 COMMIT
END TRY
BEGIN CATCH
DECLARE
@RaisErrorUser NVARCHAR(128),
@ErrorMsg AS NVARCHAR(MAX),
@XState INT;
SELECT
@RaisErrorUser = SUSER_SNAME(),
@ErrorMsg = ERROR_MESSAGE() + ‘ – at line # ‘ + CAST(ERROR_LINE() AS NVARCHAR(10)),
@XState = XACT_STATE()
IF @TranCount = 0
ROLLBACK
ELSE IF @XState -1
ROLLBACK TRANSACTION tran_xxx;
RAISERROR(@ErrorMsg, 18, 1, @RaisErrorUser)
END CATCH
You should try that with this week’s exercise. You might be surprised to find out it doesn’t work either. You’ll find out why this week. Enjoy the series!
Yes, you’re right 🙂
It does not work if cancelling the query, and maybe even worse; it leaves the transaction open (!)
Looking forward to how you are going to “catch” the cancelling…
Within the context of this quiz topic, is my use of SET XACT_ABORT to catch the client-interrupt correct, or is @@TRANCOUNT a more “production” leaning solution? I realize that the answer to these types of questions is often — It depends.
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.parent(date_added) VALUES (GETDATE());
WAITFOR DELAY ’00:00:30′; –TRY…CATCH constructs do not trap the following conditions: Attentions, such as client-interrupt requests or broken client connections.
INSERT INTO dbo.child(date_added) VALUES (GETDATE());
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
vs.
SET XACT_ABORT ON –when SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
BEGIN TRANSACTION
INSERT INTO dbo.parent(date_added) VALUES (GETDATE());
WAITFOR DELAY ’00:00:30′;
INSERT INTO dbo.child(date_added) VALUES (GETDATE());
COMMIT TRANSACTION
We’re not covering that option yet in the series. Stay tuned!
My friend says you should write it like this instead. My friend is also an idiot who thinks crayons are delicious.
CREATE OR ALTER PROC dbo.Insert_Transaction AS
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION; /* 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
BEGIN
COMMIT TRANSACTION; /* THIS IS NEW */
END
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END CATCH
GO
Can you please try this? have we stumble on some major bugs?
DROP TABLE IF EXISTS dbo.Parent;
DROP TABLE IF EXISTS dbo.Child;
CREATE TABLE dbo.Parent (ID INT IDENTITY(1,1), DateAdded DATETIME2, Comments varchar(max));
CREATE TABLE dbo.Child (ID INT IDENTITY(1,1), DateAdded DATETIME2, Comments varchar(max));
GO
CREATE OR ALTER PROC dbo.Insert_Transaction (@Comments varchar(max)) AS
BEGIN
–SET XACT_ABORT ON
BEGIN TRANSACTION; /* THIS IS NEW */
INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE());
WAITFOR DELAY ’00:00:30′;
INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE());
COMMIT TRANSACTION; /* THIS IS NEW */
END
GO
CREATE OR ALTER PROC dbo.Insert_Transaction2 (@Comments varchar(max))AS
BEGIN
SET XACT_ABORT ON
BEGIN TRANSACTION; /* THIS IS NEW */
INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE());
WAITFOR DELAY ’00:00:30′;
INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE());
COMMIT TRANSACTION; /* THIS IS NEW */
END
GO
EXEC dbo.Insert_Transaction ‘Run Insert_Transaction full 30 secs’;
SELECT * FROM dbo.Parent;
SELECT * FROM dbo.Child;
— Expected Result = 1 parent & 1 child
EXEC dbo.Insert_Transaction ‘Run Insert_Transaction stop at 5 secs’;
SELECT * FROM dbo.Parent;
SELECT * FROM dbo.Child;
— Expected Result = 2 parent & 1 child
— Actual Result = 2 parent 1 child
EXEC dbo.Insert_Transaction2 ‘Run Insert_Transaction2 full 30 secs’;
SELECT * FROM dbo.Parent;
SELECT * FROM dbo.Child;
— Expected Result = 3 parent & 2 child
— Actual Result = 3 parent 2 child
EXEC dbo.Insert_Transaction2 ‘Run Insert_Transaction2 stop at 5 secs’;
SELECT * FROM dbo.Parent;
SELECT * FROM dbo.Child;
— Expected Result = 3 parent & 2 child
— Actual Result = 1 parent 1 child !!!!! WHAT !!!!!
oh sorry, you can add the insert for comment if needed, but not necessary to produce result.
Daniel – you’re probably trying a little too much in one test. Ease up and read this week’s posts instead. Cheers!
oh, I see what’s happening, the 3rd execution has run the full begin-commit, but it’s considered as part of another begin transaction within the session, and rolled back by the 4th execution… but it’s counter intuitive… I would assume that the 3rd execution should actually be committed.
You are such a tease … the suspense is killing me! 😀
If it is a quiz *week*, the proposed solution on Day 2 will not work… 🙂
Eagerly awaiting next installments.
Hahaha, ever-insightful as always!
I love the way Erland Sommarskog put it [here](https://sommarskog.se/error_handling/Part2.html):
“Every once in a while, I get the feeling that SQL Server is intentionally designed to be as confusing as possible. When they plan for a new release they ask each other what can we do this time to confuse the users? Sometimes they run a little out of ideas, but then someone says Let’s do something with error handling!”
His posts are actually where I send readers in the final post in this week’s series! They’re so good.
I’m not sure if this adds anything different to what has been mentioned, but this is a method we use to try to trap errors and roll back an transaction.
BEGIN TRY
BEGIN TRANSACTION;
/*
DO YOU BUSINESS HERE
*/
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION;
PRINT ‘BUSINESS DONE, THANK YOU!’;
END;
END TRY
BEGIN CATCH
/*
YOUR BUSINESS IS BAD, LEAVE WITHOUT A TRACE
(If Transaction exists, roll it back)
1) ROLL BACK TRANSACTION
2) LOG ERROR TO TABLE, NOTIFY DEVELOPERS
3) RETURN ERROR CODE TO CALLING PROCESS
*/
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END;
PRINT ‘AN ERROR DONE HAPPENED!’;
END CATCH;
Chris – as I’ve kinda gently suggested to other folks throughout this week’s series, make sure to actually test your code before suggesting it’s an answer. This code fails just as the code in the blog post does. You’ll see why at the end of the week. 😉
I look forward to it! We actually use this template in some of our production code (with actual logging code in place of the comments), so it will be interesting to see what’s going wrong 🙂
First – Well that depends on how you cancel the query….
If, like me, you killed the session then you get what you expect. Zip, zilch and nada.
It took me a moment to work out what I was doing wrong!
And isn’t this why xact_abort should be on when declaring transactions?
SHOULD be, but usually isn’t!
The trick with the transaction is that you can see the uncommitted row if you run the select IN THE SAME SESSION that ran the cancelled proc in (that’s how it always works).
If you select from the tables in another session, you get blocked (transaction is still open), or if using NOLOCK you see the row (but only because of NOLOCK). Now back to the session that ran the cancelled proc, @@TRANCOUNT = 1, so you either have to commit it, then you see the one row, or ROLLBACK, then you see nothing. However, @@ERROR is 0 since we probably lost out on that last statement context. so without looking at other stuff to check, there may not necessarily be a way of knowing it was cancelled (maybe, maybe not, I haven’t looked it over that far yet).
If in any situation, there is potential for nested transaction and you want to make sure you dump it all, I use
WHILE @@trancount > 0 ROLLBACK TRAN;
That’s not a trick – I’m not sure what you mean there.
I used my SP template (without my persistent logging code) and plugged in your code, tested it by waiting 5 seconds and then killing the session. No rows are present.
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_Transaction
AS
BEGIN
DECLARE
— Result Code from Logic. Should be zero (0) or something has gone awry.
@ResultCode INT,
— Error capture variables
@msg NVARCHAR(4000),
@erruser sysname,
@errproc VARCHAR(128),
@errnum INT,
@errmsg NVARCHAR(4000),
@errline INT,
@errsev INT,
@errstate INT;
— Initialize variables
SET @ResultCode = 0;
BEGIN TRY
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRANSACTION; /* THIS IS NEW */
INSERT INTO dbo.Parent
(
DateAdded
)
VALUES
(GETDATE());
WAITFOR DELAY ’00:00:30′;
INSERT INTO dbo.Child
(
DateAdded
)
VALUES
(GETDATE());
COMMIT TRANSACTION; /* THIS IS NEW */
END TRY
BEGIN CATCH
SET @erruser = SYSTEM_USER;
SET @errproc = ERROR_PROCEDURE();
SET @errnum = ERROR_NUMBER();
SET @errmsg = ERROR_MESSAGE();
SET @errline = ERROR_LINE();
SET @errsev = ERROR_SEVERITY();
SET @errstate = ERROR_STATE();
SET @msg
= CONCAT(
‘erruser=’,
@erruser,
‘ errproc=’,
@errproc,
‘ errnum=’,
@errnum,
‘ errmsg=’,
@errmsg,
‘ errline=’,
@errline,
‘ errsev=’,
@errsev,
‘ errstate=’,
@errstate
);
— Roll back any active or uncommittable transactions before
— inserting information in the ErrorLog.
— XACT_STATE = 0 means there is no transaction and a commit or rollback operation would generate an error.
— XACT_STATE = -1 The transaction is in an uncommittable state
IF XACT_STATE() 0
BEGIN
ROLLBACK TRANSACTION;
END;
SET @ResultCode = @errnum;
IF @ResultCode 0
RAISERROR(‘%s’, 16, 1, @msg);
END CATCH;
RETURN @ResultCode;
END;
GO