Isolation level: dirty, non-repeatable, phantom read trên MySQL, PostgreSQL và Laravel

Flash sale, đúng 9 giờ. Trang sản phẩm ghi còn 5 cái. Tới 9 giờ 1 phút, kho nhận 14 đơn đã thanh toán cho đúng sản phẩm đó. Checkout nào cũng chạy trong DB::transaction(), nên team tưởng vậy là an toàn. Không phải: code đọc stock ra PHP, trừ đi một rồi ghi con số đó ngược vô. Hai transaction cùng đọc ra 5, cùng ghi 4, cùng commit mà không có lỗi nào hết.

Transaction cho mình tính nguyên tử: hoặc commit hết, hoặc không commit chút nào. Bản thân nó không làm cho các transaction chạy đồng thời cư xử như thể chạy lần lượt. Chúng thấy nhau tới mức nào là do isolation level quyết định, mà MySQL với PostgreSQL lại có default khác nhau và hành vi khác nhau dù cùng tên level. Dưới đây là mỗi level thật sự đảm bảo gì và chọn level ra sao trong Laravel.

Bug này chạy chậm lại thì trông ra sao

Đây là lost update. Không có gì cao siêu:

-- 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.

Với REPEATABLE READ mặc định của MySQL và READ COMMITTED mặc định của PostgreSQL, kiểu xen kẽ này commit ngon lành. PostgreSQL ở REPEATABLE READ sẽ từ chối câu update của session B với lỗi could not serialize access due to concurrent update. MySQL ở REPEATABLE READ thì không, vì UPDATE của InnoDB đọc dòng mới commit nhất (gọi là "current read") chứ không đọc snapshot của transaction. Cùng tên mà kết quả khác.

Mấy kiểu đọc bất thường

Chuẩn SQL định nghĩa isolation level dựa trên việc nó cấm anomaly nào:

• Dirty read: thấy dữ liệu mà transaction khác chưa commit và có thể còn rollback.
• Non-repeatable read: đọc cùng một dòng hai lần mà ra hai giá trị khác nhau vì có người commit xen giữa.
• Phantom read: chạy cùng một query theo khoảng hai lần mà xuất hiện (hoặc biến mất) dòng mới vì có người insert hay delete dòng khớp điều kiện.

Chuẩn chỉ nói cái gì không được xảy ra. Engine có quyền cấm nhiều hơn, và cả hai engine lớn đều làm vậy.

MySQL InnoDB so với PostgreSQL

MySQL InnoDB, mặc định REPEATABLE READ. SELECT thường là consistent read từ một snapshot chụp ở lần đọc đầu tiên của transaction, nên không thấy non-repeatable read hay phantom. Locking read (SELECT ... FOR UPDATE, FOR SHARE) và UPDATE/DELETE thì làm việc trên dòng mới commit nhất và giữ next-key lock (dòng cộng khoảng trống) trên đoạn index đã quét, chặn được phantom cho thao tác ghi nhưng cũng chặn insert vô mấy khoảng trống đó, một nguồn deadlock rất hay gặp. READ UNCOMMITTED thì đúng là cho dirty read. SERIALIZABLE biến SELECT thường thành SELECT ... FOR SHARE khi tắt autocommit, nên nó dựa trên lock và dễ bị chờ lock.

PostgreSQL, mặc định READ COMMITTED. Mỗi câu lệnh thấy một snapshot mới của dữ liệu đã commit. READ UNCOMMITTED được chấp nhận nhưng chạy y chang READ COMMITTED; Postgres không bao giờ cho thấy dữ liệu dơ. REPEATABLE READ là snapshot isolation: một snapshot cho cả transaction, không phantom, và nếu mình update một dòng mà người khác đã sửa sau snapshot của mình thì nhận SQLSTATE 40001 và phải retry. SERIALIZABLE thêm Serializable Snapshot Isolation (SSI): theo dõi phụ thuộc đọc/ghi và abort một transaction khi tổ hợp đó không thể xảy ra nếu chạy tuần tự. Nó không thêm lock chặn cho thao tác đọc, nhưng mình phải sẵn sàng retry.

Snapshot isolation ở cả hai engine vẫn cho phép write skew: hai transaction đọc cùng dữ kiện, mỗi bên ghi một dòng khác nhau, gộp lại thì phá vỡ ràng buộc.

-- 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.

Chỉ có SERIALIZABLE của PostgreSQL (hoặc lock tường minh) mới chặn được chuyện này.

Cách set level cũng hơi khác nhau:

-- 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

Chọn level nào

Isolation level là công cụ khá thô. Phần lớn bug về tính đúng đắn có cách sửa gọn hơn, và quyết định thường đi như vầy:

• Đọc rồi sửa một dòng (tồn kho, số dư, bộ đếm): làm phép tính ngay trong SQL bằng UPDATE ... SET stock = stock - 1 WHERE stock >= 1 nguyên tử, hoặc lock dòng bằng lockForUpdate(). Chạy đúng ở mọi level, trên cả hai engine.
• Report và đọc nhiều query cần khớp nhau (tổng khớp với từng dòng chi tiết): REPEATABLE READ cho một snapshot suốt transaction. MySQL mặc định đã vậy; trên PostgreSQL thì phải xin nó.
• Ràng buộc trải trên nhiều dòng khó lock (luật trực ca, đặt lịch bị trùng, "tối đa 3 session đang chạy"): SERIALIZABLE của PostgreSQL kèm retry, hoặc lock dòng cha (lock shift rồi mới đụng on_call).
• OLTP lưu lượng cao trên MySQL: nhiều team đổi default của session sang READ COMMITTED để giảm gap lock và deadlock, rồi dùng lock tường minh ở chỗ cần.

Cài đặt trong Laravel

Cho level tường minh và an toàn kiểu bằng một backed enum, để không ai truyền SQL tùy tiện vô câu SET:

namespace App\Database;

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

DB::transaction($callback, $attempts) của Laravel vốn đã retry khi phát hiện lỗi đồng thời (deadlock, lock wait timeout, SQLSTATE 40001). Cái nó không làm là set isolation level giùm mình. Helper dưới đây bọc chuyện đó và xử lý khác biệt giữa driver: PostgreSQL cần SET TRANSACTION là câu đầu tiên bên trong transaction, MySQL cần nó ngay trước BEGIN, và vì nó chỉ áp dụng cho transaction kế tiếp nên mỗi lần retry phải chạy lại.

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);
    }
}

Rồi chọn công cụ rẻ nhất mà vẫn đúng cho từng trường hợp:

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);
    }
}

Kiểm thử hành vi isolation

Isolation là chuyện đồng thời, nên test có ý nghĩa cần hai connection thật tới database thật. Clone config connection sang một tên thứ hai, mở transaction ở connection này, commit thay đổi qua connection kia, rồi assert connection đầu thấy gì. Đừng xài RefreshDatabase chỗ này: nó bọc test trong một transaction ở connection mặc định, che mất đúng cái hành vi mình muốn quan sát.

// 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();
});

Những lỗi hay gặp

• Nghĩ DB::transaction() chặn được lost update. Không có đâu. Đọc rồi sửa trong PHP thì cần lockForUpdate() hoặc câu update nguyên tử trong SQL.
• Tưởng "REPEATABLE READ" ở đâu cũng giống nhau. Bản của MySQL cho UPDATE thấy dữ liệu mới hơn SELECT; bản của PostgreSQL thì abort với 40001.
• Xài SERIALIZABLE mà không retry. Lỗi serialization là chuyện bình thường, không phải ngoại lệ hiếm. Không có vòng retry thì tải cao là ăn 500 ngẫu nhiên.
• Side effect bên trong transaction có retry. Retry chạy lại nguyên closure. Trừ tiền thẻ hay dispatch job bên trong có thể xảy ra hai lần.
• Set level sai thời điểm. Trên PostgreSQL, SET TRANSACTION sau bất kỳ query nào trong transaction sẽ lỗi. Trên MySQL, chạy nó trong lúc đang có transaction cũng lỗi; còn chạy một lần rồi retry thì lần retry âm thầm chạy ở level mặc định của session.
• Transaction dài dưới REPEATABLE READ. Snapshot cũ giữ các phiên bản dòng cũ sống hoài (undo log của InnoDB, dead tuple của Postgres), làm phình storage và làm chậm vacuum, purge.
• Deadlock do gap lock trên MySQL. SELECT ... FOR UPDATE theo khoảng ở REPEATABLE READ lock luôn khoảng trống. Lock theo primary key khi được, giữ thứ tự lock nhất quán, và giữ transaction ngắn.

Khi nào xài cái nào, khi nào không

Xài default khi thao tác ghi chỉ đụng một dòng và được viết bằng SQL nguyên tử, hoặc được bảo vệ bằng lockForUpdate(). Vậy là đủ cho đa số app CRUD.

Xài REPEATABLE READ cho các lần đọc nhiều câu lệnh cần khớp với nhau, như export, hóa đơn, job đối soát, nhất là trên PostgreSQL vì ở đó nó không phải default.

Xài SERIALIZABLE của PostgreSQL cho ràng buộc nghiệp vụ trải trên nhiều dòng khi lock khó làm, và chỉ khi đi kèm vòng retry cùng thân transaction idempotent.

Đừng nâng isolation level cho cả hệ thống "cho chắc ăn". Nó làm tăng abort, chờ lock và deadlock trên toàn app mà vẫn không sửa được chuyện đọc rồi sửa trong code app trên MySQL. Đừng bao giờ xài READ UNCOMMITTED cho logic nghiệp vụ.