fogserv.cloud fogserv.cloud

SQL Transactions — Multi-Statement Operations, ACID Guarantees, and Isolation Levels

Status: Active | Last Updated: 2026-08-29 Category: SQL — Transactions Prerequisites: SQL Schema Tags: sql, transactions, acid, begin, commit, rollback, isolation-levels, dirty-read, read-committed, repeatable-read, serializable, migration, prisma Estimated Time: 4-5 hours (Self-paced, includes lab time)

Summary

A transaction is a group of SQL statements that execute as a single atomic unit: either all of them succeed and are made permanent (COMMIT), or all of them are undone (ROLLBACK). Transactions provide ACID guarantees — Atomicity, Consistency, Isolation, and Durability — that make relational databases trustworthy for financial, inventory, and any data where partial updates are worse than no updates.

What You'll Learn (Core Competencies)

Table of Contents

  1. Architectural Overview & Core Schema
  2. Deep Dive & Implementation
  3. Anti-Patterns & Common Pitfalls
  4. Independent Challenge
  5. Consolidation & Key Invariants
  6. Next Steps

1. Architectural Overview & Core Schema

Why Transactions Matter

Without transactions, a multi-step operation can partially complete:

Bad scenario (no transaction):
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;   ← succeeds
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;   ← FAILS (FK violation)
  Result: Alice lost $100. Bob never received it. The database is inconsistent.

Good scenario (transaction):
  BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  COMMIT;   ← only if both succeed
  Result: Both updates happen atomically. If step 2 fails, ROLLBACK undoes step 1.

ACID Properties

Property What it guarantees Failure mode without it
Atomicity All-or-nothing execution Partial updates
Consistency Database moves valid-state to valid-state Constraint violations persist
Isolation Concurrent xacts don't interfere Dirty reads, lost updates
Durability Committed data survives crashes Data loss on power failure

The Transaction Lifecycle

BEGIN (start transaction)
  ├─ Statement 1
  ├─ Statement 2
  └─ ...
├─ COMMIT    ← all changes permanent
  OR
└─ ROLLBACK  ← all changes undone

Outside a transaction, every statement auto-commits (SQLite, MySQL auto-commit; PostgreSQL also defaults to auto-commit in most drivers).

2. Deep Dive & Implementation

BEGIN, COMMIT, ROLLBACK

BEGIN;

-- Money transfer: debit Alice, credit Bob
UPDATE accounts SET balance = balance - 100.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 100.00 WHERE id = 2;

-- Verify no overdraft before committing
SELECT balance FROM accounts WHERE id = 1;
-- If balance >= 0, proceed; otherwise ROLLBACK

COMMIT;    -- make changes permanent
-- ROLLBACK;  -- undo all changes (uncomment to test rollback)

In Prisma:

// Prisma wraps everything in a transaction automatically if the function throws
await prisma.$transaction(async (tx) => {
  await tx.account.update({
    where: { id: 1 },
    data: { balance: { decrement: 100 } }
  });
  await tx.account.update({
    where: { id: 2 },
    data: { balance: { increment: 100 } }
  });
  // If this function throws, both updates are rolled back
});

Prisma also supports interactive transactions with prisma.$transaction(async (tx) => {...}).

Isolation Levels

Concurrent transactions can interfere with each other. Isolation levels control how much interference is allowed:

Level Dirty reads Non-repeatable reads Phantom reads Performance
READ UNCOMMITTED Possible Possible Possible Fastest
READ COMMITTED Prevented Possible Possible Good
REPEATABLE READ Prevented Prevented Possible (MySQL) Moderate
SERIALIZABLE Prevented Prevented Prevented Slowest

PostgreSQL default: READ COMMITTED MySQL (InnoDB) default: REPEATABLE READ

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

-- SQLite: always SERIALIZABLE (no configurable levels)

Setting Savepoints

Savepoints allow partial rollback within a transaction:

BEGIN;
  UPDATE accounts SET balance = balance - 50 WHERE id = 1;

  SAVEPOINT sp1;   -- named checkpoint

  UPDATE accounts SET balance = balance - 30 WHERE id = 1;
  -- Oops, that second debit is wrong — undo just this part:
  ROLLBACK TO SAVEPOINT sp1;   -- reverts the second UPDATE, keeps the first

  -- Continue with correct operation:
  UPDATE accounts SET balance = balance - 20 WHERE id = 1;

COMMIT;   -- commits all un-rolled-back changes since BEGIN

Database Migrations

Migrations change the schema atomically. A good migration:

-- Good migration: wraps schema + data changes in a transaction
BEGIN;

-- Step 1: Add new column with a default
ALTER TABLE orders ADD COLUMN shipped_at TEXT;

-- Step 2: Populate the new column from existing data
UPDATE orders SET shipped_at = created_at WHERE status = 'shipped';

-- Step 3: Add a NOT NULL constraint (requires the column to be populated first)
-- Some DBs require this as a separate step

COMMIT;

With Prisma Migrate, migrations are generated as SQL files — inspect them before applying to production.

Guided Checkpoint

Verify atomicity and rollback behavior using SQLite:

sqlite3 :memory: -header -column "
  CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance REAL);

  -- Initial data
  INSERT INTO accounts VALUES (1, 'Alice', 1000.00), (2, 'Bob', 500.00);

  -- Test 1: Commit (all changes permanent)
  SELECT '=== Test 1: COMMIT — balances after transfer ===' AS test;
  BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  COMMIT;
  SELECT name, balance FROM accounts ORDER BY id;

  -- Reset for test 2
  UPDATE accounts SET balance = 1000.00 WHERE id = 1;
  UPDATE accounts SET balance = 500.00  WHERE id = 2;

  -- Test 2: Rollback (no changes)
  SELECT '=== Test 2: ROLLBACK — balances after rollback ===' AS test;
  BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    -- Simulate an error condition: force rollback
  ROLLBACK;
  SELECT name, balance FROM accounts ORDER BY id;

  -- Verify: balances should be unchanged from initial state
  SELECT '=== Balance check (should match initial: Alice=1000, Bob=500) ===' AS test;
  SELECT
    CASE WHEN name='Alice' AND balance=1000.00 AND name='Bob' AND balance=500.00
         THEN 'PASS: atomicity confirmed'
         ELSE 'FAIL: transaction leaked' END AS result
  FROM accounts;
"

Expected: After COMMIT, Alice=900, Bob=600. After ROLLBACK, Alice=1000, Bob=500 (unchanged). The final check prints PASS.

3. Anti-Patterns & Common Pitfalls

Anti-Pattern 1: Forgetting to Commit in Long-Running Transactions

Open transactions hold locks and prevent vacuum/cleanup. Long-running transactions also risk snapshot staleness.

-- Bad: BEGIN but no COMMIT/ROLLBACK — connection holds locks indefinitely
BEGIN;
  -- long-running operation...
-- connection drops here — transaction is rolled back but locks may persist until timeout

Fix: Always pair BEGIN with COMMIT or ROLLBACK in a finally block at the application layer.

Anti-Pattern 2: Using READ UNCOMMITTED for Financial Data

READ UNCOMMITTED allows dirty reads — seeing uncommitted changes from other transactions. For money transfers, this can mean reading a balance that hasn't been committed, leading to incorrect decisions.

-- Bad for financial data
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Good: use at least READ COMMITTED, SERIALIZABLE for critical data
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Anti-Pattern 3: Lost Updates (Concurrent Writes Without Isolation)

Two transactions read the same balance, both add 100, both write — one update is silently lost.

-- T1: read balance=1000
-- T2: read balance=1000
-- T1: write balance=1100  (read-1100+100-write)
-- T2: write balance=1100  ← LOST UPDATE: T1's change is overwritten

-- Fix: use SERIALIZABLE or SELECT ... FOR UPDATE (row-level lock)
BEGIN;
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- locks the row
  -- now T2 waits until T1 commits
COMMIT;

Anti-Pattern 4: Non-Atomic Migrations

Splitting a schema change into multiple non-transactional statements means partial failure.

-- Bad: if step 2 fails, step 1 is already committed
ALTER TABLE orders ADD COLUMN new_col TEXT;
UPDATE orders SET new_col = 'default';   -- fails — but column exists
ALTER TABLE orders ALTER COLUMN new_col SET NOT NULL;  -- never reached

-- Good: wrap in a transaction
BEGIN;
  ALTER TABLE orders ADD COLUMN new_col TEXT;
  UPDATE orders SET new_col = 'default';
  -- validation...
COMMIT;

Note: Some DDL statements (e.g., ALTER TABLE DROP COLUMN in PostgreSQL) cannot be run inside a transaction. Check your database's documentation.

4. Independent Challenge

Design and test a multi-step transaction for an inventory and order system.

Schema:

CREATE TABLE inventory (product_id INTEGER PRIMARY KEY, name TEXT, stock INTEGER);
CREATE TABLE orders    (id INTEGER PRIMARY KEY, product_id INTEGER, qty INTEGER, status TEXT);
CREATE TABLE balance   (account_id INTEGER PRIMARY KEY, amount REAL);

Write a transaction that:

  1. Checks that inventory.stock >= order_qty for a given product.
  2. If sufficient stock: decrement inventory, insert an order record with status confirmed, and credit the account with the total price (stock × unit_price).
  3. If insufficient stock: insert an order with status rejected, and do not modify inventory or balance.
  4. Use savepoints to handle partial failures within the transaction.
  5. Demonstrate that a ROLLBACK in the application layer correctly restores the initial state.

Save as challenge_inventory_transaction.sql. Include both a "success" and "insufficient stock" test run.

5. Consolidation & Key Invariants

Bullet-list summary of the must-remember rules.

6. Next Steps

Change Log

Choose Theme

Your selection is saved locally.

Neural Cacophony
Aperture v2
Flux v1
Mosaic Chaos
Nexus v1
Nexus Zest
Prism v2
Synapse