Когда к базе данных одновременно лезут десятки сессий, встаёт вопрос: что одна транзакция видит из недоделанной работы другой и насколько они мешают друг другу. Уровень изоляции в postgresql - это ручка, которой вы регулируете компромисс между корректностью и параллелизмом. В этом уроке разберём три уровня, которые реально использует PostgreSQL (Read Committed, Repeatable Read, Serializable), какие аномалии каждый из них пропускает, как устроена защита Serializable через SSI и как выбрать уровень под конкретную задачу. Будем гонять конкурентные транзакции в двух сеансах psql и смотреть, кто что видит.
Как это работает
Стандарт SQL описывает четыре уровня и определяет их через запреты аномалий: грязное чтение (dirty read - увидеть незакоммиченные данные), неповторяющееся чтение (non-repeatable read - перечитал строку, а она изменилась), фантом (phantom - перечитал набор по условию, а в нём прибавилось строк). PostgreSQL реализует изоляцию через MVCC и снимки данных (snapshot), поэтому ведёт себя строже стандарта. В частности, грязного чтения тут не бывает вообще, даже на самом слабом уровне. Уровень Read Uncommitted в PostgreSQL существует только на бумаге: запросить его можно, но работать он будет как Read Committed.
Read Committed - это уровень по умолчанию. На нём каждый оператор внутри транзакции видит свой свежий снимок: данные, закоммиченные на момент старта именно этого оператора. Поэтому два одинаковых SELECT в одной транзакции могут вернуть разное, если между ними кто-то успел зафиксировать изменение. Звучит как баг, но для большинства OLTP-нагрузок это разумный дефолт: вы всегда работаете с актуальными данными и редко держите долгие транзакции.
Repeatable Read поднимает планку: снимок берётся один раз, в момент первого оператора транзакции, и держится до конца. Вся транзакция видит базу как застывшую фотографию. Non-repeatable read и фантомы исчезают: что прочитали в начале, то и будете видеть. Здесь важная особенность PostgreSQL - его Repeatable Read давит фантомы полностью, тогда как стандарт этого не требует. Платой становится возможная ошибка сериализации: если ваша транзакция попытается изменить строку, которую после взятия снимка уже поменял и закоммитил кто-то другой, вы получите serialization_failure (код 40001) и транзакцию надо повторять.
Serializable - самый строгий уровень. Он гарантирует, что результат параллельного выполнения транзакций будет таким же, как если бы они шли строго по очереди, в каком-то порядке. Именно этот уровень ловит аномалию записи вразнобой, write skew, которую Repeatable Read пропускает. Классический write skew: две транзакции читают общее условие (например, на смене должен остаться хотя бы один дежурный), каждая видит двоих, каждая снимает с дежурства себя - по отдельности всё валидно, а вместе дежурных не осталось. Snapshot-изоляция этого не замечает, потому что транзакции не трогали одни и те же строки.
В PostgreSQL Serializable работает через SSI - Serializable Snapshot Isolation. Это не блокировки в лоб, а тот же MVCC-снимок плюс отслеживание опасных зависимостей чтения и записи между транзакциями. Когда движок видит цикл зависимостей, способный дать неэквивалентный последовательному результат, он откатывает одну из транзакций с ошибкой сериализации. Поэтому при Serializable приложение обязано уметь повторять упавшие транзакции. SSI берёт легковесные predicate-блокировки (SIReadLock), они не мешают чужой записи, а лишь фиксируют факт чтения для анализа конфликтов.
SQL и примеры
Посмотреть и задать уровень. Уровень фиксируется в начале транзакции и не меняется после первого запроса:
Код: Выделить всё
SHOW default_transaction_isolation; -- read committed по умолчанию
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM bookings.flights WHERE status = 'Delayed';
COMMIT;
Код: Выделить всё
-- Сеанс A (Read Committed)
BEGIN;
SELECT count(*) FROM bookings.flights WHERE status = 'Delayed'; -- допустим, 5
-- здесь не коммитим, ждём сеанс B
Код: Выделить всё
-- Сеанс B
UPDATE bookings.flights SET status = 'Delayed'
WHERE flight_id = (SELECT flight_id FROM bookings.flights
WHERE status = 'Scheduled' LIMIT 1);
COMMIT;
Код: Выделить всё
-- Сеанс A продолжает
SELECT count(*) FROM bookings.flights WHERE status = 'Delayed'; -- уже 6
COMMIT;
Воспроизведём write skew на Serializable. Смоделируем правило бронирования места: на рейс нельзя продавать билет, если свободных мест в салоне меньше двух (грубая защита от овербукинга). Считаем занятые места по boarding_passes:
Код: Выделить всё
-- оба сеанса начинают так
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- проверяем, что свободных мест ещё хватает (читают оба)
SELECT count(*) AS taken
FROM bookings.boarding_passes bp
JOIN bookings.flights f ON f.flight_id = bp.flight_id
WHERE f.flight_no = 'PG0001' AND f.scheduled_departure::date = '2017-07-15';
-- каждый сеанс, увидев "мест ещё достаточно", вставляет свой посадочный
INSERT INTO bookings.boarding_passes(ticket_no, flight_id, boarding_no, seat_no)
VALUES ('0005432000999', 30625, 200, '1A'); -- во втором сеансе другой ticket_no/seat
COMMIT;
Код: Выделить всё
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: ...
HINT: The transaction might succeed if retried.
Полезно для отладки явно посмотреть текущий снимок и уровень:
Код: Выделить всё
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT txid_current(); -- id транзакции (в PG 13+ есть pg_current_xact_id())
SHOW transaction_isolation;
COMMIT;
- Думают, что Read Committed даёт стабильную картину на всю транзакцию. Нет: снимок обновляется на каждом операторе, между двумя SELECT данные могут уехать.
- Ставят SERIALIZABLE и забывают про обработку ошибки 40001. Без логики повтора транзакций приложение начнёт ронять пользователям случайные ошибки под нагрузкой.
- Меняют уровень после первого запроса в транзакции. SET TRANSACTION ISOLATION LEVEL работает только до первого оператора, иначе ошибка.
- Ждут, что Repeatable Read поймает write skew. Не поймает: snapshot-изоляция видит только конфликты по одним и тем же строкам, а write skew - это конфликт по логике, а не по строкам.
- Путают serialization_failure с deadlock. Дедлок (40P01) - это циклическое ожидание блокировок, его движок разрывает сразу; ошибка сериализации (40001) - это вердикт SSI, оба лечатся повтором, но причины разные.
- Полагаются на уровень изоляции вместо явных блокировок там, где нужна именно блокировка. SELECT ... FOR UPDATE и advisory locks решают часть задач дешевле, чем глобальный Serializable.
- Для postgresql для 1с по привычке выкручивают изоляцию вверх. 1С сама управляет блокировками через свой менеджер; обычно достаточно Read Committed, а лишний уровень только добавит откатов.
- Откройте два сеанса psql к своей базе (можно к демобазе bookings). В обоих выполните SHOW transaction_isolation и убедитесь, что это read committed.
- В сеансе A: BEGIN; затем SELECT count(*) FROM bookings.seats WHERE aircraft_code = '773'; не коммитьте.
- В сеансе B измените одну строку seats этого самолёта (например, поменяйте fare_conditions у одного места) и сделайте COMMIT.
- В сеансе A повторите тот же SELECT и сравните результат - поймайте неповторяющееся чтение. Сделайте COMMIT.
- Повторите шаги 2-4, но в сеансе A после BEGIN добавьте SET TRANSACTION ISOLATION LEVEL REPEATABLE READ. Убедитесь, что оба SELECT совпали.
- Соберите write skew на SERIALIZABLE: в обоих сеансах прочитайте одно агрегатное условие, затем оба вставьте/обновите строку, нарушающую это условие вместе. Зафиксируйте появление ERROR 40001 во втором сеансе.
- Напишите псевдокод ретрая: повторять транзакцию при SQLSTATE 40001 до 3 раз, иначе вернуть ошибку.
- Почему в PostgreSQL не бывает грязного чтения даже на уровне Read Committed и как с этим связан MVCC?
- Чем снимок данных на Read Committed отличается от снимка на Repeatable Read и в какой момент каждый из них берётся?
- Что такое write skew, почему его пропускает Repeatable Read и как Serializable его ловит?
- Как работает SSI и чем ошибка сериализации (40001) отличается от взаимоблокировки (40P01)?
- Почему при использовании Serializable приложение обязано уметь повторять транзакции?
- В каких случаях разумнее взять SELECT ... FOR UPDATE вместо повышения уровня изоляции до Serializable?
