Skip to content
Ephizen logo ephizen / wiki
CS 503

Transactions and ACID

BEGIN, COMMIT, ROLLBACK — and what atomicity, consistency, isolation, and durability actually buy you when concurrent writers fight.

SQL III — Systems intermediate #sql #transactions #concurrency

TL;DR

A transaction groups multiple statements so they succeed or fail together. The boundary is BEGIN … COMMIT (or ROLLBACK). Inside, the database guarantees four properties — ACID:

  • Atomicity — all statements commit, or none do. No partial state.
  • Consistency — the database moves from one valid state to another; constraints (NOT NULL, CHECK, foreign keys) hold.
  • Isolation — concurrent transactions don’t see each other’s in-flight changes (subject to your chosen isolation level — see SQL 304).
  • Durability — once COMMIT returns, the change survives crashes. Usually implemented via a write-ahead log.

You debug your first race condition that double-charged a customer and ACID stops being abstract. Distributed systems compromise on these properties in well-known ways (CAP, BASE), and it’s almost always the wrong call for transactional workloads. Use a real ACID database for anything where correctness matters.

A worked example

-- Transfer $100 from Alice's account to Bob's
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;

Without a transaction, a crash between the two UPDATEs loses $100. With the transaction, either both UPDATEs commit or neither does. That’s atomicity.

If a constraint is violated (balance >= 0), the whole thing rolls back:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
-- ERROR: new row for relation "accounts" violates check constraint "balance_nonneg"
ROLLBACK;

That’s consistency.

If two transactions run the transfer concurrently, the database serializes the writes (or aborts one) so you don’t end up with balance = -100. That’s isolation.

Once COMMIT returns, even if the server crashes immediately, Bob’s account shows the new balance after restart. That’s durability — the change is in the WAL, fsynced to disk before COMMIT returns.

The four properties, more carefully

Atomicity

“All or nothing.” If statement 5 of 10 fails, statements 1–4 are undone. Implementation: the database keeps an undo log of changes; ROLLBACK applies it.

SAVEPOINTs let you partial-rollback within a transaction:

BEGIN;
INSERT INTO orders VALUES (...);
SAVEPOINT sp1;
INSERT INTO order_items VALUES (...);  -- this fails
ROLLBACK TO SAVEPOINT sp1;             -- only this insert is undone; the order stays
INSERT INTO order_items VALUES (...);  -- different items
COMMIT;

Useful for batch processing where you want to skip bad rows without losing the whole batch.

Consistency

The database starts valid and ends valid: every constraint, foreign key, and trigger holds at commit time. The DB enforces this; your code provides the invariants by declaring constraints. Without CHECK (balance >= 0), the database happily lets you go negative.

“Consistency” in ACID is not the same as “consistency” in CAP. CAP consistency means “every read sees the latest committed write across distributed replicas”. ACID consistency is about constraints. Same word, different definitions, much confusion.

Isolation

Concurrent transactions see a consistent snapshot of the database — they don’t see each other’s in-flight writes. The exact rules are governed by the isolation level (Read Committed, Repeatable Read, Serializable), covered in SQL 304. Postgres’ default is Read Committed.

The implementation in Postgres is MVCC (Multi-Version Concurrency Control): every row write creates a new version; readers see the version that was committed at their snapshot’s start. Readers don’t block writers, writers don’t block readers (mostly).

Durability

Once COMMIT returns successfully, the data survives. Mechanism: changes are first written to the write-ahead log (WAL) and fsync’d before COMMIT returns. After a crash, the WAL is replayed.

This costs latency — every COMMIT waits for an fsync. Trade-offs:

  • synchronous_commit = off — COMMIT returns before WAL fsync. Faster, but a crash within ~600ms of COMMIT can lose that transaction.
  • Group commit — multiple concurrent COMMITs share an fsync.
  • Synchronous replication — wait for a replica to also fsync. Slower still, but replica-failover-safe.

For ML feature pipelines and batch loads, synchronous_commit = off is sometimes acceptable. For payments, never.

Locking — what UPDATE actually does

When you UPDATE a row, Postgres acquires a row-level exclusive lock on it for the duration of your transaction. Other transactions trying to update the same row block until you commit or rollback.

-- Session 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- (don't COMMIT yet)

-- Session 2 (a different connection)
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- BLOCKS, waiting for Session 1 to COMMIT or ROLLBACK

Once Session 1 commits, Session 2 unblocks and applies its update on top. That’s how concurrent updates serialize correctly.

Read locks are weaker — SELECT doesn’t block writers in default Read Committed (it sees the prior committed version via MVCC).

Explicit locks

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- lock for later UPDATE
SELECT * FROM accounts WHERE id = 1 FOR SHARE;   -- shared read lock

Used when you need to read-then-decide-then-write atomically. Without FOR UPDATE, two transactions can both read the same balance, both decide they have enough, and both deduct — classic lost-update.

Deadlocks

Two transactions each holding a lock the other wants:

T1: locks row A; tries to lock row B (blocks, waiting for T2)
T2: locks row B; tries to lock row A (blocks, waiting for T1)

Both wait forever. Postgres detects this and aborts one transaction with “deadlock detected” — your code must catch and retry.

The standard prevention: always acquire locks in a consistent order (e.g., by ascending row ID). Application code that always updates rows in ORDER BY id won’t deadlock with itself.

Long transactions are an antipattern

Open transactions hold:

  • Locks (preventing others’ writes).
  • Old row versions (preventing VACUUM from cleaning them).
  • A snapshot (delaying snapshot horizon advance, bloating tables).

A 30-minute analytical query inside a transaction can stall maintenance for hours. Rules of thumb:

  • Keep transactions short (≤ a few seconds).
  • Don’t open a transaction and then go do work in your application.
  • Long-running analytical queries don’t need a transaction at all (or use SET TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY if you need a snapshot).

Common pitfalls

  • Forgetting to commit. A BEGIN with no commit/rollback holds resources until the connection closes.
  • Long transactions. As above. Especially deadly when an interactive REPL session leaves a transaction open.
  • Read-then-write without FOR UPDATE or upsert. Classic lost-update race. Either FOR UPDATE the row, or use INSERT ... ON CONFLICT.
  • DDL inside transactions. Postgres allows transactional DDL — ALTER TABLE inside a transaction is rolled back on failure. MySQL does not. Knowing the difference is the difference between safe migrations and half-applied disasters.
  • Catching errors and continuing without rollback. Once a transaction errors, every subsequent statement returns “current transaction is aborted”. You must ROLLBACK (possibly to a savepoint) before more work.

Production patterns for ML

1. Idempotent batch upserts. When loading a feature batch from a nightly job, you want re-runs to be safe:

BEGIN;
INSERT INTO features (user_id, day, value)
VALUES ($1, $2, $3)
ON CONFLICT (user_id, day) DO UPDATE
    SET value = EXCLUDED.value, updated_at = NOW();
COMMIT;

Wrapping in a transaction means a partial batch failure rolls back cleanly; the next run picks up.

2. Atomic schema swap on materialized features. When rebuilding a feature table, build into a new table and atomically rename:

BEGIN;
ALTER TABLE features RENAME TO features_old;
ALTER TABLE features_new RENAME TO features;
DROP TABLE features_old;
COMMIT;

Because Postgres has transactional DDL, this is a single atomic switchover — readers either see the old table or the new one, never an inconsistent intermediate state.

Resources