Lesson 6: Transactions in T-SQL (BEGIN/COMMIT/ROLLBACK Patterns)

Section 4 — Lesson 6: Transactions in T-SQL (BEGIN/COMMIT/ROLLBACK Patterns)

Welcome back. Transactions are one of the most important DBA concepts because they control
data integrity and concurrency.
Almost every production incident you will troubleshoot touches transactions in some way:
blocking chains, deadlocks, long-running reports holding locks, accidental partial updates,
and “why is tempdb exploding?”

In this lesson you will learn the practical transaction patterns DBAs use to protect production,
how COMMIT and ROLLBACK really behave, how to avoid “open transaction disasters,”
and how to safely combine transactions with TRY/CATCH.

Lesson description: You will learn what a transaction guarantees (ACID in practice),
the difference between autocommit and explicit transactions,
correct BEGIN/COMMIT/ROLLBACK patterns,
how to detect and avoid uncommitted transactions,
how @@TRANCOUNT and XACT_STATE() work,
what “nested transactions” actually mean in SQL Server,
how to use SAVEPOINTs,
and the DBA safety rules that prevent outages caused by transaction mistakes.

1) What a Transaction Guarantees (ACID in Real DBA Terms)

A transaction is a “unit of work” that SQL Server treats as all-or-nothing.
When you use transactions correctly, you get:

• Atomicity: Either all changes succeed, or none of them do.
• Consistency: Constraints and rules remain valid after the transaction.
• Isolation: Other sessions see safe, controlled visibility of changes.
• Durability: After commit, changes survive power loss (log-based recovery).

DBA reality: Most “data disasters” happen when someone runs changes without a safe transaction strategy,
or leaves transactions open, causing blocking and performance collapse.

2) Autocommit vs Explicit Transactions

2A) Autocommit (default behavior)

In SQL Server, if you run a single statement (INSERT/UPDATE/DELETE) without an explicit transaction,
SQL Server wraps it in an internal transaction and commits automatically if it succeeds.

This is fine for many single-statement operations, but DBAs often need multi-step operations
that must succeed together (or roll back together).

2B) Explicit transaction

An explicit transaction starts with BEGIN TRAN and ends with COMMIT or ROLLBACK.
This gives you control and safety for multi-step work.

3) The Basic Transaction Pattern (BEGIN / COMMIT)

This is the simplest transaction. If everything works, commit.

BEGIN TRAN;

-- Example (safe demo): read-only operations do not need a transaction,
-- but we show the pattern.
SELECT TOP (10) *
FROM sys.objects;

COMMIT TRAN;

DBA note: You generally do not wrap read-only queries in explicit transactions unless you need consistent reads
(and even then you usually choose an isolation strategy, discussed later).

4) The Rollback Pattern (BEGIN / ROLLBACK)

Use ROLLBACK when you detect unsafe conditions or need to undo changes.

BEGIN TRAN;

-- Example: pretend we did something risky

-- Safety stop (demo)
ROLLBACK TRAN;

DBA habit: When testing scripts in production-like environments, DBAs often run the script,
validate results, then ROLLBACK to prove the script would have done the intended work.
(You will learn this discipline more deeply in change-management sections.)

5) The #1 Transaction Disaster: Leaving an Open Transaction

An “open transaction” means you ran BEGIN TRAN but never committed or rolled back.
This can:

• hold locks for a long time
• block other sessions
• cause long blocking chains and timeouts
• grow the transaction log (log cannot truncate while a transaction remains active)

DBA rule: Every BEGIN TRAN must end with COMMIT or ROLLBACK (no exceptions).

6) @@TRANCOUNT: How Many Transactions Are “Open” in Your Session

SQL Server tracks transaction depth per session. The counter is @@TRANCOUNT.

• @@TRANCOUNT = 0 means you are not in an explicit transaction.
• @@TRANCOUNT > 0 means you have an active transaction scope.

6A) Demo pattern

SELECT @@TRANCOUNT AS TranCount_Before;

BEGIN TRAN;

SELECT @@TRANCOUNT AS TranCount_AfterBegin;

COMMIT TRAN;

SELECT @@TRANCOUNT AS TranCount_AfterCommit;

DBA caution: @@TRANCOUNT is useful, but it does not tell you if the transaction is committable.
For that, you use XACT_STATE().

7) XACT_STATE(): Is the Transaction Committable?

XACT_STATE() tells you the health of the transaction:

• 0 = no active transaction
• 1 = active transaction and it is committable
• -1 = active transaction but uncommittable (must ROLLBACK)

DBA rule: If XACT_STATE() = -1, do not attempt COMMIT.
ROLLBACK and investigate.

8) The Job-Ready Pattern: TRY/CATCH + Transaction

This is the standard DBA template for safe changes. You should memorize it.

SET NOCOUNT ON;

BEGIN TRY
    BEGIN TRAN;

    -- 1) Pre-flight checks
    -- (Example checks; customize per script)
    IF DB_NAME() IS NULL
        THROW 51001, 'No database context. Aborting.', 1;

    -- 2) Do the work
    -- Example placeholder:
    -- UPDATE dbo.Table SET ... WHERE ...
    -- DECLARE @Rows int = @@ROWCOUNT;

    -- 3) Validate results (guardrails)
    -- IF @Rows > 1000
    --     THROW 51002, 'Too many rows affected. Aborting.', 1;

    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRAN;

    SELECT
        ERROR_NUMBER()   AS ErrorNumber,
        ERROR_MESSAGE()  AS ErrorMessage,
        ERROR_LINE()     AS ErrorLine,
        ERROR_PROCEDURE() AS ErrorProcedure,
        @@TRANCOUNT      AS TranCountAfterError,
        XACT_STATE()     AS XactStateAfterError;

    THROW; -- bubble to job/caller
END CATCH;

DBA benefit: This ensures either everything commits safely, or everything rolls back and you get clean evidence.

9) “Nested Transactions” in SQL Server (What Actually Happens)

In SQL Server, you can execute BEGIN TRAN multiple times, increasing @@TRANCOUNT.
This looks like nested transactions, but there is a critical behavior:

• Only the outermost COMMIT truly commits changes.
• COMMIT decreases @@TRANCOUNT, but changes are not fully committed until @@TRANCOUNT returns to 0.
• A single ROLLBACK rolls back the entire transaction stack to @@TRANCOUNT = 0.

9A) Demo (do not use in production as-is)

SELECT @@TRANCOUNT AS TranCount_Before;

BEGIN TRAN;
SELECT @@TRANCOUNT AS TranCount_AfterBegin1;

BEGIN TRAN;
SELECT @@TRANCOUNT AS TranCount_AfterBegin2;

COMMIT TRAN;
SELECT @@TRANCOUNT AS TranCount_AfterCommit1;

COMMIT TRAN;
SELECT @@TRANCOUNT AS TranCount_AfterCommit2;

DBA takeaway: Nested BEGIN TRAN is often a symptom of layered code (procedures calling procedures).
DBAs must understand how rollback behaves across those layers.

10) SAVEPOINTs (SAVE TRAN): Partial Rollback Inside a Transaction

A savepoint lets you roll back part of a transaction without rolling back everything.
DBAs use savepoints in complex scripts where you might want to undo only the last step.

10A) Pattern

BEGIN TRAN;

-- Step 1 (safe work)
SAVE TRAN Step1Completed;

-- Step 2 (risky work)
-- If Step 2 fails, you can rollback to Step1Completed

-- ROLLBACK TRAN Step1Completed;  -- rolls back only to the savepoint
-- COMMIT TRAN;                  -- commits remaining work

ROLLBACK TRAN; -- demo cleanup

DBA caution: Savepoints do not reduce log usage the way a commit does.
They are control points, not “mini commits.”

11) Transaction Guardrails DBAs Use in Production

11A) “Dry-run” approach

DBAs commonly do:

• run the SELECT that identifies target rows
• confirm the exact rowset is correct
• run the UPDATE/DELETE inside a transaction
• verify rowcount and spot-check results
• COMMIT only when confident; otherwise ROLLBACK

11B) Always capture rowcounts

Rowcount is evidence and safety.

DECLARE @RowsAffected int;

BEGIN TRY
    BEGIN TRAN;

    -- Example placeholder
    -- UPDATE dbo.Table SET Col = ...
    -- WHERE ...;

    SET @RowsAffected = @@ROWCOUNT;

    IF @RowsAffected = 0
        THROW 51003, 'No rows affected. Aborting to avoid false success.', 1;

    IF @RowsAffected > 500
        THROW 51004, 'Too many rows affected. Aborting for safety.', 1;

    COMMIT TRAN;

    SELECT @RowsAffected AS RowsAffected, 'COMMIT' AS Outcome;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRAN;

    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

12) Practice (Job-Ready Exercises)

Save each script to your DBA script pack:

1. Write a transaction template that prints/returns @@TRANCOUNT before and after BEGIN/COMMIT.
2. Create a TRY/CATCH transaction wrapper template with rollback in CATCH and THROW.
3. Add a rowcount guardrail pattern: abort if 0 rows or more than a max threshold.
4. Create a “dry-run” script: first SELECT the target rows, then do a simulated transaction and ROLLBACK (practice discipline).
5. Write a small savepoint template (SAVE TRAN + rollback to savepoint) and document when you would use it.

Lesson Summary

• Transactions protect data integrity by making changes all-or-nothing.
• Open transactions cause blocking and log growth—DBAs must avoid them.
• @@TRANCOUNT tracks transaction depth; XACT_STATE tells if it is committable.
• TRY/CATCH + explicit transactions is the job-ready pattern for safe production changes.
• SQL Server “nested transactions” are not fully independent; a rollback can unwind everything.
• SAVEPOINTs provide controlled partial rollback inside a larger transaction.

Next Lesson: Stored Procedures — Parameters, Output, Return Codes

Next, you will learn how DBAs design stored procedures for operational work:
validating inputs, returning consistent status codes, capturing rowcounts, and writing procedures that are safe to run repeatedly.