A Nested COMMIT Does Not Commit Anything
A procedure that opens its own transaction is fine on its own. Call it from another procedure that already opened one and the arithmetic stops matching your intent.
SQL Server does not have nested transactions. It has a counter.
The counter
@@TRANCOUNT reports how many BEGIN TRANSACTION statements are outstanding on the current connection.[1]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT [at_start] = @@TRANCOUNT; BEGIN TRANSACTION; SELECT [after_outer] = @@TRANCOUNT; BEGIN TRANSACTION; SELECT [after_inner] = @@TRANCOUNT; COMMIT TRANSACTION; SELECT [after_inner_commit] = @@TRANCOUNT; ROLLBACK TRANSACTION; SELECT [after_rollback] = @@TRANCOUNT; |
|
1 2 3 4 5 |
at_start 0 after_outer 1 after_inner 2 after_inner_commit 1 after_rollback 0 |
Same on SQL Server 2019 (15.0.4480.2) and SQL Server 2025 (17.0.1125.2).
Two statements in that script are worth reading closely.
The inner COMMIT committed nothing. It took @@TRANCOUNT from 2 to 1, and that is all the script above establishes. To show what the inner commit did and did not do, the work has to be visible to somebody else, which needs a second connection.
Connection A creates a table, opens two transactions, inserts a row, and commits the inner one. The table has to be a permanent one rather than a #temp table, because a second connection needs to see it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
/* Connection A */ USE [tempdb]; GO DROP TABLE IF EXISTS [dbo].[commit_demo]; GO CREATE TABLE [dbo].[commit_demo] ( [id] int NOT NULL ); GO BEGIN TRANSACTION; BEGIN TRANSACTION; INSERT INTO [dbo].[commit_demo] ( [id] ) VALUES (1); SELECT [a_trancount] = @@TRANCOUNT; /* 2 */ COMMIT TRANSACTION; /* the inner commit */ SELECT [a_trancount] = @@TRANCOUNT; /* 1 */ |
Leave that connection exactly where it is, with its outer transaction still open, and run this on a second connection:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/* Connection B */ USE [tempdb]; GO SELECT [dirty_read] = COUNT_BIG(1) FROM [dbo].[commit_demo] WITH (NOLOCK); /* returns 1 */ SELECT [normal_read] = COUNT_BIG(1) FROM [dbo].[commit_demo]; /* blocks */ |
The first read returns 1, so the row exists. The second one blocks, because the inner COMMIT released no locks. Go back to connection A and roll back:
|
1 2 3 4 |
/* Connection A */ ROLLBACK TRANSACTION; SELECT [a_trancount] = @@TRANCOUNT; /* 0 */ |
Connection B’s blocked read now completes, and returns zero. Collected together:
|
1 2 3 4 5 6 |
after two BEGIN + insert, A trancount : 2 after the inner COMMIT, A trancount : 1 B sees via NOLOCK (dirty read) : 1 B reads normally : BLOCKED (locks not released) after single ROLLBACK, A trancount : 0 rows that survived : 0 |
The inner COMMIT left the row locked, unreadable to anyone not willing to take a dirty read, and still discardable. The single ROLLBACK then removed it. Only the commit that takes the counter from 1 to 0 makes the work durable and releases its locks.[2]
The single ROLLBACK discarded everything. It took the counter to zero and removed the inserted row, even though two BEGIN TRANSACTION statements had been issued and only one COMMIT.
Depth makes no difference to that. A bare ROLLBACK TRANSACTION rolls back the entire outermost transaction whatever the counter reads:[3]
|
1 2 3 4 5 6 7 8 9 |
BEGIN TRANSACTION; BEGIN TRANSACTION; BEGIN TRANSACTION; SELECT [before_rollback] = @@TRANCOUNT; /* 3 */ ROLLBACK TRANSACTION; SELECT [after_rollback] = @@TRANCOUNT; /* 0 */ |
Three levels deep, one statement, back to zero.
ROLLBACK TRANSACTION does take a savepoint name, and it is worth being clear about what that form does, because it is not a nested rollback. A savepoint marks a position inside one transaction. Rolling back to it undoes the work done after that point and leaves the counter alone:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
BEGIN TRANSACTION; INSERT INTO [dbo].[savepoint_demo] ([id]) VALUES (1); SAVE TRANSACTION [sp1]; INSERT INTO [dbo].[savepoint_demo] ([id]) VALUES (2); /* trancount 1, two rows */ ROLLBACK TRANSACTION [sp1]; /* trancount still 1, one row */ COMMIT TRANSACTION; /* trancount 0, and row 1 is committed */ |
The counter reads 1 before the rollback and 1 after it. Do the same thing two levels deep and it stays at 2. A savepoint never unwinds nesting, and it never moves @@TRANCOUNT; only BEGIN, a bare ROLLBACK, and COMMIT do that.
COMMIT and ROLLBACK are not symmetric. An inner COMMIT decrements the counter; a ROLLBACK at any depth resets it to zero.
Why this bites in procedures
Consider a procedure that does its own transaction handling, written the way it usually is:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE PROCEDURE [dbo].[update_office] ( @office_id int ) AS BEGIN BEGIN TRANSACTION; BEGIN TRY /* ... work ... */ COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; THROW; END CATCH; END; |
Called on its own, it behaves.
Called from a caller that has already opened a transaction, two things go wrong. On the success path, its COMMIT decrements from 2 to 1 and the procedure returns believing its work is committed when nothing has been written. On the failure path, its ROLLBACK discards the caller’s work too, including changes the caller made before it ever called this procedure.
The caller then continues, often with no idea anything was rolled back, and issues its own COMMIT against a transaction that no longer exists. That raises Msg 3902, “The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION”, and the message names the COMMIT rather than the procedure that actually caused it. The mirror image, rolling back when nothing is open, raises Msg 3903. Both confirmed on SQL Server 2019 and 2025.
Track ownership
The fix is to decide at entry whether this procedure owns the transaction, and only commit or roll back if it does.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
DECLARE @owns_transaction bit = 0; IF @@TRANCOUNT = 0 BEGIN SET @owns_transaction = 1; BEGIN TRANSACTION; END; BEGIN TRY /* ... work here ... */ IF @owns_transaction = 1 BEGIN COMMIT TRANSACTION; END; END TRY BEGIN CATCH IF @owns_transaction = 1 AND @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END; /* ... error handling here ... */ THROW; END CATCH; |
If a transaction is already open, the procedure joins it, does its work, and leaves the commit decision to whoever opened it. If not, it opens one and takes responsibility.
The @@TRANCOUNT > 0 test in the CATCH block covers the case where the transaction was already rolled back before control reached there, which a sufficiently severe error will do. Rolling back when no transaction is open raises Msg 3903.
The part the pattern does not solve
Ownership tracking keeps the counter honest. It does not make a failed procedure’s work disappear from a caller’s transaction.
When this procedure runs inside a caller’s transaction and hits an error, its changes stay in that open transaction. It has correctly declined to roll back, because rolling back would discard the caller’s work as well. What happens next is the caller’s decision, and the caller can only make it if the error reaches it, which is why the THROW matters.
Swallow the error in the CATCH block and you get the worst version: partial work sitting inside a transaction that the caller goes on to commit, believing everything succeeded.
If you need a procedure to undo only its own work while leaving the caller’s intact, the counter is the wrong tool. That is what savepoints are for, and they carry their own caveat: a savepoint cannot be used once the transaction has become uncommittable, which is the state most errors inside a TRY block leave it in.
Checking the state
Two things are worth knowing when a transaction misbehaves.
XACT_STATE() reports whether the current transaction can still be committed. It returns 1 for an active committable transaction, 0 for none, and -1 for one that has become uncommittable and can only be rolled back.[4] In a CATCH block that distinction matters more than the raw count.
The two do not agree, which is the point of checking both. A failed conversion inside a transaction gives this:
|
1 2 3 4 5 6 7 8 9 10 11 |
BEGIN TRANSACTION; BEGIN TRY SELECT [boom] = CONVERT(int, N'not-a-number'); END TRY BEGIN CATCH SELECT [error_number] = ERROR_NUMBER() , [xact_state] = XACT_STATE() , [trancount] = @@TRANCOUNT; END CATCH; |
|
1 2 3 |
error_number xact_state trancount ------------ ---------- --------- 245 -1 1 |
@@TRANCOUNT still says 1, so a test based on the counter alone concludes there is a live transaction to commit. XACT_STATE() says -1: it exists, but the only thing you can do with it is roll it back. Attempting a COMMIT there fails, and so does rolling back to a savepoint, with Msg 3931: the transaction cannot be committed and cannot be rolled back to a savepoint.
SET XACT_ABORT ON changes which errors terminate the transaction outright rather than leaving it open and uncommittable. It makes failure behaviour more predictable, and it is worth setting deliberately rather than inheriting whatever the connection happened to have.
The rule
Check @@TRANCOUNT at entry. Open a transaction only if none exists, record that you did, and commit or roll back only if you own it. Re-raise the error so the caller can make its own decision.
The habit costs three lines and a bit variable. Without it, a procedure that works in isolation becomes incorrect the first time somebody wraps a transaction around it, and that call site is usually somewhere you are not looking.
This is one of a series on the T-SQL conventions I actually use and why. The transaction guard here shares its @@TRANCOUNT check with The SET Succeeds and the Catalog Read Succeeds, Then Msg 3951.
Have you been caught by a nested COMMIT that did nothing? Bluesky or LinkedIn.
References
- @@TRANCOUNT (Transact-SQL) - Microsoft Learn. Defines the counter and how BEGIN, COMMIT, and ROLLBACK each affect it. ↩
- COMMIT TRANSACTION (Transact-SQL) - Microsoft Learn. States that an inner commit decrements the count and that only the outermost commit makes changes durable. ↩
- ROLLBACK TRANSACTION (Transact-SQL) - Microsoft Learn. Covers rolling back to the outermost transaction and the savepoint exception. ↩
- XACT_STATE (Transact-SQL) - Microsoft Learn. The committable, absent, and uncommittable states, and how they interact with XACT_ABORT. ↩