1. Почему обычного SELECT недостаточно?

В предыдущем уроке мы разобрали проблему Race Condition — ситуацию, когда результат работы системы зависит от порядка выполнения конкурентных операций.

Теперь представим Python Backend-сервис, который обрабатывает фоновые задачи из PostgreSQL.

В таблице tasks находятся три задачи:

Два независимых Python-воркера пытаются получить следующую задачу.

Оба выполняют SQL-запрос:

SELECT id, type, status
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1;

Оба воркера могут получить задачу #42.

Почему?

Потому что обычный SELECT не резервирует выбранную строку за конкретным воркером.

Пока Worker A читает задачу, Worker B тоже может её прочитать.

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

Какие проблемы нам необходимо решить?

1. Как не допустить конфликтующего захвата одной задачи двумя воркерами?
2. Как сделать так, чтобы второй воркер мог получить другую задачу, не ожидая первого?

В этом уроке мы самостоятельно исследуем механизмы PostgreSQL, которые помогают решить обе проблемы.

Что ты научишься делать?

• Понимать, как работают строковые блокировки PostgreSQL.
• Объяснять поведение SELECT FOR UPDATE.
• Использовать SKIP LOCKED для конкурентной выборки задач.
• Понимать последствия COMMIT и ROLLBACK.
• Атомарно резервировать задачи через UPDATE ... RETURNING.
• Объяснять ограничения такого решения при использовании в production.

2. Подготовка лабораторного окружения

Для выполнения практических экспериментов потребуется PostgreSQL 16 и SQL-клиент, например DBeaver.

Скачай прикреплённый к уроку ZIP-архив с материалами лабораторной работы.

Распакуй его и последовательно выполни действия из файла README_guide.txt.

Инструкция поможет создать учебную базу, подготовить таблицу tasks и настроить два независимых подключения к PostgreSQL.

Важно: используй отдельную учебную базу данных. Не выполняй лабораторные скрипты в рабочей базе.

Проверка окружения

После завершения настройки выполни запрос:

SELECT id, type, status
FROM backend_training.tasks
ORDER BY id;

Ожидаемый результат:

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

Перед каждым новым экспериментом используй 02_reset.sql, предварительно завершив открытые транзакции.

Два независимых соединения

Для наших экспериментов понадобятся два независимых соединения с PostgreSQL.

Назовём их:

• Worker A — первая SQL-консоль.
• Worker B — вторая SQL-консоль.

В каждом соединении выполни:

SELECT
    current_database(),
    pg_backend_pid();

pg_backend_pid() возвращает идентификатор серверного процесса PostgreSQL, обслуживающего соединение.

PID должны отличаться. Это подтвердит, что запросы выполняются в разных серверных сеансах.

Во время лабораторных работ мы будем управлять транзакциями вручную: через BEGIN, COMMIT и ROLLBACK.

Убедись, что SQL-клиент сохраняет одну и ту же сессию между командами и не завершает транзакцию автоматически после каждого запроса.

3. Транзакции и блокировки строк

Перед практикой разберём два понятия.

Что такое транзакция?

Транзакция позволяет объединить операции с базой данных в одну логическую единицу работы.

Например:

BEGIN;

UPDATE backend_training.tasks
SET status = 'processing'
WHERE id = 42;

COMMIT;

BEGIN начинает транзакцию.

COMMIT фиксирует изменения.

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

Что такое блокировка строки?

PostgreSQL позволяет транзакции заблокировать определённую строку.

BEGIN;

SELECT id, type, status
FROM backend_training.tasks
WHERE id = 42
FOR UPDATE;

Такой запрос устанавливает блокировку строки #42.

Пока транзакция не завершится, другая транзакция не сможет получить конфликтующую блокировку этой же строки без ожидания.

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

Важно: блокировка строки не изменяет её статус.

Задача #42 может быть заблокирована, но по-прежнему иметь статус pending.

4. Эксперимент №1. Почему Worker B ожидает Worker A?

Цель: увидеть, что происходит, когда два воркера пытаются заблокировать одну задачу.

Перед началом восстанови исходные данные с помощью 02_reset.sql.

Шаг 1. Worker A получает задачу

В консоли Worker A выполни:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT id, type, status
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE;

Ожидаемый результат:

42 | generate_report | pending

Не выполняй COMMIT или ROLLBACK.

Транзакция Worker A должна оставаться открытой.

Шаг 2. Сформулируй предположение

Теперь Worker B собирается выполнить точно такой же запрос.

При этом в таблице существуют задачи #43 и #44, которые никто не блокировал.

Какой результат ты ожидаешь?

1. Worker B сразу получит #42.
2. Worker B сразу получит #43.
3. Worker B будет ожидать.

Сначала сформулируй предположение и объясни причину.

Шаг 3. Worker B выполняет запрос

Во второй SQL-консоли:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT id, type, status
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE;

Что произошло?

Запрос Worker B не завершается сразу.

Он ожидает освобождения блокировки строки #42.

Но почему PostgreSQL не выбрал #43?

Мы указали:

ORDER BY id
LIMIT 1
FOR UPDATE;

Запрос пытается получить первую подходящую задачу.

Первой оказывается #42.

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

Обычный FOR UPDATE не означает «перейти к следующей строке, если текущая занята».

Шаг 4. Worker A завершает транзакцию

Вернись в консоль Worker A.

Выполни:

UPDATE backend_training.tasks
SET status = 'processing'
WHERE id = 42;

COMMIT;

Теперь посмотри на консоль Worker B.

Ожидаемый результат

43 | send_email | pending

Почему не #42?

Мы используем уровень изоляции READ COMMITTED.

После ожидания PostgreSQL учитывает зафиксированное изменение строки и повторно проверяет условие:

WHERE status = 'pending'

Строка #42 теперь имеет статус processing и больше не подходит.

Поэтому в нашем эксперименте запрос Worker B возвращает #43.

Шаг 5. Заверши эксперимент

В консоли Worker B:

ROLLBACK;

Вывод

FOR UPDATE защищает строку от конфликтующих операций.

Но при конкуренции за одну задачу второй воркер может ожидать.

Для очереди фоновых задач такое ожидание не всегда желательно.

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

Именно для этого пригодится SKIP LOCKED.

5. Эксперимент №2. Как работает SKIP LOCKED?

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

Шаг 1. Подготовка

Восстанови данные с помощью 02_reset.sql.

Убедись, что все три задачи имеют статус pending.

Шаг 2. Worker A блокирует задачу #42

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT id, type, status
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE;

Результат:

42 | generate_report | pending

Оставь транзакцию открытой.

Шаг 3. Сравним два запроса

Раньше Worker B выполнял:

SELECT id, type, status
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE;

Теперь добавим:

SKIP LOCKED

Получится:

SELECT id, type, status
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

SKIP LOCKED позволяет пропускать строки, заблокированные конфликтующими транзакциями.

Вопрос: какую задачу получит Worker B, если #42 по-прежнему заблокирована?

Шаг 4. Worker B выполняет запрос

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT id, type, status
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

Ожидаемый результат

43 | send_email | pending

Worker B не ожидает освобождения блокировки #42.

PostgreSQL пропускает эту строку и выбирает следующую доступную задачу.

Текущее состояние:

Обрати внимание: оба воркера получили разные задачи, но статусы строк ещё не изменились.

Это принципиальное различие между блокировкой и резервированием задачи.

Важное ограничение SKIP LOCKED

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

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

Официальная документация PostgreSQL 16

SELECT id, status
FROM backend_training.tasks
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED;

6. Эксперимент №3. Что происходит при ROLLBACK?

Цель: исследовать, как отмена транзакции влияет на доступность задач.

Продолжаем предыдущий эксперимент.

Сейчас:

• Worker A удерживает блокировку #42.
• Worker B удерживает блокировку #43.
• Обе транзакции открыты.
• Все задачи имеют статус pending.

Шаг 1. Повторный запрос Worker B

Не завершая транзакцию Worker B, повторно выполни:

SELECT id, type, status
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

Какую задачу получит Worker B: #43 или #44?

Сначала сформулируй предположение.

Ожидаемый результат

43 | send_email | pending

Почему Worker B снова получил #43?

Потому что задача по-прежнему удовлетворяет условию:

WHERE status = 'pending'

Кроме того, блокировка принадлежит самой транзакции Worker B.

Транзакция не конфликтует с собственными блокировками.

Поэтому повторный SELECT FOR UPDATE SKIP LOCKED может снова вернуть ту же строку.

Вывод: SKIP LOCKED не ведёт список задач, которые воркер уже выбирал.

Шаг 2. Worker A выполняет ROLLBACK

В консоли Worker A:

ROLLBACK;

Теперь блокировка #42 освобождена.

Worker B продолжает удерживать #43.

Шаг 3. Worker B снова ищет задачу

SELECT id, type, status
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

Вопрос: какую задачу теперь получит Worker B?

Ожидаемый результат

42 | generate_report | pending

Почему?

После ROLLBACK строка #42 снова доступна.

Её статус остался pending.

При сортировке по id она находится раньше #43.

Теперь Worker B удерживает блокировки обеих строк: #42 и #43.

Шаг 4. Заверши эксперимент

В консоли Worker B:

ROLLBACK;

Проверь состояние таблицы:

SELECT id, type, status
FROM backend_training.tasks
ORDER BY id;

Все три задачи должны остаться в состоянии pending.

Инженерный вывод

Worker A заблокировал задачу #42, но ещё не выполнил её.

Worker B пропустил #42 и получил #43.

Значит, Worker B может начать обработку #43 раньше, чем кто-либо обработает #42.

Более того, Worker A может отменить транзакцию, после чего #42 снова станет доступной.

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

Если задачи независимы, это часто приемлемый компромисс.

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

Ещё одно ограничение

Важно: SKIP LOCKED позволяет пропускать заблокированные строки, но не гарантирует отсутствия любых ожиданий.

7. Почему одного SELECT FOR UPDATE SKIP LOCKED недостаточно?

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

Но представим реальный Python Backend-сервис.

Worker A получил задачу #42:

BEGIN;

SELECT id
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

Затем выполнил:

COMMIT;

И только после этого начал выполнять задачу.

Что произойдёт с блокировкой?

Она освободится.

А что произойдёт со статусом?

Ничего. Задача останется pending.

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

Как решить проблему?

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

Правильная последовательность:

BEGIN
  |
  v
SELECT ... FOR UPDATE SKIP LOCKED
  |
  v
UPDATE status = 'processing'
  |
  v
COMMIT
  |
  v
Выполнение задачи в Python

Теперь блокировка защищает операцию резервирования.

А зафиксированный статус processing исключает задачу из следующих выборок, использующих условие status = 'pending'.

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

8. Эксперимент №4. Атомарное резервирование задачи

Цель: объединить выбор доступной задачи, её блокировку и изменение статуса в одном SQL-запросе.

8.1. Знакомство с WITH

В PostgreSQL конструкция WITH позволяет объявить именованный результат запроса и использовать его в основном SQL-операторе.

Такую конструкцию называют CTE — Common Table Expression.

Простой пример:

WITH candidate AS (
    SELECT id
    FROM backend_training.tasks
    WHERE status = 'pending'
    ORDER BY id
    LIMIT 1
)
SELECT id
FROM candidate;

Здесь candidate — имя результата подзапроса.

Это не постоянная таблица в базе данных. Имя доступно только в пределах текущего SQL-оператора.

Теперь добавим блокировку:

WITH candidate AS (
    SELECT id
    FROM backend_training.tasks
    WHERE status = 'pending'
    ORDER BY id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
SELECT id
FROM candidate;

Теперь CTE выбирает задачу, которую удалось заблокировать без ожидания конфликтующих строковых блокировок.

Остаётся использовать результат для изменения статуса.

8.2. Полный SQL-запрос

Восстанови данные через 02_reset.sql.

Затем выполни:

WITH candidate AS (
    SELECT id
    FROM backend_training.tasks
    WHERE status = 'pending'
    ORDER BY id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE backend_training.tasks AS t
SET status = 'processing'
FROM candidate AS c
WHERE t.id = c.id
RETURNING t.id, t.type, t.status;

Как работает запрос?

Первая часть — WITH.

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

Вторая часть — UPDATE.

Она изменяет статус выбранной задачи с pending на processing.

Третья часть — RETURNING.

Она возвращает данные уже обновлённой строки.

В результате приложение сразу получает информацию о зарезервированной задаче.

Важно: конструкция WITH сама по себе не делает запрос атомарным.

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

Если запрос выполняется внутри явной транзакции, для фиксации изменений необходим COMMIT.

Ожидаемый результат

42 | generate_report | processing

После фиксации изменения задача #42 больше не соответствует условию status = 'pending'.

Поэтому следующий воркер сможет получить #43.

8.3. Проверяем конкурентное выполнение

Снова восстанови исходные данные через 02_reset.sql.

Worker A:

BEGIN ISOLATION LEVEL READ COMMITTED;

WITH candidate AS (
    SELECT id
    FROM backend_training.tasks
    WHERE status = 'pending'
    ORDER BY id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE backend_training.tasks AS t
SET status = 'processing'
FROM candidate AS c
WHERE t.id = c.id
RETURNING t.id, t.type, t.status;

Ожидается:

42 | generate_report | processing

Не выполняй COMMIT.

Теперь переключись в консоль Worker B.

Worker B:

BEGIN ISOLATION LEVEL READ COMMITTED;

WITH candidate AS (
    SELECT id
    FROM backend_training.tasks
    WHERE status = 'pending'
    ORDER BY id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE backend_training.tasks AS t
SET status = 'processing'
FROM candidate AS c
WHERE t.id = c.id
RETURNING t.id, t.type, t.status;

Перед тем как посмотреть результат, попробуй предположить: какую задачу получит Worker B?

Ожидаемый результат:

43 | send_email | processing

Worker A уже заблокировал #42 и изменил её статус, хотя изменение ещё не зафиксировано.

Worker B пропускает заблокированную строку #42 и резервирует следующую доступную задачу.

Таким образом, оба воркера резервируют разные задачи.

8.4. Фиксируем изменения

В консоли Worker A:

COMMIT;

В консоли Worker B:

COMMIT;

Теперь проверь таблицу:

SELECT id, type, status
FROM backend_training.tasks
ORDER BY id;

Ожидаемый результат:

Мы получили базовый механизм атомарного резервирования задач в PostgreSQL.

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

9. Можно ли использовать это решение в production?

Наш SQL-запрос решает конкретную задачу: конкурентное резервирование доступных записей.

Но резервирование задачи и её успешное выполнение — не одно и то же.

Проблема №1. Падение воркера

Представим, что Worker A успешно зарезервировал задачу #42.

PostgreSQL зафиксировал статус:

status = 'processing'

После этого Python-процесс аварийно завершился, не успев выполнить задачу.

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

Задача #42 продолжит находиться в состоянии processing.

Другие воркеры используют условие:

WHERE status = 'pending'

Следовательно, они больше не выберут #42.

Задача может остаться в состоянии processing на неопределённое время.

Для production-системы потребуется механизм обнаружения и восстановления зависших задач.

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

Но это уже отдельная инженерная задача.

Проблема №2. Повторное выполнение

Рассмотрим другую ситуацию.

Worker A выполнил внешний HTTP-запрос и успешно создал документ.

Но до обновления статуса задачи на completed процесс аварийно завершился.

Механизм восстановления вернул задачу в pending.

После этого Worker B получил её и выполнил повторно.

В результате документ может быть создан дважды.

Для защиты от нежелательных повторных эффектов используют идемпотентность.

Идемпотентной называют операцию, повторное выполнение которой не приводит к дополнительным нежелательным изменениям результата.

Например, внешний API может принимать уникальный идентификатор операции и не создавать второй документ при повторном запросе с тем же идентификатором.

Какие гарантии даёт наш механизм?

Главный вывод: FOR UPDATE SKIP LOCKED помогает организовать конкурентное резервирование задач.

Но сам по себе этот механизм не гарантирует exactly-once выполнение.

10. Итоговое задание. Аварийное завершение воркера

Теперь рассмотрим реальную инженерную ситуацию.

Условие

Worker A зарезервировал задачу #42.

Он изменил статус задачи на processing и выполнил COMMIT.

После этого Python-процесс аварийно завершился, не успев выполнить задачу.

Worker B продолжает выбирать задачи с помощью запроса:

SELECT id, type, status
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

Вопросы

1. Сможет ли Worker B автоматически получить задачу #42?
2. Почему?
3. Какой механизм необходимо добавить в систему, чтобы задача не оставалась потерянной для обработки?

Важно: не требуется писать полноценную систему восстановления.

Достаточно объяснить причину проблемы и предложить обоснованный инженерный подход к её решению.

11. Вопросы для технического собеседования

Попробуй ответить без обращения к материалам урока.

1. Чем обычный SELECT отличается от SELECT FOR UPDATE?
2. Почему второй воркер может ожидать, даже если в таблице есть другие задачи со статусом pending?
3. Как работает SKIP LOCKED и какие ограничения есть у этого механизма?
4. Что происходит со строковыми блокировками после COMMIT и ROLLBACK?
5. Почему недостаточно выполнить SELECT FOR UPDATE SKIP LOCKED, а затем завершить транзакцию, не изменяя статус задачи?
6. Зачем при резервировании задачи изменять статус на processing?
7. Почему атомарное резервирование не гарантирует exactly-once выполнение?

На собеседовании уровня Senior важно не просто знать SQL-операторы, а уметь объяснять, какие гарантии обеспечивает решение и какие проблемы остаются.

12. Итоги урока

Мы начали с ситуации, когда два независимых воркера могли получить одну задачу.

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

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

Главное, что нужно запомнить:

Блокировка защищает операцию резервирования внутри транзакции.

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

Но успешное резервирование ещё не означает успешное выполнение.

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

Что изучим дальше?

В следующем уроке перенесём изученный механизм в Python и SQLAlchemy.

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