Transaction Isolation Levels and Anomalies

“Just use SERIALIZABLE” is good advice only if you ignore the workload. “READ COMMITTED is safe enough” is equally dangerous when a transaction protects a business invariant across several reads and writes. Transaction isolation is a concurrency contract, not a correctness switch, and the contract’s real behavior depends on the database engine, access pattern, contention, and retry logic around it.

The SQL label tells you which side effects a level is intended to permit. It doesn’t tell you exactly how PostgreSQL, MySQL, or SQL Server will implement that promise, nor whether your application’s transaction boundaries in fact protect the state you care about. Unit tests usually miss these failures because they run operations in predictable sequences, while production interleaves requests at inconvenient moments.

The practical answer is to validate isolation against production-like behavior. Controlled traffic replay, combined with database observability and anomaly checks, can reproduce contention patterns that ordinary tests never create. That’s how a backend team discovers whether its chosen level protects real invariants before concurrent requests turn a theoretical anomaly into corrupted application state.

The Hidden Cost of Database Concurrency

A database can return only committed data and still allow your application to make an incorrect decision. Consider a reservation service that checks availability, performs another operation, and then writes a booking. Each individual query may look valid, yet two concurrent transactions can both observe the same state and commit outcomes that violate the business rule.

That failure doesn’t necessarily appear as an error. The application may log successful requests, the database may report successful commits, and the resulting state may be internally valid according to constraints while still being wrong for the business. Isolation protects relationships between operations, not merely the cleanliness of individual reads.

Why ordinary tests miss the failure

Most unit and integration tests control transaction order. One request finishes before the next begins, or the test database has so little contention that the problematic interleaving never occurs. Even carefully written concurrent tests often exercise a narrow timing window rather than the combinations created by real request dependencies, retries, connection pools, and uneven query latency.

Documentation can’t close that gap by itself. Microsoft notes that isolation levels describe allowed side effects, while actual behavior varies with implementation, including the distinction between lock-based and row-versioning approaches in SQL Server’s transaction locking and row-versioning guide. The same label can therefore produce different operational symptoms after a platform change or migration.

Practical rule: Treat an isolation setting as a hypothesis about correctness. Prove it with the exact engine, schema, transaction boundaries, and workload you intend to deploy.

Replay the contention, not just the queries

A reliable validation plan needs more than a collection of SQL statements. It needs the request sequence that creates the race, the session relationships that preserve dependencies, and enough concurrency to reproduce the timing. The target environment must isolate side effects so a replay can’t send emails, charge cards, or mutate production records.

Production-like traffic replay is particularly valuable before a database migration or isolation change. Capture representative requests, remove sensitive values, send them to an isolated service and database, then compare expected invariants with the final state. When an anomaly appears, preserve the request pair or sequence, transaction traces, lock events, and database snapshots as a regression test.

The goal isn’t to maximize load for its own sake. The goal is to expose incorrect outcomes that remain invisible under sequential testing. Throughput matters, but a fast transaction that violates an invariant is not a successful optimization.

ACID Properties and the Evolution of Isolation

A transaction groups database operations into a unit that either commits or rolls back. ACID names the properties teams rely on:

• Atomicity: The transaction’s intended changes succeed together or are undone together.
• Consistency: A successful commit preserves declared database constraints and application invariants that the transaction enforces.
• Isolation: Concurrent transactions don’t produce observations or outcomes outside the chosen concurrency guarantee.
• Durability: Committed changes survive the failures covered by the database’s durability design.

Isolation is the property that becomes difficult when multiple requests run at the same time. A serial execution gives each transaction an orderly view, but real systems interleave work to improve concurrency. The database must decide which reads see which versions, which writes wait, and which transactions must retry.

Why the original trade-off still matters

Transaction isolation became a formal research and product concern in 1975, when Jim Gray and colleagues published early work on the transaction abstraction and its supporting mechanisms. By 1976, K. P. Eswaran, Jim Gray, Raymond Lorie, and Irving Traiger had published The Notions of Consistency and Predicate Locks in a Database System, a milestone in locking-based isolation theory. These historical points are documented in the SIGMOD 2025 Bernstein keynote materials.

The architects faced a problem that still defines backend design: stronger guarantees simplify reasoning about correctness, but they can reduce concurrency. Weaker levels allow more overlap, which can improve throughput, but they also permit observations that make application logic harder to trust. The industry didn’t choose weaker isolation because correctness was unimportant. It chose a range of guarantees because workloads have different tolerance for waiting, aborts, and anomalies.

The SQL standard later codified four canonical levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Serializable isolation is the strongest of these, producing the same effect as running transactions one at a time, even when the engine executes them concurrently. PostgreSQL’s documentation states that only SERIALIZABLE prevents dirty reads, non-repeatable reads, phantom reads, and serialization anomalies together.

Isolation protects invariants only inside its scope

The database can protect an invariant only if the transaction includes the relevant reads and writes. A uniqueness constraint can protect a single key, but it won’t automatically protect a rule involving an aggregate, a range, or a related table. Conversely, a short transaction that updates one row may need much less coordination than a workflow that reads several rows before deciding what to write.

That distinction gives teams a useful design question: what must appear to happen atomically from the business’s perspective? Choose the isolation semantics around that answer, then measure the cost under realistic contention rather than selecting a level by habit.

Isolation Levels and Concurrency Anomalies Explained

The standard levels are easiest to understand as permissions. Each weaker level allows more concurrent interference, while stronger levels require the engine to coordinate more aggressively or reject conflicting work.

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
READ UNCOMMITTED	Permitted	Permitted	Permitted
READ COMMITTED	Prevented	Permitted	Permitted
REPEATABLE READ	Prevented	Prevented	Defined differently by engine and standard
SERIALIZABLE	Prevented	Prevented	Prevented

Read Uncommitted

READ UNCOMMITTED allows a transaction to see changes another transaction hasn’t committed. If the writer rolls back, the reader has acted on a value that never became durable. That is a dirty read, and it can be especially damaging when a read triggers a subsequent write, notification, or external action.

This level may reduce waiting, but “fast” doesn’t mean useful if the application acts on transient state. It belongs only in narrowly understood scenarios where approximate, disposable observations are acceptable, and even then the engine’s actual behavior must be verified.

Read Committed

READ COMMITTED prevents dirty reads, which makes it a common operational baseline. It doesn’t promise that two reads in the same transaction return the same result. Another transaction can commit an update between those reads, producing a non-repeatable read, or insert rows matching a predicate, producing a phantom read.

For independent operations, that may be fine. It becomes risky when the first read establishes a condition that the later write assumes is still true. A stock check followed by a decrement, or a balance read followed by a transfer, needs explicit protection against the relevant race.

Repeatable Read

REPEATABLE READ protects the transaction’s repeated observation of rows, but the standard and individual engines differ on how they handle ranges and phantoms. A consistent snapshot can make repeated reads stable while still leaving write-skew or other serialization anomalies possible. Snapshot Isolation, in particular, can be non-serializable, as discussed in the Hermitage isolation testing analysis.

Serializable

SERIALIZABLE provides the strongest standard guarantee. The engine may use locks, dependency tracking, or a combination of techniques, but the committed result should be equivalent to some serial order. That doesn’t mean every transaction succeeds immediately. Serialization failures, deadlocks, and retries remain application concerns.

A team that selects serializable semantics must implement retry handling for safe-to-retry transactions, keep transactions short, and monitor aborts. Otherwise, the system may preserve correctness while delivering poor user experience.

Standard Definitions Versus Real Engine Behavior

The phrase “repeatable read” sounds portable. Its implementation isn’t. PostgreSQL, MySQL, and SQL Server can use different visibility rules, lock behavior, and versioning mechanisms while exposing familiar isolation names.

PostgreSQL relies heavily on multiversion concurrency control, or MVCC. Readers can work from visible row versions rather than blocking every writer, while serializable execution uses dependency tracking to identify dangerous combinations. SQL Server supports both lock-based behavior and row-versioning options, including SNAPSHOT, which gives a transaction a consistent view from its start. Microsoft documents READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, and SERIALIZABLE as principal production levels.

Same label, different mechanics

Lock-based control makes transactions wait when incompatible access overlaps. MVCC lets readers see versions that satisfy their snapshot, reducing some reader-writer blocking but introducing version retention, cleanup, and visibility complexity. Neither approach eliminates the need to understand writes, predicates, and transaction dependencies.

MySQL’s InnoDB implementation also uses MVCC and undo information for consistent reads, while its locking behavior can affect range operations and concurrent writes. PostgreSQL’s snapshot semantics can prevent some anomalies that the SQL wording does not require a REPEATABLE READ implementation to prevent. SQL Server’s behavior changes materially when a database uses row versioning instead of traditional locking for a given read-committed configuration.

The portability trap is assuming that an ANSI label is a complete behavioral specification. It isn’t. A migration can preserve the configured string while changing blocking, visibility, phantom behavior, serialization failures, and retry requirements.

Verify the engine you actually run

Read the vendor documentation, but don’t stop there. Build small two-session tests for the transaction patterns your application uses:

1. Start two transactions against the same rows or predicate.
2. Pause one session at deliberate points.
3. Execute the competing read or write in the other session.
4. Commit or roll back in different orders.
5. Record returned values, locks, waits, aborts, and final state.

Research reinforces why this verification matters. A 2025 evaluation across MySQL, PostgreSQL, MariaDB, CockroachDB, and TiDB found 48 unique isolation anomalies violating Adya-defined levels, as reported in the ISSTA 2025 research presentation. That result doesn’t mean every workload experiences every anomaly. It does mean assumptions based only on labels and documentation are not sufficient.

Performance Trade-offs and Configuration Strategies

Correctness has an operating cost. Stronger isolation can increase waiting, version pressure, conflict detection, or transaction aborts. Recent research reports that eliminating common anomalies can reduce throughput by 25% to 45% and raise latency by up to 3x for read-heavy workloads with moderate contention, according to the 2025 WJAETS study.

Those figures aren’t a universal forecast for your system. They are a warning against treating SERIALIZABLE as a free configuration change. The cost depends on query shape, indexes, transaction duration, read-write overlap, connection pool behavior, and the engine’s concurrency-control strategy.

Configure around the invariant

Start with the transaction that protects a business rule, not with a global database setting. Short, independent reads often fit READ COMMITTED or snapshot-style semantics. A workflow that checks several rows, derives a decision, and updates state may need serializable semantics, an explicit locking strategy, or a redesign that moves the invariant into a constraint or atomic statement.

Use these controls during tuning:

• Keep transactions narrow: Don’t hold locks or snapshots while calling remote services, waiting for user input, or performing unnecessary application work.
• Index predicates: Poorly indexed range checks scan more data and can widen locking or conflict effects.
• Set statement and lock timeouts: Fail predictably instead of allowing blocked requests to consume the connection pool.
• Retry deliberately: Retry serialization failures and deadlocks only when the transaction is idempotent or safely reconstructable.
• Measure the right signals: Track lock waits, deadlocks, serialization aborts, transaction duration, rollback rate, and query latency together.
• Prefer optimistic checks for suitable writes: A version column or conditional update can reject stale writes without holding a long pessimistic lock.

A useful operational reference is this guide to database performance tuning, especially when isolation changes alter lock waits or query behavior. The tuning process should compare correctness outcomes with resource costs. Lower latency is not an improvement if it increases lost updates or invalid state transitions.

Engineering rule: Use the weakest semantics that demonstrably preserve the invariant, then validate that choice under contention. Don’t weaken isolation to hide a capacity problem, and don’t strengthen it without measuring aborts and waits.

Detecting Anomalies with Production Traffic Replay

A concurrency bug often needs a precise sequence, not merely high request volume. One request reads a resource, another updates it, a retry repeats the first operation, and a third request arrives through a different endpoint. Synthetic tests may exercise each endpoint correctly while missing the interleaving that breaks the invariant.

Production traffic replay makes the application’s real request choreography available in a controlled environment. Capture HTTP requests, filter and anonymize sensitive fields, replay them against an isolated service and test database, then observe both application results and database-level conflicts.

A practical replay workflow

Suppose an order service reserves inventory and records payment status in separate operations. Under normal testing, each request runs cleanly. Under replay, overlapping checkout requests may expose a lost update, a stale availability decision, or a retry that repeats a non-idempotent transition.

Use a controlled sequence:

1. Capture representative traffic. Include the endpoints and request relationships involved in the state transition.
2. Filter and anonymize. Remove credentials, personal data, payment details, and identifiers that could reach real systems.
3. Prepare an isolated target. Use copied schema and suitable test data, with external side effects disabled.
4. Preserve useful timing. Reproduce concurrency and request ordering without assuming that raw production speed alone will recreate the race.
5. Instrument the database. Collect transaction identifiers, query timing, locks, waits, deadlocks, serialization failures, and final invariant checks.
6. Compare outcomes. Look for duplicate transitions, impossible totals, stale reads followed by successful writes, and unexpected rollbacks.
7. Turn failures into regression tests. Save the smallest request sequence that still reproduces the anomaly.

Tools such as GoReplay can capture and replay HTTP traffic into a controlled target. The database still needs its own assertions, because an HTTP response can look successful while the final state violates a cross-request rule.

A shadow setup can also send copies of live requests to a candidate service while production continues serving users. Keep the candidate isolated, use test data, and disable production side effects. Replay isn’t permission to point a load generator at live payment or messaging systems.

Observe the failure at three layers

Application logs show which request sequence preceded the anomaly. Database telemetry shows whether the engine blocked, versioned, detected a dependency, or allowed the conflicting operations. A post-replay invariant checker confirms whether the final state is correct.

This layered view matters because the same symptom can have different causes. A duplicate result may reflect an application retry, a missing uniqueness constraint, a stale snapshot, or a serialization failure that the client incorrectly retried. Correlating request IDs with transaction and database events turns a vague race into a reproducible defect.

Use the following video as a practical introduction to replay-based testing workflows:

Choosing the Right Isolation Strategy for Your Workload

Choose isolation by asking what can go wrong, how often transactions overlap, and whether the application can retry. A read-heavy reporting path may benefit from consistent snapshots without requiring every transaction to serialize. A financial transfer, inventory reservation, or entitlement decision needs stronger protection when several reads and writes jointly preserve an invariant.

Use this decision matrix as a starting point:

Workload characteristic	Practical direction
Independent reads and simple writes	Start with a weaker level, then verify stale-read and lost-update behavior
Read-heavy workload requiring a stable view	Evaluate MVCC or snapshot semantics, including version retention and write conflicts
Multi-row business invariant	Use serializable semantics, explicit locking, conditional writes, or a constraint that enforces the rule
High contention on shared state	Keep transactions short, measure waits, and implement safe retry behavior
Cross-service workflow	Avoid holding database transactions across remote calls; use explicit state transitions and idempotency

A deployment checklist

Before changing isolation in production, record the engine and version, database configuration, transaction boundaries, retry policy, and expected invariants. Test the exact predicates and write paths with two-session scenarios, then replay representative application traffic against an isolated environment.

Don’t approve the change from latency charts alone. Compare final-state correctness, serialization failures, deadlocks, lock waits, rollback rates, and user-visible retries. The strongest setting is useful only when the team can operate its conflict behavior, while the weakest setting is acceptable only when the workload can tolerate the anomalies it permits.

Adopt a small regression suite for every discovered race. Run it after database upgrades, ORM changes, index changes, connection-pool changes, and migrations between engines. Transaction isolation is a property of the entire execution path, not a single configuration line.

---

GoReplay lets teams capture and replay HTTP traffic against an isolated test target, making real request ordering and contention available for database validation without exposing production side effects. Use GoReplay to reproduce concurrency scenarios, observe isolation failures, and turn confirmed anomalies into deployment-blocking regression tests.