SQL Transactions — ACID, COMMIT, ROLLBACK, and SAVEPOINT

Why transactions exist

Imagine transferring £100 from Alice's account to Bob's. That is two writes: deduct £100 from Alice, add £100 to Bob. If the server crashes between them, Alice loses £100 and Bob gets nothing. A transaction wraps both writes into an indivisible unit: either both succeed or neither does.

This is the problem transactions solve — multiple writes that must succeed or fail together.

ACID properties

Every database transaction guarantees four properties:

Atomicity — all operations in the transaction succeed, or none do. A crash mid-transaction rolls back all partial changes.
Consistency — the transaction moves the database from one valid state to another. Constraints, foreign keys, and triggers are checked at commit.
Isolation — concurrent transactions cannot see each other's uncommitted changes (the exact degree depends on the isolation level).
Durability — once committed, the data survives crashes. The database writes to a write-ahead log before acknowledging the commit.

BEGIN, COMMIT, ROLLBACK

-- Transfer £100 from account 1 to account 2
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Verify both sides before committing
SELECT id, balance FROM accounts WHERE id IN (1, 2);

COMMIT;
-- Both changes are now permanent and visible to other connections

-- If something went wrong, undo everything since BEGIN:
-- ROLLBACK;

In Postgres, each statement is auto-wrapped in a transaction if you do not use BEGIN. In MySQL, autocommit is ON by default — each statement commits immediately. Set autocommit = 0 or use START TRANSACTION to begin an explicit transaction.

Handling errors — rollback on failure

In application code, always wrap multi-step writes in a try/catch and roll back on error:

# Python + psycopg2
conn = psycopg2.connect(dsn)
try:
    with conn:                          # psycopg2 context manager commits on exit
        with conn.cursor() as cur:
            cur.execute(
                "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                (amount, sender_id)
            )
            cur.execute(
                "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                (amount, receiver_id)
            )
            # Constraint violations, network errors, etc. raise exceptions here
except Exception:
    conn.rollback()                     # undo both updates
    raise

-- Postgres PL/pgSQL stored procedure with error handling
CREATE OR REPLACE PROCEDURE transfer_funds(
    p_from_id INT, p_to_id INT, p_amount NUMERIC
)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Sender account % not found', p_from_id;
    END IF;

    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Recipient account % not found', p_to_id;
    END IF;

    -- Both updates succeeded; implicit COMMIT at procedure end
END;
$$;

SAVEPOINT — partial rollback within a transaction

SAVEPOINT marks a point you can roll back to without abandoning the entire transaction. Useful in long transactions where one optional step might fail.

BEGIN;

INSERT INTO orders (customer_id, total_amount) VALUES (1001, 249.99)
RETURNING id;   -- suppose this returns id = 5510

SAVEPOINT after_order;

-- Try to apply a loyalty discount (may fail if customer not in program)
UPDATE loyalty_accounts SET points = points - 500 WHERE customer_id = 1001;

-- If the discount application fails, roll back to the savepoint only
-- The order INSERT above is still preserved
ROLLBACK TO SAVEPOINT after_order;

-- Now commit just the order, without the loyalty update
COMMIT;

DDL inside transactions (Postgres only)

Postgres lets you wrap DDL in a transaction — a unique and powerful feature:

BEGIN;

ALTER TABLE products ADD COLUMN weight_kg NUMERIC(8, 3);
UPDATE products SET weight_kg = 0.5 WHERE category = 'Electronics';

-- If anything fails, the column is never added
ROLLBACK;  -- or COMMIT to make it permanent

MySQL, SQL Server, and Oracle auto-commit most DDL statements — they cannot be rolled back.

Long transactions — what to avoid

Transactions hold locks. A transaction open for minutes can block other writers and cause lock waits to queue up:

-- BAD: transaction left open while waiting for user input or slow processing
BEGIN;
SELECT * FROM orders WHERE id = 1042 FOR UPDATE;  -- row lock acquired
-- ... application does processing for 10 seconds ...
UPDATE orders SET status = 'shipped' WHERE id = 1042;
COMMIT;

-- BETTER: hold the lock only for the write, not the read+processing time
-- Read without locking, process outside the transaction, then write:
SELECT * FROM orders WHERE id = 1042;  -- no lock
-- ... process ...
BEGIN;
UPDATE orders SET status = 'shipped'
WHERE id = 1042 AND status = 'processing';  -- optimistic check
COMMIT;

Recap

A transaction is the fundamental unit of reliability in a relational database. BEGIN / COMMIT / ROLLBACK bracket the unit of work; SAVEPOINT adds nested rollback points for partial recovery. Always roll back in error handlers and never leave transactions open across user-facing wait times — open transactions hold locks that block other writers. In Postgres, use DDL inside transactions so schema migrations can be rolled back if a later step fails.

More ways to practice