SELECT FOR UPDATE: построчные блокировки, очереди и дедлоки в PostgreSQL

Иногда обычного SELECT недостаточно.

Представьте ситуацию: приложение читает строку из базы, считает новое значение в коде, а потом записывает результат обратно.

Например:

1. Прочитали баланс пользователя.
2. Проверили, хватает ли денег.
3. Посчитали новый баланс.
4. Записали его в таблицу.

На первый взгляд всё нормально. Но если две транзакции делают это одновременно, начинается гонка.

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

Чтобы такого не происходило, в PostgreSQL используют:

SELECT id, amount FROM accounts WHERE user_id = 42 FOR UPDATE;

Эта конструкция читает строки и сразу берёт на них блокировку.

Пока ваша транзакция не завершится через COMMIT или ROLLBACK, другая транзакция не сможет изменить или удалить эти же строки.

Это называется пессимистичная блокировка.

Разберёмся, как она работает и где легко ошибиться.

Зачем нужен SELECT FOR UPDATE

Начнём с простой задачи.

Есть таблица счетов accounts. В ней лежат балансы пользователей:

id	user_id	amount
1	42	1000

Пользователь хочет списать 300 рублей.

Наивный сценарий в приложении может выглядеть так:

SELECT amount
FROM accounts
WHERE user_id = 42;

Приложение получает:

1000

Потом в коде считает:

1000 - 300 = 700

И обновляет баланс:

UPDATE accounts
SET amount = 700
WHERE user_id = 42;

Проблема появляется, если одновременно пришли две операции списания.

Например, две транзакции почти одновременно прочитали баланс 1000.

Первая считает:

1000 - 300 = 700

Вторая тоже считает:

1000 - 300 = 700

Обе записывают 700.

Хотя если было два списания по 300, правильный баланс должен быть:

400

Так появляется потерянное обновление.

Чтобы не дать двум транзакциям одновременно работать с одной и той же строкой, можно заблокировать строку при чтении:

BEGIN;

SELECT id, amount
FROM accounts
WHERE user_id = 42
FOR UPDATE;

-- app checks the balance and computes the new value here

UPDATE accounts
SET amount = amount - 300
WHERE user_id = 42;

COMMIT;

Ключевая часть здесь:

SELECT id, amount FROM accounts WHERE user_id = 42 FOR UPDATE;

Она означает:

«Я собираюсь обновлять эти строки. Заблокируй их для других транзакций, пока моя транзакция не закончится».

Что именно блокирует FOR UPDATE

SELECT ... FOR UPDATE берёт блокировку на конкретные строки, которые попали в результат запроса.

Например:

BEGIN;

SELECT id, amount
FROM accounts
WHERE user_id = 42
FOR UPDATE;

Если строка найдена, она будет заблокирована до конца транзакции.

Блокировка снимется только после COMMIT или ROLLBACK.

Пока блокировка держится, другая транзакция не сможет выполнить над этой строкой:

UPDATE accounts
SET amount = amount - 100
WHERE user_id = 42;

Она будет ждать.

Также будет ждать другая транзакция, которая попробует взять такую же блокировку:

SELECT id, amount
FROM accounts
WHERE user_id = 42
FOR UPDATE;

Но обычный SELECT без блокировки обычно продолжит работать:

SELECT id, amount
FROM accounts
WHERE user_id = 42;

Почему?

Потому что PostgreSQL использует MVCC — механизм многоверсионности. Упрощённо: читатель может видеть старую подтверждённую версию строки, пока другая транзакция держит блокировку.

То есть обычное чтение не обязано ждать блокировку FOR UPDATE.

Но изменение строки — обязано.

Пример с заказом

Допустим, есть таблица заказов orders. И мы хотим безопасно перевести заказ из статуса pending в статус paid.

BEGIN;

SELECT id, user_id, amount, status
FROM orders
WHERE id = 1001
  AND status = 'pending'
FOR UPDATE;

UPDATE orders
SET status = 'paid'
WHERE id = 1001
  AND status = 'pending';

COMMIT;

Что здесь происходит:

1. Начинаем транзакцию.
2. Находим заказ в статусе pending.
3. Блокируем эту строку через FOR UPDATE.
4. Проверяем данные и выполняем нужную бизнес-логику.
5. Обновляем статус.
6. Завершаем транзакцию.

Если в этот момент другая транзакция попробует оплатить тот же заказ, она не сможет параллельно изменить эту строку. Ей придётся ждать завершения первой транзакции.

Это защищает от ситуации, когда один и тот же заказ случайно обрабатывается два раза.

Важное правило: транзакция должна быть короткой

FOR UPDATE держит блокировку до конца транзакции.

Поэтому между SELECT ... FOR UPDATE и COMMIT не должно быть долгих действий.

Плохой пример:

BEGIN;

SELECT id, amount
FROM orders
WHERE id = 1001
FOR UPDATE;

-- call external payment API
-- wait 5s for the response
-- another HTTP request
-- write logs
-- complex business logic

UPDATE orders
SET status = 'paid'
WHERE id = 1001;

COMMIT;

Пока приложение ждёт внешний API, строка в базе остаётся заблокированной.

Если другие транзакции хотят работать с этим же заказом, они стоят в очереди.

Лучше делать так:

1. Внешние долгие действия выполнять до блокировки, если это возможно.
2. После FOR UPDATE делать только то, что действительно должно быть защищено блокировкой.
3. Быстро обновлять данные.
4. Быстро завершать транзакцию.

Хорошая транзакция с блокировкой — короткая транзакция.

FOR UPDATE и очередь ожидания

По умолчанию, если строка уже заблокирована, PostgreSQL будет ждать.

Представим две транзакции.

Первая:

BEGIN;

SELECT id, amount
FROM accounts
WHERE user_id = 42
FOR UPDATE;

Она заблокировала строку и пока не сделала COMMIT.

Вторая транзакция выполняет:

BEGIN;

SELECT id, amount
FROM accounts
WHERE user_id = 42
FOR UPDATE;

Что произойдёт?

Вторая транзакция зависнет в ожидании.

Она не получит ошибку сразу. Она будет ждать, пока первая транзакция завершится.

Когда первая транзакция сделает COMMIT, вторая сможет продолжить.

Это нормальное поведение. Так PostgreSQL выстраивает транзакции в очередь за одной и той же строкой.

Но если блокировки держатся долго, очередь может стать проблемой для всего приложения.

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

Иногда ждать не хочется.

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

В такой ситуации можно не заставлять пользователя ждать, а быстро вернуть сообщение:

«Заказ уже обрабатывается, попробуйте позже».

Для этого используют NOWAIT:

BEGIN;

SELECT id, amount, status
FROM orders
WHERE id = 1001
FOR UPDATE NOWAIT;

Если строка свободна, PostgreSQL её заблокирует.

Если строка уже заблокирована другой транзакцией, запрос не будет ждать. Он сразу завершится ошибкой.

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

SKIP LOCKED: пропустить занятые строки

SKIP LOCKED работает иначе.

Он говорит PostgreSQL:

«Если строка заблокирована другой транзакцией, не жди её. Просто пропусти и возьми следующую свободную».

Это особенно полезно для очередей задач.

Представим таблицу jobs. В ней лежат задачи для обработки:

id	status	created_at
1	pending	2026-01-01 10:00:00
2	pending	2026-01-01 10:01:00
3	pending	2026-01-01 10:02:00

У нас есть несколько воркеров. Каждый воркер должен взять одну свободную задачу и начать её обрабатывать.

Если все воркеры будут делать обычный SELECT, они могут выбрать одну и ту же задачу.

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

BEGIN;

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

Если первый воркер забрал задачу id = 1, второй воркер не будет ждать эту строку. Он пропустит её и возьмёт следующую свободную.

После выбора задачу обычно сразу переводят в другой статус:

UPDATE jobs
SET status = 'processing'
WHERE id = 1;

COMMIT;

Так несколько воркеров могут параллельно разбирать очередь и не мешать друг другу.

Более удобный паттерн для очереди задач

Часто выбор и обновление задачи объединяют в один запрос через CTE.

Например:

WITH picked_job AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'processing'
FROM picked_job p
WHERE j.id = p.id
RETURNING j.id, j.status;

Что здесь происходит:

1. В CTE picked_job выбирается одна свободная задача.
2. FOR UPDATE SKIP LOCKED блокирует выбранную строку.
3. UPDATE сразу переводит её в статус processing.
4. RETURNING возвращает задачу воркеру.

Такой подход уменьшает окно между «выбрали задачу» и «пометили её как занятую».

Для очередей это очень важно.

Почему SKIP LOCKED может вернуть меньше строк, чем LIMIT

Допустим, вы написали:

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

Вы ожидаете получить 10 задач.

Но PostgreSQL может вернуть 7, 3 или даже 0.

Почему?

Потому что часть подходящих строк уже может быть заблокирована другими воркерами. SKIP LOCKED не ждёт их, а пропускает.

Для очередей это нормальное поведение.

Если воркер не получил задачи, он может:

• немного подождать;
• повторить попытку позже;
• завершить текущий цикл обработки.

Главное — понимать, что SKIP LOCKED выбирает не «строго первые 10 задач», а «до 10 свободных задач».

FOR UPDATE, FOR SHARE и другие режимы

В PostgreSQL есть несколько режимов построчных блокировок.

Самый строгий и понятный — FOR UPDATE.

Он нужен, когда вы собираетесь менять выбранные строки.

Например:

SELECT id, amount
FROM accounts
WHERE id = 1
FOR UPDATE;

Но есть и более слабые режимы.

FOR SHARE

FOR SHARE берёт разделяемую блокировку.

Несколько транзакций могут одновременно держать FOR SHARE на одной строке, но другая транзакция не сможет спокойно изменить или удалить эту строку.

Пример:

BEGIN;

SELECT id
FROM users
WHERE id = 42
FOR SHARE;

INSERT INTO orders (user_id, amount, status)
VALUES (42, 200, 'pending');

COMMIT;

Так можно сказать:

«Пока я создаю заказ для пользователя, не надо удалять или существенно менять эту строку пользователя».

На практике часто хватает обычных внешних ключей, но понимать идею полезно.

FOR NO KEY UPDATE

FOR NO KEY UPDATE похож на более мягкий вариант FOR UPDATE.

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

Например, меняете имя пользователя, но не его id.

FOR KEY SHARE

FOR KEY SHARE — ещё более мягкий режим. Он нужен в сценариях, связанных с проверками внешних ключей и защитой ключевых значений.

Для начинающего главное правило такое:

если вы точно собираетесь менять выбранные строки и хотите защититься от гонок, чаще всего нужен FOR UPDATE.

Остальные режимы важны, когда вы глубже оптимизируете конкуренцию и хотите уменьшить лишние конфликты между транзакциями.

FOR UPDATE при JOIN

Допустим, есть запрос с соединением:

SELECT o.id, o.amount, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.id = 1001
FOR UPDATE;

Важный нюанс: при JOIN PostgreSQL может заблокировать строки из нескольких таблиц, участвующих в запросе.

Но часто нам нужно заблокировать только заказ, а пользователя просто прочитать.

Тогда лучше явно указать таблицу:

SELECT o.id, o.amount, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.id = 1001
FOR UPDATE OF o;

Часть FOR UPDATE OF o означает:

«Блокируй строки только из таблицы orders, у которой алиас o».

Это хорошая привычка для запросов с JOIN.

Так вы не берёте лишние блокировки и меньше мешаете другим транзакциям.

Где FOR UPDATE использовать нельзя

FOR UPDATE блокирует конкретные строки таблицы.

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

Например, нельзя просто так заблокировать результат агрегации:

SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
FOR UPDATE;

Почему?

Потому что результат:

user_id | count

это уже не конкретная строка из orders, а агрегированная строка, собранная из многих записей.

PostgreSQL не понимает, какую именно строку нужно заблокировать как результат COUNT(*).

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

Дедлоки: когда транзакции ждут друг друга

Блокировки защищают данные, но у них есть опасность — дедлоки.

Дедлок — это ситуация, когда две транзакции ждут друг друга, и ни одна не может продолжить.

Классический пример — перевод денег между двумя счетами.

Есть два счёта:

id	amount
1	1000
2	500

Транзакция A переводит деньги со счёта 1 на счёт 2.

Она делает:

SELECT id, amount
FROM accounts
WHERE id = 1
FOR UPDATE;

Потом хочет заблокировать счёт 2.

В это же время транзакция B переводит деньги наоборот — со счёта 2 на счёт 1.

Она делает:

SELECT id, amount
FROM accounts
WHERE id = 2
FOR UPDATE;

Потом хочет заблокировать счёт 1.

Получается:

• транзакция A держит счёт 1 и ждёт счёт 2;
• транзакция B держит счёт 2 и ждёт счёт 1.

Обе ждут друг друга.

Это и есть дедлок.

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

Но для приложения это всё равно неприятно: одну операцию придётся повторять.

Как уменьшить риск дедлоков

Главное правило:

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

Например, при переводе между двумя счетами можно всегда блокировать счета по возрастанию id.

BEGIN;

SELECT id, amount
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

UPDATE accounts
SET amount = amount - 200
WHERE id = 1;

UPDATE accounts
SET amount = amount + 200
WHERE id = 2;

COMMIT;

Если все транзакции придерживаются одного порядка, ситуация становится безопаснее.

Транзакция A и транзакция B не будут брать строки в противоположном порядке. Они обе сначала попытаются заблокировать меньший id, потом больший.

Одна транзакция подождёт другую, но дедлока не будет.

Ожидание — нормально. Дедлок — плохо.

Пример безопасного перевода денег

Допустим, нужно перевести 200 со счёта 1 на счёт 2.

BEGIN;

SELECT id, amount
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

UPDATE accounts
SET amount = amount - 200
WHERE id = 1;

UPDATE accounts
SET amount = amount + 200
WHERE id = 2;

COMMIT;

Но в реальном коде нужно ещё проверить, хватает ли денег.

Например:

BEGIN;

SELECT id, amount
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

-- app verifies account 1 has enough funds

UPDATE accounts
SET amount = amount - 200
WHERE id = 1;

UPDATE accounts
SET amount = amount + 200
WHERE id = 2;

COMMIT;

Проверка баланса должна происходить после блокировки.

Почему?

Потому что до блокировки баланс мог измениться другой транзакцией.

Если вы сначала прочитали баланс без блокировки, а потом позже заблокировали строку, вы могли принимать решение на основе устаревших данных.

Можно ли обойтись без SELECT FOR UPDATE

Иногда можно.

Например, если операция простая, лучше сделать её одним атомарным UPDATE.

Допустим, нужно списать 300 только если денег достаточно:

UPDATE accounts
SET amount = amount - 300
WHERE user_id = 42
  AND amount >= 300;

Если запрос изменил одну строку — списание прошло.

Если изменил ноль строк — денег не хватило или счёт не найден.

Такой подход часто лучше, чем:

1. SELECT;
2. расчёт в приложении;
3. UPDATE.

Потому что база сама атомарно проверяет условие и меняет значение.

SELECT FOR UPDATE нужен, когда вам действительно нужно:

• прочитать данные;
• принять решение;
• возможно, изменить несколько строк;
• защитить этот участок от конкурентных изменений.

Оптимистичная модель: когда блокировки не нужны заранее

FOR UPDATE — это пессимистичный подход.

Он говорит:

«Я заранее заблокирую строку, потому что боюсь конфликта».

Но иногда конфликты редкие. Тогда блокировать строки заранее может быть слишком дорого.

В таких случаях используют оптимистичный подход.

Идея такая:

1. Читаем строку вместе с номером версии.
2. Пользователь или приложение меняет данные.
3. При записи проверяем, что версия не изменилась.
4. Если версия изменилась, значит кто-то нас опередил.

Пример таблицы:

id	amount	version
1	1000	7

Обновление:

UPDATE accounts
SET amount = 700,
    version = version + 1
WHERE id = 1
  AND version = 7;

Если строка обновилась, всё хорошо.

Если PostgreSQL обновил 0 строк, значит версия уже не 7. Кто-то изменил эту запись раньше.

Тогда приложение должно:

• перечитать свежие данные;
• заново принять решение;
• повторить операцию или показать пользователю конфликт.

FOR UPDATE или оптимистичная версия: что выбрать

Простое правило такое.

SELECT FOR UPDATE хорошо подходит, когда:

• конфликты вероятны;
• несколько транзакций часто трогают одни и те же строки;
• ошибка недопустима;
• нужно изменить несколько связанных строк;
• операция должна пройти строго последовательно.

Например:

• перевод денег;
• списание остатка со склада;
• обработка одного заказа;
• выбор задачи из очереди;
• бронирование ограниченного ресурса.

Оптимистичная версия лучше, когда:

• конфликты редкие;
• пользователей много;
• блокировать заранее слишком дорого;
• проще повторить операцию при конфликте.

Например:

• редактирование профиля;
• изменение настроек;
• обновление черновика;
• формы в админке.

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

Частые ошибки с SELECT FOR UPDATE

Ошибка 1. Держать транзакцию слишком долго

Плохо:

BEGIN;

SELECT id
FROM orders
WHERE id = 1001
FOR UPDATE;

-- long external calls
-- waiting for user response
-- heavy processing

UPDATE orders
SET status = 'paid'
WHERE id = 1001;

COMMIT;

Лучше держать блокировку как можно меньше времени.

Ошибка 2. Блокировать лишние таблицы в JOIN

Не очень хорошо:

SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.id = 1001
FOR UPDATE;

Лучше явно указать, что блокируем только заказ:

SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.id = 1001
FOR UPDATE OF o;

Ошибка 3. Блокировать строки в разном порядке

Опасно:

-- transaction one
SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

SELECT *
FROM accounts
WHERE id = 2
FOR UPDATE;

А другая транзакция делает наоборот:

SELECT *
FROM accounts
WHERE id = 2
FOR UPDATE;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

Так легко получить дедлок.

Лучше всегда блокировать в одном порядке:

SELECT *
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

Ошибка 4. Использовать SKIP LOCKED там, где нельзя пропускать строки

SKIP LOCKED хорош для очередей.

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

Например, для фоновых задач это нормально:

«Эта задача занята другим воркером — возьму следующую».

А для финансовой операции это может быть неприемлемо:

«Этот счёт заблокирован — пропущу его и сделаю вид, что его нет».

Так делать нельзя.

Ошибка 5. Читать данные до блокировки и принимать решение по старому значению

Плохо:

SELECT amount
FROM accounts
WHERE id = 1;

-- app decided there are enough funds

BEGIN;

SELECT amount
FROM accounts
WHERE id = 1
FOR UPDATE;

UPDATE accounts
SET amount = amount - 300
WHERE id = 1;

COMMIT;

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

Решение нужно принимать после чтения с блокировкой или делать атомарный UPDATE с условием.

Как это выглядит в MySQL и ClickHouse

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

В MySQL с InnoDB похожий механизм тоже есть:

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

Также в современных версиях MySQL доступны варианты вроде NOWAIT и SKIP LOCKED.

Но семантика блокировок в InnoDB отличается. Например, там есть gap locks — блокировки промежутков в индексах. Из-за этого запрос может блокировать не только найденные строки, но и диапазоны между ними, особенно при определённых уровнях изоляции и условиях поиска.

Поэтому переносить поведение PostgreSQL в MySQL один к одному нельзя.

А ClickHouse — это другая история. Это аналитическая СУБД, рассчитанная на быстрые чтения и массовую обработку данных. В ней нет привычных OLTP-транзакций и построчных блокировок в стиле PostgreSQL.

То есть конструкции уровня SELECT ... FOR UPDATE для ClickHouse — не основной сценарий и не привычный инструмент.

Короткая шпаргалка

Заблокировать строку перед изменением:

BEGIN;

SELECT id, amount
FROM accounts
WHERE id = 1
FOR UPDATE;

UPDATE accounts
SET amount = amount - 300
WHERE id = 1;

COMMIT;

Не ждать заблокированную строку:

SELECT id
FROM orders
WHERE id = 1001
FOR UPDATE NOWAIT;

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

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

Блокировать только одну таблицу в JOIN:

SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.id = 1001
FOR UPDATE OF o;

Блокировать несколько строк в стабильном порядке:

SELECT id, amount
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

Оптимистичное обновление через версию:

UPDATE accounts
SET amount = 700,
    version = version + 1
WHERE id = 1
  AND version = 7;

Главное правило

SELECT FOR UPDATE нужен, когда вы читаете строки, принимаете решение и хотите гарантировать, что никто не изменит эти строки до конца вашей транзакции.

Он защищает от гонок, потерянных обновлений и двойной обработки данных.

Но за безопасность приходится платить:

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

Поэтому запомните практическое правило:

Используйте SELECT FOR UPDATE, когда конфликт за одни и те же строки вероятен и ошибка недопустима. Держите транзакцию короткой. Блокируйте строки в одинаковом порядке. Для очередей используйте SKIP LOCKED. Если конфликты редкие, подумайте об оптимистичной версии через колонку version.

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