What are the four SQL transaction isolation levels and what anomalies does each one prevent?

The SQL standard defines four isolation levels, each preventing a different subset of three classical concurrency anomalies. READ UNCOMMITTED is the weakest level: it allows dirty reads (a transaction can read uncommitted changes from a concurrent transaction that may later be rolled back), non-repeatable reads (a row read twice within the same transaction may return different values if a concurrent transaction commits a change between the two reads), and phantom reads (a range query run twice may return different rows if a concurrent transaction inserts or deletes rows matching the range between the two reads). In practice, no major production database should run at READ UNCOMMITTED for write workloads. READ COMMITTED prevents dirty reads (every read sees only committed data as of the time the read statement executes) but allows non-repeatable reads and phantom reads. This is the Postgres default and the MySQL InnoDB default. REPEATABLE READ prevents dirty reads and non-repeatable reads (a row read once in a transaction will return the same value if read again later in the same transaction) but allows phantom reads in the SQL standard definition. Postgres's MVCC implementation prevents most phantom reads at REPEATABLE READ because Postgres takes a consistent snapshot of the database at the first statement of the transaction, not at each statement — which is a stronger guarantee than the SQL standard requires. MySQL REPEATABLE READ still allows phantom reads for locking reads (SELECT ... FOR SHARE or FOR UPDATE) because MySQL uses next-key locks rather than true MVCC snapshots. SERIALIZABLE prevents all three anomalies plus serialization anomalies — schedules where concurrent transactions interleave in ways that produce a result that no serial execution of those transactions could have produced. Postgres implements SERIALIZABLE using Serializable Snapshot Isolation (SSI), which tracks read/write dependencies between transactions and aborts transactions that would create serialization anomalies; it is not implemented using traditional lock-based serialization. The lost update anomaly — two concurrent transactions both read the same value, compute an update based on it, and both write, causing one write to be lost — is not listed in the SQL standard's three anomalies but is the most common correctness failure in production systems. READ COMMITTED does not prevent lost updates for multi-statement read-modify-write cycles; preventing lost updates requires SELECT FOR UPDATE (pessimistic locking), an atomic UPDATE that includes the prior value in the WHERE clause (compare-and-swap), or SERIALIZABLE isolation.

How does Postgres READ COMMITTED differ from what most developers expect it to prevent?

The most common misconception about Postgres READ COMMITTED is that a transaction — everything between BEGIN and COMMIT — sees a consistent snapshot of the database. This is not how READ COMMITTED works. In Postgres READ COMMITTED, each individual SQL statement sees a snapshot of all data committed before that statement began executing, not before the transaction began. A two-statement sequence — SELECT balance FROM accounts WHERE id = 1, then UPDATE accounts SET balance = balance - 100 WHERE id = 1 — runs as two separate snapshot acquisitions. If a concurrent transaction commits a balance change between the SELECT and the UPDATE, the UPDATE's snapshot will include that concurrent change, but the SELECT's snapshot will not. The SELECT may have seen a balance of 500, passed the balance check (500 >= 100), and then the UPDATE will execute against a current balance that may have been reduced to 50 by the concurrent transaction. The account goes from 50 to -50. This is a lost update. It is not a bug in Postgres. It is the documented behavior of READ COMMITTED as Postgres implements it. The fix is not to upgrade to REPEATABLE READ — which would prevent the non-repeatable read (both SELECT and UPDATE see the same snapshot) but would still allow a concurrent transaction to commit a competing update before the transaction commits, and Postgres would detect this as a write-write conflict and abort the later transaction with a serialization error. The correct fix for lost updates in most financial write patterns is to eliminate the SELECT and write an atomic UPDATE: UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100; then check the rowcount to determine whether the update succeeded (rowcount = 1) or whether the balance was insufficient (rowcount = 0). This form is atomic — the database evaluates the WHERE clause and applies the SET in a single atomic operation — and prevents the lost update without requiring explicit locking or transaction isolation escalation. The SELECT FOR UPDATE alternative acquires a row-level lock on the row at SELECT time, preventing concurrent transactions from modifying the row until the lock is released at COMMIT or ROLLBACK. SELECT FOR UPDATE is the appropriate choice when the read value must be used in application logic before the UPDATE — for example, when the application needs to compute the new balance value based on business rules that cannot be expressed in a SQL WHERE clause.

When should a database transaction use SERIALIZABLE isolation, and what are the performance trade-offs?

SERIALIZABLE isolation is appropriate for operation classes where correctness requires that the entire transaction appears to execute atomically relative to all concurrent transactions — where a serialization anomaly (a result that no serial execution of the concurrent transactions could have produced) would violate a business invariant. The canonical examples: a financial batch that reads all pending transfers and applies them in aggregate (a serialization anomaly would allow a transfer to be applied twice if two batch transactions overlap in their read and write sets); a seat reservation system where overbooking must be prevented beyond a configurable threshold (READ COMMITTED allows two transactions to both read 'seats remaining: 1', both succeed the availability check, and both reserve — SERIALIZABLE would abort one); a credit limit check where the invariant is the sum of all active credit lines rather than a single account balance (a serialization anomaly would allow two concurrent credit line approvals to both read the same sum of active lines, both pass the limit check, and both commit — violating the aggregate constraint). The performance trade-off for Postgres SERIALIZABLE (SSI) is: SSI tracks read/write dependencies between concurrent transactions using predicate locks; the overhead is proportional to the number of concurrent transactions with overlapping read sets; under low concurrency, SSI overhead is negligible; under high concurrency with many overlapping reads, SSI can abort transactions that do not actually conflict (false positives from conservative dependency tracking). The operational trade-off is that SERIALIZABLE introduces transaction aborts that READ COMMITTED does not: any transaction that would create a serialization anomaly is aborted with a serialization failure error (SQLSTATE 40001), and the application must retry the transaction. Applications that use SERIALIZABLE isolation must implement retry logic for every operation that runs at SERIALIZABLE — a requirement that is rarely implemented in codebases that adopt SERIALIZABLE opportunistically ('for consistency') without understanding what the abort case requires. Long-running read-only transactions at SERIALIZABLE are particularly expensive: they hold predicate locks for their entire duration, blocking concurrent write transactions that touch the same rows. A financial report that scans 50 million rows at SERIALIZABLE for 8 minutes effectively serializes all concurrent inserts into those rows behind the report for 8 minutes — degrading write throughput for the duration of the report run.

What should a transaction isolation decision record include?

A transaction isolation ADR needs five sections. First, isolation level selection and the anomaly acceptance model: which isolation level is the default for the service, which operation classes use a different level, and the specific anomalies tolerated at each level and why those anomalies are acceptable for the workload. 'READ COMMITTED because it is the Postgres default' is not an anomaly acceptance model. 'READ COMMITTED because our critical write paths use atomic UPDATE statements that prevent lost updates without requiring transaction-level locking, and our read operations tolerate non-repeatable reads because they do not make correctness decisions based on multiple reads of the same row in a single transaction' is an anomaly acceptance model. Second, concurrent access pattern inventory: the enumeration of every operation class that involves reading a value and computing a write based on that read — the operations where isolation level determines correctness. For each: the operation description, the correctness invariant (what must not happen), the anomaly risk (which anomaly would violate the invariant under READ COMMITTED), and the compensating mechanism (atomic UPDATE, SELECT FOR UPDATE, optimistic locking, SERIALIZABLE). Third, compensating mechanism specification: for each mechanism in use — SELECT FOR UPDATE, atomic UPDATE, optimistic locking — the conditions under which the mechanism is applied, the failure behavior (what happens when the mechanism detects a conflict), and the retry policy. Fourth, lock contention model and throughput ceiling: which transactions hold which locks, for how long, and which concurrent workloads those locks block; the connection pool configuration that preserves isolation level settings (pgBouncer transaction mode, Sequelize transaction options, SQLAlchemy isolation level per operation); the read replica routing policy for analytical workloads. Fifth, isolation level evolution policy: the conditions that would trigger an isolation upgrade (a correctness bug traced to an anomaly that the current level allows) or downgrade (a performance regression traced to lock contention from the current level), the test that validates correctness after the change, and the monitoring that detects lock wait events attributable to isolation level changes.

2026-07-24 · ~22 min read · Decision records

The transaction isolation decision record: why the isolation level you chose determines your concurrent mutation correctness ceiling and your phantom read surface

Database transaction isolation decisions are made during three sessions that never document the isolation model — the initial project setup session that accepts Postgres's READ COMMITTED default without reading what READ COMMITTED actually prevents, the bug investigation session that adds a SELECT FOR UPDATE to fix a race condition without documenting which concurrent access pattern required it, and the performance tuning session that downgrades isolation on a batch job to reduce lock contention without recording the correctness trade-off. What none of these sessions produce is the isolation level specification (what anomalies are prevented and which are tolerated), the concurrent access pattern inventory (which operations require which isolation guarantees), or the compensating mechanism for the anomalies the chosen level accepts — the gaps that determine whether your database transactions are correct under concurrent load or correct only in the single-threaded conditions your test suite happens to exercise.

The ledger that went negative under concurrent payments

A fintech startup is building a wallet service. The lead engineer asks Claude: "How do I implement a balance deduction in Postgres?" Claude returns a straightforward implementation: open a transaction, SELECT the current balance, check whether it covers the requested amount, run the UPDATE if so, COMMIT. The engineer implements it, writes a unit test that runs one deduction at a time, the test passes, the code ships. The wallet service handles payments without incident through the company's early growth — two dozen transactions per minute, mostly sequential.

Eight months later, the company runs a promotional campaign that produces a 40x traffic spike over a weekend. On Monday morning, the operations team finds eleven user accounts with negative balances. The amounts are small — the largest is negative $23.47 — but the invariant that balances cannot go below zero is a core correctness property of the wallet, not a cosmetic issue. The support team manually corrects the balances. The engineering team opens a post-mortem.

The post-mortem investigation finds the sequence. Two concurrent API requests arrive for the same user account within a 12-millisecond window — a payment to a merchant and a subscription renewal, both initiated from the user's mobile app during a loading retry. Both requests open a transaction, both execute SELECT balance FROM wallets WHERE user_id = X, both read 18.50 (the available balance), both check whether 18.50 covers their respective amounts (12.00 and 9.99), both pass the check, both execute UPDATE wallets SET balance = balance - amount WHERE user_id = X. The first UPDATE runs against the current row state (18.50) and sets it to 6.50. The second UPDATE runs against its own view of the row, which in Postgres READ COMMITTED is the committed state as of the time the second UPDATE statement begins — which is now 6.50, after the first transaction committed. The second UPDATE sets the balance to 6.50 - 9.99 = -3.49.

The engineer reading the Postgres documentation during the post-mortem finds the sentence that nobody had read during implementation: "in READ COMMITTED mode, each SQL command sees a snapshot of committed data as of the time that command began." Not as of the time the transaction began. As of the time each individual SQL statement began. The SELECT saw a snapshot from statement-start time. The UPDATE saw a different snapshot from its own statement-start time. Between those two statement-start times — a few milliseconds in production — a concurrent transaction committed. The balance check based on the SELECT result was no longer valid by the time the UPDATE executed.

This is the lost update anomaly. It is not a race condition in the colloquial sense of a timing coincidence that occurs under high load. It is the documented, specified behavior of READ COMMITTED isolation. READ COMMITTED prevents dirty reads (the system will never read uncommitted data from a concurrent transaction). It does not prevent the lost update pattern for multi-statement read-then-write sequences. Every Postgres database operating at the default isolation level is exposed to this anomaly for every operation that reads a value, evaluates it in application code, and then writes based on that evaluation — unless the operation uses a compensating mechanism to prevent it.

The fix the team applied was SELECT ... FOR UPDATE on the balance read — acquiring a row-level lock at read time that prevents any concurrent transaction from modifying the row until the lock is released at COMMIT. The bug report was filed as "race condition in wallet service under high load." The ticket was closed when the SELECT FOR UPDATE was merged. No ADR was written. The next engineer to work on the wallet service found a SELECT FOR UPDATE in the balance deduction code, assumed it was a performance optimization or a defensive pattern copied from another service, and copied it into three similar operations — two of which did not need it and one of which did, for a different reason that was also not documented. The decisions that were never written down were: what isolation level the wallet service uses, what anomalies that level allows, which operations are exposed to the lost update anomaly, and what the compensating mechanism is for each. Those four things together constitute the isolation model. Without the isolation model, every engineer who adds a new read-modify-write operation to the service is making an implicit correctness decision without the context to make it correctly.

The SERIALIZABLE audit query that serialized order insertions for six years

An e-commerce company's original database architect left the company in 2020. Before she left, she wrote the financial reporting queries that the accounting team runs nightly: ledger reconciliation, revenue aggregation, tax calculation, refund liability. She was meticulous about consistency. She set each query's isolation level to SERIALIZABLE at the query level, inside the BEGIN block: BEGIN ISOLATION LEVEL SERIALIZABLE. She added a comment to the query file: "SERIALIZABLE for consistency — these queries must see a stable snapshot of financial data." She was right that SERIALIZABLE provides consistency guarantees. She did not document what those guarantees cost under concurrent write load, or under what conditions the cost would become a production problem.

For two years, the nightly reporting queries ran cleanly. The e-commerce platform had modest write volume — a few hundred orders per night during the reporting window, mostly from international time zones. The SERIALIZABLE queries took 3–4 minutes each, held predicate locks for that duration, and the concurrent write volume was low enough that the lock contention was negligible. The system worked.

In late 2022, the company ran a successful holiday promotion that tripled its customer base. The nightly order volume grew from a few hundred to several thousand. The reporting window (2:00 AM to 3:30 AM) now overlapped with significant write activity from customers in Asia-Pacific time zones. The on-call engineer started receiving latency alerts during the reporting window: order insertion latency spiked from 8ms to 340ms on nights when the accounting reports ran. The team did not immediately connect the reporting queries to the order insertion latency — the two systems seemed unrelated. The alert was acknowledged, the latency returned to normal after the reporting window, and the incident was closed without a root cause investigation.

The pattern repeated every night for four months before an engineer profiling a different performance issue noticed that pg_locks showed a large number of relation locks held by the accounting reporting session during its execution window. She read the query file, found the SERIALIZABLE comment, and understood the mechanism: a SERIALIZABLE transaction in Postgres uses Serializable Snapshot Isolation, which acquires predicate locks on the key ranges it reads. A query that scans all orders in a date range holds a predicate lock on that range. Any concurrent INSERT into the orders table that falls within a locked key range must wait for the SERIALIZABLE transaction to complete before the insert can proceed. The accounting reports were scanning multi-week date ranges across the orders table — a table that was receiving thousands of inserts per night from active customers. Every insert by an active customer was waiting behind the accounting query's predicate locks for up to 8 minutes per report run.

The fix was straightforward: move the accounting queries to a read replica, which runs without predicate locking and without affecting write throughput on the primary. The queries did not need SERIALIZABLE isolation to produce accurate financial results — they needed a consistent snapshot, which Postgres READ COMMITTED provides at the statement level and REPEATABLE READ provides at the transaction level; a read replica running REPEATABLE READ would have provided the consistent-snapshot property the original architect intended without the predicate locking overhead that came with SERIALIZABLE.

The post-fix audit found seventeen additional queries in the codebase running at SERIALIZABLE isolation — background batch jobs, data export scripts, and an analytics pipeline — each added by a different engineer over six years who had seen the SERIALIZABLE pattern in the accounting queries, assumed it was the correct pattern for "important" queries, and applied it. None of the seventeen queries required SERIALIZABLE guarantees; none of the engineers who added them had read the Postgres documentation on predicate locking. The original architect's comment ("SERIALIZABLE for consistency") had propagated across the codebase as a cargo-culted pattern because it contained no documentation of the trade-off it made, the conditions under which it became expensive, or the alternative isolation levels that would have provided the consistency she needed at a fraction of the cost.

The new technical leader inheriting a codebase in this state cannot determine from the code which queries need SERIALIZABLE isolation, which are cargo-culted copies, and which have a correctness dependency on the specific anomalies SERIALIZABLE prevents rather than the weaker guarantees REPEATABLE READ provides. Those distinctions live in the reasoning of the engineer who wrote each query — reasoning that was never written down.

Three structural properties the transaction isolation decision determines

1. The isolation level and the anomaly surface

The SQL standard defines four isolation levels, each preventing a different subset of three classical concurrency anomalies: dirty reads, non-repeatable reads, and phantom reads. The levels are ordered from weakest (READ UNCOMMITTED) to strongest (SERIALIZABLE), with each stronger level preventing everything the weaker levels prevent plus an additional anomaly class.

READ UNCOMMITTED allows dirty reads: a transaction can read data written by a concurrent transaction that has not yet committed. If that concurrent transaction is subsequently rolled back, the read data was never durable — the reading transaction observed a state that the database has since discarded. No production database should run application workloads at READ UNCOMMITTED; it exists for specialized reporting cases where approximate counts are acceptable and read performance is critical. READ COMMITTED prevents dirty reads: every read sees only data that was committed before the read statement began. It allows non-repeatable reads (read the same row twice in a transaction; if a concurrent transaction committed a change between the reads, the second read returns the updated value) and phantom reads (read a range twice; if a concurrent transaction committed an insert or delete matching the range between the reads, the second read returns a different set of rows). READ COMMITTED is the Postgres default and the correct choice for most OLTP workloads — with the critical qualification that multi-statement read-modify-write cycles require explicit compensating mechanisms to prevent lost updates, because READ COMMITTED does not prevent them.

REPEATABLE READ prevents dirty reads and non-repeatable reads: a row read once in a transaction will return the same value if read again in the same transaction, regardless of concurrent commits. The SQL standard allows phantom reads at REPEATABLE READ. Postgres's MVCC implementation provides a stronger guarantee than the standard requires: Postgres takes a snapshot of the entire database at the start of the first statement in a REPEATABLE READ transaction, and all subsequent reads in that transaction see the same snapshot. This prevents most phantom reads in practice, though write-write conflicts (two concurrent REPEATABLE READ transactions both modifying the same row) are detected at commit time and the second committer receives a serialization error (ERROR: could not serialize access due to concurrent update). MySQL InnoDB REPEATABLE READ uses a different mechanism — a consistent read view created at the first read statement — and still allows phantom reads for locking reads (SELECT ... FOR SHARE or FOR UPDATE), which acquire next-key locks that interact with concurrent inserts differently from Postgres's snapshot approach.

SERIALIZABLE prevents all three standard anomalies plus serialization anomalies — concurrent transaction schedules where the interleaving produces a result that no serial execution of those transactions could have produced. Postgres implements SERIALIZABLE using Serializable Snapshot Isolation (SSI), a non-locking approach that tracks read/write dependencies between concurrent transactions and aborts any transaction whose commit would create a cycle in the dependency graph. SSI is more concurrent than traditional lock-based serializable isolation (which would block a transaction rather than abort it) but introduces the possibility of abort-on-commit for transactions that are not actually involved in a conflict but are conservatively flagged by the dependency tracker. Applications using SERIALIZABLE must implement retry logic for serialization failures (SQLSTATE 40001), which is a requirement most ORMs and application frameworks handle inconsistently.

The lost update anomaly is absent from the SQL standard's three-anomaly taxonomy but is the concurrency failure engineers encounter most frequently in production. Two concurrent transactions both read a value, both use that value to compute an update, and both write — the second write overwrites the first. READ COMMITTED does not prevent lost updates. REPEATABLE READ in Postgres detects write-write conflicts at commit time and aborts the second writer, which prevents the lost update but requires the application to handle the abort and retry. The most efficient prevention for most lost update patterns is not isolation escalation but operation restructuring: replace the SELECT-then-UPDATE pattern with an atomic UPDATE that includes the prior value in the WHERE clause, eliminating the read-then-write gap entirely. UPDATE wallets SET balance = balance - 100 WHERE user_id = X AND balance >= 100 is atomic — the database evaluates the condition and applies the mutation in a single operation — and prevents the lost update without locks, without isolation escalation, and without retry logic.

The MVCC implementation details differ across the database vendor choices that teams make for other reasons. Postgres uses true MVCC for both READ COMMITTED and REPEATABLE READ, meaning readers never block writers and writers never block readers — concurrent reads and writes proceed without lock contention at these two levels. MySQL InnoDB uses MVCC for READ COMMITTED and REPEATABLE READ reads but uses next-key locking for locking reads and for SERIALIZABLE. SQL Server uses a pessimistic locking model by default (READ COMMITTED acquires shared locks that block writes) unless READ_COMMITTED_SNAPSHOT is enabled at the database level, which switches to an MVCC-like approach. These implementation differences mean that an isolation model designed for Postgres does not transfer directly to MySQL or SQL Server without re-evaluating the lock contention assumptions.

2. The concurrent access pattern inventory and the isolation requirement

The isolation level is a database-wide default, but different operation classes within the same service have different isolation requirements. The isolation model for a service is not "we use READ COMMITTED" — it is the per-operation-class specification of which isolation level each class uses, which anomalies each class is exposed to at that level, and which compensating mechanism prevents the relevant anomalies for each class.

Financial write operations — balance deductions, inventory holds, seat reservations, credit line activations — share the same structure: read a current value, evaluate a business invariant (is the balance sufficient? is inventory available?), write an update conditional on the invariant being satisfied. The invariant check and the write must be atomic relative to concurrent operations that would modify the same value. The options: atomic UPDATE with the invariant in the WHERE clause (best for simple numeric checks — balance subtraction, inventory decrement — where the invariant can be fully expressed in SQL); SELECT FOR UPDATE (best for complex invariant evaluation in application code where the check cannot be expressed in a WHERE clause — for example, checking a composite credit limit across multiple accounts); SERIALIZABLE isolation (appropriate only when the invariant spans multiple tables and the correctness requirement cannot be expressed as a single atomic UPDATE or a set of row-level locks acquired in a consistent order). Each option has a different failure behavior: atomic UPDATE returns rowcount 0 (insufficient funds, insufficient inventory); SELECT FOR UPDATE blocks until the lock is released; SERIALIZABLE may abort with a serialization error requiring a full transaction retry.

Analytical read operations — balance history displays, revenue summaries, audit trail queries — typically require a consistent snapshot rather than strict serializability. A revenue summary that reads orders over a 30-day window needs each row to reflect its committed final state; it does not need the snapshot to be consistent with concurrent reads the application is making in parallel transactions. READ COMMITTED provides a fresh committed snapshot at each statement. If the analytical query makes multiple passes over the same dataset (reading order totals, then reading refunds, then computing net revenue), READ COMMITTED allows each pass to see a different committed snapshot — which could produce a net revenue figure that is inconsistent if refunds are committed between the passes. For multi-pass queries, REPEATABLE READ provides a consistent snapshot across all statements in the transaction. The appropriate isolation level for analytical queries depends on whether the query is single-pass (READ COMMITTED is sufficient) or multi-pass (REPEATABLE READ provides the consistency guarantee the query requires without the predicate locking overhead of SERIALIZABLE).

Background batch jobs — nightly aggregation, data export, cache warming — are the operation class most often incorrectly assigned SERIALIZABLE isolation. A batch job that reads a large dataset and produces an aggregate output does not require serializability unless the correctness of the aggregate depends on preventing concurrent inserts from being included in the result set. Most batch jobs can tolerate seeing the consistent committed state as of the batch start time — which REPEATABLE READ provides. Batch jobs that need to run without affecting concurrent write workloads should be routed to a read replica regardless of isolation level, because even REPEATABLE READ scans on a table with a high insert rate create I/O contention that affects write latency on the primary. The database sharding decision changes the batch isolation model: cross-shard queries cannot participate in a single-database transaction, and the consistency guarantee for a cross-shard aggregate must be implemented at the application layer rather than through isolation levels.

The ORM layer adds a complication: ORMs abstract isolation level through their transaction management APIs, and the isolation level a developer specifies in the ORM configuration may not be the isolation level the database applies if the connection pool is configured in a mode that strips per-transaction isolation level settings. Sequelize sets isolation level through transaction options: await sequelize.transaction({ isolationLevel: Transaction.ISOLATION_LEVELS.REPEATABLE_READ }, async (t) => { ... }); SQLAlchemy sets it via the connection: connection.execution_options(isolation_level='REPEATABLE READ'). Both work correctly when the connection pool returns a connection that respects these settings. pgBouncer in transaction-mode pooling — a common configuration for high-connection-count Postgres deployments — strips SET LOCAL TRANSACTION ISOLATION LEVEL statements because the connection is returned to the pool at the end of each transaction, and SET LOCAL statements do not persist to the next transaction that acquires the connection. The isolation level setting must be placed inside the BEGIN block (BEGIN ISOLATION LEVEL REPEATABLE READ) rather than as a separate SET statement, to ensure it is part of the transaction that the database processes as a unit.

3. The lock contention model and the throughput ceiling

Isolation level interacts with write concurrency through the lock types each level acquires. Understanding the lock contention model is necessary for evaluating whether a given isolation choice is sustainable at the expected write volume, and for anticipating which workload changes will push a previously acceptable configuration into a contention problem.

Postgres READ COMMITTED acquires only row-level locks for UPDATE and DELETE operations, and tuple-level share locks for SELECT FOR UPDATE. Readers (SELECT without FOR UPDATE) acquire no locks — Postgres's MVCC ensures readers and writers never contend. This is why READ COMMITTED is the right default for OLTP workloads: the only lock contention is write-write contention (two concurrent UPDATEs to the same row), which is unavoidable regardless of isolation level. The lock duration is from the write statement to the transaction COMMIT or ROLLBACK — minimizing lock hold time by keeping transactions short is the most effective throughput optimization at READ COMMITTED.

Postgres SERIALIZABLE (SSI) adds predicate locks to track the read sets of concurrent transactions. Predicate locks are not exclusive locks — they do not block concurrent reads or writes. They record which key ranges each SERIALIZABLE transaction has read, so the SSI algorithm can detect read-write dependencies that would create serialization anomalies. The overhead of SSI is proportional to the number of concurrent SERIALIZABLE transactions with overlapping key ranges. Under low concurrency, SSI overhead is small. Under high concurrency with many transactions reading and writing overlapping ranges, the dependency tracking can cause the abort rate to rise — not because transactions actually conflict, but because the conservative dependency tracking flags potential conflicts that would not actually produce serialization anomalies. The SSI documentation recommends monitoring the pg_stat_activity view for serialization errors and tuning the max_pred_locks_per_transaction parameter if the abort rate is high. Long-running read-only SERIALIZABLE transactions are the highest-risk configuration: they hold predicate locks for their entire duration, and any concurrent write to a key range those locks cover must wait. Routing long-running read queries to a read replica eliminates the predicate lock contention on the primary.

SELECT FOR UPDATE creates explicit row-level locks at the point the SELECT executes. Two concurrent transactions that both attempt SELECT FOR UPDATE on the same row will have the second transaction block at the SELECT until the first transaction commits or rolls back and releases the lock. The deadlock risk is real: if transaction A acquires a lock on row X then attempts to acquire a lock on row Y, while transaction B has locked row Y and is attempting to lock row X, the database detects the deadlock and aborts one of the transactions (the one with the lower lock cost). Preventing deadlocks from SELECT FOR UPDATE requires consistent lock acquisition order across all code paths that acquire locks on multiple rows — if two operations both need locks on accounts and transfers tables, both must acquire the accounts lock before the transfers lock. The lock acquisition order is an implicit global constraint that is not enforced by the database and is difficult to verify statically. Documenting the lock acquisition order in the isolation ADR is the only mechanism that makes this constraint visible to future engineers adding new code paths. The distributed locking problem is an extension of this: when the correctness requirement spans multiple services rather than multiple tables in a single database, the isolation guarantees of the individual database cannot provide the correctness property, and a distributed coordination mechanism is required.

The connection pool configuration interacts with isolation level in ways that are not obvious from the database documentation alone. PgBouncer transaction mode — which returns the database connection to the pool at the end of each transaction, allowing a higher number of application connections to share a smaller number of database connections — is incompatible with session-level isolation level settings (SET DEFAULT_TRANSACTION_ISOLATION TO REPEATABLE READ) because the session state is reset when the connection is recycled. Applications using pgBouncer transaction mode must set isolation level inside each transaction block, not at the session level. HikariCP and other JDBC connection pools may apply a session-level isolation setting at connection creation time, which is compatible with transaction-mode pooling only if the pooler preserves session state across transactions — a behavior that varies by pooler and configuration. Auditing the effective isolation level of production queries requires reading the pg_stat_activity view during execution rather than relying on the application's isolation level configuration, because the pool may be stripping the setting.

Five ADR sections the transaction isolation decision record needs

1. Isolation level selection and the anomaly acceptance model

Document the default isolation level for the service or schema and the specific anomalies that level tolerates — not as an abstract list, but as a concrete description of which production scenarios the accepted anomalies apply to and why those scenarios are acceptable.

"READ COMMITTED because it is the Postgres default" is not an anomaly acceptance model. An anomaly acceptance model for READ COMMITTED looks like: "READ COMMITTED is the default isolation level for all operations in this service. READ COMMITTED tolerates non-repeatable reads (a row read twice in the same transaction may return different values if a concurrent transaction committed between the reads) and phantom reads (a range query may return different rows if concurrent inserts or deletes were committed between two reads in the same transaction). These anomalies are acceptable for this service because: (1) no operation reads the same row twice within a single transaction and makes a correctness decision based on the comparison; (2) all read-modify-write operations use atomic UPDATEs or SELECT FOR UPDATE rather than read-then-write in application code; (3) analytical queries that read ranges are single-pass aggregations that do not rely on the consistency of range results across multiple statements. The lost update anomaly — which READ COMMITTED also tolerates — is prevented for every operation class in the concurrent access pattern inventory through the compensating mechanisms documented in section 2. No operation relies on READ COMMITTED to prevent lost updates."

The anomaly acceptance model forces the team to verify that the accepted anomalies are actually accepted — that no code path relies on READ COMMITTED preventing them. This verification should be performed as an audit when the ADR is first written, and re-performed when new read-modify-write operations are added to the service. Document the result of the audit: which queries were reviewed, which were found to require compensating mechanisms, and which are genuinely safe at the default level without additional protection.

Document the operation classes that use a non-default isolation level. If REPEATABLE READ is used for multi-pass analytical queries, list which queries and why REPEATABLE READ is required rather than READ COMMITTED. If SERIALIZABLE is used for any operation, document the specific serialization anomaly it is preventing, the expected abort rate under the anticipated concurrent load, and the retry logic that handles the serialization error. "SERIALIZABLE for consistency" without these details is the pattern that propagates cargo-culted SERIALIZABLE usage across the codebase.

2. Concurrent access pattern inventory

Enumerate every operation class in the service that involves reading a value and then writing based on that value — the read-modify-write patterns where isolation level determines correctness. For each operation class: the operation description, the correctness invariant that must be preserved under concurrent execution, the anomaly risk at the current default isolation level, and the compensating mechanism that prevents the anomaly.

The inventory should be exhaustive. Common operation classes in web services: balance or quota deduction (invariant: balance cannot go below zero; anomaly risk: lost update at READ COMMITTED; compensating mechanism: atomic UPDATE with WHERE balance >= amount OR SELECT FOR UPDATE); inventory reservation (invariant: units held cannot exceed units available; anomaly risk: lost update; mechanism: atomic UPDATE with WHERE quantity >= requested OR SELECT FOR UPDATE on the product row); account activation or status transition (invariant: status can only transition through valid state machine paths; anomaly risk: two concurrent transitions may both read the same current status and both apply their transition, leaving the account in an invalid state; mechanism: UPDATE with WHERE status = expected_current_status, check rowcount for success); unique constraint bypass (invariant: a user can only have one pending application; anomaly risk: two concurrent inserts both check for an existing pending application using SELECT, find none, both insert, violating the uniqueness constraint; mechanism: database-level UNIQUE constraint on the relevant columns, which will cause one of the concurrent inserts to fail with a unique violation that the application handles; do not use application-level uniqueness checks for invariants that the database can enforce).

The inventory also documents operation classes that do not require compensating mechanisms — read-only operations, operations with no invariant that concurrent writes could violate, operations where the only correctness requirement is seeing committed data (which READ COMMITTED provides). Documenting these as explicitly safe prevents future engineers from adding compensating mechanisms defensively to operations that do not need them, which increases lock contention without improving correctness.

The billing architecture is the subsystem where the concurrent access pattern inventory is most consequential: subscription upgrades, proration calculations, credit balance deductions, and payment retry scheduling are all read-modify-write operations with correctness invariants that READ COMMITTED does not automatically protect. The billing subsystem should have its own isolation ADR section or a separate billing-specific ADR that extends the service-level isolation model.

3. Compensating mechanism specification

For each compensating mechanism used in the service, document the specific conditions under which the mechanism is applied, the failure behavior (what the application observes when the mechanism detects a conflict), and the retry policy for conflict cases.

Atomic UPDATE (compare-and-swap): applied when the invariant can be fully expressed as a SQL predicate in the WHERE clause. The mechanism succeeds when the UPDATE rowcount is 1 (the row was found and matched the predicate) and fails when the rowcount is 0 (the row was not found, or the predicate was not satisfied — balance insufficient, inventory exhausted). The failure case must be distinguished from the row-not-found case: if the query returns rowcount 0, the application must determine whether the failure was due to an insufficient value or because the row does not exist. The most robust approach is a two-statement sequence within a transaction: SELECT the current value first (to return the current state for the error message), then attempt the atomic UPDATE; if the UPDATE returns 0 rows and the prior SELECT returned a row, the condition was not met; if the prior SELECT returned no row, the object does not exist. This two-statement sequence itself requires careful design: the SELECT does not need FOR UPDATE because the UPDATE will atomically check the condition — the SELECT is purely for the error message, not for the invariant check.

SELECT FOR UPDATE: applied when the invariant requires application-code evaluation that cannot be expressed in a WHERE clause. The mechanism acquires a row-level lock at SELECT time; the lock is held until COMMIT or ROLLBACK. The failure case is a deadlock (two transactions have locked rows in an order that creates a cycle) — the database aborts one transaction with ERROR: deadlock detected (SQLSTATE 40P01). The retry policy for deadlock must be at the transaction level (retry the full transaction, not just the SELECT FOR UPDATE statement), because the aborted transaction's locks have been released and the retry must re-acquire them. Document the lock acquisition order for every code path that acquires locks on multiple rows: the order must be consistent across all paths, and any new code path that acquires locks must conform to the documented order. Violations of lock acquisition order are not detectable at code review time without an explicit constraint to check against — the isolation ADR is the only place that constraint lives.

Optimistic locking (version field): applied when lock contention is expected to be low — most reads will not encounter a concurrent writer, and the retry cost of a conflict is acceptable. The mechanism adds a version integer column to the row. The UPDATE includes WHERE id = X AND version = expected_version; if a concurrent transaction has incremented the version between the application's read and its write, the UPDATE returns 0 rows. The retry policy is to re-read the current row (including its updated version) and re-apply the business logic with the fresh state. Optimistic locking is lower overhead than SELECT FOR UPDATE under low-contention conditions (no lock held during the application-code evaluation) but higher overhead under high-contention conditions (frequent retries with re-reads). The read replica routing decision interacts with optimistic locking: if reads are routed to a replica and writes go to the primary, the version value read from the replica may lag behind the primary, increasing the false-conflict rate. Optimistic locking reads must go to the primary when the write will also go to the primary.

4. Lock contention model and throughput ceiling

Document the expected lock contention pattern for the service under normal and peak load, the monitoring that surfaces lock wait events, and the thresholds at which lock contention becomes a throughput constraint.

The lock contention model should specify which tables experience concurrent write pressure, which operations acquire locks on those tables, the average lock hold time per transaction, and the expected concurrent transaction count at peak load. From these inputs, the expected lock queue depth can be estimated: if a table receives 1,000 writes per second, the average lock hold time is 5ms, and each transaction acquires one row-level lock, the average concurrent lock holders is 5 (1,000 × 0.005). If the concurrent transaction count exceeds 5 for the same row, the additional transactions queue. The queue depth is the observable signal that lock contention is degrading throughput.

Postgres provides pg_locks and pg_stat_activity for observing current lock state. A query against pg_locks joined to pg_stat_activity shows which sessions are waiting for locks and which session holds the blocking lock. This query should be run during load testing to verify that the expected lock contention model matches observed behavior before deploying to production. Alert thresholds for lock wait time (transactions waiting more than 200ms for a lock) should be set in the monitoring system alongside the standard latency alerts — lock wait time is a leading indicator for throughput degradation that precedes visible latency increases in the API response time metrics.

The database migration case requires special attention in the lock contention model. Schema migrations that take an exclusive lock on a table (ALTER TABLE ADD COLUMN NOT NULL without a default, CREATE INDEX without CONCURRENTLY) block all reads and writes to that table for the duration of the migration. The isolation level of the migration transaction determines which other operations the migration blocks and is blocked by, but the exclusive lock supersedes isolation level considerations — even a READ COMMITTED SELECT is blocked by an exclusive table lock. Document the migration locking model in the isolation ADR: which migration patterns require exclusive locks, the expected duration of those migrations at production data volume, and the procedure for running long migrations without extended table downtime (CREATE INDEX CONCURRENTLY, pg_repack, multi-step ADD COLUMN with a nullable default then backfill then NOT NULL constraint). The lock from a long-running migration that was not designed for concurrency is the most common production incident caused by isolation-adjacent decisions.

The SLO error budget provides the quantitative frame for the throughput ceiling: if the service SLO is 99.9% availability, the error budget allows for 43.8 minutes of downtime per month. A lock contention event that causes a 5-minute degradation in order processing consumes 11% of the monthly error budget. The isolation level and locking decisions should be evaluated against the SLO: what is the probability that the current locking model produces a lock wait event long enough to burn a material portion of the error budget? That probability should be part of the trade-off documentation when choosing between optimistic and pessimistic locking for high-throughput operations.

5. Isolation level evolution policy

Document the conditions under which the isolation level for an operation class should be upgraded or downgraded, the validation process for the change, and the monitoring that would signal that the current level has become insufficient or unnecessarily expensive.

Upgrade triggers: a correctness bug traced to an anomaly that the current isolation level allows. If a bug investigation concludes that two concurrent transactions both read the same value and wrote conflicting updates — and the existing compensating mechanism was absent, incorrectly applied, or insufficient for the specific operation class — the isolation ADR should be updated to add the compensating mechanism that prevents the anomaly, or, if the operation class cannot be protected by an atomic UPDATE or SELECT FOR UPDATE (for example, because the invariant spans multiple rows that cannot be locked in a consistent order), to upgrade the operation class to SERIALIZABLE isolation. Document the specific bug, the anomaly it exposed, and the change made to prevent recurrence — not just the code change, but the isolation model update that explains why the compensating mechanism is now required.

Downgrade triggers: a performance regression traced to lock contention or predicate lock overhead from an isolation level that is more restrictive than the operation's correctness requirements demand. An operation running at SERIALIZABLE that does not require serialization guarantees — where the correctness invariant is fully protected by the application's compensating mechanisms at READ COMMITTED — should be downgraded. The downgrade requires a correctness argument: describe the anomalies that SERIALIZABLE prevents for this operation class and demonstrate that each anomaly is also prevented by the compensating mechanisms at READ COMMITTED. The correctness argument should be reviewed by at least one engineer other than the author before the downgrade is deployed, because the downgrade removes a correctness guarantee and incorrect reasoning about anomalies at READ COMMITTED produces production bugs under concurrent load.

Validation before isolation changes: any change to the isolation level of a production operation class requires a concurrency test that exercises the relevant anomaly. For a downgrade from SERIALIZABLE to READ COMMITTED on a financial write operation: a test that runs 100 concurrent instances of the operation against the same row, validates that the invariant holds at the end of the concurrent run, and measures the observed retry rate (which should be zero if atomic UPDATEs are used, or proportional to the conflict rate if SELECT FOR UPDATE is used). The test should run against a production-size dataset at production-level concurrent load to surface contention patterns that only emerge at scale. Unit tests and integration tests against a single-threaded test database do not exercise the anomalies that isolation level exists to prevent — only concurrent load tests can validate the isolation model.