Explicit/Implicit Transactions, XACT_STATE, TRY/CATCH, Savepoints, and Error Semantics

Learning outcomes

ServiceHub now updates inventory and audit data as one business action. A constraint error in the second statement raises a practical question: did SQL Server undo only that statement, the whole transaction, or nothing yet? This lesson replaces assumptions with session-visible transaction state. The core distinction is that @@TRANCOUNT reports transaction nesting count, while XACT_STATE() reports whether an active user transaction is committable.

1. Autocommit does not mean “no transaction”

With IMPLICIT_TRANSACTIONS OFF, a normal DML statement executed outside an explicit transaction is automatically committed or rolled back as a statement-sized transaction. Explicit transactions group statements under a caller-controlled boundary. With IMPLICIT_TRANSACTIONS ON, certain statements start a transaction automatically and the session must explicitly commit or roll it back. Because this setting is session scoped, client code that changes it must restore the intended state.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab07') IS NULL EXEC(N'CREATE SCHEMA lab07 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab07.WorkAudit;DROP TABLE IF EXISTS lab07.Inventory;GOCREATE TABLE lab07.Inventory(  part_id int NOT NULL CONSTRAINT PK_lab07_Inventory PRIMARY KEY,  on_hand int NOT NULL CONSTRAINT CK_lab07_Inventory CHECK (on_hand >= 0));CREATE TABLE lab07.WorkAudit(  audit_id bigint IDENTITY PRIMARY KEY,  work_order_id bigint NOT NULL,  note nvarchar(200) NOT NULL);INSERT lab07.Inventory(part_id,on_hand) VALUES (10,5),(20,2);SELECT @@TRANCOUNT AS trancount, XACT_STATE() AS xact_state;GO

At a clean session boundary the expected pair is 0, 0. Both values are session scoped. If you see an unexpected positive count before a request begins, stop and determine who owns that transaction rather than blindly committing it.

2. @@TRANCOUNT and XACT_STATE answer different questions

Each BEGIN TRANSACTION increments @@TRANCOUNT. An ordinary inner COMMIT decrements it but does not make inner work independently durable. A full rollback without a savepoint name resets the transaction. A savepoint records a rollback target inside the current transaction; rolling back to it does not end the outer transaction. XACT_STATE() returns 1 for an active committable transaction, -1 for active but uncommittable, and 0 for none.

BEGIN TRANSACTION;SAVE TRANSACTION BeforeAudit;INSERT lab07.WorkAudit(work_order_id,note) VALUES (7001,N'draft');SELECT @@TRANCOUNT AS before_rollback, XACT_STATE() AS state_before;ROLLBACK TRANSACTION BeforeAudit;SELECT @@TRANCOUNT AS after_savepoint_rollback, XACT_STATE() AS state_after;COMMIT TRANSACTION;SELECT @@TRANCOUNT AS after_commit, XACT_STATE() AS final_state;GO

The savepoint rollback reverses the inserted audit row but leaves the outer transaction active. This pattern can help when a recoverable sub-operation is optional, but it should not be used to fake autonomous nested transactions.

3. Deliberately create and diagnose an uncommittable transaction

Error behavior depends on context. With SET XACT_ABORT ON, many runtime errors terminate the transaction and can leave it uncommittable while control is inside a CATCH. That is why production error handlers should test transaction state rather than issuing an unconditional commit.

SET XACT_ABORT ON;BEGIN TRY  BEGIN TRANSACTION;  UPDATE lab07.Inventory SET on_hand=on_hand-1 WHERE part_id=10;  UPDATE lab07.Inventory SET on_hand=-1 WHERE part_id=20; -- CHECK violation  COMMIT TRANSACTION;END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS error_number,         ERROR_MESSAGE() AS error_message,         @@TRANCOUNT AS trancount,         XACT_STATE() AS xact_state;  IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;END CATCH;SET XACT_ABORT OFF;SELECT * FROM lab07.Inventory ORDER BY part_id;GO

The inventory should return to its original values after rollback. The diagnostic output inside the catch is the important evidence. Compile-time errors at the same execution level and severe connection failures have different catchability rules, so do not generalize one demonstration to every possible error.

4. Session reuse makes transaction cleanup an application contract

Long-lived connections and pools are designed for reuse. Standard providers usually reset pooled sessions, but application correctness should still own the transaction lifecycle explicitly. A session that remains open with a transaction can retain locks and log-reuse dependencies and make later work appear mysteriously blocked. Dispose/rollback paths, request correlation IDs, and transaction-age monitoring are operational safeguards.

SELECT s.session_id,s.login_name,       at.transaction_begin_time,at.transaction_state,at.transaction_typeFROM sys.dm_tran_session_transactions AS stJOIN sys.dm_tran_active_transactions AS at ON at.transaction_id=st.transaction_idJOIN sys.dm_exec_sessions AS s ON s.session_id=st.session_idWHERE s.is_user_process=1ORDER BY at.transaction_begin_time;GO

Server-wide DMV visibility is permission dependent. On SQL Server 2022 and later, many performance DMVs require VIEW SERVER PERFORMANCE STATE. Record that permission context when collecting evidence.

5. Production judgment

Keep transaction scope as short as correctness permits, but never split one atomic business invariant simply to make blocking disappear. Define whether transaction ownership belongs to the stored procedure or application, whether XACT_ABORT ON is expected, what gets retried, and what cleanup happens after cancellation. The rollback plan for this lab is the explicit rollback paths above and, after the chapter, dropping lab07.

Authoritative references

• XACT_STATE — committable state
• @@TRANCOUNT — nesting count
• SET XACT_ABORT — runtime-error behavior
• TRY/CATCH — error handling
• SAVE TRANSACTION — savepoints
• SQL Server 2025 builds — CU baseline