Transaction Isolation Levels And Concurrency Problems

Transaction isolation levels are policies that shape what concurrent transactions can observe and when a database blocks or aborts work. The SQL names are shared vocabulary, but production behavior depends on the database engine, version, statement type, indexes, and configuration.

The earlier ACID Isolation lesson explains the property and anomalies. This lesson turns that model into practical selection, configuration, testing, and retry decisions for PHP applications.

Do Not Choose From A Memorized Table Alone

The standard level names are:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

Textbook tables often mark whether each level permits dirty reads, non-repeatable reads, and phantoms. That is a starting point. Modern databases may use multiversion concurrency control, snapshots, next-key locks, predicate locks, or serialization checks that make behavior more nuanced.

For example, a level called Repeatable Read in one engine may not behave exactly like the same name in another. Serializable may block work or may allow it to proceed before aborting a transaction at commit. Verify official documentation and run a two-connection test.

Read Uncommitted

Read Uncommitted offers the weakest standard isolation and may permit a transaction to observe changes another transaction has not committed. If that writer rolls back, the reader acted on a value that never became durable state.

Many applications have no good reason to request dirty reads. Approximate reporting should usually use a replica, summary, or analytics system with explicit freshness semantics instead of reading uncommitted transactional data.

Some engines internally provide behavior stronger than the name suggests. Do not infer actual dirty-read behavior from the configured string alone.

Read Committed

Read Committed commonly gives each statement a view of data committed before that statement begins. A second query in the same transaction can see a newer committed value.

This level can work well for short web transactions when code does not assume repeated reads remain unchanged. It does not protect a decision that reads a value and later writes based on it.

For a stock reservation, use an atomic conditional update rather than expecting Read Committed to hold the earlier value stable:

UPDATE products
SET stock = stock - :quantity
WHERE id = :id AND stock >= :quantity;

For an editor, use an optimistic version predicate. For a protected multi-step decision, use a locking read or stronger transaction policy.

Repeatable Read

Repeatable Read aims to keep a transaction's repeated reads stable. Snapshot-based implementations may let a transaction continue seeing its earlier snapshot even after another transaction commits.

This can help reports that need an internally stable view. It can also surprise code that expects a fresh read after another process updates data. Long-lived snapshots retain database resources and can delay cleanup.

Repeatable row reads do not automatically protect every cross-row invariant. Write skew can still be possible in snapshot-based designs. MySQL/InnoDB also has engine-specific range and next-key locking behavior for locking statements. Test the exact query shape.

Serializable

Serializable aims to ensure committed transactions have an outcome equivalent to some serial execution. It is the strongest standard level, but it does not mean every transaction simply waits and succeeds.

A database may reject a transaction with a serialization failure. The application should rerun the entire safe transaction from the beginning, using fresh reads. Retrying only the final statement can reproduce a decision based on a stale snapshot.

Serializable is valuable for selected invariants that are difficult to protect with direct constraints or locks. Using it globally may reduce concurrency or increase aborts. Measure the workload.

Isolation Is Usually Set Per Transaction Or Connection

Database and PDO support varies. Some engines accept a statement before beginning the transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;

Others support session settings or transaction APIs. A framework may expose configuration. Confirm whether the setting applies to the next transaction, current transaction, session, or all new connections.

Connection pools and long-running workers make session settings risky. A changed isolation level can leak into later work unless reset. Prefer a transaction-scoped API where available and test the actual connection lifecycle.

Do not interpolate untrusted input into an isolation-level statement. Choose from an application-defined enum or fixed mapping.

Default Levels Differ

Do not assume the default. MySQL/InnoDB and PostgreSQL have historically used different defaults, and managed services may configure behavior. SQLite uses its own locking and transaction model rather than behaving like a networked MVCC server.

Record the expected default in environment diagnostics and deployment documentation. An upgrade or migration to another database can change concurrency behavior even when SQL syntax remains valid.

Tests should state the level they depend on. A test that passes only under a developer's local default is fragile.

Match The Technique To The Invariant

Isolation level is one tool among several:

• A unique constraint protects unique usernames more directly than Serializable application checks.
• An atomic conditional update protects stock decrement efficiently.
• An integer version column rejects stale edits.
• A row lock protects a known row during a multi-step decision.
• A shared coordination row can serialize a cross-row rule.
• Serializable can protect a predicate-based invariant when simpler techniques do not fit.

Prefer the narrowest technique that clearly proves the rule. Stronger global isolation is not a substitute for schema constraints.

Locking Reads Need Correct Indexes

A locking read such as SELECT ... FOR UPDATE should identify a narrow, indexed set of rows. An unindexed predicate can scan and lock more data, increasing contention.

SELECT id, balance_pence
FROM accounts
WHERE id IN (:first_id, :second_id)
ORDER BY id
FOR UPDATE;

Locking account IDs in a stable order reduces opposite-order deadlocks. Parameter syntax for lists must be generated safely by the database abstraction; one placeholder cannot necessarily represent an arbitrary list.

Locks last until commit or rollback. Keep the transaction short and do not call remote services while holding them.

Optimistic Locking Works Across Requests

A web edit often spans user think time, so holding a database lock is inappropriate. Load a version number with the form and include it in the update:

UPDATE articles
SET title = :title,
    body = :body,
    version = version + 1
WHERE id = :id
  AND version = :expected_version;

Zero affected rows means the article changed or disappeared. Show a conflict, preserve the user's draft, and offer a reload or merge. Do not silently overwrite.

This application-level concurrency control complements transaction isolation because the read and write occur in separate HTTP requests and separate transactions.

Deadlocks Can Occur At Any Useful Level

Even Read Committed transactions can deadlock when they update resources in opposite order. A deadlock is not proof that the database is broken; it is a possible result of concurrent locking.

Reduce risk through consistent access order, short transactions, selective indexes, and fewer unnecessary writes. Still implement bounded retries for identified deadlock victims when the operation is safe to repeat.

Capture structured information: database error code, attempt number, operation ID, transaction duration, and affected resource identifiers that are safe to log. Avoid dumping SQL parameters containing sensitive data.

Serialization And Deadlock Retries Need A Boundary

A retry wrapper must rerun the complete transaction callback:

<?php

declare(strict_types=1);

function runWithRetries(callable $operation, int $maximumAttempts): mixed
{
    for ($attempt = 1; $attempt <= $maximumAttempts; $attempt++) {
        try {
            return $operation();
        } catch (RetryableTransactionException $exception) {
            if ($attempt === $maximumAttempts) {
                throw $exception;
            }
        }
    }

    throw new LogicException('Retry loop completed unexpectedly.');
}

The database-specific adapter should translate only recognized deadlock or serialization signals into RetryableTransactionException. The transaction callback must begin anew, reread state, and avoid non-idempotent external effects.

Add small backoff or jitter when appropriate, but do not sleep while a transaction remains open.

Read-Only Transactions And Stable Reports

A long report may need a stable snapshot so totals agree across several queries. A read-only transaction at an appropriate level can provide that consistency, but it may retain snapshots and consume resources.

For operational reporting, consider one set-based query, a materialized view, a reporting replica, or an analytics store. Do not keep an interactive browser request open in a long database transaction merely to hold a snapshot.

Replica reads have separate freshness behavior. A stable replica snapshot can still be behind the primary.

Test With Two Real Connections

Concurrency behavior cannot be proven with one connection executing statements sequentially. A practical test uses two connections:

Connection A: begin and read or lock
Connection B: begin and attempt conflicting work
Test barrier: observe block, success, or failure
Connection A: commit or roll back
Connection B: finish
Assert final rows and errors

Use explicit process coordination, test hooks, or barriers. Arbitrary sleeps produce flaky tests and may miss the intended interleaving.

Run against the production database engine and configured isolation level. Containers can make these tests repeatable in CI. Include engine version and isolation diagnostics in failures.

Classify The Expected Result

For each concurrency test, define whether the second transaction should:

• block until the first finishes;
• fail immediately;
• fail at commit with a serialization error;
• succeed without violating the invariant;
• detect a version conflict through zero affected rows.

A test that merely says "one request wins" is insufficient. Assert the committed rows, balances, versions, and retry count.

Avoid Reading Decisions From Replicas

A lagging replica must not be used to check a condition that guards a write on the primary. Two workers can see stale availability or permissions and then issue conflicting writes.

Read from the primary for transactional decisions, or use a database operation on the primary that combines the condition and write. Read/write splitting is a scaling policy, not an isolation mechanism.

Observe Contention In Production

Monitor lock waits, deadlocks, serialization aborts, transaction duration, connection usage, slow statements, and retry outcomes. Correlate them with routes, jobs, deploys, and database plans.

A rising retry rate can indicate changed traffic, a missing index, larger batches, or a new access order. Do not simply increase retries; identify the contention source.

Set transaction timeouts or statement timeouts according to the workload. An abandoned request should not hold locks indefinitely.

A Selection Process

For each use case:

1. State the invariant and expected concurrent operations.
2. Add database constraints that directly express it.
3. Prefer atomic conditional writes where possible.
4. Choose optimistic locking for user edits and infrequent conflicts.
5. Use narrow pessimistic locks for protected multi-step decisions.
6. Select a transaction isolation level for remaining observation requirements.
7. Define expected blocking or abort behavior.
8. Add bounded, idempotent retries where required.
9. Test with two connections on the real engine.
10. Monitor contention after deployment.

Review Checklist

Before relying on an isolation level, verify:

• the exact database engine and version;
• configured default and transaction-specific setting;
• connection-pool reset behavior;
• anomaly or invariant being protected;
• indexes used by locking predicates;
• expected block, conflict, or abort outcome;
• retryable error translation;
• idempotency of the complete retry body;
• absence of external effects inside retries;
• a deterministic two-connection test;
• production monitoring for waits and aborts.

What You Should Be Able To Do

After this lesson, you should be able to compare Read Uncommitted, Read Committed, Repeatable Read, and Serializable without assuming identical behavior across databases. You should understand transaction-scoped configuration, connection leakage, lock ordering, optimistic versions, and serialization retries.

You should also be able to choose a narrow concurrency technique from the invariant, create a deterministic two-connection test, and interpret blocking or aborted work as part of a designed isolation policy rather than an unexpected database mystery.