Зачем нужны блокировки в базе данных

Что проверяют

Понимаете ли вы, какие аномалии возникают при конкурентных запросах и как их закрывают на уровне базы, а не проверками в PHP-коде. Следом обычно спрашивают про deadlock и про пессимистичную и оптимистичную блокировку.

Как отвечать

Блокировки нужны, чтобы конкурентные транзакции не портили данные друг друга. Классический пример: два запроса одновременно читают баланс 100, оба списывают по 70, оба записывают 30. Это lost update, и проверка if ($balance >= 70) в PHP его не предотвращает.

Что назвать:

• MVCC: в PostgreSQL и InnoDB обычное чтение не блокирует запись и наоборот, блокировки нужны прежде всего при изменении данных;
• строчные блокировки: SELECT ... FOR UPDATE захватывает строку до конца транзакции, FOR SHARE позволяет другим тоже взять разделяемую блокировку, но не даёт менять строку. NOWAIT падает сразу, SKIP LOCKED пропускает занятые строки, это удобно для очередей;
• табличные и advisory: LOCK TABLE для крупных операций, pg_advisory_lock() в PostgreSQL и GET_LOCK() в MySQL для блокировки по произвольному ключу, например чтобы крон не запустился дважды;
• InnoDB: на уровне REPEATABLE READ, который там по умолчанию, работают gap- и next-key-блокировки, они защищают от фантомов;
• оптимистичная блокировка: колонка version и UPDATE ... WHERE id = ? AND version = ?. Если затронуто ноль строк, кто-то успел раньше.

Часто хватает атомарного запроса: UPDATE accounts SET balance = balance - 70 WHERE id = ? AND balance >= 70.

В PHP это PDO::beginTransaction(), commit(), rollBack(), в Laravel DB::transaction() и lockForUpdate(), в Doctrine LockMode::PESSIMISTIC_WRITE и поле с атрибутом #[ORM\Version].

Где спотыкаются

Используют FOR UPDATE вне явной транзакции. В режиме autocommit блокировка снимается сразу после запроса и ничего не защищает.

Держат транзакцию открытой во время HTTP-запроса к внешнему API. Строки заблокированы секундами, остальные запросы выстраиваются в очередь.

Не обрабатывают deadlock. База откатит одну из транзакций, и код должен поймать ошибку и повторить её целиком: в Laravel это второй аргумент DB::transaction(), число попыток.