What Is a Transaction?

Transactions are the foundation of data integrity in relational databases. They guarantee that complex operations — such as transferring money between accounts — either complete entirely or not at all, even in the event of system failures.

-- A classic bank transfer as a single transaction
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1001;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2002;

COMMIT;

The ACID Properties

BEGIN, COMMIT, and ROLLBACK

Every transaction starts with BEGIN and ends with either COMMIT (success) or ROLLBACK (failure).

-- Start a transaction
BEGIN TRANSACTION;

-- Perform operations
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (42, 7, 3, GETDATE());

UPDATE inventory SET stock = stock - 3 WHERE product_id = 7;

-- Verify before committing
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM inventory WHERE product_id = 7;

-- If everything looks correct
COMMIT;

-- If something went wrong
-- ROLLBACK;

-- Error handling with transactions
BEGIN TRANSACTION;

BEGIN TRY
    UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
    UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
    INSERT INTO transfer_log (from_id, to_id, amount, transfer_date)
    VALUES (1, 2, 1000, GETDATE());
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    THROW;
END CATCH;

SAVEPOINT for Partial Rollbacks

BEGIN TRANSACTION;

INSERT INTO customers (name, email) VALUES ('Alice', 'alice@example.com');
SAVEPOINT after_customer_insert;

INSERT INTO orders (customer_id, product_id, quantity)
VALUES (SCOPE_IDENTITY(), 5, 2);

-- Oops, wrong product — roll back only the order
ROLLBACK TO SAVEPOINT after_customer_insert;

-- Customer insert is still intact — insert correct order
INSERT INTO orders (customer_id, product_id, quantity)
VALUES (SCOPE_IDENTITY(), 8, 2);

COMMIT;

Transaction Isolation Levels

Different isolation levels balance consistency against concurrency. Higher isolation reduces anomalies but decreases performance.

-- Set isolation level for a session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

-- This transaction now runs with full isolation
SELECT * FROM products WHERE category = 'Electronics';
-- No phantom reads allowed — other transactions cannot insert
-- new Electronics rows while this transaction is active

UPDATE products SET price = price * 0.9 WHERE category = 'Electronics';
COMMIT;

-- Or set it per-transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM inventory WHERE product_id = 42;
COMMIT;

Autocommit Behavior

Most database clients operate in autocommit mode by default, where each individual statement is implicitly treated as its own transaction.

-- Autocommit (default in most clients)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- This is committed immediately

-- Explicit transaction gives you control
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Not yet committed — can still be rolled back
ROLLBACK;
-- The UPDATE is undone

Nested Transactions

SQL Server supports nested BEGIN TRANSACTION calls, but only the outermost COMMIT actually persists changes. Inner commits merely decrement the transaction nesting level.

BEGIN TRANSACTION;          -- nesting level = 1
    INSERT INTO logs (message) VALUES ('Step 1');

    BEGIN TRANSACTION;      -- nesting level = 2
        INSERT INTO logs (message) VALUES ('Step 2');
    COMMIT;                 -- nesting level = 1 (no real commit)

    BEGIN TRANSACTION;      -- nesting level = 2
        INSERT INTO logs (message) VALUES ('Step 3');
    ROLLBACK;               -- undoes Step 3 only, nesting = 1

COMMIT;                     -- commits Step 1 and Step 2

Practical Example: Order Processing

BEGIN TRANSACTION;

-- Step 1: Create the order
INSERT INTO orders (customer_id, order_date, status)
VALUES (101, GETDATE(), 'PENDING');

DECLARE @order_id INT = SCOPE_IDENTITY();

-- Step 2: Add order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (@order_id, 10, 2, 29.99),
       (@order_id, 15, 1, 49.99);

-- Step 3: Update inventory
UPDATE inventory SET stock = stock - 2 WHERE product_id = 10;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 15;

-- Step 4: Verify stock is not negative
IF EXISTS (SELECT 1 FROM inventory WHERE stock < 0)
BEGIN
    RAISERROR('Insufficient stock', 16, 1);
    ROLLBACK;
    RETURN;
END

-- Step 5: Update order total
UPDATE orders
SET total = (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = @order_id)
WHERE order_id = @order_id;

COMMIT;