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