To find out, let’s set up a simple status log table:
|
1 2 3 4 5 6 |
DROP TABLE IF EXISTS dbo.StatusLog; CREATE TABLE dbo.StatusLog (TimeItHappened DATETIME2 PRIMARY KEY CLUSTERED, Step VARCHAR(20)); GO |
And then let’s try a two-part transaction:
|
1 2 3 4 5 6 7 8 9 10 11 |
BEGIN TRAN INSERT INTO dbo.StatusLog VALUES (GETDATE(), 'Step 1'); WAITFOR DELAY '00:00:01'; BEGIN TRAN INSERT INTO dbo.StatusLog VALUES (GETDATE(), 'Step 2'); SELECT @@TRANCOUNT AS OpenTransactions; |
Right now, SQL Server shows that I have 2 open transactions:
What Happens If I Roll Back?
But what does “2 open transactions” mean, really? If I do a rollback, what gets rolled back? Let’s find out:
|
1 2 3 |
ROLLBACK; SELECT @@TRANCOUNT AS OpenTransactions; SELECT * FROM dbo.StatusLog; |
The results:
Both of our transactions were rolled back, and there’s nothing left in the table.
Let’s Try It Again, but Commit This Time
Roll through the same setup code, but then commit:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
BEGIN TRAN INSERT INTO dbo.StatusLog VALUES (GETDATE(), 'Step 1'); WAITFOR DELAY '00:00:01'; BEGIN TRAN INSERT INTO dbo.StatusLog VALUES (GETDATE(), 'Step 2'); GO COMMIT SELECT @@TRANCOUNT AS OpenTransactions; SELECT * FROM dbo.StatusLog; |
The results are a little weird:
Only one transaction is shown as open – and right now, both rows are in the results table. If we roll back now, what happens?
Even though we said we “committed” our inner transaction, it doesn’t matter – BOTH of our transactions got rolled back.
You Can’t Really Nest Transactions Like This.
Think of @@TRANCOUNT as the number of times remaining that you either need to commit or roll back. If you open a bunch of nested transactions in a row, it’s up to you to commit every single one of ’em. If ANY of them are rolled back, EVERYTHING is rolled back.
This is particularly troublesome if you try to break up locking by using a bunch of little transactions wrapped in one big outer transaction. I recently had a client who thought they could:
- Start an outer transaction for a business process
- Start an inner transaction, acquire locks on OrderHeader, make changes, commit, and release the OrderHeader locks when the transaction committed
- Go on to another inner transaction, acquiring other locks on OrderDetails, while other processes were able to work lock-free on the OrderHeader table because step 2’s locks were released
But as you can see here, even when you commit one part of a transaction, SQL Server still isn’t quite done with it. It has to maintain those locks because if any of your open transactions are rolled back, SQL Server’s gonna roll back everything you did – even the parts you thought were finished.
Update: a couple of commenters have pointed out SAVE TRANSACTION, and I gotta say I’m not a fan of that because of the complexity, especially around lock escalation. If you choose to use that feature, read the documentation really carefully, especially around lock escalation. Saved transactions don’t release the locks once they’ve been escalated, and if you’ve been through my Mastering classes, you know how easy it is to hit lock escalation.
5 Comments. Leave new
If you don’t worry about locks, and have to “rollback” part of transaction, you can use “SAVE TRANSACTION” with named savepoints or named trans. But this is truly the gate to hell. If it will be accurately implemented, all your stored proc become unreadable with tons on “if @@trancount” blocks and dozens lines of code in it.
Handling nested trans with SAVE TRAN @SavePoint is about 20 lines total of boilerplate code instead of 5 not doing it (i.e. just BEGIN TRAN/COMMIT/ROLLBACK) so we do this on all our stored procedures which modify data in a transaction out of habit — not that it’s super useful and our business logic relies on this.
Great point. I’ve added an update to the bottom of the post talking about my feelings about SAVE TRAN. (I hate that thing.)
I never new that ROLLBACK TRAN @SavePoint actually released *any* of the locks acquired but blindly assumed it behaves like nested COMMIT which holds everything intact so there is nothing to complain on my part if ROLLBACK at least tries to drop non-escalated locks — nice to know it does this!
Prefer starting and committing transactions at a _single_ level, and making transaction handling part of the application’s design.
If a procedure is called, with an open transaction, you need to save a checkpoint, to handle local commit/rollback.
sql
declare @Independent bit = case when (@@tranCount = 0) then 1 else 0 end; -- 0 = called with open transaction
if @Independent = 1
begin transaction;
else
save transaction MySavePoint;
/!\ Never use the transaction name on the
rollbackstatement, as it leaves the code with a potential hidden bug.The
with markoption causes the transaction name to be placed in the transaction log. Can be useful when restoring the database.sql
begin transaction CandidateDelete with mark N'Deleting a Job Candidate';
Also, always use
begin try.., when using transactions, we don’t want them hanging around.