Зачем явные блокировки

В прошлом уроке мы узнали, что PostgreSQL не блокирует читателей. Это здорово для конкуренции — но иногда именно блокировка нужна. Когда?

Классический случай: ты хочешь прочитать строку и в этой же транзакции её обновить — и тебе важно, чтобы между чтением и обновлением никто другой её не трогал. На READ COMMITTED между твоим SELECT и UPDATE может вклиниться чужая транзакция: lost update.

Решений два:

1. Поднять уровень изоляции до REPEATABLE READ или SERIALIZABLE — PostgreSQL сам отловит конфликт и кинет 40001. Нужен retry.
2. Явно заблокировать строку через SELECT ... FOR UPDATE — PostgreSQL поставит лок, остальные SELECT FOR UPDATE той же строки будут ждать твой COMMIT. Retry не нужен.

Второй вариант предсказуемее по latency и не требует retry-логики. Это «классический» способ для финансовых операций и бронирования.

SELECT FOR UPDATE

Синтаксис простой:

SELECT ... FROM ... WHERE ... FOR UPDATE;

Что это делает:

1. Возвращает строки, как обычный SELECT.
2. Накладывает
   эксклюзивный row-level lock
   на каждую возвращённую строку.
3. Lock держится до COMMIT или ROLLBACK транзакции.
4. Пока ты держишь lock — другие транзакции на SELECT ... FOR UPDATE/UPDATE/DELETE той же строки ждут. Обычные SELECT без FOR UPDATE не блокируются.

SELECT FOR UPDATE обязательно нужно использовать внутри транзакции — BEGIN ... SELECT FOR UPDATE ... COMMIT. Иначе lock схватывается, и тут же отпускается, что бесполезно.

FOR SHARE

Если тебе нужно прочитать строку и гарантировать, что её никто не изменит до твоего commit’а — но самой её менять не собираешься — используй FOR SHARE. Это «shared lock»: несколько транзакций могут держать FOR SHARE одновременно, но никто не может UPDATE/DELETE или взять FOR UPDATE пока хоть один FOR SHARE живёт.

Типичный кейс: «проверить, что внешний ключ ещё существует». В PostgreSQL это и так делает foreign key, но в кастомных проверках иногда нужно вручную.

BEGIN;
SELECT id FROM categories WHERE id = 5 FOR SHARE;
-- Уверены, что category 5 не удалится до нашего COMMIT
INSERT INTO products (category_id, ...) VALUES (5, ...);
COMMIT;

FOR SHARE редко нужен в обычной работе. На 90% задач хватает FOR UPDATE.

NOWAIT — не ждать, упасть с ошибкой

По умолчанию SELECT FOR UPDATE ждёт, если строка заблокирована другой транзакцией. Иногда это плохо: тебе не нужна эта строка прямо сейчас, лучше выдать ошибку или попробовать что-то другое.

Для этого есть NOWAIT:

SELECT ... FROM ... WHERE ... FOR UPDATE NOWAIT;

Если хоть одна строка заблокирована — запрос немедленно возвращает ошибку: could not obtain lock on row in relation.

Использование: интерактивные приложения, где пользователь не должен ждать секунды на блокировке. Сервис говорит «попробуйте позже» вместо того, чтобы виснуть.

SKIP LOCKED — пропустить заблокированные

Самая интересная опция, появилась в PostgreSQL 9.5. Если строка заблокирована — пропустить её, как будто она не подошла под WHERE. Без ошибки, без ожидания.

Главное применение — очереди задач. Несколько воркеров одновременно делают «выбери N необработанных задач и заблокируй их». Если без SKIP LOCKED, второй воркер ждёт первый. С SKIP LOCKED — каждый берёт свои строки, и они не пересекаются. Это очередь практически бесплатно, без отдельной message queue.

В реальности воркер делает оба шага в одной транзакции через CTE:

WITH picked AS (
  SELECT id FROM jobs
  WHERE status = 'pending'
  ORDER BY id
  LIMIT 3
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'picked', picked_at = now()
FROM picked
WHERE jobs.id = picked.id
RETURNING jobs.id, jobs.payload;

Если у вас простая очередь задач (не миллион сообщений в секунду) — PostgreSQL SKIP LOCKED часто проще и надёжнее, чем поднимать отдельный Redis/RabbitMQ.

Deadlock

1. Транзакция A заблокировала row 1.
2. Транзакция B заблокировала row 2.
3. A пытается заблокировать row 2 — ждёт B.
4. B пытается заблокировать row 1 — ждёт A.
5. Никто не двигается.

PostgreSQL автоматически обнаруживает такие циклы (через периодическую проверку графа ожиданий, по умолчанию каждую секунду) и убивает одну из транзакций с ошибкой deadlock detected (SQLSTATE 40P01). Та сторона должна сделать ROLLBACK и retry.

Главное правило профилактики deadlock’ов: всегда блокируй строки в одном и том же порядке. Если все транзакции лочат row 1, потом row 2 — никогда не возникнет цикл «A ждёт B, B ждёт A». Если кто-то блокирует в порядке row 2, потом row 1 — добро пожаловать в deadlock.

Конкретно для нашего e-commerce: при создании заказа из нескольких товаров — сортируй product_id по возрастанию перед SELECT FOR UPDATE. Это самый простой паттерн «канонический порядок локов».

Иерархия блокировок в PostgreSQL

Понимать одну важную деталь: блокировки в PostgreSQL бывают разных типов и разной грануляции.

• Row-level locks — то, что даёт SELECT FOR UPDATE. Блокируется конкретная строка, остальные строки таблицы свободны. Это самый тонкий тип.
• Table-level locks — блокируется вся таблица. Бывают разной строгости: ACCESS SHARE (берёт обычный SELECT, чтобы DROP TABLE не убил его в процессе), ROW EXCLUSIVE (берёт UPDATE/DELETE), EXCLUSIVE (берёт ALTER TABLE и т.д.), ACCESS EXCLUSIVE (берёт DROP TABLE — никто не может ничего делать).
• Advisory locks — приложение-управляемые локи. Произвольное число, любая семантика. Работают через pg_advisory_lock(key). Полезно для кросс-транзакционной координации.

Большинство приложений работают только с row-level locks через FOR UPDATE/FOR SHARE (явно) и через автоматические локи UPDATE/DELETE. Table-level появляются в основном при миграциях схемы. Advisory — это уже специальные нужды.

Посмотреть текущие локи в базе можно через pg_locks:

SELECT locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE pid <> pg_backend_pid();

Это полезно при отладке «почему мой запрос висит» — обычно ответ виден в pg_locks + pg_stat_activity.

Когда явные локи не нужны

Не каждое обновление требует FOR UPDATE. Если ты делаешь самодостаточный UPDATE — UPDATE accounts SET balance = balance - 100 WHERE id = 1 — PostgreSQL сам берёт row lock на время этого UPDATE. Не нужно отдельно делать SELECT FOR UPDATE перед ним.

SELECT FOR UPDATE нужен только тогда, когда между чтением и записью в твоём коде есть логика, которая зависит от прочитанного значения, и эта логика выполняется на стороне приложения. Например: «прочитал баланс, на Python проверил, что хватает, послал UPDATE». Между прочитал и послал может пройти миллисекунды, в которые баланс изменится.

Если ты можешь записать всё в одно выражение SQL — делай так, и блокировка не нужна:

-- Нужен FOR UPDATE: логика на Python
SELECT balance FROM accounts WHERE id = 1;  -- читаем
-- if balance >= 100:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- пишем

-- Не нужен FOR UPDATE: всё в SQL
UPDATE accounts SET balance = balance - 100
WHERE id = 1 AND balance >= 100
RETURNING balance;
-- 0 строк = недостаточно средств

Второй вариант атомарный и не требует никаких локов от тебя — PostgreSQL сам сериализует параллельные UPDATE одной строки.

Что не делает FOR UPDATE

Важные ограничения:

• FOR UPDATE блокирует существующие строки. Если другая транзакция вставит новую строку, удовлетворяющую твоему WHERE, — её ты не заблокировал. Это значит, что SELECT * FROM jobs WHERE status='pending' FOR UPDATE не защитит от появления новой pending-задачи. (Защита от phantom — это другая история, через REPEATABLE READ/SERIALIZABLE.)
• FOR UPDATE отпускается только при COMMIT/ROLLBACK транзакции. Не существует «разблокируй строку явно». Закончил с строкой раньше всей транзакции — единственный способ её разблокировать — COMMIT всю транзакцию.
• На таблицах с FOREIGN KEY в PostgreSQL FOR UPDATE может блокировать больше, чем кажется (через locks KEY UPDATE). Это редко важно, но при отладке странных блокировок — посмотри pg_locks.

Чек-лист

• SELECT ... FOR UPDATE — эксклюзивный row-level lock; держится до COMMIT/ROLLBACK.
• SELECT ... FOR SHARE — shared lock; несколько одновременно, никто не может UPDATE.
• NOWAIT — упасть с ошибкой вместо ожидания.
• SKIP LOCKED — пропустить заблокированные строки. Идеально для очередей задач.
• Deadlock детектится автоматически через ~1 секунду; одна из транзакций откатывается с SQLSTATE 40P01.
• Профилактика deadlock — единый порядок блокировок по всему приложению.
• FOR UPDATE не нужен, если ты можешь записать всю логику одним UPDATE с условием в WHERE.
• Уровень изоляции и явные локи — два разных инструмента, часто используются вместе.