Transactions

A transaction is a sequence of database operations executed as a single unit of work. Either all operations in the transaction succeed and their effects persist, or none of them take effect. This all-or-nothing guarantee is fundamental to building correct applications on top of databases that handle concurrent access and can fail at any moment.

What Is a Transaction

Consider a bank transfer: debit $200 from account A, credit $200 to account B. If the system crashes after the debit but before the credit, $200 disappears. If two transfers run simultaneously, both might read the same balance and each deduct from it, creating phantom money. Transactions prevent both scenarios.

A transaction groups operations into a boundary:

BEGIN;
  UPDATE accounts SET balance = balance - 200 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 200 WHERE id = 'B';
COMMIT;

Until COMMIT, changes are tentative — visible only within this transaction, not to other sessions. At COMMIT, the database makes all changes durable and atomically visible to everyone. If anything fails before commit, ROLLBACK undoes all changes as if they never happened.

ACID Properties

The four ACID properties define what a correct transaction system must guarantee. (See the ACID and BASE guide for full detail; this guide focuses on the mechanics.)

Commit and Rollback

COMMIT finalizes the transaction. The database writes a commit record to the write-ahead log (WAL) and flushes it to disk. After this point, the changes are durable. If the system crashes immediately after the flush, the WAL ensures recovery replays the commit. The changes become visible to other transactions.

ROLLBACK aborts the transaction and reverses all changes made since BEGIN. The database uses undo information (stored in the WAL or undo log) to reverse each operation in reverse order. After rollback, the database is in the exact state it was before the transaction began.

Rollback is triggered in two ways:

• Explicit: the application calls ROLLBACK (e.g., a business rule check failed).
• Implicit: the database aborts the transaction due to a deadlock, constraint violation, or error. The application must check for errors and retry if needed.

Savepoints

A savepoint is a named marker within a transaction that you can roll back to without aborting the entire transaction.

BEGIN;
  INSERT INTO orders (id, customer) VALUES (1, 'alice');
  SAVEPOINT before_items;
  INSERT INTO order_items (order_id, product, qty) VALUES (1, 'widget', 3);
  -- If this fails or we change our mind:
  ROLLBACK TO SAVEPOINT before_items;
  -- The order row still exists; only order_items insert was undone
  INSERT INTO order_items (order_id, product, qty) VALUES (1, 'gadget', 1);
COMMIT;

Savepoints are useful for partial retry logic within a transaction — for example, when inserting a batch of rows and wanting to skip individual failures without losing the entire batch. They add overhead (the database must track more undo information), so use them deliberately.

Isolation Levels

Full isolation (serializable) is expensive because it requires preventing every possible interference between concurrent transactions. SQL databases offer weaker isolation levels that trade correctness guarantees for performance:

Most production databases default to Read Committed. Each statement sees only committed data, but two reads of the same row within a transaction can return different values if another transaction commits in between.

Repeatable Read ensures that if you read a row, subsequent reads within the same transaction see the same version — even if another transaction updates it. PostgreSQL implements this via MVCC snapshots taken at transaction start.

Serializable provides the illusion of serial execution. PostgreSQL uses Serializable Snapshot Isolation (SSI) — a mostly optimistic approach that detects serialization conflicts at commit time and aborts transactions that would violate serializability. SSI has much lower overhead than traditional locking-based serializability.

Concurrency Problems

Without sufficient isolation, concurrent transactions create anomalies:

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 row R. Transaction B updates and commits R. Transaction A reads R again and gets a different value. The same query returned different results within one transaction.

Phantom read: Transaction A queries rows matching a condition (e.g., WHERE salary > 100000) and gets 5 rows. Transaction B inserts a new row matching the condition and commits. Transaction A reruns the same query and gets 6 rows. A "phantom" row appeared.

Write skew: Transactions A and B each read overlapping data, make decisions based on it, and write non-overlapping data. Each individual write is valid given what it read, but the combined result violates an application invariant. Classic example: two doctors both read "at least one doctor on call" as true, both go off call, and now zero doctors are on call. Only Serializable isolation prevents write skew.

Lost update: Two transactions read the same row, compute a new value based on it, and write back. The second write overwrites the first — one update is lost. Common with UPDATE accounts SET balance = balance + N patterns when done read-then-write at the application level.

How Databases Implement Transactions

Write-Ahead Log (WAL): Every change is written to the WAL — a sequential append-only log on disk — before it is applied to the data pages. On crash, the database replays the WAL from the last checkpoint to recover committed transactions and undo uncommitted ones. The WAL is the foundation of both atomicity and durability.

Multi-Version Concurrency Control (MVCC): Instead of locking rows for reads, the database keeps multiple versions of each row. Each transaction sees a consistent snapshot of the data as it existed at a point in time. Readers don't block writers; writers don't block readers. PostgreSQL, MySQL InnoDB, and Oracle all use MVCC. Old row versions are eventually garbage-collected by a vacuum/purge process.

Locking: For writes, databases use row-level locks to prevent two transactions from modifying the same row simultaneously. Deadlocks occur when two transactions each hold a lock the other needs. The database detects cycles in the lock-wait graph and aborts one transaction to break the deadlock. The aborted transaction gets a serialization error and must be retried.

Two-Phase Locking (2PL): A classic algorithm for achieving serializability. In the growing phase, a transaction acquires all locks it needs. In the shrinking phase (after the first lock is released), it can only release locks — no new acquisitions. This prevents cycles. 2PL has higher overhead than MVCC for read-heavy workloads, but some databases use it for write-heavy critical sections.

Design Considerations

• Keep transactions short. Long transactions hold locks longer, increase conflict probability, block MVCC vacuum/purge, and grow the WAL. Do computation outside the transaction; keep only the reads and writes inside.
• Never do I/O inside a transaction. If you hold a database transaction open while making an HTTP call or waiting for user input, you hold locks for seconds or minutes. Other transactions queue up. Use the outbox pattern or two-phase approaches instead.
• Always handle serialization errors. At Serializable isolation (and sometimes at lower levels for deadlocks), transactions can be aborted with a serialization error. The application must catch these and retry the entire transaction from scratch — not just the failing statement.
• Autocommit mode. Most database drivers operate in autocommit mode by default: each statement is its own transaction. If you want multi-statement transactions, you must explicitly call BEGIN / disable autocommit. Forgetting this is a common source of inconsistency in application code.
• Idempotent retries. If your application retries a transaction after a failure, ensure the transaction is idempotent — retrying must not cause double-writes. Use unique constraints, conditional updates (INSERT ... ON CONFLICT DO NOTHING), or idempotency keys.