Блокування й дедлоки: бази даних для PHP-розробника

01 · ХТО КОГО ЧЕКАЄ

Два записи в один рядок

Коли дві транзакції хочуть змінити той самий рядок, одна мусить почекати, інакше друга перезапише зміну, якої не бачила. Для цього база ставить . (S) дозволяє іншим теж читати з блокуванням, але не змінювати. (X) не пускає нікого, хто теж хоче блокування. Звичайний SELECT у MySQL і PostgreSQL блокувань рядків не бере зовсім, крім рівня SERIALIZABLE в InnoDB: він читає знімок і не чекає навіть на X.

СЛОВНИКБлокування. Позначка, що транзакція працює з обʼєктом: рядком, діапазоном, таблицею. Інша транзакція з несумісним блокуванням чекає, поки перша закомітить або відкотиться.

СЛОВНИКСпільне блокування. S, або FOR SHARE. Кілька транзакцій можуть тримати його на тому самому рядку одночасно, але змінити рядок ніхто з них не може, поки інші його тримають.

СЛОВНИКЕксклюзивне блокування. X, або FOR UPDATE. Його ставлять UPDATE, DELETE і SELECT … FOR UPDATE. Несумісне ні з S, ні з іншим X на тому самому рядку. Звичайний SELECT воно не зупиняє.

UPDATE і DELETE ставлять X на рядки, які змінюють, і тримають до COMMIT або ROLLBACK. Те саме можна зробити читанням: SELECT … FOR UPDATE бере X, SELECT … FOR SHARE бере S. Це потрібно, коли ви читаєте рядок, щоб на основі прочитаного його змінити.

Рівень блокування залежить від рушія. InnoDB і PostgreSQL блокують рядки, тож дві сесії, які змінюють різні рядки однієї таблиці, одна одній не заважають. MyISAM блокує всю таблицю, SQLite всю базу. InnoDB ще й блокує між записами індексу, щоб у прочитаний діапазон ніхто не вставив новий рядок.

СЛОВНИКGap і next-key блокування. В InnoDB на REPEATABLE READ блокувальне читання діапазону блокує ще й проміжки між записами індексу (gap), а запис разом з проміжком перед ним називається next-key. Так InnoDB не дає вставити фантом. PostgreSQL проміжків не блокує.

Якщо A тримає рядок 1 і чекає рядок 2, а B тримає рядок 2 і чекає рядок 1, чекати можна вічно. Це . Обидві бази шукають такі цикли самі й відкочують одну з транзакцій з помилкою. Окремо стоять : ALTER TABLE чекає, поки таблицею перестануть користуватися, а всі нові запити стають у чергу вже за ним.

СЛОВНИКДедлок. Цикл очікування: кожна транзакція чекає блокування, яке тримає інша. Сам він не розвʼяжеться, тому база вибирає жертву і відкочує її повністю. InnoDB шукає цикл одразу, PostgreSQL після deadlock_timeout, за замовчуванням 1 секунда.

СЛОВНИКБлокування метаданих. Блокування таблиці як обʼєкта. Будь-який запит бере слабке, ALTER TABLE сильне. У MySQL це metadata lock (MDL), у PostgreSQL ACCESS SHARE для SELECT і ACCESS EXCLUSIVE для більшості ALTER TABLE.

02 · ДВІ СЕСІЇ

Паралельно, в черзі чи по колу

Дві сесії працюють з таблицею vacancies. Перемикайте сценарії: рушій з блокуванням рядків, рушій з блокуванням таблиці, дедлок, той самий обмін з однаковим порядком блокувань і черга задач зі SKIP LOCKED. Пісочний годинник на доріжці означає, що сесія чекає.

СЦЕНАРІЙ

Сесія A Сесія B t0

BEGIN

UPDATE … WHERE id = 1

UPDATE … WHERE id = 2

🔒 рядок 1

🔒 рядок 2

COMMIT

ПАРАЛЕЛЬНОInnoDB блокує лише рядки, які змінює. Дві сесії оновлюють різні рядки й не заважають одна одній: UPDATE у B виконався за соті частки секунди, поки A ще не закомітила. PostgreSQL поводиться так само.

Сесія A Сесія B t0

UPDATE … WHERE id = 1 (3 с)

🔒 уся таблиця

UPDATE … WHERE id = 2

🔒 уся таблиця

⏳ Waiting for table level lock

готово

UPDATE виконано

ЧЕРГАMyISAM блокує на запис усю таблицю. Поки довгий UPDATE у A не закінчився, B чекає, хоча її рядок ніхто не чіпає: її UPDATE одного рядка тривав 2,5 секунди. BEGIN і ROLLBACK тут нічого не дають: транзакцій у MyISAM немає.

PROCESSLIST · MySQL 8.0

STATE                          INFO
User sleep                     UPDATE vacancies SET views = views + SLEEP(3) WHERE id = 1
Waiting for table level lock   UPDATE vacancies SET views = views + 1 WHERE id = 2

Сесія B

Query OK, 1 row affected (2.50 sec)

Сесія A Сесія B t0

BEGIN

UPDATE id = 1 · 🔒 1

UPDATE id = 2 · 🔒 2

UPDATE id = 2 · ⏳ чекає B

UPDATE id = 1 · ⏳ чекає A

UPDATE виконано

ERROR · ROLLBACK

COMMIT

ДЕДЛОКA закриває вакансії 1 і 2, B позначає застарілими 2 і 1, тобто в зворотному порядку. Кожна взяла перший рядок і чекає другий. MySQL помітив цикл одразу, щойно запит B його замкнув, і відкотив B. PostgreSQL перевіряє цикл лише після deadlock_timeout очікування: у нашому запуску A чекала довше, через секунду перевірила й відкотила саму себе. Хто стане жертвою, документація PostgreSQL прямо радить не вгадувати.

MySQL 8.0 · сесія B

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

PostgreSQL 16 · сесія A, через 1 с очікування

ERROR:  deadlock detected
DETAIL:  Process 15138 waits for ShareLock on transaction 812; blocked by process 15144.
Process 15144 waits for ShareLock on transaction 811; blocked by process 15138.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "vacancies"

Сесія A Сесія B t0

BEGIN

UPDATE id = 1 · 🔒 1

UPDATE id = 1 · ⏳ чекає A

UPDATE id = 2 · 🔒 2

⏳ чекає A

COMMIT

UPDATE id = 1 виконано

UPDATE id = 2 · COMMIT

БЕЗ ДЕДЛОКУТі самі дві транзакції, але обидві беруть рядки за зростанням id. B упирається вже в рядок 1 і чекає, нічого не тримаючи. Циклу немає: A доробляє, комітить, і B проходить слідом. Дедлок перетворився на коротке очікування.

Сесія A Сесія B t0

BEGIN

SELECT … LIMIT 2 FOR UPDATE · 🔒 1, 2

обробляє 1 і 2

… FOR UPDATE SKIP LOCKED · 🔒 3, 4

COMMIT

ЧЕРГА ЗАДАЧДва воркери беруть задачі з черги content_tasks. A заблокувала задачі 1 і 2. B з FOR UPDATE SKIP LOCKED не чекає, а пропускає зайняті рядки й отримує 3 і 4. Без SKIP LOCKED вона стояла б за A до її COMMIT.

MySQL 8.0 і PostgreSQL 16 · сесія B

id
3
4

Сценарії підготовлені заздалегідь. Стани, час і повідомлення знято з MySQL 8.0.46 і PostgreSQL 16.15.

03 · СУМІСНІСТЬ

Сесія A тримає, сесія B хоче

Кожен рядок нижче виконано в двох сесіях на MySQL 8.0 і PostgreSQL 16. Зліва що зробила A у відкритій транзакції, справа що спробувала B.

A ТРИМАЄ · B ХОЧЕ

A: SELECT … FOR UPDATE → B: SELECT не чекає Звичайне читання бере знімок і блокувань не дивиться. В обох базах B отримала рядок за мілісекунду.

A: SELECT … FOR SHARE → B: SELECT … FOR SHARE сумісні Два спільні блокування на одному рядку уживаються: FOR SHARE NOWAIT у B пройшов в обох базах.

A: SELECT … FOR SHARE → B: UPDATE чекає Змінити рядок під чужим S не можна. У PostgreSQL UPDATE чекав 1,45 с, доки A не закомітила.

A: SELECT … FOR UPDATE → B: SELECT … FOR UPDATE чекає Два X несумісні. З NOWAIT замість очікування B отримує помилку: ERROR 3572 у MySQL, could not obtain lock on row у PostgreSQL.

A: SELECT … FOR UPDATE → B: SELECT … FOR UPDATE SKIP LOCKED пропускає B не чекає і не бачить заблокованих рядків, ніби їх немає. Для черг саме те, для звітів небезпечно.

A: UPDATE … WHERE id = 1 → B: UPDATE … WHERE id = 2 паралельно Різні рядки, різні блокування. Так в InnoDB і PostgreSQL, але не в MyISAM.

A: SELECT … WHERE id BETWEEN 12 AND 18 FOR UPDATE → B: INSERT id = 15 лише MySQL чекає Рядків у діапазоні немає, але InnoDB заблокував проміжок, і вставка чекала до тайм-ауту. PostgreSQL проміжків не блокує: INSERT пройшов за 1 мс.

A: BEGIN; SELECT … FROM vacancies → B: ALTER TABLE vacancies … чекає, а за ним усі ALTER чекає, поки A закомітить. І кожен новий запит до таблиці, навіть звичайний SELECT, стає в чергу вже за ALTER.

Рівень ізоляції READ COMMITTED у PostgreSQL і REPEATABLE READ у MySQL, тобто ті, що стоять за замовчуванням.

04 · FOR UPDATE · NOWAIT · SKIP LOCKED

Заблокувати, не чекати або пропустити

Сесія A взяла дві задачі з черги: SELECT id FROM content_tasks WHERE status = 'pending' ORDER BY id LIMIT 2 FOR UPDATE. Сесія B пробує те саме зі SKIP LOCKED, а потім конкретну задачу 1 з NOWAIT. SKIP LOCKED і NOWAIT є в MySQL з 8.0, у PostgreSQL SKIP LOCKED зʼявився у 9.5, а NOWAIT значно раніше.

Подивитися, хто що тримає, можна в обох базах, але в різних місцях. MySQL показує блокування рядків у performance_schema.data_locks. PostgreSQL блокувань рядків у памʼяті не тримає зовсім: він записує номер транзакції в xmax самого рядка. Тому в pg_locks їх немає, і обмежень на кількість заблокованих рядків теж немає. Побачити їх можна розширенням pgrowlocks.

MySQL 8.0 PostgreSQL 16 SQLite 3.45

1-- Сесія B

2START TRANSACTION;

3SELECT id FROM content_tasks WHERE status = 'pending'

4ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED;

5SELECT id FROM content_tasks WHERE id = 1 FOR UPDATE NOWAIT;

6ROLLBACK;

8-- Блокування сесії A

9SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA

10FROM performance_schema.data_locks;

+----+
| id |
+----+
|  3 |
|  4 |
+----+
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

+---------------+------------+-----------+---------------+--------------+
| OBJECT_NAME   | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_DATA    |
+---------------+------------+-----------+---------------+--------------+
| content_tasks | NULL       | TABLE     | IX            | NULL         |
| content_tasks | status     | RECORD    | X             | 'pending', 1 |
| content_tasks | status     | RECORD    | X             | 'pending', 2 |
| content_tasks | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1            |
| content_tasks | PRIMARY    | RECORD    | X,REC_NOT_GAP | 2            |
+---------------+------------+-----------+---------------+--------------+

IX на таблиці це намір блокувати рядки всередині: він не заважає іншим IX, але не дасть LOCK TABLES. Пошук ішов індексом status, і там блокування X без уточнення, тобто next-key: запис разом з проміжком перед ним. У первинному ключі REC_NOT_GAP, лише сам рядок.

1-- Сесія B

2BEGIN;

3SELECT id FROM content_tasks WHERE status = 'pending'

4ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED;

5SELECT id FROM content_tasks WHERE id = 1 FOR UPDATE NOWAIT;

6ROLLBACK;

8-- Блокування сесії A (вона ще взяла задачу 5 через FOR SHARE)

9CREATE EXTENSION pgrowlocks;

10SELECT locked_row, locker, multi, modes FROM pgrowlocks('content_tasks');

 id
----
  3
  4
(2 rows)

ERROR:  could not obtain lock on row in relation "content_tasks"

 locked_row | locker | multi |     modes
------------+--------+-------+----------------
 (0,1)      |    778 | f     | {"For Update"}
 (0,2)      |    778 | f     | {"For Update"}
 (0,5)      |    778 | f     | {"For Share"}

locker це номер транзакції A, він же стоїть у xmax цих рядків. У pg_locks на той момент було лише RowShareLock на саму таблицю. FOR UPDATE у PostgreSQL має ще слабшого брата FOR NO KEY UPDATE: його бере звичайний UPDATE, якщо не змінює ключових колонок, і він не конфліктує з FOR KEY SHARE, яке ставлять перевірки зовнішніх ключів.

1SELECT id FROM content_tasks WHERE status = 'pending'

2LIMIT 2 FOR UPDATE SKIP LOCKED;

4-- Сесія A: BEGIN IMMEDIATE; UPDATE accounts … (не комітить)

5-- Сесія B:

6UPDATE accounts SET balance = 50 WHERE id = 2;

7-- Сесія B з очікуванням до 5 с:

8.timeout 5000

9UPDATE accounts SET balance = 50 WHERE id = 2;

Error: in prepare, near "FOR": syntax error
Error: stepping, database is locked (5)
-- з .timeout: чекає, поки A закомітить, і виконується

У SQLite один записувач на всю базу, тож блокувати окремі рядки нема чого, і синтаксису FOR UPDATE немає. Laravel на SQLite просто не дописує lock-частину. Друга сесія без тайм-ауту одразу отримує database is locked, з busy_timeout чекає. У конфігу Laravel для цього є ключ busy_timeout.

05 · БЛОКУВАННЯ ПРОМІЖКІВ

InnoDB блокує навіть те, чого немає

У таблиці applications рядки з id 10, 20 і 30. Сесія A на REPEATABLE READ читає діапазон, у якому рядків немає зовсім, з FOR UPDATE. Потім сесія B вставляє рядок 25, а потім рядок 15.

MySQL 8.0 PostgreSQL 16

1-- Сесія A

2START TRANSACTION;

3SELECT * FROM applications WHERE id BETWEEN 12 AND 18 FOR UPDATE;

5-- Сесія B, innodb_lock_wait_timeout = 3

6INSERT INTO applications VALUES (25, 'd@x.ua');

7INSERT INTO applications VALUES (15, 'e@x.ua');

9SELECT INDEX_NAME, LOCK_MODE, LOCK_STATUS, LOCK_DATA

10FROM performance_schema.data_locks WHERE LOCK_TYPE = 'RECORD';

Empty set (0.00 sec)                  -- сесія A
Query OK, 1 row affected (0.00 sec)   -- рядок 25: проміжок (20, 30) вільний

+------------+------------------------+-------------+-----------+
| INDEX_NAME | LOCK_MODE              | LOCK_STATUS | LOCK_DATA |
+------------+------------------------+-------------+-----------+
| PRIMARY    | X,GAP                  | GRANTED     | 20        |
| PRIMARY    | X,GAP,INSERT_INTENTION | WAITING     | 20        |
+------------+------------------------+-------------+-----------+

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction   -- рядок 15

X,GAP з LOCK_DATA 20 означає проміжок перед записом 20, тобто між 10 і 20. Вставка 15 потрапляє в нього й чекає з INSERT_INTENTION. Так REPEATABLE READ в InnoDB захищає блокувальні читання від фантомів. На READ COMMITTED gap-блокування для таких читань не ставляться: та сама вставка 15 пройшла одразу.

1-- Сесія A

2BEGIN;

3SELECT * FROM applications WHERE id BETWEEN 12 AND 18 FOR UPDATE;

5-- Сесія B

6INSERT INTO applications VALUES (15, 'e@x.ua');

INSERT 0 1
Time: 1.292 ms

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

06 · ALTER TABLE НА ЖИВІЙ ТАБЛИЦІ

Міграція, яка поклала сайт

Міграція додає колонку до vacancies. Сама операція миттєва в обох базах, але їй потрібне сильне блокування таблиці, і воно стає в чергу за будь-якою відкритою транзакцією, яка цю таблицю вже читала. Поки ALTER чекає, нові запити, навіть звичайні SELECT, стають у чергу за ним. Сайт стоїть, хоча жоден запит не важкий.

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

MySQL 8.0 PostgreSQL 16

1SELECT ID, COMMAND, TIME, STATE, INFO FROM information_schema.PROCESSLIST

2WHERE DB = 'demo' ORDER BY ID;

4-- Що можна зробити без перебудови таблиці

5ALTER TABLE vacancies ADD COLUMN closed_at DATETIME NULL, ALGORITHM=INSTANT;

6ALTER TABLE vacancies MODIFY views BIGINT NOT NULL DEFAULT 0, ALGORITHM=INSTANT;

7ALTER TABLE vacancies MODIFY views BIGINT NOT NULL DEFAULT 0, ALGORITHM=INPLACE, LOCK=NONE;

+-----+---------+------+---------------------------------+------------------------------------------+
| ID  | COMMAND | TIME | STATE                           | INFO                                     |
+-----+---------+------+---------------------------------+------------------------------------------+
| 139 | Sleep   |    2 |                                 | NULL                                     |
| 140 | Query   |    2 | Waiting for table metadata lock | ALTER TABLE vacancies ADD COLUMN closed… |
| 141 | Query   |    1 | Waiting for table metadata lock | SELECT title FROM vacancies WHERE id = 2 |
+-----+---------+------+---------------------------------+------------------------------------------+

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Сесія 139 нічого не виконує (Sleep), але її транзакція відкрита, тому вона тримає metadata lock на vacancies, хоча її SELECT давно закінчився. Явний ALGORITHM корисний як запобіжник: якщо операцію не можна зробити так, MySQL відмовить одразу, а не почне копіювати таблицю. Зміна типу колонки тут можлива лише через COPY, тобто перезапис таблиці.

1SELECT pid, pg_blocking_pids(pid) AS blocked_by, state,

2 wait_event_type, wait_event, left(query, 45) AS query

3FROM pg_stat_activity

4WHERE datname = 'demo' AND pid <> pg_backend_pid()

5ORDER BY backend_start;

7-- Той самий ALTER, але не довше 2 секунд очікування

8SET lock_timeout = '2s';

9ALTER TABLE vacancies ADD COLUMN closed_at timestamptz;

  pid  | blocked_by |        state        | wait_event_type | wait_event |                     query
-------+------------+---------------------+-----------------+------------+-----------------------------------------------
 16298 | {}         | idle in transaction | Client          | ClientRead | SELECT title FROM vacancies WHERE id = 1;
 16346 | {16298}    | active              | Lock            | relation   | ALTER TABLE vacancies ADD COLUMN closed_at ti
 16594 | {16346}    | active              | Lock            | relation   | SELECT title FROM vacancies WHERE id = 2

SET
ERROR:  canceling statement due to lock timeout

ALTER чекає на 16298, а звичайний SELECT уже на ALTER. ADD COLUMN без DEFAULT чи з незмінним DEFAULT таблицю не переписує, але бере ACCESS EXCLUSIVE. lock_timeout перетворює вічну чергу на помилку, після якої міграцію можна повторити. Зміна типу колонки чи DEFAULT з volatile-функцією переписує всю таблицю й індекси під тим самим блокуванням.

07 · У LARAVEL

Блокування й повтор у коді

Query Builder дописує блокування в кінець SELECT, а DB::transaction() вміє повторити транзакцію після дедлоку. Усе нижче звірено з vendor/laravel/framework 13.30: граматики Query\Grammars, ManagesTransactions і ConcurrencyErrorDetector.

php CloseVacancies.php Один порядок блокувань

1DB::transaction(function () use ($ids) {

2 $vacancies = Vacancy::query()

3 ->whereKey($ids)

4 ->orderBy('id') // усі беруть рядки в одному порядку

5 ->lockForUpdate() // for update; на SQLite нічого

6 ->get();

8 foreach ($vacancies as $vacancy) {

9 $vacancy->update(['status' => 'closed']);

10 }

11});

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

php CloseVacancies.php Черга з SKIP LOCKED

1DB::transaction(function () {

2 $tasks = ContentTaskModel::query()

3 ->where('status', 'pending')

4 ->orderBy('id')

5 ->limit(10)

6 ->lock('for update skip locked')

7 ->get();

8 // позначити орендованими й закомітити

9});

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

php CloseVacancies.php Не чекати зовсім

1try {

2 $vacancy = Vacancy::query()->whereKey($id)

3 ->lock('for update nowait')

4 ->firstOrFail();

5} catch (QueryException $e) {

6 // зайнято: ERROR 3572 у MySQL, SQLSTATE 55P03 у PostgreSQL

ЩО ПЕРЕВІРЯЄ: NOWAIT кидає помилку одразу, якщо рядок зайнятий. DB::transaction() з attempts таку помилку не повторює: це не дедлок.
КОЛИ БРАТИ: Коли краще одразу сказати користувачу «вакансію саме редагує інший модератор», ніж змусити його чекати.

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

1DB::transaction(function () {

2 // лише робота з базою, без листів і HTTP

3}, attempts: 3);

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

php CloseVacancies.php Унікальний індекс замість перевірки

1// Міграція

2$table->unique(['vacancy_id', 'email']);

4// Код

5try {

6 Application::query()->create([...]);

7} catch (UniqueConstraintViolationException) {

8 throw new InvalidArgumentException('Ви вже відгукнулись на цю вакансію.');

ЩО ПЕРЕВІРЯЄ: Друга з двох одночасних вставок чекає, поки перша закомітить, і отримує duplicate key. Laravel перетворює це на UniqueConstraintViolationException, нащадка QueryException.
КОЛИ БРАТИ: Замість «перевірити exists(), потім вставити», де між перевіркою і вставкою встигає пролізти інший запит.

08 · ЯК У PHP UKRAINE

Блокування в коді сайту

Явне блокування в коді одне: оренда контент-задач, і про неї докладно в темі про рівні ізоляції. Важливе тут те, що leaseBatch() сортує задачі за id перед lockForUpdate(), тож два воркери беруть рядки в одному порядку й можуть лише чекати, а не зациклитися. attempts у DB::transaction() не передає жодне місце в коді, тож дедлок чи тайм-аут блокування долетить до користувача винятком. Відгук на вакансію перевіряє дубль через exists() і лише потім вставляє, а індекс (vacancy_id, email) звичайний, не унікальний. Ми відтворили цю пару запитів у двох одночасних транзакціях на PostgreSQL 16: обидві отримали false і обидві вставили рядок. З унікальним індексом друга дочекалася б першої й отримала duplicate key.

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

1public function handle(int $jobId, string $name, string $email, ?string $link, string $message, ?int $userId = null, ?ApplicationMaterials $materials = null): Application

3 $job = $this->jobs->findById($jobId);

5 if ($this->applications->exists($jobId, $email)) {← SELECT без блокування

6 throw new InvalidArgumentException('Ви вже відгукнулись на цю вакансію з цієї пошти.');

7 }

9 // знімок вакансії

11 $application = Application::submit($jobId, $job->companyId, $name, $email, $link, $message, $this->clock->now(), $userId, $materials->resumePath, $materials->resumeName, $snapshot);

12 $this->applications->save($application);← INSERT у власній транзакції

14 return $application;

15}

src/ContentOps/Infrastructure/Persistence/EloquentContentTaskRepository.phpleaseBatch(): where status pending або прострочена оренда, orderBy('id'), limit, lockForUpdate(). Однаковий порядок блокувань у всіх воркерів.

src/Hiring/Infrastructure/Persistence/EloquentApplicationRepository.phpЗміни відгуку захищає не блокування, а версія: UPDATE … WHERE id = ? AND version = ?. Ніхто не чекає, а запізнілий запис отримує StaleApplication.

database/migrations/2026_09_10_000005_add_imported_from_to_vacancies.phpforeignId('imported_from_id')->constrained('vacancies'). На PostgreSQL Laravel виконує міграцію в транзакції, і ми перевірили ці два ALTER у pg_locks: ACCESS EXCLUSIVE на vacancies до кінця міграції.

deploy/entrypoint.shphp artisan migrate --force під час старту контейнера, без lock_timeout. Відкрита транзакція на vacancies в цей момент поставила б і міграцію, і всі запити до вакансій у чергу.

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

Звідки беруться дедлоки й черги

✕ 1

Різний порядок блокувань

Один код оновлює вакансію, потім компанію, інший навпаки. Або обидва оновлюють список id у тому порядку, у якому він прийшов з форми. Під навантаженням це дедлоки.

Натомість: Один порядок скрізь: таблиці в однаковій послідовності, рядки за зростанням ключа (orderBy('id') перед lockForUpdate()).

✕ 2

Блокувальне читання без індексу

SELECT … WHERE status = 'pending' FOR UPDATE без індексу на status. InnoDB блокує кожен рядок, який переглянув: ми отримали X на всіх чотирьох рядках таблиці й тайм-аут для UPDATE зовсім іншого рядка.

Натомість: Індекс на колонки умови блокувального запиту. З індексом на status той самий UPDATE пройшов одразу.

✕ 3

Вважати, що Lock wait timeout відкотив транзакцію

У MySQL ERROR 1205 за замовчуванням відкочує лише останній оператор. Ми перехопили помилку, закомітили, і перший UPDATE транзакції лишився в базі, а другий ні.

Натомість: Після будь-якої помилки блокування робіть ROLLBACK усієї транзакції явно. DB::transaction() так і робить.

✕ 4

Повтор транзакції з побічними ефектами

DB::transaction(..., attempts: 3) з відправкою листа всередині. Дедлок на другому запиті, повтор, і лист іде двічі.

Натомість: У замиканні лише SQL. Лист, подія, HTTP-запит через DB::afterCommit() або job з afterCommit.

✕ 5

ALTER TABLE на живій таблиці без тайм-ауту

Міграція чекає на забуту транзакцію, а за нею стає в чергу весь сайт. Помилки немає, просто все висить.

Натомість: PostgreSQL: SET lock_timeout перед ALTER і повтор міграції. MySQL: ALGORITHM=INSTANT або INPLACE з LOCK=NONE і обмежений lock_wait_timeout. Перед деплоєм перевірте довгі транзакції.

10 · СЛОВНИК

Блокування: Позначка, що транзакція працює з обʼєктом: рядком, діапазоном, таблицею. Інша транзакція з несумісним блокуванням чекає, поки перша закомітить або відкотиться.
Спільне блокування: S, або FOR SHARE. Кілька транзакцій можуть тримати його на тому самому рядку одночасно, але змінити рядок ніхто з них не може, поки інші його тримають.
Ексклюзивне блокування: X, або FOR UPDATE. Його ставлять UPDATE, DELETE і SELECT … FOR UPDATE. Несумісне ні з S, ні з іншим X на тому самому рядку. Звичайний SELECT воно не зупиняє.
Gap і next-key блокування: В InnoDB на REPEATABLE READ блокувальне читання діапазону блокує ще й проміжки між записами індексу (gap), а запис разом з проміжком перед ним називається next-key. Так InnoDB не дає вставити фантом. PostgreSQL проміжків не блокує.
Дедлок: Цикл очікування: кожна транзакція чекає блокування, яке тримає інша. Сам він не розвʼяжеться, тому база вибирає жертву і відкочує її повністю. InnoDB шукає цикл одразу, PostgreSQL після deadlock_timeout, за замовчуванням 1 секунда.
Блокування метаданих: Блокування таблиці як обʼєкта. Будь-який запит бере слабке, ALTER TABLE сильне. У MySQL це metadata lock (MDL), у PostgreSQL ACCESS SHARE для SELECT і ACCESS EXCLUSIVE для більшості ALTER TABLE.

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

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

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

Спільне (FOR SHARE) можуть тримати кілька транзакцій на одному рядку одночасно, але змінити рядок не може ніхто, поки його тримають інші. Ексклюзивне (FOR UPDATE, а також UPDATE і DELETE) тримає лише одна транзакція, і всі, хто хоче будь-яке блокування цього рядка, чекають. Звичайний SELECT у MySQL і PostgreSQL жодного з них не чекає: він читає знімок.

Цикл очікування: A тримає рядок 1 і чекає рядок 2, B тримає 2 і чекає 1. InnoDB помічає цикл одразу й відкочує одну транзакцію з ERROR 1213, PostgreSQL перевіряє після deadlock_timeout (1 с) і кидає deadlock detected. Відкочена транзакція скасовується повністю, і її треба повторити. Уникають дедлоків одним порядком блокувань, короткими транзакціями й індексами під блокувальні запити, а повтор лишають як страховку.

З простим FOR UPDATE другий воркер упирається в рядки, які заблокував перший, і чекає його COMMIT, тож воркери працюють по черзі. SKIP LOCKED пропускає заблоковані рядки й одразу повертає наступні вільні, і воркери беруть різні пачки паралельно. Сортування за id і LIMIT лишаються, а дублів немає, бо зайнятий рядок ніхто, крім власника, не отримає. У Laravel це lock('for update skip locked').

Додавання колонки без перезапису справді миттєве, але потребує сильного блокування таблиці: ACCESS EXCLUSIVE у PostgreSQL, metadata lock у MySQL. Воно чекає, поки закінчиться будь-яка транзакція, що вже звернулася до таблиці, а всі нові запити стають у чергу за ALTER. Одна довга транзакція чи повільний звіт кладуть сайт. Рішення: lock_timeout на кілька секунд і повтор міграції в PostgreSQL, ALGORITHM=INSTANT і обмежений lock_wait_timeout у MySQL, перевірка довгих транзакцій перед деплоєм. Якщо ж операція переписує таблицю (зміна типу), її роблять поступово: нова колонка, заповнення пакетами, перемикання.

← ПОПЕРЕДНЯMVCC НАСТУПНА →InnoDB, MyISAM і MEMORY

ТЕМАРівні ізоляції ТЕМАMVCC ТЕМАACID ТЕМАІндекси ПИТАННЯ · SQLЯк виникають deadlock-и і як ви їх діагностуєте та уникаєте? ПИТАННЯ · SQLЯк забезпечити узгодженість при паралельних записах: блокування, версії, обмеження? ПИТАННЯ · SQLЯкі рівні ізоляції транзакцій ви використовуєте і чому? КОНЦЕПЦІЯ · АРХІТЕКТУРАСутність і агрегат КОНЦЕПЦІЯ · АРХІТЕКТУРАOutbox

Блокування й дедлокиLocks and deadlocks

Два записи в один рядок

Паралельно, в черзі чи по колу

Сесія A тримає, сесія B хоче

Заблокувати, не чекати або пропустити

InnoDB блокує навіть те, чого немає

Міграція, яка поклала сайт

Блокування й повтор у коді

Блокування в коді сайту

Звідки беруться дедлоки й черги

Різний порядок блокувань

Блокувальне читання без індексу

Вважати, що Lock wait timeout відкотив транзакцію

Повтор транзакції з побічними ефектами

ALTER TABLE на живій таблиці без тайм-ауту

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