Рівні ізоляції: бази даних для PHP-розробника

01 · ЧОТИРИ РІВНІ, ТРИ АНОМАЛІЇ

Що одна транзакція бачить з чужих змін

Поки транзакція T1 читає, транзакція T2 може змінювати ті самі рядки. Повна ізоляція означала б чергу, де кожна транзакція чекає на попередню. Це повільно, тому стандарт SQL описує чотири і для кожного каже, яких аномалій на ньому бути не може.

СЛОВНИКРівень ізоляції. Обіцянка бази, яких аномалій не буде, коли транзакції йдуть одночасно. Задається для сесії або для однієї транзакції: SET TRANSACTION ISOLATION LEVEL …

Аномалій три. : T1 бачить зміну, яку T2 ще не закомітила і може відкотити. : T1 двічі читає той самий рядок і отримує різні значення, бо між читаннями T2 закомітила UPDATE. : T1 двічі виконує запит з умовою, а другий раз отримує інший набір рядків, бо T2 вставила або видалила рядок під цю умову.

СЛОВНИКБрудне читання. Читання незакоміченої зміни іншої транзакції. Якщо та відкотиться, виявиться, що ви працювали з даними, яких ніколи не було.

СЛОВНИКНеповторюване читання. Той самий рядок, прочитаний двічі в одній транзакції, має різні значення, бо між читаннями інша транзакція його змінила й закомітила.

СЛОВНИКФантом. Той самий запит з умовою, виконаний двічі, повертає інший набір рядків: інша транзакція вставила чи видалила рядок під умову.

READ UNCOMMITTED дозволяє всі три. READ COMMITTED забороняє брудне читання. REPEATABLE READ ще й неповторюване. SERIALIZABLE обіцяє результат, ніби транзакції йшли одна за одною. Стандарт задає лише мінімум, і бази можуть давати більше: PostgreSQL на REPEATABLE READ не показує фантомів, хоча міг би.

Читання при цьому не стоїть у черзі за записом завдяки . UPDATE не перезаписує рядок, а створює нову версію, і стара живе, поки її може бачити чиясь транзакція. Читання бере , тобто версії, закомічені до певного моменту. Рівні здебільшого відрізняються тим, коли цей знімок береться: на кожен запит чи один на всю транзакцію.

СЛОВНИКMVCC. Multiversion concurrency control: база тримає кілька версій рядка, і кожна транзакція бачить ті, що відповідають її знімку. В InnoDB старі версії відновлюються з undo log, у PostgreSQL лежать у самій таблиці, поки їх не прибере VACUUM.

СЛОВНИКЗнімок. Набір версій, закомічених до певного моменту. На READ COMMITTED кожен запит бере свій знімок, на REPEATABLE READ один знімок діє до кінця транзакції.

02 · ТАЙМЛАЙН

Одна історія на чотирьох рівнях

T1 двічі читає баланс рахунку 1 (SELECT balance FROM accounts WHERE id = 1) і двічі рахує рахунки з додатним балансом (SELECT COUNT(*) FROM accounts WHERE balance > 0). Між цим T2 змінює баланс з 100 на 50, додає рахунок 2 і комітить.

Посередині версії рядка: суцільна рамка це закомічена версія, пунктир ще ні, закреслена стара, яку база тримає для чужих знімків. Перемикайте рівень і дивіться, що повертає кожне читання T1 і де трапляється аномалія.

РІВЕНЬ ІЗОЛЯЦІЇ · СТАНДАРТ SQL

T1 · читає рахунок id=1 T2 · пише t0

BEGIN

v1 · 100

BEGIN

SELECT balance → 100 → 100 → 100 → 100

v1 · 100

SELECT COUNT(*) → 1 → 1 → 1 → 1

v1 · 100

v1 · 100 v2 · 50

UPDATE balance = 50

SELECT balance → 50 БРУДНЕ ЧИТАННЯ → 100 → 100 → 100

v1 · 100 v2 · 50

v1 · 100 v2 · 50 id=2 · 80

INSERT id=2, 80

v1 · 100 v2 · 50 id=2 · 80

COMMIT

SELECT balance → 50 → 50 НЕПОВТОРЮВАНЕ → 100 → 100

v1 · 100 v2 · 50 id=2 · 80

SELECT COUNT(*) → 2 ФАНТОМ → 2 ФАНТОМ → 2 ФАНТОМ → 1

v1 · 100 v2 · 50 id=2 · 80

COMMIT

v2 · 50 id=2 · 80

ПІДСУМОК На t4 T1 бачить 50 ще до COMMIT у T2. Якби T2 відкотилась, T1 працювала б з балансом, якого ніколи не було. Далі і COUNT виріс з 1 до 2: фантом. Брудного читання немає: незакомічені 50 T1 не бачить. Але кожен запит бере свіжий знімок, тож два однакові SELECT в одній транзакції повернули 100 і 50, а COUNT виріс з 1 до 2. Значення рядка стабільне: T1 до кінця бачить 100. Але за стандартом нові рядки під умову зʼявитися можуть, і COUNT має право вирости до 2. Як ніби T1 відпрацювала цілком до T2: жодної аномалії. Ціна різна в різних базах: очікування на блокуваннях або відкат однієї з транзакцій з проханням повторити.

InnoDB справді віддає незакомічену версію: на t4 T1 отримала 50. Рівень майже ніколи не потрібен у застосунку.

PostgreSQL приймає READ UNCOMMITTED, але працює як READ COMMITTED: на t4 T1 отримала 100. Брудного читання в PostgreSQL не буває взагалі, так сказано і в документації.

Поведінка як у стандарті: 100, 100, 50, а COUNT 1 і 2. У MySQL це не рівень за замовчуванням, його вмикають явно, наприклад isolation_level у конфігу підключення Laravel.

Рівень за замовчуванням у PostgreSQL і на цьому сайті. Результат той самий: 100, 100, 50, а COUNT 1 і 2. Кожен оператор бачить знімок на момент свого старту.

Рівень за замовчуванням у MySQL. Звичайний SELECT читає знімок, узятий першим читанням транзакції, тож T1 бачить 100 і COUNT = 1 до кінця, фантома немає. Але блокувальне читання поводиться інакше: SELECT … FOR UPDATE на t8 повернуло 2 рядки, бо читає поточні закомічені дані й ставить next-key блокування, яке ще й не дає іншим вставити рядок у прочитаний діапазон.

Знімок один на всю транзакцію, тож фантомів немає: 100 і COUNT = 1, хоча стандарт дозволив би 2. Якщо T1 спробує змінити або заблокувати рядок, який T2 змінила після знімка, вона отримає ERROR: could not serialize access due to concurrent update і має повторити транзакцію.

InnoDB перетворює звичайні SELECT усередині транзакції на SELECT … FOR SHARE. Тому UPDATE у T2 на t3 чекає, поки T1 закомітить. Якщо чекати довше за innodb_lock_wait_timeout (50 секунд за замовчуванням), T2 отримає ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

SSI: ніхто не чекає, T1 читає той самий знімок, що й на REPEATABLE READ, і в цьому сценарії обидві транзакції комітяться, бо T1 можна поставити перед T2. Коли такого порядку немає, наприклад обидві прочитали суму й обидві дописали рядок, друга на COMMIT отримує ERROR: could not serialize access due to read/write dependencies among transactions.

Таймлайн показує мінімум, який гарантує стандарт SQL. Як поводяться справжні бази, у вкладках MySQL і PostgreSQL: той самий сценарій виконано на MySQL 8.0.46 (InnoDB) і PostgreSQL 16.15 у двох сесіях, обидві на вибраному рівні.

03 · SELECT … FOR UPDATE

Прочитати й одразу заблокувати

Знімок захищає читання, але не логіку «прочитав, порахував у PHP, записав». Дві транзакції читають баланс 100, кожна знімає 30 і пише 70: одне списання зникло. Це втрачене оновлення. Ми відтворили його на MySQL у READ COMMITTED і REPEATABLE READ і на PostgreSQL у READ COMMITTED: в усіх трьох випадках другий UPDATE пройшов і баланс став 70 замість 40. Лише PostgreSQL на REPEATABLE READ відкотив другу транзакцію з помилкою серіалізації.

Надійний спосіб тут один для всіх рівнів: прочитати рядок через SELECT … FOR UPDATE. Таке читання ставить блокування, і друга транзакція з тим самим запитом чекає, поки перша закомітить, а потім бачить уже нове значення. Ще простіше, коли можна, рахувати в самій базі: UPDATE accounts SET balance = balance - 30.

MySQL 8.0 PostgreSQL 16 SQLite 3.45

1-- Сесія B, поки сесія A тримає FOR UPDATE і списує 30

2BEGIN;

3SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

4COMMIT;

+---------+
| balance |
+---------+
|      70 |
+---------+
1 row in set (1.50 sec)

Півтори секунди B чекала на блокування A, а потім прочитала вже 70. Блокувальне читання завжди бере останню закомічену версію, навіть на REPEATABLE READ, і ставить next-key блокування: на рядок разом із проміжком перед ним. Для спільного читання є FOR SHARE, у старих версіях LOCK IN SHARE MODE.

1-- Сесія B, READ COMMITTED: чекає й читає нове значення

2BEGIN;

3SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

4COMMIT;

6-- Сесія B, REPEATABLE READ: знімок узято до чужого COMMIT

7BEGIN ISOLATION LEVEL REPEATABLE READ;

8SELECT balance FROM accounts WHERE id = 1;

9SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

 balance
---------
      70
(1 row)

 balance
---------
     100
(1 row)

ERROR:  could not serialize access due to concurrent update

На READ COMMITTED PostgreSQL після очікування перечитує рядок і віддає 70. На REPEATABLE READ і SERIALIZABLE рядок уже змінено після знімка, тому замість 70 буде помилка і транзакцію треба повторити. NOWAIT дає помилку замість очікування, SKIP LOCKED пропускає зайняті рядки: це для черг.

1SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

Parse error: near "FOR": syntax error

У SQLite немає блокувань рядків: писати може одна транзакція на всю базу. Тому Laravel на SQLite просто не дописує FOR UPDATE, і тест на SQLite не перевіряє, що блокування взагалі є.

04 · У LARAVEL

DB::transaction і lockForUpdate

Laravel не змінює рівень ізоляції сам: транзакції йдуть на рівні бази або того, що задано в конфігу підключення. Він дає BEGIN і COMMIT навколо замикання, повтор після дедлоку й блокування рядків у Query Builder. Усе нижче звірено з кодом vendor/laravel/framework 13.30.

php LeaseTasks.php Транзакція

1DB::transaction(function () {

2 // BEGIN … COMMIT, виняток усередині означає ROLLBACK

3 DB::transaction(function () {

4 // вкладений виклик: SAVEPOINT trans2

5 });

6});

ЩО ПЕРЕВІРЯЄ: transaction() відкриває транзакцію, виконує замикання й комітить. Будь-який виняток відкочує її й летить далі. Вкладений виклик не відкриває нової транзакції, а ставить SAVEPOINT: його виняток відкочує зміни до цієї точки й летить у зовнішнє замикання.
КОЛИ БРАТИ: Коли кілька записів мають стати видимими разом або не стати зовсім.

php LeaseTasks.php Повтор після дедлоку

1DB::transaction(function () {

2 // …

3}, attempts: 3);

ЩО ПЕРЕВІРЯЄ: Другий аргумент це кількість спроб. Laravel повторює замикання лише після помилки конкурентності: SQLSTATE 40001 або повідомлення на кшталт Deadlock found when trying to get lock, deadlock detected, Lock wait timeout exceeded. Будь-який інший виняток летить одразу. Усередині вкладеної транзакції повтору немає: помилка стає DeadlockException і йде до зовнішньої.
КОЛИ БРАТИ: Для коротких транзакцій без побічних ефектів поза базою. Лист чи HTTP-запит у замиканні піде стільки разів, скільки буде спроб.

php LeaseTasks.php lockForUpdate

1$task = ContentTaskModel::query()

2 ->whereKey($id)

3 ->lockForUpdate() // MySQL і PostgreSQL: for update, SQLite: нічого

4 ->first();

6DB::table('accounts')->where('id', 1)

7 ->sharedLock() // MySQL: lock in share mode, PostgreSQL: for share

8 ->first();

10DB::table('content_tasks')->lock('for update skip locked');

ЩО ПЕРЕВІРЯЄ: lockForUpdate() дописує for update, sharedLock() дописує lock in share mode або for share. lock() з рядком дописує будь-який хвіст, наприклад for update skip locked. Усі три шлють запит на підключення для запису. Сенс вони мають лише всередині транзакції: без неї блокування знімається, щойно запит завершився.
КОЛИ БРАТИ: Перед «прочитав, порахував, записав» над тим самим рядком, і для черг задач, де кілька воркерів беруть роботу.

php LeaseTasks.php Рівень ізоляції

1// config/database.php, підключення mysql або pgsql

2'isolation_level' => 'READ COMMITTED',

4// PostgreSQL: першою командою всередині транзакції

5DB::transaction(function () {

6 DB::statement('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');

7});

9// MySQL: перед транзакцією, діє на одну наступну

10DB::statement('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');

11DB::transaction(function () {

12 // …

13});

ЩО ПЕРЕВІРЯЄ: Ключ isolation_level конектори MySQL і PostgreSQL виконують одразу після підключення для всієї сесії. Для однієї транзакції порядок різний: PostgreSQL приймає SET TRANSACTION лише до першого запиту в транзакції, MySQL усередині транзакції відмовляє з ERROR 1568: Transaction characteristics can't be changed while a transaction is in progress.
КОЛИ БРАТИ: Рівень сесії, коли весь застосунок живе на одній базі. Рівень транзакції для рідкісних місць, де потрібен SERIALIZABLE з повтором через attempts.

05 · ЯК У PHP UKRAINE

Черга контент-задач і lockForUpdate

Локальний воркер забирає з черги пачку задач. leaseBatch() у транзакції вибирає задачі в статусі pending або з простроченою орендою і блокує їх lockForUpdate(), а потім позначає орендованими. Ми відтворили два одночасні виклики на PostgreSQL 16, де працює сайт, на READ COMMITTED за замовчуванням: друга сесія чекала, поки перша закомітить, а потім отримала наступні задачі 3 і 4, а не ті самі 1 і 2. PostgreSQL перечитав заблоковані рядки, побачив статус leased і пропустив їх, а LIMIT добрав наступні. Подвійної оренди немає, ціна в очікуванні: з for update skip locked друга сесія взяла б вільні рядки одразу. У тестах на SQLite lockForUpdate() нічого не дописує, тож ця поведінка там не перевіряється.

php EloquentContentTaskRepository.php фрагмент

1public function leaseBatch(int $limit, DateTimeImmutable $now, int $minutes): array

3 return DB::transaction(function () use ($limit, $now, $minutes): array {← BEGIN … COMMIT

4 $models = ContentTaskModel::query()

5 ->where(fn ($q) => $q

6 ->where('status', TaskStatus::Pending->value)

7 ->orWhere(fn ($q2) => $q2->where('status', TaskStatus::Leased->value)->where('leased_until', '<=', $now)))

8 ->orderBy('id')

9 ->limit($limit)

10 ->lockForUpdate()← for update, на SQLite нічого

11 ->get();

13 $leased = [];

15 foreach ($models as $model) {

16 $task = $this->toDomain($model);

17 $task->lease($now, $minutes);

18 $this->save($task);← UPDATE під тим самим блокуванням

19 $leased[] = $task;

20 }

22 return $leased;

23 });

24}

src/Hiring/Infrastructure/Persistence/EloquentApplicationRepository.phpsave() відгуку не блокує рядок, а перевіряє версію: UPDATE … WHERE id = ? AND version = ?. Якщо хтось уже змінив відгук, оновиться 0 рядків, і save() кидає StaleApplication. Це оптимістичне блокування: ніхто не чекає, запізнілий запис просто відмовляється.

config/database.phpПідключення pgsql не задає isolation_level, тож сайт працює на READ COMMITTED, рівні PostgreSQL за замовчуванням.

app/Livewire/Developer/Register.phpРеєстрація створює користувача і профіль в одному DB::transaction(): або зʼявляються обидва, або жоден.

06 · ТИПОВІ ПОМИЛКИ

Де ізоляція підводить

✕ 1

Прочитати, порахувати в PHP, записати

Дві паралельні транзакції читають той самий баланс і обидві пишуть свій результат. На READ COMMITTED в обох базах і на REPEATABLE READ у MySQL друге оновлення просто перезаписує перше.

Натомість: Рахуйте в базі (balance = balance - 30) або читайте через lockForUpdate(). Для рідкісних конфліктів підходить версія рядка, як у відгуках цього сайту.

✕ 2

lockForUpdate() без транзакції

Поза транзакцією кожен запит комітиться сам, і блокування знімається одразу після SELECT. Код виглядає захищеним, а наступний UPDATE іде вже без нього.

Натомість: Блокувальне читання і запис завжди в одному DB::transaction().

✕ 3

Довга транзакція

HTTP-запит чи відправка листа всередині транзакції тримають блокування й старі версії рядків секундами. Інші чекають, InnoDB не може очистити undo log, PostgreSQL не може прибрати мертві версії.

Натомість: У транзакції лише робота з базою. Зовнішні виклики до або після, а «після коміту» через DB::afterCommit() чи події з afterCommit.

✕ 4

Розраховувати на рівень за замовчуванням

MySQL за замовчуванням працює на REPEATABLE READ, PostgreSQL на READ COMMITTED. Код, який на MySQL бачив стабільний знімок, після переїзду на PostgreSQL почне бачити чужі коміти посеред транзакції.

Натомість: Задайте isolation_level явно або пишіть критичні місця з FOR UPDATE, щоб вони не залежали від рівня.

✕ 5

Не повторювати після помилки серіалізації

На REPEATABLE READ і SERIALIZABLE PostgreSQL відкочує транзакцію з could not serialize access, а будь-яка база може обрати вас жертвою дедлоку. Без повтору користувач бачить помилку 500.

Натомість: DB::transaction(..., attempts: 3) для коротких транзакцій без побічних ефектів, або власний повтор зі зростаючою паузою.

07 · СЛОВНИК

Рівень ізоляції: Обіцянка бази, яких аномалій не буде, коли транзакції йдуть одночасно. Задається для сесії або для однієї транзакції: SET TRANSACTION ISOLATION LEVEL …
Брудне читання: Читання незакоміченої зміни іншої транзакції. Якщо та відкотиться, виявиться, що ви працювали з даними, яких ніколи не було.
Неповторюване читання: Той самий рядок, прочитаний двічі в одній транзакції, має різні значення, бо між читаннями інша транзакція його змінила й закомітила.
Фантом: Той самий запит з умовою, виконаний двічі, повертає інший набір рядків: інша транзакція вставила чи видалила рядок під умову.
MVCC: Multiversion concurrency control: база тримає кілька версій рядка, і кожна транзакція бачить ті, що відповідають її знімку. В InnoDB старі версії відновлюються з undo log, у PostgreSQL лежать у самій таблиці, поки їх не прибере VACUUM.
Знімок: Набір версій, закомічених до певного моменту. На READ COMMITTED кожен запит бере свій знімок, на REPEATABLE READ один знімок діє до кінця транзакції.

08 · ПЕРЕВІР СЕБЕ

Питання зі співбесід

Усі питання з SQL →

Читання незакоміченої зміни іншої транзакції. Стандарт дозволяє його лише на READ UNCOMMITTED. MySQL на цьому рівні справді його показує, PostgreSQL ні: його READ UNCOMMITTED працює як READ COMMITTED.

MySQL (InnoDB) працює на REPEATABLE READ, PostgreSQL на READ COMMITTED. На MySQL звичайні SELECT в одній транзакції бачать один знімок, на PostgreSQL кожен запит бачить свіжі коміти. Логіка, яка покладалася на стабільний знімок, після переїзду може читати різні значення того самого рядка.

Звичайний SELECT це consistent read зі знімка, узятого першим читанням транзакції. Блокувальне читання (FOR UPDATE, FOR SHARE, а також UPDATE і DELETE) працює з останніми закоміченими даними, бо блокувати можна лише справжні поточні рядки. Тому в одній транзакції два запити можуть бачити різний стан, а next-key блокування ще й не дадуть вставити нові рядки в прочитаний діапазон.

Другий воркер упирається в блокування першого і чекає його COMMIT. На PostgreSQL READ COMMITTED він потім перечитує рядки, пропускає вже орендовані й добирає наступні, тож дублів немає, але воркери працюють по черзі. FOR UPDATE SKIP LOCKED (PostgreSQL 9.5+, MySQL 8.0+) пропускає зайняті рядки одразу, і воркери беруть різні пачки паралельно. У Laravel це lock('for update skip locked').

InnoDB робить звичайні SELECT блокувальними (FOR SHARE), тож конфліктні записи чекають і можуть впертися в таймаут чи дедлок. PostgreSQL використовує SSI: читання нікого не блокують, база стежить за залежностями між транзакціями й відкочує одну з них з could not serialize access due to read/write dependencies among transactions, якщо послідовного порядку не існує. В обох випадках застосунок мусить уміти повторити транзакцію.

← ПОПЕРЕДНЯACID НАСТУПНА →MVCC

ТЕМАACID ТЕМАMVCC ТЕМАБлокування й дедлоки ПИТАННЯ · SQLЯкі рівні ізоляції транзакцій ви використовуєте і чому? ПИТАННЯ · SQLЩо таке транзакція, що гарантує ACID і що з цього не гарантується за умовчанням? ПИТАННЯ · SQLЯк виникають deadlock-и і як ви їх діагностуєте та уникаєте? ПИТАННЯ · SQLЯк забезпечити узгодженість при паралельних записах: блокування, версії, обмеження?

Рівні ізоляціїIsolation levels

Що одна транзакція бачить з чужих змін

Одна історія на чотирьох рівнях

Прочитати й одразу заблокувати

DB::transaction і lockForUpdate

Черга контент-задач і lockForUpdate

Де ізоляція підводить

Прочитати, порахувати в PHP, записати

lockForUpdate() без транзакції

Довга транзакція

Розраховувати на рівень за замовчуванням

Не повторювати після помилки серіалізації

Питання зі співбесід