ACID Transactions in SQL Server: BEGIN, COMMIT, ROLLBACK Explained

You’ve been using BEGIN TRANSACTION, COMMIT, and ROLLBACK since Chapter 4’s stored procedure pattern, largely as boilerplate to copy. This lesson is about the actual guarantee underneath that boilerplate — four properties that guarantee your data stays correct even when things go wrong mid-operation, not just “it undoes things on error.”

The Four Properties

The Classic Transfer

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.BankAccount SET balance = balance - 1000 WHERE account_id = 1;
    UPDATE dbo.BankAccount SET balance = balance + 1000 WHERE account_id = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

What Happens Without a Transaction

Without wrapping both updates in one transaction, exactly this failure is possible — not hypothetically, but as a genuine risk any time two related writes happen as separate statements. That’s the specific problem transactions exist to prevent, and it’s Atomicity specifically doing the protecting here: SQL Server guarantees that if the crash happens after the first UPDATE but before the second, the entire transaction rolls back on recovery, leaving neither account touched.

Proving It Yourself

-- Deliberately introduce a failure between the two updates to watch Atomicity work
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE dbo.BankAccount SET balance = balance - 1000 WHERE account_id = 1;
    SELECT 1/0; -- deliberate error, simulates a crash mid-transfer
    UPDATE dbo.BankAccount SET balance = balance + 1000 WHERE account_id = 2;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH;

SELECT balance FROM dbo.BankAccount WHERE account_id IN (1,2); -- both unchanged, no money lost

Diagnosing Blocking in SQL Server: Finding Who’s Blocking Whom

A deadlock (previous lesson) is SQL Server actively resolving an impossible situation by killing one transaction. Ordinary blocking is different and far more common: one session simply waiting its turn for a lock another session holds, which resolves on its own once the first session finishes — no error, no victim, just a delay. Not every case of one session waiting on another is a bug; moderate, short-lived blocking is normal under real concurrent load. Here’s how to tell when it’s actually a problem.

Finding the Blocker

SELECT
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocked.wait_type,
    blocked.wait_time,
    blocked_text.text AS blocked_query
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_sessions blocking ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
WHERE blocked.blocking_session_id <> 0;

This is the same blocking_session_id column flagged as the single most actionable field in Chapter 8’s DMV lesson — this query is that pointer, fully realized into a real diagnostic report.

The Real Root Cause, Most of the Time

It becomes a genuine problem when a transaction holds locks far longer than necessary. The fix is almost always “keep transactions as short as possible” — not “add more indexes” or “increase timeout,” which just makes users wait longer for a symptom instead of fixing the actual cause.

-- The specific anti-pattern that causes most real-world blocking incidents:
BEGIN TRANSACTION;
UPDATE dbo.Order SET status = 'processing' WHERE order_id = 500;
-- ...application code here calls an external API, waits on a user click,
-- or does anything else slow, all while the transaction (and its locks) stays open...
COMMIT TRANSACTION; -- doesn't happen until that slow thing finishes

-- The fix: do all slow, non-database work BEFORE or AFTER the transaction,
-- never DURING it. Keep the window between BEGIN and COMMIT as short as possible.

Chapter 9, End to End

These three lessons form one continuous story: ACID (Lesson 1) is the guarantee; isolation levels (Lesson 2) are the tunable dial controlling how strictly “Isolation” is enforced, with deadlocks as the sharp edge of getting concurrent access patterns wrong; and blocking (this lesson) is the everyday, non-error version of the same underlying mechanism — locks doing their job, just visible when they hold longer than expected.