P-3 — Transactions and ACID in Practice

The Problem Transactions Solve

Imagine a bank transfer: debit $100 from Alice, credit $100 to Bob.

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

What happens if the server crashes, the network drops, or an error occurs between the two statements? Alice has lost $100 and Bob never received it. The money has vanished.

Transactions prevent this by grouping operations: either both updates happen, or neither does.

BEGIN, COMMIT, ROLLBACK

sql
BEGIN;  -- start a transaction

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

COMMIT;  -- make both changes permanent

If anything goes wrong between BEGIN and COMMIT:

sql
BEGIN;

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

-- Something goes wrong (application error, network failure, etc.)

ROLLBACK;  -- undo the debit — Alice gets her $100 back

Without a BEGIN, every statement is its own transaction — it auto-commits immediately. This is fine for isolated statements, but dangerous for multi-step operations.

With a BEGIN, nothing is visible to other connections until COMMIT. If the session disconnects before COMMIT, PostgreSQL automatically rolls back the transaction.

ACID: What It Actually Means

A — Atomicity: All operations in a transaction succeed, or none do. There is no partial success.

sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Crash here → the UPDATE is rolled back. Alice keeps her $100.

C — Consistency: A transaction brings the database from one valid state to another. Constraints (NOT NULL, FOREIGN KEY, CHECK) are enforced at commit time.

sql
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;  -- balance would go to -500
-- CHECK (balance >= 0) fires at COMMIT
COMMIT;  -- ERROR: check constraint violated → automatic rollback

I — Isolation: Transactions run as if they are the only transaction in the system. Other transactions' uncommitted changes are invisible.

sql
-- Session A:
BEGIN;
UPDATE tasks SET status = 'in_progress' WHERE id = 1;
-- NOT committed yet

-- Session B (concurrent):
SELECT status FROM tasks WHERE id = 1;
-- Returns 'todo' — Session A's change is not visible until committed

D — Durability: Once committed, changes survive crashes. PostgreSQL's Write-Ahead Log (WAL) ensures this.

Isolation Levels in Practice

PostgreSQL supports four isolation levels. The default — and the one you will use for almost everything — is READ COMMITTED.

READ COMMITTED (default)

Each query in a transaction sees a fresh snapshot of committed data at the time that query starts. This means a transaction can see different data at the beginning vs. the end if other transactions commit in between.

sql
-- Session A:
BEGIN;
SELECT COUNT(*) FROM tasks;  -- returns 100

-- Session B (commits a new task while A is open):
INSERT INTO tasks (...) VALUES (...);
COMMIT;

-- Session A continues:
SELECT COUNT(*) FROM tasks;  -- returns 101 — sees B's committed change
COMMIT;

This is acceptable for most application reads. Each query gets a consistent view; the transaction as a whole does not.

REPEATABLE READ

The entire transaction sees the same snapshot of data as of when the transaction started. New inserts/updates by other transactions are invisible for the duration.

sql
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

SELECT COUNT(*) FROM tasks;  -- returns 100

-- Session B inserts and commits a task while we wait

SELECT COUNT(*) FROM tasks;  -- still returns 100 — snapshot is frozen
COMMIT;

Use this when your transaction needs a consistent view across multiple queries — for example, generating a report where all queries must see the same data point in time.

SERIALIZABLE

The strongest level. Transactions execute as if they were serialised one after another. PostgreSQL detects situations where concurrent transactions could produce results different from any serial execution and fails one of them.

sql
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Used for financial operations where concurrent updates to related rows
-- could produce incorrect results even if each individual update is correct

Use this sparingly — it adds overhead and can cause transaction failures that need retrying.

Rule for most applications: use the default READ COMMITTED. Move to REPEATABLE READ only when you need a consistent multi-query snapshot. Use SERIALIZABLE only for complex financial operations where non-serialisable anomalies are a real risk.

SAVEPOINT — Partial Rollback

A SAVEPOINT marks a point within a transaction you can roll back to without abandoning the entire transaction.

sql
BEGIN;

INSERT INTO orders (customer_id, total) VALUES (1, 150.00);

SAVEPOINT before_items;  -- mark this point

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 999, 1);
-- ↑ This fails: product 999 doesn't exist (foreign key violation)

-- Roll back to the savepoint — the order INSERT is preserved,
-- but the order_items INSERTs are undone
ROLLBACK TO SAVEPOINT before_items;

-- Try again with correct data
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 2, 1);

COMMIT;  -- order + 1 item committed successfully

Transaction Best Practices

Keep transactions short

A transaction holds locks on the rows it modifies until it commits. Long-running transactions block other operations and accumulate dead tuples (see Phase 3, autovacuum).

sql
-- ❌ SLOW: fetching from an external API inside a transaction
BEGIN;
SELECT * FROM orders WHERE status = 'pending';
-- ... application calls payment API (100ms to 5 seconds) ...
UPDATE orders SET status = 'confirmed' WHERE id = $id;
COMMIT;
-- Locks held for 5+ seconds

-- ✅ FAST: do external work outside the transaction
-- 1. Fetch pending orders (no transaction needed for reads in most cases)
SELECT * FROM orders WHERE status = 'pending';
-- 2. Call payment API outside the transaction (no locks held)
-- 3. Open transaction only for the write
BEGIN;
UPDATE orders SET status = 'confirmed' WHERE id = $id;
COMMIT;

Never ignore transaction errors

In application code, check for errors after every database call. In PostgreSQL, once an error occurs inside a transaction, the transaction is aborted — all subsequent statements fail until you ROLLBACK.

sql
BEGIN;
INSERT INTO tasks (...) VALUES (...);

-- If this INSERT fails (e.g., foreign key violation):
INSERT INTO invalid_table (...) VALUES (...);  -- ERROR

-- This subsequent SELECT will also fail:
SELECT * FROM tasks;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block

ROLLBACK;  -- must rollback before doing anything else

In application code (Node.js example):

javascript
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, 2]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;  // re-throw so the caller knows something went wrong
} finally {
  client.release();  // always return the connection to the pool
}

SELECT ... FOR UPDATE — Pessimistic Locking

When multiple transactions need to read and then modify the same row, you need to prevent another transaction from changing that row between your SELECT and your UPDATE.

sql
BEGIN;

-- Lock the row immediately — no one else can update it until we commit
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- Application logic: check if balance >= amount
-- (the row is locked, so the balance cannot change while we check)

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

COMMIT;

Without FOR UPDATE, two sessions could read the same balance, both decide they can proceed, and both subtract — leading to a negative balance.

FOR UPDATE SKIP LOCKED — useful for job queues:

sql
-- Claim the next available task without blocking on locked rows
BEGIN;
SELECT id, title
FROM tasks
WHERE status = 'todo'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;  -- skip rows locked by other workers

-- Update the claimed task
UPDATE tasks SET status = 'in_progress' WHERE id = $id;
COMMIT;

Multiple workers can run this concurrently — each claims a different task because SKIP LOCKED skips rows already locked by another worker.

Common Transaction Patterns

Safe inventory deduction

sql
BEGIN;

-- Check and lock in one step
UPDATE products
SET stock = stock - $quantity
WHERE id = $product_id AND stock >= $quantity
RETURNING stock;

-- If no rows returned, stock was insufficient — rollback
-- Application checks: if rowCount === 0, rollback

COMMIT;

Atomic counter increment

sql
-- This is safe — the entire operation is atomic
UPDATE page_views
SET count = count + 1
WHERE page_id = $page_id;
-- No transaction needed — a single UPDATE is always atomic

Creating a record and its related records

sql
BEGIN;

INSERT INTO orders (customer_id, status)
VALUES ($customer_id, 'pending')
RETURNING id AS order_id;

-- Use the returned order_id
INSERT INTO order_items (order_id, product_id, quantity)
VALUES ($order_id, $product_id, $quantity);

-- Update inventory
UPDATE products SET stock = stock - $quantity WHERE id = $product_id;

COMMIT;

Practical Exercise: Safe Bank Transfers

sql
CREATE TABLE bank_accounts (
  id      BIGSERIAL PRIMARY KEY,
  owner   TEXT      NOT NULL,
  balance NUMERIC(15,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO bank_accounts (owner, balance) VALUES
  ('Alice', 1000.00),
  ('Bob',   500.00);

-- Write a transfer function
CREATE OR REPLACE FUNCTION transfer(
  from_id   BIGINT,
  to_id     BIGINT,
  amount    NUMERIC
) RETURNS VOID AS $$
BEGIN
  -- Validate amount
  IF amount <= 0 THEN
    RAISE EXCEPTION 'Transfer amount must be positive: %', amount;
  END IF;

  -- Lock both accounts in ID order (prevents deadlock)
  UPDATE bank_accounts SET balance = balance - amount
  WHERE id = LEAST(from_id, to_id)
    AND id = from_id;

  UPDATE bank_accounts SET balance = balance + amount
  WHERE id = LEAST(from_id, to_id)
    AND id = to_id;

  -- Simpler approach:
  UPDATE bank_accounts
  SET balance = balance + CASE WHEN id = from_id THEN -amount ELSE amount END
  WHERE id IN (from_id, to_id);
END;
$$ LANGUAGE plpgsql;

-- Test the transfer
BEGIN;
SELECT transfer(1, 2, 200);  -- Alice sends $200 to Bob
COMMIT;

SELECT owner, balance FROM bank_accounts;
-- Alice: 800.00, Bob: 700.00

-- Test that CHECK constraint prevents overdraft
BEGIN;
SELECT transfer(1, 2, 1000);  -- Alice tries to send $1000 (only has $800)
-- ERROR: new row for relation "bank_accounts" violates check constraint
ROLLBACK;

SELECT owner, balance FROM bank_accounts;
-- Alice still has 800.00 — rollback preserved the balance

Summary

Module P-4 covers schema design for real applications — normalisation, the correct data types for money and time, soft deletes, audit fields, and the migration tools that keep schemas manageable as they evolve.

Next: P-4 — Schema Design for Real Applications →