SQL Transaction Control Language (TCL)

A transaction is a group of SQL statements that execute as a single unit. TCL gives you explicit control over when changes become permanent (COMMIT) and when they’re undone (ROLLBACK).

What is a Transaction?

When you transfer money between bank accounts, two things must happen:

Debit $500 from account A
Credit $500 to account B

If only step 1 completes (database crashes, network fails, code throws an error), you’ve lost $500 with no corresponding deposit. A transaction prevents this — either both statements commit, or neither does.

BEGIN;
    UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;
    UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;
COMMIT;

If anything fails between BEGIN and COMMIT, the database state reverts to where it was at the start of the transaction.

ACID Properties

Every database transaction adheres to ACID:

A — Atomicity
    All statements in the transaction succeed, or none of them do.
    No partial updates reach the permanent database state.

C — Consistency
    A transaction takes the database from one valid state to another.
    All constraints, rules, and integrity checks still hold after commit.

I — Isolation
    Concurrent transactions can't see each other's uncommitted changes.
    The level of isolation is configurable (see Isolation Levels below).

D — Durability
    Once committed, changes survive crashes, power failures, and restarts.
    Committed data is written to durable storage.

BEGIN, COMMIT, ROLLBACK

-- Start a transaction
BEGIN;
-- or: START TRANSACTION;

-- Make changes
INSERT INTO orders (customer_id, amount, status)
VALUES (1042, 299.99, 'pending');

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 8821;

-- If everything looks good: make changes permanent
COMMIT;

-- If something went wrong: undo all changes since BEGIN
ROLLBACK;

In most databases (PostgreSQL, MySQL InnoDB, SQL Server), auto-commit is ON by default — each statement is its own implicit transaction. You only need explicit BEGIN when grouping multiple statements together.

SAVEPOINT: Partial Rollback

SAVEPOINT marks a point within a transaction that you can roll back to without undoing the entire transaction:

BEGIN;
    -- First batch of inserts
    INSERT INTO orders (customer_id, amount) VALUES (101, 150.00);
    INSERT INTO orders (customer_id, amount) VALUES (102, 75.00);

    SAVEPOINT after_first_batch;

    -- Second batch (might fail)
    INSERT INTO orders (customer_id, amount) VALUES (999, 50.00);
    -- Suppose this fails because customer_id 999 doesn't exist

    -- Roll back to the savepoint, keeping the first batch
    ROLLBACK TO SAVEPOINT after_first_batch;

    -- Continue without the failed insert
    INSERT INTO orders (customer_id, amount) VALUES (103, 200.00);

COMMIT;
-- Commits: orders for customers 101, 102, and 103 — not 999

Release a savepoint (PostgreSQL) when you no longer need it:

RELEASE SAVEPOINT after_first_batch;

Transaction Isolation Levels

Isolation levels control how concurrent transactions interact. Higher isolation = more safety, but less concurrency:

READ UNCOMMITTED
  Can read uncommitted changes from other transactions (dirty reads).
  Not supported in PostgreSQL; rarely used in practice.

READ COMMITTED (default in PostgreSQL and SQL Server)
  Only sees committed data. Repeated reads may return different values
  if another transaction commits between reads (non-repeatable reads).

REPEATABLE READ (default in MySQL)
  Reads within a transaction always return the same data.
  Prevents non-repeatable reads. May still see phantom rows.

SERIALIZABLE
  Strictest level — transactions behave as if they ran one at a time.
  No dirty reads, no non-repeatable reads, no phantoms.
  Slowest; most conflicts and retries.

Set the isolation level for a transaction:

BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- Operations requiring strict isolation
COMMIT;

-- Or set at the session level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Common Concurrency Problems

Dirty Read: Transaction A reads data written by Transaction B before B commits. If B rolls back, A has read data that never existed.

Non-Repeatable Read: Transaction A reads a row. Transaction B updates and commits that row. Transaction A reads the same row again and gets a different value.

Phantom Read: Transaction A queries a range of rows. Transaction B inserts a new matching row and commits. Transaction A re-runs the same query and sees the new “phantom” row.

Lost Update: Two transactions both read a row, compute a new value based on it, and both write back — the second write overwrites the first’s change.

Practical Transaction Patterns

Safe data migration inside a transaction:

BEGIN;
    -- Step 1: Create new column
    ALTER TABLE customers ADD COLUMN full_name TEXT;

    -- Step 2: Populate it
    UPDATE customers SET full_name = first_name || ' ' || last_name;

    -- Step 3: Add constraint
    ALTER TABLE customers ALTER COLUMN full_name SET NOT NULL;

    -- Step 4: Verify
    -- SELECT COUNT(*) FROM customers WHERE full_name IS NULL;

COMMIT;  -- or ROLLBACK if something is wrong

Test-before-commit pattern:

BEGIN;
    DELETE FROM orders WHERE status = 'test' AND created_at < '2024-01-01';
    -- Check: how many rows deleted?
    -- SELECT COUNT(*) FROM orders WHERE status = 'test';
ROLLBACK;  -- first run: undo to verify count was right
-- Then re-run with COMMIT when satisfied

Explicit locking for concurrent updates:

BEGIN;
    -- Lock the row to prevent concurrent updates
    SELECT balance FROM accounts WHERE account_id = 101 FOR UPDATE;

    -- Now safely compute and update
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 101;
COMMIT;

FOR UPDATE locks the selected rows until the transaction ends, preventing other transactions from updating them simultaneously.

TCL in Application Code

Most application frameworks manage transactions at a higher level, but the underlying SQL still applies:

# Python with psycopg2
with conn:  # auto-commit or rollback on exception
    with conn.cursor() as cur:
        cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (500, 101))
        cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (500, 202))
# conn.__exit__ calls COMMIT on success, ROLLBACK on exception