SELECT FOR UPDATE: пессимистичная блокировка строк в PostgreSQL

Прямой ответ

SELECT ... FOR UPDATE блокирует прочитанные строки до конца транзакции. Другие транзакции, пытающиеся заблокировать те же строки, ждут. Это пессимистичный подход: предполагается, что конфликт вероятен, поэтому доступ сериализуется заранее.

BEGIN;

SELECT balance
  FROM accounts
 WHERE id = 'acc-42'
   FOR UPDATE;                 -- строка заблокирована до COMMIT

UPDATE accounts
   SET balance = balance - 100
 WHERE id = 'acc-42';

COMMIT;

Варианты блокировки в PostgreSQL:

Два полезных модификатора:

SELECT ... FOR UPDATE NOWAIT;        -- ошибка сразу, если строка занята
SELECT ... FOR UPDATE SKIP LOCKED;   -- пропускает заблокированные строки

SKIP LOCKED — основа реализации очередей на таблице:

BEGIN;

SELECT id, payload
  FROM jobs
 WHERE status = 'new'
 ORDER BY created_at
 LIMIT 1
   FOR UPDATE SKIP LOCKED;       -- каждый воркер берёт свою задачу

UPDATE jobs SET status = 'in_progress' WHERE id = :id;

COMMIT;

Без SKIP LOCKED все воркеры выстраивались бы в очередь за одной строкой; с ним каждый берёт первую незаблокированную задачу, и параллелизм работает.

Практика: безопасное использование блокировок

1. Блокировка работает только внутри транзакции. Запрос FOR UPDATE вне явной транзакции освобождает блокировку немедленно, поскольку каждый запрос выполняется в собственной транзакции.

2. Держите транзакцию короткой. Блокировка удерживается до COMMIT. Внешний HTTP-вызов внутри транзакции означает, что строка заблокирована на время сетевого обмена — это частая причина исчерпания пула соединений.

3. Соблюдайте единый порядок блокирования. Если одна транзакция блокирует счета A затем B, а другая — B затем A, возникает взаимная блокировка. Фиксированный порядок, например по возрастанию идентификатора, устраняет проблему; о диагностике — в статье про deadlock-и.

4. Задавайте таймаут блокировки. Параметр lock_timeout прерывает запрос вместо бесконечного ожидания:

SET LOCAL lock_timeout = '2s';

Без таймаута медленный клиент блокирует всех остальных, и проблема выглядит как зависание базы.

5. Используйте NOWAIT, когда ожидание бесполезно. Если задача может быть выполнена другим процессом, нет смысла ждать; лучше сразу взять другую.

6. Проверяйте индекс. FOR UPDATE по неиндексированному условию может заблокировать значительно больше строк, чем ожидается, поскольку проверка выполняется по всем прочитанным строкам.

7. Различайте блокировку строк и таблиц. FOR UPDATE блокирует строки; LOCK TABLE — всю таблицу. Смешение в одном коде приводит к неожиданной сериализации.

8. Проверяйте влияние на реплики. Блокировки действуют только на основном узле; чтение с реплики их не видит и может вернуть данные, которые в этот момент меняются. Для согласованного чтения нужен основной узел либо явная проверка состояния репликации.

Рекомендации:

1. Применяйте при высокой конкуренции. Если конфликты частые, ожидание дешевле отклонённых попыток; о противоположном подходе — в статье про оптимистичную блокировку.
2. Избегайте внешних вызовов в транзакции. Все данные, нужные для решения, читаются до начала блокирующей секции.
3. Измеряйте время ожидания. Метрика длительности блокировок показывает узкие места раньше, чем они станут инцидентом.
4. Учитывайте уровень изоляции. На уровне SERIALIZABLE явная блокировка часто избыточна, поскольку конфликты обнаруживаются автоматически; об уровнях — в статье про изоляцию транзакций.
5. Не блокируйте больше, чем нужно. Точечное условие в WHERE сокращает число заблокированных строк и снижает конкуренцию.

Практический сценарий: очередь задач обрабатывалась одним воркером из восьми

Контекст: восемь воркеров забирали задачи из таблицы. Фактически задачи выполнялись последовательно, а производительность не отличалась от одного воркера.

1. Диагностика. Запрос выбора задачи использовал FOR UPDATE без SKIP LOCKED. Все воркеры блокировали первую строку и ждали завершения первого.
2. Проверка. pg_locks показал восемь транзакций, ожидающих одну и ту же строку. Среднее время ожидания составляло несколько секунд.
3. Дополнительная находка. Часть воркеров удерживала блокировку во время внешнего вызова, поэтому транзакции длились до десяти секунд.
4. Действия. (а) Добавили SKIP LOCKED к выбору задачи; (б) перенесли внешний вызов за пределы транзакции: задача помечалась как взятая, транзакция закрывалась, затем выполнялась работа; (в) установили lock_timeout = 2s, чтобы ожидание не было бесконечным; (г) добавили индекс на (status, created_at), поскольку выборка выполнялась по неиндексированному условию; (д) добавили метрику времени ожидания блокировки.
5. Результат. Восемь воркеров начали работать параллельно; пропускная способность очереди выросла примерно в семь раз, а ожидание блокировки перестало превышать десятки миллисекунд.

Вывод: без SKIP LOCKED очередь на таблице сериализуется на первой строке. Длительность транзакции влияет на блокировку напрямую, поэтому внешние вызовы должны находиться за её пределами.

FAQ

Что произойдёт при взаимной блокировке? PostgreSQL обнаружит цикл, прервёт одну из транзакций с ошибкой deadlock и запишет детали в лог. Приложение должно обработать ошибку и повторить транзакцию.

Можно ли использовать FOR UPDATE для чтения без изменения? Да, если последующая запись зависит от прочитанного и должна быть согласована. Для простого чтения блокировка не нужна и только снижает параллелизм.

Чем SKIP LOCKED отличается от NOWAIT? NOWAIT немедленно возвращает ошибку, если строка заблокирована. SKIP LOCKED пропускает заблокированные строки и возвращает остальные, что удобно для очередей.

Влияет ли блокировка на чтение без FOR UPDATE? Нет. Обычное чтение использует снимок данных и не блокируется, что обеспечивается многоверсионностью; о механизме — в статье про MVCC.

Источники: PostgreSQL docs (explicit locking, SELECT, lock runtime config) — ссылки в шапке.