LLM Best Practices

Transaction isolation

2 min read

View markdown source

Overview

This page is the atomic definition. Postgres transaction patterns live at postgres.

Definition

Transaction isolation is the “I” in acid. SQL defines four standard levels, each preventing a different class of anomaly. Read Uncommitted: a transaction can read uncommitted changes from other transactions (dirty reads). Most databases do not implement this level. Read Committed: a transaction only sees committed data, but may read different values if it re-reads the same row within the same transaction (non-repeatable reads). PostgreSQL defaults to this level. Repeatable Read: a transaction sees a consistent snapshot of data as of its start time; no non-repeatable reads. PostgreSQL’s Repeatable Read prevents phantoms too via MVCC. Serializable: transactions behave as if they ran one at a time in some serial order; eliminates all anomalies but reduces throughput. PostgreSQL implements true Serializable Snapshot Isolation (SSI). Higher isolation levels prevent more anomalies but increase lock contention and the risk of deadlock.

When it applies

Use the default Read Committed for most OLTP workloads. Use Repeatable Read or Serializable for financial reconciliation, report generation, or any workflow that reads a value and then writes a decision based on it. Avoid Serializable for high-throughput write paths unless anomaly prevention is required.

Example

-- Serializable prevents the "write skew" anomaly in appointment booking:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM slots WHERE date = '2026-06-01'; -- 1 slot left
INSERT INTO bookings (date, user_id) VALUES ('2026-06-01', 42);
COMMIT; -- retried if another transaction booked the last slot concurrently
  • acid - isolation is the I in ACID.
  • deadlock - higher isolation levels increase deadlock probability.
  • optimistic-locking - an application-level alternative to high isolation levels.
  • postgres - PostgreSQL MVCC implementation details.

Citing this term

See Transaction isolation (llmbestpractices.com/glossary/transaction-isolation).

Graph View

Backlinks