Урок 5. Уровни изоляции транзакций PostgreSQL

В предыдущих уроках мы научились безопасно резервировать фоновые задачи несколькими Python-воркерами с помощью FOR UPDATE SKIP LOCKED.

Но у нас остался более фундаментальный вопрос: почему две транзакции могут видеть разные данные, хотя обращаются к одной базе PostgreSQL?

В этом уроке мы разберём уровни изоляции PostgreSQL 16, исследуем их поведение на знакомой таблице backend_training.tasks и узнаем, почему даже стабильный снимок данных не всегда гарантирует корректность конкурентной бизнес-логики.

1. Зачем нужна изоляция транзакций?

Представим, что два Python-воркера работают с одной таблицей:

Worker A читает задачу #42 и видит статус pending.

В это время Worker B меняет статус той же задачи на processing.

Что должен увидеть Worker A?

• Может ли он увидеть изменение Worker B до COMMIT?
• Увидит ли он изменение после COMMIT?
• Изменится ли ответ, если Worker A продолжает работать внутри прежней транзакции?

Ответ зависит от уровня изоляции транзакции.

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

2. Транзакция, конкурентность и снимок данных

2.1. Транзакция

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

BEGIN;

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

COMMIT;

COMMIT фиксирует изменения. ROLLBACK отменяет изменения транзакции.

2.2. Конкурентные транзакции

Две транзакции называются конкурентными, если их выполнение пересекается во времени.

Например, Worker A начал транзакцию, прочитал задачу и ещё не завершил работу. В этот момент Worker B начал другую транзакцию и изменил ту же задачу.

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

2.3. MVCC и snapshot

PostgreSQL использует MVCC — многоверсионное управление конкурентным доступом.

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

Снимок данных (snapshot) определяет, какие изменения транзакций видны конкретному запросу.

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

Но snapshot — это не физическая копия базы и не копирование всей таблицы в память. Это механизм определения видимых версий строк.

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

3. Аномалии чтения

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

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

3.1. Грязное чтение (Dirty Read)

Начальное состояние задачи #42: pending.

Worker B выполняет:

BEGIN;

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

-- COMMIT ещё не выполнен

Worker A пытается прочитать эту задачу.

Если бы Worker A увидел processing, он прочитал бы незавершённое изменение Worker B.

А Worker B ещё может выполнить ROLLBACK. Тогда изменение вообще не будет зафиксировано.

Такое чтение называется грязным.

В PostgreSQL грязное чтение не допускается ни на одном уровне изоляции.

3.2. Неповторяющееся чтение (Non-repeatable Read)

Теперь Worker A выполняет два одинаковых запроса в одной транзакции READ COMMITTED.

Первый запрос:

SELECT status
FROM backend_training.tasks
WHERE id = 42;

Результат:

pending

Между запросами Worker B меняет статус #42 на processing и делает COMMIT.

Worker A повторяет тот же SELECT.

Результат:

processing

Что изменилось? Значение поля status у той же задачи #42.

Это неповторяющееся чтение: повторное чтение ранее прочитанной строки вернуло другое значение.

Для READ COMMITTED такое поведение допустимо.

3.3. Фантомное чтение (Phantom Read)

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

Допустим, Worker A ищет все задачи со статусом pending:

SELECT id
FROM backend_training.tasks
WHERE status = 'pending'
ORDER BY id;

Первый результат:

42
43

В это время Worker B добавляет новую задачу #44 со статусом pending и делает COMMIT.

Worker A повторяет тот же поиск.

Второй результат:

42
43
44

Почему #44 называется фантомом?

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

Сама задача #44 реальна и уже зафиксирована. Слово «фантом» не означает, что PostgreSQL показал несуществующие данные.

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

Важно: фантом может появиться и без INSERT.

Например, задача #45 уже существовала со статусом processing. Worker B изменил её статус на pending и сделал COMMIT.

При следующем поиске всех pending Worker A увидит #45 — состав подходящих строк изменился.

Запомни: фантомное чтение — нормальный допустимый эффект READ COMMITTED, а не ошибка СУБД.

if pending_count < 3:
    create_task()

4. READ UNCOMMITTED

Стандарт SQL определяет уровень READ UNCOMMITTED, допускающий грязное чтение.

Но в PostgreSQL этот уровень фактически работает как READ COMMITTED.

BEGIN ISOLATION LEVEL READ UNCOMMITTED;

Команда допустима, но незавершённые изменения других транзакций обычному SELECT всё равно не видны.

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

5. READ COMMITTED — новый снимок для каждого запроса

Это уровень изоляции PostgreSQL по умолчанию.

Основное правило: каждый обычный SELECT получает новый снимок зафиксированных данных на момент начала этого запроса.

Worker A начал транзакцию и увидел задачу #42 в статусе pending.

Затем Worker B обновил статус на processing и выполнил COMMIT.

Следующий SELECT Worker A увидит processing, даже если транзакция A ещё не завершена.

Почему? Второй запрос получил новый снимок.

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

Вывод: READ COMMITTED подходит для многих backend-операций, но не гарантирует, что данные, прочитанные отдельным запросом, останутся актуальными для последующего решения.

6. REPEATABLE READ — стабильный снимок данных

Представим, что Worker A формирует отчёт по нескольким SQL-запросам.

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

Здесь полезен REPEATABLE READ.

Основное правило: транзакция использует один стабильный снимок данных.

Worker A прочитал статус задачи #42: pending.

Worker B изменил статус на processing и выполнил COMMIT.

Worker A снова читает #42 в своей транзакции и продолжает видеть pending.

Почему? Его снимок был получен раньше и остаётся стабильным.

Важная тонкость: снимок при REPEATABLE READ обычно устанавливается первым запросом к данным, а не самой командой BEGIN.

Если Worker A выполнил BEGIN, но ещё ничего не читал, а Worker B успел сделать COMMIT, первый SELECT Worker A может увидеть изменение Worker B.

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

А что с фантомами?

В PostgreSQL REPEATABLE READ предотвращает фантомное чтение.

Если Worker A получил список задач 42, 43, а Worker B позже добавил новую подходящую задачу #44, повторный SELECT Worker A в той же транзакции не увидит #44.

Это особенность PostgreSQL: стандарт SQL допускает фантомы на REPEATABLE READ, но PostgreSQL предоставляет более строгую гарантию.

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

7. Почему REPEATABLE READ иногда недостаточно?

Разберём аномалию write skew — несогласованная запись.

У нас есть две задачи, использующие один ограниченный ресурс.

Наше бизнес-правило: не более одной из этих двух задач может одновременно находиться в processing.

Для этого эксперимента задачи распределены заранее:

• Worker A отвечает за задачу #42.
• Worker B отвечает за задачу #43.

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

Перед изменением каждый выполняет:

SELECT COUNT(*)
FROM backend_training.tasks
WHERE id IN (42, 43)
  AND status = 'processing';

Если результат равен нулю, воркер переводит свою задачу в processing.

Порядок выполнения:

На REPEATABLE READ обе транзакции в таком сценарии могут успешно завершиться.

Итог:

Бизнес-правило нарушено.

Почему PostgreSQL допустил это?

Оба воркера прочитали согласованные снимки и получили COUNT = 0.

Они изменили разные строки. Прямого конфликта обновления одной и той же строки не было.

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

Это и есть write skew.

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

8. SERIALIZABLE — результат как при выполнении по очереди

Теперь попробуем решить проблему из предыдущего раздела.

SERIALIZABLE обеспечивает самую строгую изоляцию PostgreSQL.

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

8.1. Представим последовательное выполнение

Сначала работает Worker A.

Он проверяет COUNT = 0, переводит #42 в processing и делает COMMIT.

Затем работает Worker B.

Он проверяет COUNT = 1 и уже не может начинать обработку #43 по нашему бизнес-правилу.

Если первым работает Worker B, результат аналогичный: только #43 окажется в processing.

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

А при конкурентном REPEATABLE READ мы получили именно такой результат.

Следовательно, такое выполнение нельзя объяснить никаким последовательным порядком.

8.2. Как PostgreSQL узнаёт о конфликте?

Каждый воркер сначала читает данные, а затем изменяет свою задачу.

Worker A получил COUNT = 0. Но запись Worker B в задачу #43 могла бы изменить результат чтения A.

Аналогично Worker B получил COUNT = 0. Запись Worker A в задачу #42 могла бы изменить результат чтения B.

Получается противоречие:

• Чтобы объяснить чтение A, транзакция A должна логически идти раньше изменения B.
• Чтобы объяснить чтение B, транзакция B должна логически идти раньше изменения A.

Но одновременно A раньше B и B раньше A быть не может.

PostgreSQL обнаруживает опасную комбинацию зависимостей чтения и записи.

Для этого в режиме SERIALIZABLE используется механизм SSI — Serializable Snapshot Isolation.

Важно: PostgreSQL не блокирует всю таблицу, пока работает Worker A.

Worker B может читать данные и изменять другую строку параллельно.

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

Пример ошибки:

ERROR: could not serialize access
due to read/write dependencies among transactions

SQLSTATE: 40001

Это не означает, что PostgreSQL всегда отменяет именно Worker B. Какую транзакцию придётся повторить, зависит от фактического выполнения.

8.3. Как это понять совсем просто?

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

Саша и Маша оба проверили комнату, пока место было свободно.

Каждый решил, что может начать играть.

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

Воспитатель не запрещает детям одновременно проверять комнату.

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

Примерно так SERIALIZABLE предотвращает аномалию сериализации.

8.4. Что происходит после ошибки 40001?

Допустим, Worker A успешно зафиксировал перевод #42 в processing, а транзакция Worker B была отменена.

Приложение должно выполнить новую транзакцию B.

В новой транзакции Worker B снова проверяет количество обрабатываемых задач.

Теперь он получает COUNT = 1, потому что #42 уже в processing.

Следовательно, Worker B не переводит #43 в обработку.

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

Повтор только последнего UPDATE может сохранить ошибочное старое решение.

8.5. SERIALIZABLE не знает бизнес-правил

PostgreSQL не знает, что одновременно в processing должна находиться только одна из двух наших задач.

Если убрать проверку COUNT и просто изменить разные строки, оба UPDATE могут успешно зафиксироваться и на SERIALIZABLE.

Почему? Такое выполнение допускает обычный последовательный порядок.

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

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

9. Сравнение уровней изоляции PostgreSQL 16

Таблица описывает именно PostgreSQL 16. READ UNCOMMITTED здесь работает как READ COMMITTED.

10. Практические эксперименты

Открой два отдельных SQL-соединения в DBeaver: Консоль A и Консоль B.

Выполняй команды по очереди, согласно указанным шагам.

Не запускай весь сценарий одним блоком. Следи за режимом автокоммита DBeaver.

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

Эксперимент 1. READ COMMITTED

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

Сначала верни задачу #42 в pending и зафиксируй это изменение.

UPDATE backend_training.tasks
SET status = 'pending',
    lease_until = NULL
WHERE id = 42;

Консоль A:

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT status
FROM backend_training.tasks
WHERE id = 42;

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

Консоль B:

BEGIN;

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

COMMIT;

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

SELECT status
FROM backend_training.tasks
WHERE id = 42;

COMMIT;

Ожидаемый результат второго SELECT: processing.

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

Эксперимент 2. REPEATABLE READ

Цель: проверить стабильность снимка.

После завершения предыдущего эксперимента снова установи #42 в pending.

UPDATE backend_training.tasks
SET status = 'pending',
    lease_until = NULL
WHERE id = 42;

Консоль A:

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT status
FROM backend_training.tasks
WHERE id = 42;

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

Консоль B:

BEGIN;

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

COMMIT;

Консоль A:

SELECT status
FROM backend_training.tasks
WHERE id = 42;

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

Затем в консоли A:

COMMIT;

SELECT status
FROM backend_training.tasks
WHERE id = 42;

Ожидаемый результат нового SELECT: processing.

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

Эксперимент 3. Write skew при REPEATABLE READ

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

После завершения всех прежних транзакций подготовь две задачи:

UPDATE backend_training.tasks
SET status = 'pending',
    lease_until = NULL
WHERE id IN (42, 43);

Проверь, что обе строки существуют и имеют статус pending.

Консоль A — начинаем транзакцию и проверяем бизнес-правило:

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT COUNT(*)
FROM backend_training.tasks
WHERE id IN (42, 43)
  AND status = 'processing';

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

Консоль B:

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT COUNT(*)
FROM backend_training.tasks
WHERE id IN (42, 43)
  AND status = 'processing';

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

Теперь Worker A начинает обработку своей заранее назначенной задачи #42.

Консоль A:

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

Worker B начинает обработку своей заранее назначенной задачи #43.

Консоль B:

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

Консоль A:

COMMIT;

Консоль B:

COMMIT;

Проверь итог:

SELECT id, status
FROM backend_training.tasks
WHERE id IN (42, 43)
ORDER BY id;

Ожидаем, что обе задачи окажутся в processing.

В этом сценарии две транзакции обновляли разные строки и успешно зафиксировались, хотя совместный результат нарушает бизнес-правило.

Эксперимент 4. SERIALIZABLE

Цель: воспроизвести конфликт зависимостей и ошибку сериализации.

Закрой предыдущие транзакции и снова установи обе задачи в pending:

UPDATE backend_training.tasks
SET status = 'pending',
    lease_until = NULL
WHERE id IN (42, 43);

Теперь повтори предыдущий сценарий, используя SERIALIZABLE в обеих транзакциях.

Консоль A:

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT COUNT(*)
FROM backend_training.tasks
WHERE id IN (42, 43)
  AND status = 'processing';

Консоль B:

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT COUNT(*)
FROM backend_training.tasks
WHERE id IN (42, 43)
  AND status = 'processing';

Обе транзакции должны сначала получить 0.

Консоль A:

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

Консоль B:

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

Затем последовательно попытайся выполнить COMMIT в обеих консолях.

COMMIT;

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

Ожидается ошибка с SQLSTATE 40001 у одного из участников.

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

После ошибки выполни ROLLBACK в соответствующем соединении, если транзакция ещё находится в состоянии ошибки.

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

Наблюдение: в отличие от REPEATABLE READ, SERIALIZABLE предотвращает успешную фиксацию обеих конфликтующих транзакций с нарушением общего правила.

11. Какой уровень изоляции выбирать?

Не стоит автоматически использовать SERIALIZABLE во всём приложении.

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

Кроме того, при использовании SERIALIZABLE для защиты бизнес-правил необходимо учитывать все операции чтения и записи, влияющие на соответствующие данные. Смешение уровней изоляции может нарушить ожидаемые гарантии.

И наконец: уровни изоляции PostgreSQL не делают внешние HTTP-вызовы транзакционными.

Если внешний сервис выполнил операцию, а транзакция PostgreSQL затем откатилась, результат во внешнем сервисе автоматически не отменится.

Эту проблему мы отдельно исследуем в уроке о повторных попытках и идемпотентности внешних API.

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

• READ COMMITTED — каждый обычный SELECT получает новый снимок зафиксированных данных.
• REPEATABLE READ — транзакция работает со стабильным снимком, но возможен write skew.
• SERIALIZABLE — PostgreSQL дополнительно отслеживает опасные зависимости, чтобы успешно зафиксированные транзакции были эквивалентны некоторому последовательному выполнению.
• При SQLSTATE 40001 приложение должно повторить всю транзакцию с самого начала, включая чтение и принятие решений.

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

Источники

Е. Рогов, «PostgreSQL 16 изнутри», глава 2 «Изоляция», стр. 47–74; глава 4 «Снимки данных».

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

• 13.2. Transaction Isolation
• 13.1. MVCC Introduction
• 13.4. Data Consistency Checks
• 13.5. Serialization Failure Handling