SELECT ... FOR UPDATE: Pessimistic Row Locking in SQL

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

Зачем нужна блокировка строк

Классическая ошибка — прочитать баланс, посчитать новый в коде приложения и записать его. Между чтением и записью другая транзакция успевает сделать то же самое, и одно из обновлений теряется.

FOR UPDATE решает это, блокируя строки прямо при чтении. Блокировка держится до COMMIT или ROLLBACK.

BEGIN;
SELECT id, amount FROM orders WHERE user_id = 42 AND status = 'pending' FOR UPDATE;
-- rows are now locked until COMMIT
UPDATE orders SET status = 'paid' WHERE user_id = 42 AND status = 'pending';
COMMIT;

Любая другая транзакция, которая попытается взять FOR UPDATE на те же строки, будет ждать вашего COMMIT. Обычный SELECT без блокировки при этом по-прежнему видит старую версию строки (MVCC) и не блокируется.

FOR UPDATE против FOR SHARE

PostgreSQL предлагает несколько режимов:

• FOR UPDATE — эксклюзивная блокировка: никто не может ни читать с блокировкой, ни менять строку.
• FOR SHARE — разделяемая: несколько транзакций могут держать её одновременно, но никто не может выполнить UPDATE/DELETE. Удобно, чтобы «заморозить» строку-родителя, пока вставляете детей.
• FOR NO KEY UPDATE и FOR KEY SHARE — более слабые варианты, которые меньше конфликтуют с проверками внешних ключей.

BEGIN;
-- freeze the user row while we add an order for them
SELECT id FROM users WHERE id = 42 FOR SHARE;
INSERT INTO orders (user_id, amount, status) VALUES (42, 200, 'pending');
COMMIT;

NOWAIT и SKIP LOCKED

По умолчанию заблокированная строка заставляет вас ждать. Двумя модификаторами этим управляют:

• NOWAIT — не ждать вовсе: если строка занята, запрос немедленно падает с ошибкой. Хорошо для интерактивных операций, где лучше быстро вернуть «попробуйте позже».
• SKIP LOCKED — пропустить занятые строки и вернуть только свободные. Это рабочая лошадка очередей задач: каждый воркер забирает свою порцию, не мешая остальным.

-- worker pulls the next free job, ignoring rows other workers already took
BEGIN;
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- process it, then mark done
UPDATE orders SET status = 'processing' WHERE id = :id;
COMMIT;

Перевод денег и порядок блокировок

Перевод между счетами трогает две строки, и тут легко получить дедлок: транзакция A блокирует строку 1, потом строку 2; транзакция B — наоборот. Каждая ждёт другую.

Лекарство простое — всегда блокировать строки в одном и том же порядке, например по возрастанию id. IN (1, 2) с ORDER BY гарантирует детерминированный порядок.

BEGIN;
-- always lock in a deterministic order to avoid deadlocks
SELECT id, amount FROM orders
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

UPDATE orders SET amount = amount - 200 WHERE id = 1;
UPDATE orders SET amount = amount + 200 WHERE id = 2;
COMMIT;

Грабли и тонкости:

• FOR UPDATE нельзя использовать с агрегатами и GROUP BY — блокировать нечего, конкретных строк нет.
• При JOIN по умолчанию блокируются строки всех таблиц; уточняйте FOR UPDATE OF orders, чтобы блокировать только нужную.
• Долгая транзакция держит блокировки всё это время — не вызывайте внешний API между FOR UPDATE и COMMIT.
• SKIP LOCKED может вернуть меньше строк, чем LIMIT: это плата за конкурентность, и для очередей это норма.

Когда выбрать оптимистичную модель

Пессимистичные блокировки стоят денег: транзакции выстраиваются в очередь. Если конфликты редки, дешевле оптимистичный подход — добавьте колонку version и проверяйте её при записи.

-- optimistic update: succeeds only if nobody changed the row meanwhile
UPDATE orders
SET amount = 300, version = version + 1
WHERE id = 1 AND version = 7;

Если UPDATE вернул 0 строк, кто-то опередил вас — перечитайте и повторите. Правило: высокая конкуренция за одни и те же строки — берите FOR UPDATE; редкие конфликты при массовом параллелизме — оптимистичная версия.

Замечания по движкам: в MySQL (InnoDB) FOR UPDATE, FOR SHARE, NOWAIT и SKIP LOCKED поддерживаются, но семантика блокировок (gap-локи) отличается. ClickHouse — аналитическая СУБД без построчных блокировок и транзакций в привычном смысле, там подобных конструкций нет.