Introduction

Production databases rarely process one transaction at a time. Several users, services, background jobs, and scheduled processes can read and update the same data concurrently.

Each transaction can be individually correct but still produce an invalid result when its operations are interleaved with another transaction.

Transaction isolation controls which changes concurrent transactions can observe and which interleavings the database permits.

Isolation helps control problems such as:

• Reading data that is later rolled back
• Reading the same row twice and receiving different values
• Receiving a different set of rows from the same repeated query
• Overwriting another transaction's update
• Overselling inventory or seats
• Violating cross-row business invariants
• Producing inconsistent reports

In your System Design curriculum, Isolation is Topic 5.6 under Relational Data Modeling & SQL. It follows transactions and precedes B-tree and composite indexes, query plans, and connection pools.

Prerequisites

What Is Transaction Isolation?

Transaction isolation determines how concurrent transactions interact and which changes one transaction can observe from another.

Transaction A
      |
      | reads and writes shared data
      v
Database concurrency control
      ^
      | reads and writes shared data
      |
Transaction B

A stronger isolation policy prevents more unsafe interleavings. However, it can also increase blocking, version-storage usage, conflict detection, transaction retries, or other concurrency-control work.

Isolation vs Atomicity

Two transactions can each be atomic while their combined concurrent result still violates a business rule.

Final-seat Example

Available seats:

1


Transaction A:

Reads available seats = 1.
Creates Booking A.
Sets available seats = 0.
Commits.


Transaction B:

Also reads available seats = 1.
Creates Booking B.
Sets available seats = 0.
Commits.


Final result:

Two confirmed bookings
for one available seat.

Each transaction committed completely, but insufficient concurrency control allowed an invalid combined outcome.

Concurrency Anomalies

Dirty Read

A dirty read occurs when a transaction reads data written by another transaction that has not committed.

Initial balance:

500


Transaction A:

Updates balance to 100.
Does not commit.


Transaction B:

Reads balance as 100.
Makes a decision using that value.


Transaction A:

Rolls back.


Result:

Transaction B used a value
that never became committed.

Dirty reads can expose temporary, invalid, or rolled-back data to another transaction.

Non-repeatable Read

A non-repeatable read occurs when one transaction reads the same row twice and sees different committed values because another transaction updates the row between the reads.

Transaction A:

Reads Order 9001 status:
pending


Transaction B:

Updates Order 9001 status:
confirmed

Commits.


Transaction A:

Reads Order 9001 again:
confirmed

Both values were committed when read, but Transaction A did not receive a repeatable value throughout its unit of work.

Phantom Read

A phantom read occurs when a transaction repeats a predicate query and receives a different set of matching rows.

Transaction A:

SELECT all pending orders.

Result:
10 rows.


Transaction B:

Inserts another pending order.
Commits.


Transaction A:

Repeats the same query.

Result:
11 rows.

The additional matching row is called a phantom.

Lost Update

A lost update occurs when two transactions read the same value, calculate new values independently, and one update overwrites the other.

Initial balance:

1,000


Transaction A reads:

1,000

Adds 200.
Plans to write 1,200.


Transaction B reads:

1,000

Subtracts 100.
Plans to write 900.


Transaction A writes:

1,200


Transaction B writes:

900


Final balance:

900


Correct combined balance:

1,100

Transaction A's update was lost because Transaction B calculated from a stale value.

Unsafe Read-modify-write

1. SELECT balance.
2. Calculate new balance in application.
3. UPDATE balance with calculated value.

Relative Database Update

UPDATE accounts
SET balance = balance + :amount
WHERE account_id = :account_id;

A relative database update can avoid the particular stale read-modify-write pattern, although the complete business rule can still require additional checks.

Write Skew

Write skew can occur when concurrent transactions read overlapping data but update different rows. Each transaction individually preserves its local condition, while the combined result violates a cross-row invariant.

On-call Example

Business rule:

At least one assigned person
must remain on call.


Initial state:

Person A = on call
Person B = on call


Transaction A:

Reads both assignments.
Sees Person B is on call.
Sets Person A off call.


Transaction B:

Reads both assignments.
Sees Person A is on call.
Sets Person B off call.


Both commit.


Final state:

Nobody remains on call.

The transactions updated different rows, so a simple conflict on one row might not be detected.

Standard Isolation Levels

SQL commonly describes four isolation levels:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

These names provide a conceptual framework, but exact behaviour differs among database products and configurations.

Read Uncommitted

Read Uncommitted provides weak isolation and can allow one transaction to observe another transaction's uncommitted changes.

SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED;

BEGIN;

SELECT
    account_id,
    balance
FROM accounts
WHERE account_id = 101;

COMMIT;

This level can expose values that are later rolled back. It is unsuitable when a decision must be made from reliable committed data.

Read Committed

Read Committed prevents dirty reads by allowing a query to read committed data. Another transaction can still commit changes between two statements, so repeated reads can return different results.

SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;

BEGIN;

SELECT
    order_status
FROM orders
WHERE order_id = 9001;

-- Another transaction can commit an update.

SELECT
    order_status
FROM orders
WHERE order_id = 9001;

COMMIT;

Depending on the database implementation, each statement can observe a different committed state.

Repeatable Read

Repeatable Read provides stronger stability for rows read during the transaction.

SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ;

BEGIN;

SELECT
    order_status
FROM orders
WHERE order_id = 9001;

SELECT
    order_status
FROM orders
WHERE order_id = 9001;

COMMIT;

The intended guarantee is that rereading a previously read row does not produce a different committed value during that transaction. Range-query, phantom, and conflict behaviour depends on the database implementation.

Serializable

Serializable provides the strongest standard isolation level. Committed transactions must have an outcome equivalent to some valid serial order.

Concurrent execution:

Transaction A and Transaction B overlap.


Serializable requirement:

The committed result must be equivalent to:

A followed by B

or

B followed by A

SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE;

BEGIN;

SELECT
    available_seats
FROM courses
WHERE course_id = :course_id;

UPDATE courses
SET available_seats =
    available_seats - 1
WHERE course_id = :course_id;

COMMIT;

Serializable does not necessarily mean that transactions literally execute one at a time. The database can use locking, validation, versioning, or another concurrency-control method. Conflicting transactions can block or be aborted and require retry.

Snapshot Isolation

Some database systems provide snapshot-based isolation using row versions. A transaction reads a consistent database snapshot rather than waiting for every concurrent writer.

Transaction starts
      |
      v
Database establishes visible snapshot
      |
      v
Transaction reads versions valid
for that snapshot
      |
      v
Concurrent commits create newer versions
      |
      v
Transaction continues according to
snapshot and conflict rules

Snapshot techniques can reduce reader-writer blocking. However, snapshot visibility alone does not automatically protect every cross-row invariant. The exact guarantees must be verified for the selected database.

Statement vs Transaction Snapshots

Product-specific documentation determines which isolation levels use each snapshot scope.

Lock-based Concurrency Control

Lock-based systems coordinate concurrent operations by granting compatible locks and delaying incompatible operations.

Lock names, compatibility, escalation, duration, and range behaviour vary between database systems.

Multi-version Concurrency Control

Multi-version concurrency control, commonly abbreviated as MVCC, maintains several row versions so readers can access an appropriate committed version while writers create newer versions.

Row version 7
Valid for earlier snapshot

Row version 8
Created by newer committed transaction

Reader:
Uses the version visible to its snapshot

Writer:
Creates or updates the newer version

MVCC can reduce reader-writer blocking but introduces version storage, cleanup, visibility, and conflict-management responsibilities.

Pessimistic Locking

Pessimistic concurrency assumes conflicts are sufficiently likely or costly that the database should lock the relevant data before modification.

BEGIN;

SELECT
    available_quantity
FROM inventory
WHERE product_id = :product_id
FOR UPDATE;

UPDATE inventory
SET available_quantity =
    available_quantity - :quantity
WHERE product_id = :product_id
  AND available_quantity >= :quantity;

COMMIT;

Suitable Characteristics

• High contention
• Short transaction duration
• Conflict must be prevented before work proceeds
• Contested resource is easy to identify

Considerations

• Blocking
• Lock waits
• Deadlocks
• Reduced concurrency
• Lock escalation or broader locking

Optimistic Concurrency

Optimistic concurrency allows work to proceed without holding a long-lived write lock and detects whether the data changed before applying the update.

UPDATE customers
SET
    display_name = :display_name,
    row_version = row_version + 1
WHERE customer_id = :customer_id
  AND row_version = :expected_version;

If the affected-row count is zero, the expected version no longer matches the current row.

Client read:

row_version = 7


Another transaction updates row:

row_version = 8


Client update requires:

row_version = 7


Result:

No row updated.
Concurrency conflict detected.

Pessimistic vs Optimistic Control

Correct Final-seat Reservation

A conditional update can atomically test and reserve the final seat.

BEGIN;

UPDATE courses
SET available_seats =
    available_seats - 1
WHERE course_id = :course_id
  AND course_status = 'published'
  AND available_seats > 0;

-- Verify exactly one row was updated.

INSERT INTO enrollments
(
    enrollment_id,
    learner_id,
    course_id,
    enrollment_status
)
VALUES
(
    :enrollment_id,
    :learner_id,
    :course_id,
    'active'
);

COMMIT;

If no course row is updated, the transaction must not create the confirmed enrollment.

Preventing Lost Updates

Unsafe Pattern

SELECT
    available_quantity
FROM inventory
WHERE product_id = :product_id;

-- Application calculates a new value.

UPDATE inventory
SET available_quantity = :new_quantity
WHERE product_id = :product_id;

Atomic Relative Update

UPDATE inventory
SET available_quantity =
    available_quantity - :quantity
WHERE product_id = :product_id
  AND available_quantity >= :quantity;

Version-protected Update

UPDATE inventory
SET
    available_quantity = :new_quantity,
    row_version = row_version + 1
WHERE product_id = :product_id
  AND row_version = :expected_version;

Select the pattern that correctly protects the full business rule.

Preventing Duplicate Relationships

Unique constraints provide atomic protection against concurrent duplicate inserts.

CREATE TABLE enrollments
(
    enrollment_id BIGINT PRIMARY KEY,
    learner_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,

    CONSTRAINT uq_enrollment_learner_course
        UNIQUE
        (
            learner_id,
            course_id
        )
);

Two transactions can both check that no enrollment exists. The UNIQUE constraint decides atomically which insert can succeed.

Deadlocks

A deadlock occurs when transactions wait on resources held by one another.

Transaction A:

Locks Account 1.
Requests Account 2.


Transaction B:

Locks Account 2.
Requests Account 1.


Result:

Circular wait.

The database can detect the cycle and abort one transaction.

Deadlock-reduction Practices

• Access resources in a consistent order
• Keep transactions short
• Use selective queries
• Create suitable indexes
• Avoid remote calls while holding locks
• Update only required rows
• Implement safe bounded retries

Blocking and Lock Waits

Transaction A:

Locks Order 9001.
Performs additional work.


Transaction B:

Attempts to update Order 9001.
Waits for Transaction A.


Possible outcomes:

- Transaction A commits and B continues
- Transaction A rolls back and B continues
- B reaches its lock timeout
- A deadlock is detected

Long lock waits increase response latency and can consume database connections and worker capacity.

Transaction Duration and Isolation

Strong isolation is easier to operate when transactions are small and focused.

Begin transaction

Lock inventory
Call payment provider
Generate document
Send notification
Wait for response

Commit

Perform safe external preparation

Begin transaction

Read current state
Validate conditions
Reserve inventory
Create order
Insert outbox event

Commit

Perform external follow-up asynchronously

Isolation Failures and Retries

Stronger isolation can reject a transaction when the database cannot preserve the required concurrent history.

Transaction conflict
      |
      v
Database aborts transaction
      |
      v
Application rolls back
      |
      v
Classify failure as retryable
      |
      v
Wait with bounded backoff and jitter
      |
      v
Start a new transaction
      |
      v
Reread current database state
      |
      v
Retry the complete safe operation

A retried transaction must not continue from stale values captured during the failed attempt.

Isolation and Idempotency

Isolation controls concurrent database interactions. Idempotency controls repeated execution of one logical request.

Isolation:

Prevents concurrent transactions from
producing an unsafe combined outcome.


Idempotency:

Prevents the same logical API operation
from creating duplicate intended effects.

A transaction retried after a deadlock or lost network response might need both isolation and a scoped idempotency key.

Setting Isolation in SQL

SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;

BEGIN;

SELECT
    order_id,
    order_status
FROM orders
WHERE customer_id = :customer_id;

COMMIT;

Exact syntax and whether the setting applies to one transaction, connection, or session depend on the database platform.

PHP Optimistic-concurrency Example

<?php

declare(strict_types=1);

function updateCustomerName(
    PDO $pdo,
    int $customerId,
    int $expectedVersion,
    string $displayName
): void {
    $pdo->beginTransaction();

    try {
        $statement =
            $pdo->prepare(
                '
                UPDATE customers
                SET
                    display_name =
                        :display_name,
                    row_version =
                        row_version + 1
                WHERE customer_id =
                        :customer_id
                  AND row_version =
                        :expected_version
                '
            );

        $statement->execute([
            'display_name' =>
                $displayName,
            'customer_id' =>
                $customerId,
            'expected_version' =>
                $expectedVersion
        ]);

        if ($statement->rowCount() !== 1) {
            throw new RuntimeException(
                'The customer was modified by another transaction.'
            );
        }

        $pdo->commit();
    } catch (Throwable $exception) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }

        throw $exception;
    }
}

Production code should distinguish a missing resource from a version conflict and map each result to an appropriate domain response.

PHP Transaction-retry Pattern

<?php

declare(strict_types=1);

function executeWithRetry(
    PDO $pdo,
    callable $operation,
    int $maximumAttempts = 3
): mixed {
    for (
        $attempt = 1;
        $attempt <= $maximumAttempts;
        $attempt++
    ) {
        $pdo->beginTransaction();

        try {
            $result =
                $operation(
                    $pdo
                );

            $pdo->commit();

            return $result;
        } catch (PDOException $exception) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }

            $retryable =
                isRetryableConcurrencyError(
                    $exception
                );

            if (!$retryable ||
                $attempt === $maximumAttempts) {

                throw $exception;
            }

            $maximumDelayMicroseconds =
                min(
                    500000,
                    50000 * (2 ** ($attempt - 1))
                );

            usleep(
                random_int(
                    0,
                    $maximumDelayMicroseconds
                )
            );
        } catch (Throwable $exception) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }

            throw $exception;
        }
    }

    throw new RuntimeException(
        'The transaction could not be completed.'
    );
}

The retryable database error codes and transaction-state rules are platform-specific. The complete operation must also be safe to retry and protected against duplicate external effects.

Testing Isolation

Isolation bugs require coordinated concurrent sessions. Single-session tests cannot reproduce most transaction interleavings.

Session A

BEGIN;

SELECT
    available_seats
FROM courses
WHERE course_id = 101;

-- Keep the transaction open
-- while Session B runs.

Session B

BEGIN;

UPDATE courses
SET available_seats =
    available_seats - 1
WHERE course_id = 101
  AND available_seats > 0;

COMMIT;

Session A Continues

SELECT
    available_seats
FROM courses
WHERE course_id = 101;

COMMIT;

Repeat the experiment under approved isolation configurations and document the observed values, blocking, errors, and final database state.

Choosing an Isolation Strategy

1. Define the business invariant.
2. Identify the rows and predicates involved.
3. Draw two or more conflicting transaction timelines.
4. Identify dirty reads, lost updates, phantoms, or write skew.
5. Determine whether a constraint can enforce the invariant.
6. Consider an atomic conditional update.
7. Consider optimistic version checks.
8. Consider explicit locking for highly contested resources.
9. Select the minimum isolation that safely supports the operation.
10. Test the actual database implementation.
11. Add retry handling for abortable conflicts.
12. Monitor contention, waits, failures, and transaction duration.

Isolation Observability

Useful concurrency metrics include:

• Transaction count by isolation level
• Transaction duration
• Lock-wait duration
• Blocked transaction count
• Deadlock count
• Serialization-failure count
• Optimistic-concurrency conflict count
• Retry count and retry success rate
• Long-running transaction count
• Version-store or row-version pressure
• Lock-timeout count
• Contested tables, operations, and resources

Use bounded resource identifiers and trace information in diagnostics without exposing credentials or sensitive business values.

Common Isolation Mistakes

Recommended Test Cases

Isolation Best Practices

Practice Exercise

Test and protect concurrent course enrollment for the final available seat in your online learning platform.

Requirements

1. Create a course with one remaining seat.
2. Open two independent database sessions.
3. Start one enrollment transaction in each session.
4. Demonstrate the unsafe read-then-write pattern.
5. Record the final capacity and enrollment count.
6. Replace the pattern with an atomic conditional update.
7. Verify the affected-row count.
8. Protect duplicate enrollment with a unique constraint.
9. Test Read Committed behaviour.
10. Test Repeatable Read behaviour.
11. Test Serializable behaviour.
12. Record blocking and transaction-conflict errors.
13. Add bounded retry handling.
14. Add an idempotency key for the enrollment request.
15. Verify that only one enrollment receives the final seat.

Safe Enrollment Transaction

BEGIN;

UPDATE courses
SET available_seats =
    available_seats - 1
WHERE course_id = :course_id
  AND course_status = 'published'
  AND available_seats > 0;

-- Continue only when exactly one row was updated.

INSERT INTO enrollments
(
    enrollment_id,
    learner_id,
    course_id,
    enrollment_status,
    enrolled_at
)
VALUES
(
    :enrollment_id,
    :learner_id,
    :course_id,
    'active',
    CURRENT_TIMESTAMP
);

COMMIT;

Isolation Test Matrix

Frequently Asked Questions