ACID: Isolation

Isolation is the ACID property concerned with concurrent transactions. It controls how one transaction's reads and writes interact with work happening at the same time. Isolation matters because a production PHP application rarely has only one request. PHP-FPM workers, queue consumers, scheduled jobs, and administrative tools can all touch the same rows concurrently.

Starting a transaction does not automatically remove every race condition. Correct behavior depends on the isolation level, SQL statements, indexes, locks, constraints, and application retry policy.

Think In Timelines, Not Sequential Code

A function may look safe when read from top to bottom:

Read stock: 1
Check that stock is available
Write stock: 0

Now interleave two requests:

Transaction A reads stock 1
Transaction B reads stock 1
Transaction A writes stock 0
Transaction B writes stock 0
Both report success

One item produced two successful purchases. Each PHP request followed its local logic, but their combined timeline violated the inventory rule.

Concurrency review asks where another transaction can run between a read and the write based on that read.

Isolation Levels Are Named Tradeoffs

SQL standards describe four familiar levels:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

The names are useful vocabulary, not a complete cross-database specification. PostgreSQL, MySQL/InnoDB, MariaDB, SQL Server, and SQLite implement concurrency with different combinations of locks, multiversion concurrency control, snapshots, and conflict detection. Defaults differ, and the same named level can have different practical behavior.

Always check the engine and version used by the application. A framework configuration label cannot replace database documentation and a concurrency test.

Dirty Reads

A dirty read occurs when one transaction observes another transaction's uncommitted change. If the writer later rolls back, the reader acted on a value that never became committed state.

A changes balance from 1000 to 0 but has not committed
B reads balance 0
A rolls back to 1000

Many widely used database configurations prevent dirty reads. Even so, understanding the anomaly explains why uncommitted data is unsafe for business decisions.

Read Uncommitted is the standard level associated with permitting dirty reads, although an engine may internally provide stronger behavior.

Non-Repeatable Reads

A non-repeatable read occurs when one transaction reads a row twice and sees another transaction's committed update between those reads.

A reads order status pending
B changes status to paid and commits
A reads the same order again and sees paid

This can be acceptable when every statement should see current committed data. It can be dangerous when a transaction assumes its earlier decision remains valid.

Read Committed commonly allows this behavior because each statement gets an appropriate committed view. Repeatable Read aims to keep repeated reads stable, but details vary.

Phantom Reads

A phantom occurs when a repeated predicate query returns a different set of rows because another transaction inserted, deleted, or changed matching rows.

A counts confirmed bookings for room 7 and finds 0
B inserts a confirmed booking for room 7 and commits
A repeats the count and finds 1

Protecting one previously read row is insufficient when the rule concerns a range or absence of rows. Engines may use predicate locks, range locks, snapshots, serialization checks, or constraints to address this.

Lost Updates

A lost update happens when concurrent operations derive new values from the same old value and one overwrites the other.

A reads profile version 4 and changes the display name
B reads profile version 4 and changes the timezone
A writes its full profile record
B writes its full profile record
A's display-name change disappears

Avoid writing an entire stale record when only one field changed. Use targeted updates, atomic expressions, row locks, or optimistic version checks.

For counters, let the database update the current value:

UPDATE article_stats
SET view_count = view_count + 1
WHERE article_id = :article_id;

Do not read the count into PHP, increment it, and write it back unless the transaction protects that sequence.

Write Skew

Write skew occurs when two transactions read related rows, each decides its own write is valid, and together they violate a cross-row invariant. The classic example has two doctors on call. Each transaction sees the other doctor available, so each marks itself unavailable. Different rows are written, but the final state has nobody on call.

Row locks on only the records being changed may not prevent this. Solutions include serializable isolation, locking a shared parent or coordination row, an enforceable constraint, or redesigning how the invariant is stored.

This is why "our writes touch different rows" does not prove concurrency safety.

Prefer Atomic Conditional Statements

The inventory race can often be solved without an initial read:

<?php

declare(strict_types=1);

function reserveStock(PDO $pdo, int $productId, int $quantity): bool
{
    if ($quantity < 1) {
        throw new InvalidArgumentException('Quantity must be positive.');
    }

    $statement = $pdo->prepare(
        'UPDATE products
         SET stock = stock - :quantity
         WHERE id = :id AND stock >= :quantity'
    );
    $statement->execute(['quantity' => $quantity, 'id' => $productId]);

    return $statement->rowCount() === 1;
}

The condition and write are evaluated by the database as one statement. This narrows the race and often reduces lock duration. Put it inside a wider transaction if reservation must commit with an order or audit row.

Use Pessimistic Locking For Protected Decisions

Pessimistic locking assumes conflicting work is likely or expensive. A locking read such as SELECT ... FOR UPDATE asks the database to lock selected rows until the transaction finishes.

BEGIN;
SELECT balance_pence FROM accounts WHERE id = :id FOR UPDATE;
UPDATE accounts SET balance_pence = :new_balance WHERE id = :id;
COMMIT;

Exact syntax and behavior are engine-specific. The transaction must be short, the lookup should use appropriate indexes, and every competing code path must follow a compatible locking strategy.

Locks can reduce concurrency and create waits or deadlocks. Never hold them while calling a remote API or waiting for user input.

Use Optimistic Concurrency For Infrequent Conflicts

Optimistic concurrency allows concurrent reads and rejects a stale write. Add a version column:

UPDATE profiles
SET display_name = :display_name,
    version = version + 1
WHERE id = :id AND version = :expected_version;

If rowCount() is zero, the row changed or disappeared after it was read. The application can show a conflict, reload and merge, or retry when the operation is safely repeatable.

<?php

declare(strict_types=1);

function updateDisplayName(
    PDO $pdo,
    int $id,
    int $expectedVersion,
    string $displayName,
): void {
    $statement = $pdo->prepare(
        'UPDATE profiles
         SET display_name = :name, version = version + 1
         WHERE id = :id AND version = :version'
    );
    $statement->execute([
        'name' => $displayName,
        'id' => $id,
        'version' => $expectedVersion,
    ]);

    if ($statement->rowCount() !== 1) {
        throw new RuntimeException('Profile changed since it was loaded.');
    }
}

A timestamp can be used, but an integer version is usually clearer and avoids timestamp precision surprises.

Serializable Does Not Mean No Failures

Serializable isolation aims to make committed transactions equivalent to some serial order. A database may achieve this by blocking conflicting operations or aborting one transaction when it cannot safely serialize the result.

Therefore, stronger isolation can convert silent anomalies into explicit serialization failures. The application must be prepared to retry a safe transaction or report a conflict.

Do not blindly retry every exception. Identify driver-specific deadlock or serialization signals, limit attempts, add jitter when appropriate, and ensure the complete operation is idempotent. A transaction that charges an external payment before retrying is not safe merely because its SQL can rerun.

Deadlocks Are A Normal Concurrency Outcome

A deadlock can occur when transaction A holds one lock and waits for B, while B holds another lock and waits for A. Databases detect the cycle and abort one participant.

Reduce deadlocks by:

• accessing shared resources in a consistent order;
• keeping transactions short;
• using selective indexed queries;
• avoiding unnecessary locks;
• updating only required rows;
• separating slow external work;
• retrying known deadlock victims safely.

For a transfer, always lock account rows in ascending ID order rather than source-then-destination order. Opposite-direction transfers then request locks consistently.

Indexes Affect Isolation Behavior

A missing index is not only a speed problem. A locking query that scans many rows may lock or inspect a wider range than intended, increasing contention. Range-lock behavior also depends on the access path chosen by the database.

Use EXPLAIN, inspect query plans, and test under realistic data volume. A query that behaves well with ten rows may cause broad waits with ten million.

Read Replicas Do Not Solve Transaction Isolation

An asynchronous read replica usually does not participate in the primary's current transaction. Reading from a replica after writing to the primary can return stale state. This is replication consistency, not an isolation-level anomaly inside one local transaction.

Use the primary for decisions that must include the latest write, or use infrastructure that can route based on replication position. Never perform a check on a lagging replica and assume a later write to the primary is protected from races.

Test With Separate Connections

A sequential unit test cannot reveal most isolation failures. Use two independent database connections and coordinate their steps:

1. Transaction A reads or locks the target.
2. Transaction B attempts its conflicting operation.
3. Observe whether B blocks, fails, or succeeds.
4. Commit or roll back A.
5. Verify final state and B's result.

Run these tests against the same database engine and isolation configuration used in production. SQLite in-memory connections are useful for ordinary SQL tests but do not model MySQL or PostgreSQL concurrency semantics.

Concurrency tests can be timing-sensitive. Use explicit barriers, advisory coordination, or test hooks instead of arbitrary sleep calls where possible. Record server versions and isolation settings in failure output.

Choose The Narrowest Reliable Technique

Do not raise every transaction to Serializable without measuring impact. Do not use optimistic locking when users cannot sensibly resolve conflicts. Do not lock rows when one atomic statement expresses the invariant.

A practical order of consideration is:

1. Can a constraint make invalid state impossible?
2. Can one atomic conditional statement perform the decision and write?
3. Can optimistic version checking reject stale updates?
4. Does the workflow require a locking read?
5. Does a cross-row invariant justify Serializable or a coordination record?

The answer depends on conflict rate, user expectations, database behavior, and operational load.

Review Checklist

For concurrent database code, ask:

• Which reads influence later writes?
• What can another worker change between them?
• Which anomaly would violate the business rule?
• Can a constraint or atomic statement close the race?
• Is pessimistic or optimistic concurrency more suitable?
• What isolation level is actually configured?
• Are locks acquired in a consistent order?
• Are deadlock and serialization retries bounded and safe?
• Do indexes keep locking queries narrow?
• Has the scenario been tested with separate real-engine connections?

What You Should Be Able To Do

After this lesson, you should be able to describe dirty reads, non-repeatable reads, phantoms, lost updates, and write skew with concrete timelines. You should understand that named isolation levels differ by database implementation and that stronger isolation may produce retryable transaction failures.

You should also be able to choose between atomic conditional writes, pessimistic locks, optimistic version checks, constraints, and Serializable transactions. Most importantly, you should review the interleaving of multiple workers rather than trusting code that only appears correct when run sequentially.