ACID Transactions and Isolation Levels

ACID transactions are PostgreSQL’s unit of reliable change. They let an application group several reads and writes so the database either applies the whole unit or leaves durable data as if the unit never happened. Isolation levels decide what each transaction is allowed to see while other transactions are running at the same time.

In the Transactions and Concurrency section of this PostgreSQL course, the practical outcome is specific: you should be able to choose the right transaction boundary, predict how snapshots and locks affect concurrent sessions, recognize anomalies such as nonrepeatable reads and serialization failures, and test that business invariants survive real interleavings.

ACID in PostgreSQL Terms

Atomicity means all statements in a transaction commit together. If one statement fails and the transaction is rolled back, PostgreSQL discards all uncommitted row versions created by that transaction. Consistency means committed transactions must satisfy database rules such as primary keys, foreign keys, check constraints, exclusion constraints, and trigger-enforced rules. Isolation means concurrent transactions observe controlled views of data rather than arbitrary half-finished changes. Durability means a committed transaction survives crashes through write-ahead logging and checkpoint recovery.

PostgreSQL implements much of this through multi-version concurrency control, or MVCC. An update does not overwrite a row in place for readers. It creates a new tuple version and marks transaction identity metadata on old and new versions. A query reads a snapshot that says which transaction IDs were already committed, still running, or invisible when the snapshot was taken. Vacuum later removes tuple versions that no active snapshot can still need.

How MVCC, Locks, and Snapshots Fit Together

A PostgreSQL transaction starts with BEGIN or implicitly around a single statement. Each statement receives a snapshot depending on the isolation level. Under READ COMMITTED, every statement gets a fresh snapshot. Under REPEATABLE READ and SERIALIZABLE, a transaction keeps one stable snapshot for ordinary reads.

MVCC avoids many reader-writer blocks: a plain SELECT usually reads an older committed tuple version while another transaction updates the same logical row. Row-level locks matter when a statement intends to change a row or explicitly uses clauses such as FOR UPDATE. Table-level locks also exist, but normal DML uses modes designed to allow high concurrency. Deadlocks can still occur when sessions lock the same resources in opposite orders.

PostgreSQL exposes four SQL isolation names, but READ UNCOMMITTED behaves like READ COMMITTED because dirty reads are not allowed. READ COMMITTED prevents dirty reads but can see different committed data in later statements. REPEATABLE READ provides a stable snapshot and prevents nonrepeatable reads and phantom reads for normal predicate reads in PostgreSQL. SERIALIZABLE adds Serializable Snapshot Isolation, tracking dangerous read-write dependency patterns and aborting one transaction when the concurrent history cannot be made equivalent to some serial order.

Syntax Anatomy

A transaction boundary is explicit when you use BEGIN, COMMIT, and ROLLBACK. Isolation can be set for one transaction with BEGIN ISOLATION LEVEL ... or SET TRANSACTION ISOLATION LEVEL ... before the first query. Savepoints create nested recovery points inside a transaction; they are useful for optional work that can fail without discarding the entire unit.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 25 WHERE id = 1;
COMMIT;

Statements that need to coordinate with future writes can lock selected rows. FOR UPDATE locks rows as if they will be updated. FOR SHARE protects against conflicting updates while allowing compatible shared locks. NOWAIT fails immediately instead of waiting, and SKIP LOCKED is useful for job queues where workers should move past rows already claimed by another worker.

BEGIN;
SELECT id, status
FROM jobs
WHERE status = 'ready'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;
UPDATE jobs SET status = 'running' WHERE id = 10;
COMMIT;

Example 1: Atomic Money Transfer

The first example shows atomicity and constraint-backed consistency. The transfer debits one account and credits another. Both updates must commit together, and a check constraint can reject negative balances if the application makes a mistake.

CREATE TEMP TABLE accounts (
  id integer PRIMARY KEY,
  balance integer NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (id, balance) VALUES (1, 100), (2, 40);

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
COMMIT;

SELECT id, balance FROM accounts ORDER BY id;

The deterministic result is account 1 with balance 70 and account 2 with balance 70. If the debit attempted to subtract 130 from account 1, the check constraint would raise an error. A following ROLLBACK would leave the table at its previous committed state. The important design point is that the invariant is not only application code; PostgreSQL participates by rejecting impossible persisted state.

Example 2: Read Committed Snapshots

This example needs two sessions and an ordinary shared accounts table where account 1 currently has balance 70. It demonstrates why READ COMMITTED is often right for simple OLTP statements but unsafe for code that assumes repeated reads inside one transaction will show the same answer.

-- Session A
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- returns 70 after Example 1

-- Session B
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
COMMIT;

-- Session A
SELECT balance FROM accounts WHERE id = 1;
-- now returns 80
COMMIT;

No dirty read occurred: Session A never saw Session B’s uncommitted value. However, Session A’s second statement received a newer snapshot after Session B committed. This is a nonrepeatable read. It is acceptable for many request handlers that execute one statement at a time, but it is a poor match for a multi-step calculation that must use one consistent view.

Example 3: Serializable Write Skew Protection

Serializable isolation protects invariants that are broader than one row. Suppose at least one doctor must remain on call. Two transactions each see two doctors on call, each turns off a different doctor, and both would commit under weaker designs unless a constraint or stronger isolation prevents the write skew.

CREATE TEMP TABLE doctor_shift (
  doctor text PRIMARY KEY,
  on_call boolean NOT NULL
);

INSERT INTO doctor_shift (doctor, on_call)
VALUES ('Ava', true), ('Noor', true);

-- Run this pattern concurrently in two sessions, changing the doctor name.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctor_shift WHERE on_call;
UPDATE doctor_shift SET on_call = false WHERE doctor = 'Ava';
COMMIT;

If one session updates Ava and the other updates Noor after both read the count, PostgreSQL can abort one commit with SQLSTATE 40001, reported as a serialization failure. The expected application behavior is to roll back that transaction and retry the whole transaction from the beginning. Retrying only the failed statement is wrong because the snapshot and earlier decision are part of the transaction’s logic.

Design Choices and Trade-offs

READ COMMITTED is PostgreSQL’s default because it keeps transactions short, reduces aborts, and fits single-statement changes well. Its trade-off is that multi-statement logic must not assume a stable view unless it locks rows or uses a stronger level.

REPEATABLE READ gives a stable snapshot, which is useful for reports, exports, and calculations that should not drift while they run. The trade-off is that it can read older tuple versions for longer, which may delay vacuum cleanup if the transaction remains open. It also does not make every business invariant serializable; write skew can still matter depending on the pattern.

SERIALIZABLE is the strongest general isolation level in PostgreSQL. It is valuable when correctness depends on predicate reads and cross-row decisions. The trade-off is operational: some concurrent transactions fail and must be retried. Good clients treat 40001 as a normal concurrency outcome, keep transactions small, and avoid interactive user waits inside open transactions.

Explicit locks are sometimes simpler than changing a whole transaction’s isolation level. A seat reservation can lock the course-run row before decrementing seats. A worker queue can use SKIP LOCKED so many workers claim different jobs. The trade-off is blocking and lock-order risk; when you lock, do it in a documented order and hold locks for the shortest practical time.

Failure Modes and Troubleshooting

Symptom: requests hang and then time out. Cause: a transaction is waiting on a row lock held by another open transaction. Diagnose: inspect pg_stat_activity for sessions with wait events and join to blocking process IDs with pg_blocking_pids(pid). Correct: commit or roll back the blocker, shorten transaction scope, add a lock timeout for request paths, and avoid doing network calls while holding locks.

Symptom: occasional ERROR: could not serialize access due to read/write dependencies among transactions. Cause: serializable isolation detected a non-serializable concurrent history. Diagnose: confirm SQLSTATE 40001, capture the transaction name and inputs, and verify the retry wrapper restarts the entire transaction. Correct: implement bounded retries with jitter, reduce the amount of data read before writes, or use a more direct constraint or lock when one precisely models the invariant.

Symptom: vacuum does not reclaim dead tuples and table bloat grows. Cause: a long-running transaction keeps an old snapshot alive. Diagnose: look for old xact_start values in pg_stat_activity and compare them with tables showing many dead tuples. Correct: close idle transactions, paginate long exports, run reporting at READ COMMITTED when stable snapshots are unnecessary, and keep user think time outside transactions.

Reliability, Performance, and Security Implications

Reliability improves when invariants are enforced in the database, not only in request code. Use constraints for facts that are always invalid, transactions for units of change, and isolation or locks for concurrent decision points. Performance depends on short transactions, good indexes for locked predicates, and avoiding unnecessary serializable work on high-contention hot rows. Security intersects with transactions through error handling: never expose raw transaction inputs or sensitive row contents in logs when recording deadlock, timeout, or serialization errors. Log stable identifiers, SQLSTATE, isolation level, retry count, and duration.

Hands-on Lab: Observe Isolation

Prerequisites: access to a PostgreSQL database where you can create and drop a small table, and two SQL sessions such as two psql terminals. Use a disposable database or schema because the lab creates a shared table visible to both sessions.

  1. In Session A, create and seed the table.
  2. In Session A, start a READ COMMITTED transaction and read the balance.
  3. In Session B, update and commit the same row.
  4. In Session A, read again and confirm the value changed.
  5. Repeat with REPEATABLE READ and confirm the second read stays stable until commit.
DROP TABLE IF EXISTS lab_accounts;
CREATE TABLE lab_accounts (
  id integer PRIMARY KEY,
  balance integer NOT NULL CHECK (balance >= 0)
);
INSERT INTO lab_accounts VALUES (1, 100);

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM lab_accounts WHERE id = 1;

-- Session B
UPDATE lab_accounts SET balance = 125 WHERE id = 1;
COMMIT;

-- Session A
SELECT balance FROM lab_accounts WHERE id = 1;
COMMIT;

Verification: under REPEATABLE READ, Session A’s two reads both return 100, then a new transaction in Session A sees 125. Under READ COMMITTED, the second read in Session A sees 125 after Session B commits. Cleanup: run DROP TABLE IF EXISTS lab_accounts; after the lab.

Assessment Exercises

  1. A checkout transaction reads inventory, calls a payment API, then updates inventory. Identify which part should be outside the database transaction and explain why.
  2. Two administrators can approve expenses while a monthly approval limit must not be exceeded. Describe one design using SERIALIZABLE and one design using a database constraint or lock table.
  3. A report must include all rows as of one point in time but can run for ten minutes. Which isolation level would you choose, and what operational risk would you monitor?
  4. An application receives SQLSTATE 40001 once every few thousand requests. Write the retry rule, including what must be retried and what must not be duplicated externally.
  5. For a job queue using FOR UPDATE SKIP LOCKED, explain why jobs need a recovery path if a worker exits after claiming a row.

Summary

PostgreSQL transactions are not just wrappers around SQL statements. They are MVCC snapshots, tuple versions, locks, write-ahead log records, constraints, and retry rules working together. Choose READ COMMITTED for short independent statements, REPEATABLE READ for stable views, SERIALIZABLE for cross-row correctness that must match a serial order, and explicit locks when they model the contested resource directly. The practical test is whether your invariant still holds when two sessions act at once and one of them must wait, abort, or retry.