Narcis Miclaus

7 min read

SQL Server, from the ground up Lesson 18 / 40

Transactions and the ACID properties

What atomic, consistent, isolated, and durable really mean. BEGIN TRAN, COMMIT, ROLLBACK, SAVEPOINT, and the error-handling patterns that keep data honest.

A transaction is a group of database operations that either all happen or none happen. That’s it. That’s the whole idea.

And yet the word “transaction” hides the reason every serious business runs on an RDBMS. When Runehold’s customer checks out — payment processed, order created, inventory decremented, shipping address written, confirmation email queued — every one of those operations needs to succeed, or none of them should. You don’t want money charged and no order. You don’t want an order with no inventory decrement. You don’t want a half-done checkout.

This is what transactions solve.

BEGIN, COMMIT, ROLLBACK

Three keywords. Learn them forever.

BEGIN TRANSACTION;         -- or BEGIN TRAN, same thing

    UPDATE Inventory.Stock
    SET Quantity = Quantity - 1
    WHERE ProductId = @product AND WarehouseId = @warehouse;

    INSERT INTO Sales.Orders (CustomerId, Total, CountryCode, VatRate)
    VALUES (@customer, @total, @country, @vat);

    INSERT INTO Shipping.Shipment (OrderId, Address)
    VALUES (SCOPE_IDENTITY(), @address);

COMMIT TRANSACTION;

If any statement inside the BEGIN/COMMIT block fails (or you explicitly call ROLLBACK), everything inside it is undone. The database state is exactly as if the whole thing had never started.

Without a BEGIN TRAN, every statement is its own implicit transaction. That’s fine for single-row operations but catastrophic when you need several statements to stay in sync.

ACID, one letter at a time

A — Atomic

“All or nothing.” The transaction is indivisible. Either every change inside it is visible to the outside world, or none are.

If the server crashes mid-commit — the light in the server room blinks, the power fails, the container is killed — SQL Server’s write-ahead log ensures that on restart, any transaction that wasn’t committed is rolled back. You never see half-applied transactions.

C — Consistent

The database moves from one valid state to another valid state. Foreign keys stay valid, check constraints hold, unique constraints aren’t violated. Your business rules — enforced by constraints — are always true at the end of a transaction.

Note: SQL’s “consistency” is about the schema’s rules, not distributed-systems consistency (CAP theorem). Different word, same letters.

I — Isolated

Concurrent transactions don’t step on each other. Reads from one transaction don’t see writes from another that haven’t committed yet. This is more complex than it sounds — lesson 19 covers the different isolation levels you can choose from.

D — Durable

Once a transaction is committed, it stays committed. Even if the server crashes immediately after the COMMIT. The commit writes to the transaction log, which is flushed to disk before COMMIT returns. “Durable” means “on disk, not just in RAM.”

SQL Server has an optional delayed durability mode that weakens this — commits return before the log is flushed. Faster, risky. Default is full durability.

Explicit error handling: TRY/CATCH

Without error handling, a statement inside BEGIN TRAN that fails can leave your transaction in a weird state. The pattern you want:

BEGIN TRY
    BEGIN TRANSACTION;

        UPDATE Inventory.Stock SET Quantity = Quantity - 1 WHERE ...;
        IF @@ROWCOUNT = 0
            THROW 50001, 'Not enough stock', 1;

        INSERT INTO Sales.Orders ...;
        INSERT INTO Shipping.Shipment ...;

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

    -- Log the error, re-raise, or handle however your app expects
    THROW;
END CATCH;

Structure to internalize:

  • BEGIN TRY / END TRY wraps the statements that might fail.
  • BEGIN CATCH / END CATCH runs on any error inside the TRY block.
  • XACT_STATE() returns 0 (no transaction), 1 (active, can commit), or -1 (active but uncommittable — some errors leave the transaction in a doomed state).
  • THROW without arguments re-raises the caught error. Use it to propagate errors to the caller unchanged.

Every stored procedure that modifies more than one table at Runehold uses this pattern. It’s the price of reliability.

@@TRANCOUNT and nested transactions

SELECT @@TRANCOUNT;     -- 0 outside any transaction, 1 inside

Important: SQL Server doesn’t really have nested transactions. BEGIN TRAN inside an active transaction increments @@TRANCOUNT; a COMMIT decrements it. Only when @@TRANCOUNT hits zero does anything actually commit.

BEGIN TRAN;          -- @@TRANCOUNT = 1
    BEGIN TRAN;      -- @@TRANCOUNT = 2, no real effect
        ...
    COMMIT;          -- @@TRANCOUNT = 1, still not committed
    ROLLBACK;        -- everything rolls back, @@TRANCOUNT = 0

ROLLBACK rolls back the entire transaction stack, not just one level. If you call a stored procedure that does BEGIN TRAN; ... ROLLBACK;, it rolls back your outer transaction too. This is surprising and the reason stored procedures that manage transactions need careful design.

The common pattern: stored procs detect whether they’re inside a transaction and only start their own if they aren’t:

CREATE PROCEDURE Sales.CreateOrder ...
AS
BEGIN
    DECLARE @nested INT = CASE WHEN @@TRANCOUNT = 0 THEN 0 ELSE 1 END;
    IF @nested = 0 BEGIN TRANSACTION;

    BEGIN TRY
        ... work ...
        IF @nested = 0 COMMIT;
    END TRY
    BEGIN CATCH
        IF @nested = 0 AND XACT_STATE() <> 0 ROLLBACK;
        THROW;
    END CATCH;
END;

Ugly but correct. For new code, keep transactions at the top level (the application or the outer proc) and let inner procs inherit.

SAVEPOINT: partial rollback

A savepoint is a named checkpoint inside a transaction. You can roll back to it without rolling back the whole transaction:

BEGIN TRAN;
    UPDATE Sales.Customer SET ... WHERE ...;

    SAVE TRAN step1;

    UPDATE Sales.Orders SET ... WHERE ...;

    IF @someCondition = 0
        ROLLBACK TRAN step1;    -- undoes the Orders update, keeps the Customer update

COMMIT;

Rarely needed in day-to-day code. Useful inside large data-loading jobs where some sections of the work are optional.

Implicit vs explicit transactions

SQL Server defaults to autocommit mode: every individual statement is automatically committed. Writing UPDATE Customer SET Name = 'X' with no BEGIN TRAN still runs inside an implicit transaction that commits the moment the statement returns.

Some older codebases enable SET IMPLICIT_TRANSACTIONS ON per session, which means any data-modifying statement starts a transaction that stays open until you explicitly COMMIT or ROLLBACK. Easy to forget to commit. Easy to hold locks for hours. Easy to bring down a production workload.

Recommendation: leave IMPLICIT_TRANSACTIONS off (the default). Start your transactions explicitly with BEGIN TRAN. End them explicitly with COMMIT or ROLLBACK.

The “long transaction” problem

A transaction holds locks from the moment it acquires them until it commits. The longer a transaction is open, the longer those locks are held, the more other sessions pile up waiting.

Anti-patterns:

  • Doing a slow network call to an external service inside a transaction. The service hangs; your transaction is open for 30 seconds; everyone waits.
  • Holding a transaction open while you fetch parameters from the user (“click Confirm”). User goes for coffee; lock is held for an hour.
  • Big batch updates inside one transaction. Ten-minute lock on half the table.

Rules:

  1. Keep transactions short. Ideally milliseconds.
  2. Do expensive work outside the transaction. Computations, API calls, file reads — do them first, then BEGIN TRAN for the data change.
  3. Batch big operations. As shown in lesson 17, UPDATE TOP (10000) in a loop keeps each transaction short.

Return values and row counts

@@ROWCOUNT returns the number of rows affected by the previous statement. Reset after each statement. Always capture it immediately if you need it:

UPDATE Sales.Orders SET Status = 2 WHERE OrderId = @id;
DECLARE @rc INT = @@ROWCOUNT;

IF @rc = 0
    THROW 50002, 'Order not found', 1;

The canonical use: verify that your UPDATE actually touched the row you expected. Useful for optimistic concurrency (“update only if the version matches”).

Run this on your own machine

USE Runehold;
GO

-- 1. The safety drill: BEGIN TRAN, inspect, ROLLBACK
BEGIN TRAN;
    DELETE FROM Sales.Orders WHERE Total < 10;
    SELECT @@ROWCOUNT AS would_delete;
ROLLBACK;
-- Rows are back. No harm done. This is the habit to build.

-- 2. TRY/CATCH with a controlled failure
BEGIN TRY
    BEGIN TRAN;
        -- This will succeed
        UPDATE Sales.Customer SET Country = 'XX' WHERE CustomerId = 1;

        -- This will fail: Country is CHAR(2), 'TOOLONG' doesn't fit
        UPDATE Sales.Customer SET Country = 'TOOLONG' WHERE CustomerId = 2;

    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    SELECT
        ERROR_NUMBER()  AS err_num,
        ERROR_MESSAGE() AS err_msg,
        ERROR_LINE()    AS err_line;
END CATCH;

-- Verify CustomerId 1's country went back to what it was
SELECT CustomerId, Country FROM Sales.Customer WHERE CustomerId IN (1, 2);

-- 3. Long transaction anti-pattern (don't do this in real code!)
BEGIN TRAN;
    UPDATE Sales.Orders SET Notes = 'holding locks' WHERE OrderId = 1;
    -- Imagine a 30-second API call here. Other sessions trying to touch
    -- OrderId 1 will block until we commit. DON'T DO THIS.
    WAITFOR DELAY '00:00:03';
COMMIT;

-- 4. SAVEPOINT example
BEGIN TRAN;
    UPDATE Sales.Customer SET Country = 'NL' WHERE CustomerId = 1;

    SAVE TRAN checkpoint_a;

    UPDATE Sales.Customer SET Country = 'ZZ' WHERE CustomerId = 1;

    -- Undo only the second update, not the first
    ROLLBACK TRAN checkpoint_a;

    SELECT Country FROM Sales.Customer WHERE CustomerId = 1;
COMMIT;

-- 5. @@ROWCOUNT verification for optimistic concurrency
DECLARE @expectedVersion INT = 42;
UPDATE Sales.Customer SET Name = N'New Name' WHERE CustomerId = 1 AND Version = @expectedVersion;
IF @@ROWCOUNT = 0
    RAISERROR('Version mismatch or row not found', 16, 1);

Next lesson: isolation levels. Why your queries sometimes see weirdly-inconsistent views of data, how to fix that (without killing throughput), and the secret life of READ COMMITTED SNAPSHOT.

Search