In yesterday’s epic cliffhanger, you might have been shocked to discover that a plain ol’ transaction does not solve our problem – and in fact, it makes things worse. With yesterday’s code, we still got rows inserted into the Parent table, no rows in the Child table, and a nasty surprise that our session will have to deal with down the road.
The size of the turkey might have become apparent when you tried to close your query. I’m using SQL Server Management Studio, and when I try to close that query’s tab, I get a popup warning:
Let’s look at our code again to understand what happened:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE OR ALTER PROC dbo.Insert_Transaction AS BEGIN BEGIN TRANSACTION; INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE()); WAITFOR DELAY '00:00:30'; INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE()); COMMIT TRANSACTION; END GO |
When we executed the proc, waited a few seconds, and then clicked cancel, we only canceled the execution of the waitfor. The transaction was still left open.
I’ve seen this error in so many applications, I can’t even begin to tell you. But here’s the crappy part: it’s almost impossible to track down where the bug is. All it takes is to make this coding mistake just once, and the transaction will be left open for other parts of code to deal with. I’ve seen so many cases where applications leave transactions open for days, and nobody’s sure exactly where the transaction was started.
The solution is to combine TRY/CATCH and a transaction. Before I get to the code, though, I’ll make sure to run this a few times just to make doggone sure I didn’t leave any transactions open in this window:
|
1 2 |
ROLLBACK; GO |
Let’s do a transaction in a TRY/CATCH block.
I know, I know, the code is a little wordy given that all we’re tryin’ to do is a pair of inserts, but here we go:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR ALTER PROC dbo.Insert_TryCatch_Transaction AS BEGIN 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 END GO |
You know the routine: clear out the tables, run the stored procedure, wait a couple of seconds, cancel it, and check the contents of our tables:
|
1 2 3 4 5 6 7 8 9 10 |
TRUNCATE TABLE dbo.Parent; TRUNCATE TABLE dbo.Child; GO EXEC dbo.Insert_TryCatch_Transaction /* Let that run for a few seconds, then cancel it. Then: */ SELECT * FROM dbo.Parent; SELECT * FROM dbo.Child; GO |
And before you try it, for the last time this week, take a guess at what’s going to happen:
- We’ll have no new rows in either Parent or Child, and no open transactions
- We’ll have a new row in Parent, but not Child, and an open transaction
And then run it yourself to see whether your hunch was correct. After your experimentations, make sure to close your SSMS window juuuust in case – we’ve been playing around with a lot of transactions this week.
18 Comments. Leave new
Of course “new row in Parent, but not Child, and an open transaction”. But! But if we cancel execution in trigger transaction will be rolled back:
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 TRIGGER dbo.parent_i ON dbo.Parent AFTER INSERT AS
BEGIN
SET NOCOUNT ON;
WAITFOR DELAY ’00:00:01′; — we will not cancel here
END
GO
CREATE TRIGGER dbo.child_i ON dbo.Child AFTER INSERT AS
BEGIN
SET NOCOUNT ON;
WAITFOR DELAY ’00:00:30′; — we will cancel here!
END
GO
CREATE OR ALTER PROC dbo.Insert_Transaction AS
BEGIN
BEGIN TRANSACTION; — no any try/catch
INSERT INTO dbo.Parent(DateAdded) VALUES (GETDATE());
INSERT INTO dbo.Child(DateAdded) VALUES (GETDATE());
COMMIT TRANSACTION;
END
GO
TRUNCATE TABLE dbo.Parent;
TRUNCATE TABLE dbo.Child;
GO
EXEC dbo.Insert_Transaction
/* Let that run for a few seconds, then cancel it. Then: */
SELECT * FROM dbo.Parent;
SELECT * FROM dbo.Child;
GO
OK, cool – let’s hold off introducing more unrelated features for now, and just follow along with the code. Most folks are going to struggle getting the transaction right as it is without triggers involved. (And your example doesn’t fix it either, heh.) Neat demo though!
There is no wordy code even if you think you are doing a simple thing. My experience from Oracle is that we always had exception block WHEN OTHERS and in this block we had a proper error logging, rollback and throwing error to procedure / code which called it. It saved us so many times 🙂
adding SET XACT_ABORT ON in the proc, before the transaction block, will rollback all the inserts
You’re getting warmer! That’s the subject of another post this week.
I must be doing something wrong because I do not get any rows on the Parent table, or open transactions (no changes to the stored proc). To check, I opened another query and set it to read uncommitted, run the stored proc and I see the row on the Parent table, I cancel the query, I rerun the select and the parent table is empty.
Got it. My friend has SSMS “option” SET XACT_ABORT ON.
My friend wants to know if this article is the last in the series because he’s still not getting the result he wants. He found changing a setting that starts with the 24th letter of the alphabet before running todays code gave him the result he was expecting. If this is the last article in the series (which he thinks is implied by the text “the solution is”), he feels that setting should probably be mentioned as well for others with the same issue. My friend thinks this message should be deleted if today was another cliffhanger.
Disclaimer: My friend can be an idiot sometimes….
I think Brent is heading towards XACT_STATE lines for COMMIT or ROLLBACK.
Lucky for me I don’t gamble.
My Standard Transaction code is something like this:
BEGIN TRANSACTION
BEGIN TRY
Update dbo.TableName
Set FirstName = ‘MyFirstName’
COMMIT
END TRY
BEGIN CATCH
Print Error_Message()
ROLLBACK
END CATCH
Bad news: it suffers from the problem I’m demonstrating in the post. Make sure to actually read it and try the code.
Seems sql server doesn’t treat cancelling a query execution in SSMS as an error
I’m behind, see my post in the previous article, same rules apply. open transaction left after cancel, then run commit, see 1 row, or run a rollback instead and see nothing. You can see all the changes in the same session when not committed, and blocked in another session until the transaction state is resolved.
HWDan – I hear you, but make sure you read the full series in detail where I talk about the problems with open transactions. I see that you’ve left a couple of comments already saying you haven’t read things yet – let’s hit the brakes until we’ve finished reading. Cheers!
Just tried this and wonder: Isn’t torture against the Geneva Convention?
HAHAHA
Right, basically ATTENTION is untrappable. And you are dead in the water awaiting command with an open transaction. Only the same session running the proc can see the uncommitted insert and that part might confuse some. Other sessions will get blocked, or if RCSI is on in this database and with moderate activity on the same table, someone will be scratching their head in a few weeks when tempdb is 500TB and still growing (or you run out of space, whichever occurs first).
You can check the @@TRANCOUNT if the transaction was left open, but at least in SSMS, you need manual intervention to kick start the next command to check it. XACT_ABORT may only stop the orphaned transaction from being inadvertently left open. But unless you are paying attention with SSMS, some logic in this case will be needed after the fact to check if the expected data made it in. I think this was your point, more-or-less right? Orphaned transaction inherited by the next process using the same session_id, and/or potentially lost inserts if the code doesn’t validate it’s work.
Also the best debugging you can do if the transaction stops right there is get the sql_handle from dbo.sysprocesses (not in any of the more modern EXEC DMV’s) as that’s the only place an inactive session still has the sql_handle exposed. But you can only see the SQL for the procedure call itself, not the last executed statement. If real app code reuses the same connection, then the only clue you can find about the orphan is where the table and indexes locks are on.
I get this a lot with vendor code, especially with implicit transactions where they either forgot to check the @@TRANCOUNT and add explicit commit code, or didn’t realize that their connection defaulted to use implicit transactions rather than auto-commit.
Hi Dan. No, these are all really interesting points, but they’re beyond the scope of this week’s blog post series.
It does sound like you’ve got a lot of interesting information you’d like to share – have you thought about starting your own blog?