Transactions & ACID

A transaction is a group of SQL statements that SQL Server treats as a single indivisible unit of work. Either every statement in the group succeeds and their changes are permanently saved, or something goes wrong and every change made by every statement in the group is undone — leaving the database exactly as it was before the transaction started. This guarantee is not optional or approximate: it is the foundational promise that makes relational databases trustworthy for financial systems, order processing, inventory management, and any other domain where partial success is worse than complete failure. The four properties that define this promise — Atomicity, Consistency, Isolation, and Durability — are known collectively as ACID, and SQL Server enforces all four for every transaction. Understanding how to write and manage transactions correctly is one of the most important practical skills in T-SQL.

ACID Properties

ACID is not a feature you turn on — it is a set of guarantees that SQL Server provides by design for every transaction. Atomicity means the transaction is all or nothing: if any statement fails, all previous statements in the transaction are rolled back. Consistency means the database moves from one valid state to another valid state — all constraints, rules, and cascades are enforced at commit time. Isolation means concurrent transactions do not see each other's uncommitted changes — each transaction appears to run in isolation even when hundreds are running simultaneously. Durability means once a transaction commits, its changes are permanent — even a power failure immediately after the commit will not lose the data, because SQL Server writes to the transaction log before writing to data pages.

-- Demonstrate each ACID property with DataplexaStore examples
USE DataplexaStore;

-- ATOMICITY — place an order with its line items as one atomic unit
-- Either the order AND all items are inserted, or nothing is

BEGIN TRANSACTION;

    DECLARE @NewOrderID INT;

    INSERT INTO Orders (UserID, TotalAmount, Status)
    VALUES (2, 0.00, 'processing');          -- placeholder total, updated after items

    SET @NewOrderID = SCOPE_IDENTITY();

    INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
    VALUES (@NewOrderID, 1, 2, 29.69),       -- 2x Wireless Mouse
           (@NewOrderID, 3, 1, 44.99);       -- 1x USB-C Hub

    -- Recalculate and update the order total from its actual line items
    UPDATE Orders
    SET    TotalAmount = (
               SELECT SUM(Quantity * UnitPrice)
               FROM   OrderItems
               WHERE  OrderID = @NewOrderID
           )
    WHERE  OrderID = @NewOrderID;

COMMIT TRANSACTION;

-- Verify the committed order
SELECT o.OrderID, o.TotalAmount, o.Status,
       oi.ProductID, oi.Quantity, oi.UnitPrice
FROM   Orders     o
JOIN   OrderItems oi ON o.OrderID = oi.OrderID
WHERE  o.OrderID = @NewOrderID;

• All three statements — INSERT into Orders, INSERT into OrderItems, and UPDATE Orders — are wrapped in one transaction; if any one of them had failed (a FK violation, a constraint breach, a network drop) the ROLLBACK would have unwound all three, leaving no partial order in the database
• TotalAmount = 104.37 is (2 × 29.69) + (1 × 44.99) = 59.38 + 44.99 = 104.37 — calculated from the actual inserted line items, ensuring the stored total is always consistent with its line items
• SCOPE_IDENTITY() inside the transaction correctly returns the new OrderID — it is transaction-aware and returns values from the current scope even before the transaction commits

Explicit vs Implicit Transactions

SQL Server runs in autocommit mode by default — every single statement is automatically its own transaction that commits immediately on success or rolls back on failure. This is an implicit transaction, and it is what happens when you run INSERT, UPDATE, or DELETE without any explicit BEGIN TRANSACTION. Explicit transactions are those you open manually with BEGIN TRANSACTION and close with COMMIT or ROLLBACK. They are essential whenever you need multiple statements to succeed or fail together. A third mode, SET IMPLICIT_TRANSACTIONS ON, makes SQL Server automatically open a transaction at the start of certain statements and require an explicit COMMIT or ROLLBACK — this mode is rarely used and is mentioned here only because you may encounter it in legacy code.

-- Autocommit vs explicit transaction — behavioural difference

-- AUTOCOMMIT (default) — each statement is its own transaction
-- This UPDATE commits immediately even with no BEGIN TRANSACTION
UPDATE Products SET Price = ROUND(Price * 1.01, 2) WHERE ProductID = 1;
-- If this line errors, the UPDATE above is already committed — no rollback possible

-- EXPLICIT TRANSACTION — control commit/rollback manually
BEGIN TRANSACTION;

    UPDATE Products SET Price = ROUND(Price * 1.01, 2) WHERE ProductID = 2;
    UPDATE Products SET Price = ROUND(Price * 1.01, 2) WHERE ProductID = 3;

    -- Check @@TRANCOUNT — confirms we are inside an open transaction
    SELECT @@TRANCOUNT AS open_transactions;   -- returns 1

    -- Decide to keep the changes
    COMMIT TRANSACTION;

-- After COMMIT, @@TRANCOUNT returns to 0
SELECT @@TRANCOUNT AS open_transactions;       -- returns 0

-- Verify the changes
SELECT ProductID, ProductName, Price
FROM   Products
WHERE  ProductID IN (1, 2, 3)
ORDER BY ProductID;

• @@TRANCOUNT is SQL Server's open transaction counter — it increments by 1 for each BEGIN TRANSACTION and decrements by 1 for each COMMIT; when it reaches 0 the outermost transaction has committed and changes are permanent
• Wireless Mouse shows 29.99 rather than the accumulated price from earlier lessons because the autocommit UPDATE above ran as its own transaction and committed — autocommit makes every statement independently permanent
• The practical rule is: any operation that requires more than one statement to be correct must be wrapped in an explicit transaction; autocommit is fine for standalone single-statement DML

ROLLBACK — Undoing a Transaction

ROLLBACK TRANSACTION undoes every change made since the most recent BEGIN TRANSACTION, restoring the database to exactly the state it was in when the transaction started. ROLLBACK is used when an error is detected, when a validation check fails, when @@ROWCOUNT does not match the expected number, or when a deliberate decision is made to abandon a set of changes. After a ROLLBACK @@TRANCOUNT returns to 0 and the transaction is closed — the data pages are restored from the transaction log and any locks held by the transaction are released. ROLLBACK does not require you to specify what to undo — SQL Server tracks everything the transaction touched and reverses it all.

-- ROLLBACK — demonstrate complete undo of all statements in the transaction

BEGIN TRANSACTION;

    -- Make changes that we will later decide to roll back
    UPDATE Products SET StockQty = 0    WHERE Category = 'Electronics';
    DELETE FROM Reviews WHERE Rating < 5;

    -- Inspect the uncommitted state inside the transaction
    SELECT ProductName, StockQty FROM Products WHERE Category = 'Electronics';
    SELECT COUNT(*) AS remaining_reviews FROM Reviews;

    -- Decision: these changes are wrong — roll back everything
    ROLLBACK TRANSACTION;

-- Verify the database is restored to its pre-transaction state
SELECT ProductName, StockQty FROM Products WHERE Category = 'Electronics' ORDER BY ProductName;
SELECT COUNT(*) AS remaining_reviews FROM Reviews;

• Inside the transaction the UPDATE and DELETE changes are visible to the current session — the uncommitted rows appear with StockQty = 0 and only 3 reviews; after ROLLBACK every change is completely reversed
• ROLLBACK is immediate and complete — there is no partial undo, no choice about which statements to reverse; the entire transaction is unwound at once using the before-images stored in the transaction log
• Other sessions running concurrently with the default READ COMMITTED isolation level would never have seen the zeroed stock or the deleted review — the changes were never committed, so they were never visible outside the transaction that made them

SAVE TRANSACTION — Partial Rollback with Savepoints

A savepoint marks a named point within a transaction that you can roll back to without abandoning the entire transaction. SAVE TRANSACTION name sets the marker. ROLLBACK TRANSACTION name rolls back to that marker — undoing everything after the savepoint but preserving everything before it. The transaction remains open after a partial rollback to a savepoint, and must still be committed or fully rolled back. Savepoints are useful for complex multi-step operations where one step failing should undo only that step while preserving successful earlier work — for example processing a batch of orders where one invalid order should not cancel the valid ones already processed.

-- SAVE TRANSACTION — partial rollback to a named savepoint

BEGIN TRANSACTION;

    -- Step 1: update Bob's membership tier — this step is correct
    UPDATE Users SET MembershipTier = 'VIP' WHERE UserID = 2;

    SAVE TRANSACTION AfterMembershipUpdate;  -- savepoint after step 1

    -- Step 2: attempt a price update that turns out to be wrong
    UPDATE Products SET Price = 0 WHERE Category = 'Stationery';  -- accidental zero price

    -- Detect the problem and roll back only to the savepoint — not the whole transaction
    ROLLBACK TRANSACTION AfterMembershipUpdate;

    -- Step 1 is still in effect — only step 2 was undone
    -- Optionally continue with a corrected step 2
    UPDATE Products SET Price = ROUND(Price * 0.95, 2) WHERE Category = 'Stationery';

COMMIT TRANSACTION;

-- Verify: Bob is VIP and Stationery prices are reduced 5% (not zeroed)
SELECT UserID, FullName, MembershipTier FROM Users  WHERE UserID = 2;
SELECT ProductName, Price               FROM Products WHERE Category = 'Stationery';

• ROLLBACK TRANSACTION AfterMembershipUpdate undid the zero-price UPDATE but left Bob's membership change intact — the savepoint allowed surgical undo without abandoning the entire transaction
• @@TRANCOUNT remains 1 after a savepoint rollback — the transaction is still open; only a COMMIT or a full ROLLBACK (without a savepoint name) closes it
• Savepoints are an advanced pattern used in stored procedures that call other stored procedures — each inner procedure can roll back its own work to a savepoint without killing the outer transaction

TRY...CATCH with Transactions

SQL Server's TRY...CATCH block is the correct error handling pattern for transactional code. Statements inside the TRY block run normally; if any error occurs SQL Server jumps immediately to the CATCH block. Inside CATCH, the transaction may still be open and must be explicitly rolled back — SQL Server does not automatically roll back uncommitted transactions when an error occurs and execution passes to CATCH. The XACT_STATE() function reveals the transaction's current state: 1 means the transaction is active and committable, -1 means it is active but doomed (uncommittable, must be rolled back), and 0 means no transaction is open. Checking XACT_STATE() before deciding to ROLLBACK or COMMIT is the correct defensive pattern.

-- TRY...CATCH with transaction — the production-safe error handling pattern

BEGIN TRANSACTION;

BEGIN TRY

    -- Insert a valid order
    INSERT INTO Orders (UserID, TotalAmount, Status)
    VALUES (3, 159.99, 'processing');

    DECLARE @OrderID INT = SCOPE_IDENTITY();

    -- This will fail — ProductID 999 does not exist (FK violation)
    INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice)
    VALUES (@OrderID, 999, 1, 159.99);    -- Msg 547: FK constraint violated

    COMMIT TRANSACTION;                   -- never reached if error occurs above

END TRY
BEGIN CATCH

    -- Check transaction state before rolling back
    IF XACT_STATE() <> 0                  -- -1 (doomed) or 1 (open) — both need rollback
        ROLLBACK TRANSACTION;

    -- Report the error details
    SELECT
        ERROR_NUMBER()    AS error_number,
        ERROR_SEVERITY()  AS severity,
        ERROR_MESSAGE()   AS error_message,
        ERROR_LINE()      AS error_line;

END CATCH;

-- Confirm no partial order was committed
SELECT COUNT(*) AS orders_for_user_3 FROM Orders WHERE UserID = 3;

• The FK violation on ProductID 999 triggered the CATCH block — XACT_STATE() returned -1 (the transaction was doomed by the error) and ROLLBACK correctly cleaned up both the failed INSERT and the successful INSERT before it
• orders_for_user_3 = 1 because Clara Davis (UserID 3) had one pre-existing order from the seed data — the failed transaction's incomplete order was fully rolled back and does not appear
• ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE(), and ERROR_SEVERITY() are only valid inside a CATCH block — they return the details of the error that triggered the CATCH, making structured error logging straightforward

Summary Table

Practice Questions

Quiz

Next up - Locks & Concurrency - Understand how SQL Server controls simultaneous access to data, what blocking is, and how isolation levels change what your queries can see.