Transaction Isolation Levels: Dirty, Non-Repeatable and Phantom Reads in MySQL and PostgreSQL

Flash sale, 9:00 sharp. The product page says 5 left. By 9:01 the warehouse has 14 paid orders for that product. Every checkout ran inside DB::transaction(), so the team assumed it was safe. It was not: the code read stock into PHP, subtracted one, and wrote the number back. Two transactions read the same 5, both wrote 4, and both committed without a single error.

A transaction gives you atomicity: all of it commits or none of it does. It does not, on its own, make concurrent transactions behave as if they ran one after another. How much they can see of each other is controlled by the isolation level, and MySQL and PostgreSQL ship with different defaults and different behaviour under the same names. Here is what each level really guarantees and how to pick one in Laravel.

The Bug in Slow Motion

This is a lost update. Nothing in it is exotic:

-- Session A                                  -- Session B
BEGIN;                                        BEGIN;
SELECT stock FROM products WHERE id = 7;      SELECT stock FROM products WHERE id = 7;
-- app sees 5, computes 5 - 1 = 4             -- app sees 5, computes 5 - 1 = 4
UPDATE products SET stock = 4 WHERE id = 7;
COMMIT;
                                              UPDATE products SET stock = 4 WHERE id = 7;
                                              COMMIT;
-- Two units sold, stock went from 5 to 4.

Under MySQL's default REPEATABLE READ and PostgreSQL's default READ COMMITTED, this interleaving commits happily. PostgreSQL REPEATABLE READ would reject session B's update with could not serialize access due to concurrent update. MySQL REPEATABLE READ would not, because InnoDB's UPDATE reads the latest committed row (a "current read"), not the transaction's snapshot. Same name, different outcome.

The Read Anomalies

The SQL standard defines isolation levels by which anomalies they forbid:

• Dirty read: you see data another transaction has not committed and may roll back.
• Non-repeatable read: you read the same row twice and get different values because someone committed in between.
• Phantom read: you run the same range query twice and new rows appear (or vanish) because someone inserted or deleted matching rows.

The standard says what must not happen. Engines are free to forbid more, and both of the big ones do.

MySQL InnoDB vs PostgreSQL

MySQL InnoDB, default REPEATABLE READ. Plain SELECTs are consistent reads from a snapshot taken at the first read of the transaction, so they see neither non-repeatable reads nor phantoms. Locking reads (SELECT ... FOR UPDATE, FOR SHARE) and UPDATE/DELETE work on the latest committed rows and take next-key locks (row plus gap) on the scanned index range, which blocks phantoms for writes but also blocks inserts into those gaps, a common source of deadlocks. READ UNCOMMITTED really does allow dirty reads. SERIALIZABLE turns plain SELECT into SELECT ... FOR SHARE when autocommit is off, so it is lock based and prone to lock waits.

PostgreSQL, default READ COMMITTED. Each statement sees a fresh snapshot of committed data. READ UNCOMMITTED is accepted but behaves exactly like READ COMMITTED; Postgres never shows dirty data. REPEATABLE READ is snapshot isolation: one snapshot for the whole transaction, no phantoms, and if you try to update a row that someone else changed after your snapshot, you get SQLSTATE 40001 and must retry. SERIALIZABLE adds Serializable Snapshot Isolation (SSI): it tracks read/write dependencies and aborts one transaction when the combination could not have happened serially. It uses no extra blocking locks for reads, but you must be ready to retry.

Snapshot isolation in both engines still allows write skew: two transactions read the same facts, each writes a different row, and together they break an invariant.

-- Rule: at least one doctor must stay on call for shift 42.
-- Both transactions run under REPEATABLE READ (snapshot isolation).

-- A: SELECT COUNT(*) FROM on_call WHERE shift_id = 42;   -> 2, ok to leave
-- B: SELECT COUNT(*) FROM on_call WHERE shift_id = 42;   -> 2, ok to leave
-- A: DELETE FROM on_call WHERE shift_id = 42 AND doctor_id = 1; COMMIT;
-- B: DELETE FROM on_call WHERE shift_id = 42 AND doctor_id = 2; COMMIT;
-- Result: nobody on call. Different rows were written, so no write conflict.
-- PostgreSQL SERIALIZABLE detects this and aborts one of them with SQLSTATE 40001.

Only PostgreSQL SERIALIZABLE (or explicit locking) prevents that.

Setting the level is also slightly different:

-- MySQL: applies to the NEXT transaction in this session only
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ...
COMMIT;

-- PostgreSQL: must be the first statement inside the transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT;

-- Check the current level
SELECT @@transaction_isolation;        -- MySQL
SHOW transaction_isolation;            -- PostgreSQL

Choosing a Level

The isolation level is a blunt instrument. Most correctness bugs have a sharper fix, and the decision usually goes like this:

• Single-row read-modify-write (stock, balances, counters): do the math in SQL with an atomic UPDATE ... SET stock = stock - 1 WHERE stock >= 1, or lock the row with lockForUpdate(). Works at any level, on both engines.
• Reports and multi-query reads that must be internally consistent (totals matching line items): REPEATABLE READ gives one snapshot for the whole transaction. MySQL already does this by default; on PostgreSQL ask for it.
• Invariants spanning rows you cannot easily lock (on-call rules, booking overlaps, "max 3 active sessions"): PostgreSQL SERIALIZABLE with retries, or a lock on a parent row (lock the shift, then touch on_call).
• High-throughput OLTP on MySQL: many teams switch the session default to READ COMMITTED to reduce gap locks and deadlocks, then rely on explicit locks where needed.

Implementation in Laravel

Make the level explicit and type-safe with a backed enum, so nobody can pass arbitrary SQL into a SET statement:

namespace App\Database;

enum IsolationLevel: string
{
    case ReadCommitted = 'READ COMMITTED';
    case RepeatableRead = 'REPEATABLE READ';
    case Serializable = 'SERIALIZABLE';
}

Laravel's DB::transaction($callback, $attempts) already retries when it detects a concurrency error (deadlocks, lock wait timeouts, SQLSTATE 40001). What it does not do is set the isolation level for you. The helper below wraps that, and handles the driver difference: PostgreSQL needs SET TRANSACTION as the first statement inside the transaction, MySQL needs it right before BEGIN, and since it only applies to the next transaction it has to be reissued on every retry.

namespace App\Database;

use Closure;
use Illuminate\Database\ConnectionInterface;
use Illuminate\Database\QueryException;
use Illuminate\Support\Facades\DB;
use Throwable;

final class Isolated
{
    /** SQLSTATEs that mean "safe to retry the whole transaction". */
    private const RETRYABLE = ['40001', '40P01'];

    /** @param Closure(ConnectionInterface): mixed $callback */
    public static function run(
        IsolationLevel $level,
        Closure $callback,
        int $attempts = 3,
        ?string $connection = null,
    ): mixed {
        $db = DB::connection($connection);

        for ($attempt = 1; ; $attempt++) {
            try {
                if ($db->getDriverName() === 'pgsql') {
                    return $db->transaction(function () use ($db, $level, $callback) {
                        // Must be the first statement of the transaction in PostgreSQL.
                        $db->statement('SET TRANSACTION ISOLATION LEVEL ' . $level->value);

                        return $callback($db);
                    });
                }

                // MySQL / MariaDB: SET TRANSACTION affects the next transaction only,
                // so it has to be issued before BEGIN, on every attempt.
                $db->statement('SET TRANSACTION ISOLATION LEVEL ' . $level->value);

                return $db->transaction(fn () => $callback($db));
            } catch (QueryException $e) {
                if ($attempt >= $attempts || ! self::isRetryable($e)) {
                    throw $e;
                }
                usleep(random_int(5_000, 20_000) * $attempt);
            }
        }
    }

    public static function isRetryable(Throwable $e): bool
    {
        $state = $e instanceof QueryException ? ($e->errorInfo[0] ?? $e->getCode()) : $e->getCode();

        return in_array((string) $state, self::RETRYABLE, true);
    }
}

Then use the cheapest tool that is correct for each case:

namespace App\Actions;

use App\Database\Isolated;
use App\Database\IsolationLevel;
use App\Models\OnCall;
use App\Models\Product;
use DomainException;
use Illuminate\Support\Facades\DB;

final class InventoryActions
{
    /** Atomic decrement: no isolation tricks needed, the row lock does the work. */
    public function sell(int $productId, int $qty): void
    {
        $affected = Product::query()
            ->whereKey($productId)
            ->where('stock', '>=', $qty)
            ->decrement('stock', $qty);

        if ($affected === 0) {
            throw new DomainException('Out of stock');
        }
    }

    /** Invariant across several rows: let SERIALIZABLE catch write skew, then retry. */
    public function leaveShift(int $shiftId, int $doctorId): void
    {
        Isolated::run(IsolationLevel::Serializable, function () use ($shiftId, $doctorId) {
            $count = OnCall::query()->where('shift_id', $shiftId)->count();

            if ($count <= 1) {
                throw new DomainException('Someone has to stay on call');
            }

            OnCall::query()
                ->where('shift_id', $shiftId)
                ->where('doctor_id', $doctorId)
                ->delete();
        }, attempts: 5);
    }
}

Testing Isolation Behaviour

Isolation is about concurrency, so a meaningful test needs two real connections to a real database. Clone the connection config under a second name, open a transaction on one, commit a change through the other, and assert what the first one sees. Do not use RefreshDatabase here: it wraps the test in a transaction on the default connection, which hides exactly the behaviour you want to observe.

// tests/Feature/IsolationLevelTest.php  (runs against a real PostgreSQL database)
use Illuminate\Database\QueryException;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

beforeEach(function () {
    config(['database.connections.pgsql_b' => config('database.connections.pgsql')]);
    Schema::connection('pgsql')->dropIfExists('iso_accounts');
    Schema::connection('pgsql')->create('iso_accounts', function ($table) {
        $table->id();
        $table->integer('balance');
    });
    DB::connection('pgsql')->table('iso_accounts')->insert(['id' => 1, 'balance' => 100]);
});

afterEach(function () {
    DB::purge('pgsql_b');
    Schema::connection('pgsql')->dropIfExists('iso_accounts');
});

function balance(string $connection): int
{
    return (int) DB::connection($connection)->table('iso_accounts')->where('id', 1)->value('balance');
}

it('sees a non-repeatable read under READ COMMITTED', function () {
    $a = DB::connection('pgsql');
    $a->beginTransaction();
    expect(balance('pgsql'))->toBe(100);

    DB::connection('pgsql_b')->table('iso_accounts')->where('id', 1)->update(['balance' => 50]);

    expect(balance('pgsql'))->toBe(50);
    $a->rollBack();
});

it('keeps a stable snapshot under REPEATABLE READ', function () {
    $a = DB::connection('pgsql');
    $a->beginTransaction();
    $a->statement('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
    expect(balance('pgsql'))->toBe(100);

    DB::connection('pgsql_b')->table('iso_accounts')->where('id', 1)->update(['balance' => 50]);

    expect(balance('pgsql'))->toBe(100);

    expect(fn () => $a->table('iso_accounts')->where('id', 1)->update(['balance' => 90]))
        ->toThrow(QueryException::class, 'could not serialize access');
    $a->rollBack();
});

Common Pitfalls

• Thinking DB::transaction() prevents lost updates. It does not. Read-modify-write in PHP needs lockForUpdate() or an atomic SQL update.
• Assuming "REPEATABLE READ" means the same thing everywhere. MySQL's version lets UPDATE see newer data than your SELECT; PostgreSQL's version aborts with 40001.
• Using SERIALIZABLE without retries. Serialization failures are normal, not exceptional. No retry loop means random 500s under load.
• Side effects inside a retried transaction. A retry replays the closure. Charging a card or dispatching a job inside it can happen twice.
• Setting the level at the wrong time. In PostgreSQL, SET TRANSACTION after any query in the transaction fails. In MySQL, issuing it inside an active transaction fails; issuing it once and then retrying silently runs the retry at the session default.
• Long transactions under REPEATABLE READ. Old snapshots keep old row versions alive (InnoDB undo log, Postgres dead tuples), which bloats storage and slows vacuum and purge.
• Gap-lock deadlocks on MySQL. SELECT ... FOR UPDATE on a range in REPEATABLE READ locks gaps too. Lock by primary key where you can, keep a consistent lock order, and keep transactions short.

When to Use What, and When Not To

Use the defaults when your writes are single-row and expressed as atomic SQL, or protected by lockForUpdate(). That covers most CRUD apps.

Use REPEATABLE READ for multi-statement reads that must agree with each other, such as exports, invoices and reconciliation jobs, especially on PostgreSQL where it is not the default.

Use PostgreSQL SERIALIZABLE for business invariants across rows when locking is awkward, and only together with a retry loop and idempotent transaction bodies.

Do not raise the isolation level globally "to be safe". It increases aborts, lock waits and deadlocks across the whole app and still does not fix application-level read-modify-write on MySQL. Never use READ UNCOMMITTED for business logic.