PostgreSQL Middle · транзакции, JSONB, блокировки

Два ментора в админке BatSchool одновременно нажали “засчитать” одно и то же ДЗ, каждый дописал свой комментарий и сохранил. Через минуту один из них пишет в чат: “а почему моя правка исчезла?” В коде админки стоит SELECT submission ... -> форма -> UPDATE submission ... WHERE id = ?. Без транзакции и без блокировки правка второго ментора затёрла правку первого: оба читали одну и ту же версию строки, а писали по очереди.

Этот баг живёт не в коде, а в зазоре между “прочитать” и “записать”. Залатать его одной строкой в API нельзя, лечится он на уровне БД: уровнем изоляции, блокировкой, иногда триггером.

Сегодня разбираем именно это, плюс заодно две темы, которые Middle обычно знает поверхностно: JSONB как замена половине NoSQL, и триггеры как способ закопать логику в БД. После фундамента из урока 01 у тебя есть SQL, схема и индексы; теперь добавим к ним транзакции и режим многопользовательской работы. MVCC только знакомим, глубокое погружение в уроке 04.

К концу занятия ты сможешь:

• воспроизвести lost update и phantom read в двух psql-сессиях и починить их повышением уровня изоляции;
• объяснить разницу между FOR UPDATE, FOR NO KEY UPDATE и FOR SHARE, выбрать подходящий под задачу;
• написать запрос по JSONB-колонке с GIN-индексом и понять, когда индекс не сработает;
• повесить триггер аудита и осознанно решить, оставлять его или вынести в код приложения.

0. ACID без магии

ACID, это не “магическая надёжность”, а четыре конкретные гарантии СУБД. Каждая буква стоит денег: реализация поверх обычного диска, файловой системы и сетевого стека требует строго определённых техник.

Буква	Что обещает	Чем держится в Postgres
Atomicity	транзакция либо целиком, либо никак	WAL до commit, на ROLLBACK просто не применяем
Consistency	переход между валидными состояниями БД	NOT NULL, CHECK, FK, UNIQUE, твоя бизнес-логика поверх
Isolation	параллельные транзакции выглядят так, будто шли по очереди	MVCC + блокировки, фактически зависит от уровня
Durability	после успешного COMMIT данные переживут падение	fsync WAL на диск, опционально синхронная репликация

Самый коварный пункт здесь, C. Многие думают, что Consistency, это про “согласованность данных”, но в ACID она про переход от одного валидного состояния к другому. Валидность задаёшь ты сам: ограничения схемы плюс инварианты приложения. БД лишь проверяет то, что ты ей объявил.

WAL, это ядро Atomicity и Durability одновременно. Без него fsync пришлось бы делать на каждую страницу данных, и Postgres был бы в 10 раз медленнее. Подробно про WAL разбираем в уроке 04, сейчас достаточно понимать: на COMMIT журнал гарантированно лежит на диске.

Isolation в Postgres настраиваемая: один и тот же код в Read Committed и Serializable ведёт себя по-разному. Это самый дорогой и самый недопонятый из четырёх, поэтому раздел 2 целиком про него.

1. Транзакции и SAVEPOINT

Транзакция, это явная группа операций между BEGIN и COMMIT/ROLLBACK. В Postgres каждый одиночный запрос автоматически обёрнут в транзакцию (autocommit). Явные транзакции нужны, когда два или больше изменений обязаны жить или умереть вместе.

BEGIN;

INSERT INTO homework_submissions (user_id, lesson_id, hw_id, url)
VALUES (42, 7, 'hw-bat-schema', 'https://github.com/.../pr/1');

UPDATE progress
SET state = 'done', updated_at = now()
WHERE user_id = 42 AND lesson_id = 7;

COMMIT;

Если второй UPDATE упадёт (например, нарушен CHECK), весь блок откатится: вставленная сдача исчезнет. Без BEGIN, INSERT бы остался, и в БД появился бы “осиротевший” факт сдачи без прогресса.

ROLLBACK и состояние “транзакция сломана”

В psql после ошибки внутри транзакции все следующие запросы возвращают ERROR: current transaction is aborted, commands ignored until end of transaction block. Postgres защищает тебя от случайного COMMIT поломанного состояния, придётся явно сделать ROLLBACK (или COMMIT, который тоже сработает как ROLLBACK).

В драйверах (pg, postgres.js, Drizzle) то же поведение: после exception нужно вызвать rollback и начать новую транзакцию. Прозрачные “транзакции” во многих ORM, это та самая обёртка.

SAVEPOINT

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

BEGIN;

INSERT INTO users (email, display_name) VALUES ('a@b.c', 'A');

SAVEPOINT before_optional;

  INSERT INTO progress (user_id, lesson_id, state)
  VALUES ((SELECT id FROM users WHERE email = 'a@b.c'), 999, 'todo');
  -- lesson 999 не существует, FK сломается

ROLLBACK TO SAVEPOINT before_optional;

-- транзакция жива, первая вставка ещё в силе
INSERT INTO progress (user_id, lesson_id, state)
VALUES ((SELECT id FROM users WHERE email = 'a@b.c'), 1, 'todo');

COMMIT;

В реальном продакшне SAVEPOINT почти не пишут руками. Они нужны:

• драйверу для “вложенных транзакций” (Drizzle, Prisma эмулируют nested transactions через savepoint);
• при батч-операциях, где разрешено пропускать отдельные строки (импорт CSV: на падающей строке ROLLBACK TO, продолжаем).

Long-running транзакции, дорогая привычка

Транзакция, которую забыли закоммитить, держит снимок данных и блокировки. Vacuum не может почистить dead tuples, которые видны хоть одной живой транзакции, поэтому таблицы пухнут.

Симптом, который видишь в pg_stat_activity: запись с state = 'idle in transaction'. На каждом серьёзном инциденте кто-нибудь вспоминает про неё первым делом.

SELECT pid, usename, state, query, xact_start, now() - xact_start AS age
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY age DESC;

В прод-конфиге обязательно ставится idle_in_transaction_session_timeout (типично 60s, иногда меньше), чтобы такие сессии закрывались автоматически.

2. Уровни изоляции

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

Аномалии

• Dirty read: видишь изменения, которые ещё не закоммичены.
• Non-repeatable read: одну и ту же строку читаешь дважды, получаешь разные значения.
• Phantom read: повторный запрос с тем же условием возвращает новые строки.
• Serialization anomaly: каждая транзакция по отдельности корректна, но их комбинация нарушает инвариант.

Уровни и что они закрывают

Уровень	Dirty	Non-repeatable	Phantom	Serialization	Что значит на практике
Read Uncommitted	в PG нет	да	да	да	в Postgres равен Read Committed
Read Committed (default)	нет	да	да	да	каждый запрос видит свежий снимок
Repeatable Read	нет	нет	нет*	да	вся транзакция видит снимок начала
Serializable	нет	нет	нет	нет	полная сериализуемость, дорого

*В стандарте SQL Repeatable Read ещё допускает phantom read, но реализация Postgres строже и фантомы тоже закрывает за счёт snapshot isolation.

Переключается уровень одной строкой в начале транзакции:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- или
BEGIN ISOLATION LEVEL SERIALIZABLE;

Lost update вживую

Это та самая race condition из урока 01, теперь поломаем её руками.

Откроем две psql-сессии (две вкладки терминала). В обеих сессиях стартанём транзакции на Read Committed. Допустим, у нас есть таблица счётчиков:

CREATE TABLE counters (id int PRIMARY KEY, value int NOT NULL);
INSERT INTO counters VALUES (1, 100);

T1:

BEGIN;
SELECT value FROM counters WHERE id = 1; -- 100
-- читаем 100, считаем 100 + 10 = 110

T2:

BEGIN;
SELECT value FROM counters WHERE id = 1; -- 100
-- читаем 100, считаем 100 + 5 = 105

T1:

UPDATE counters SET value = 110 WHERE id = 1;
COMMIT;

T2:

UPDATE counters SET value = 105 WHERE id = 1;
COMMIT;

Итог: value = 105. Изменение T1 потеряно. Это lost update в чистом виде.

Чинится тремя способами, идущими от плохого к хорошему:

1. Атомарный UPDATE на сервере (если бизнес-логика позволяет):

UPDATE counters SET value = value + 10 WHERE id = 1;

Здесь читаем и пишем в одной операции, БД сама ставит row-level lock. Подходит для простых счётчиков, не подходит, если нужно решение на основе значения (“если меньше лимита, увеличь”).

2. SELECT ... FOR UPDATE (пессимистическая блокировка):

BEGIN;
SELECT value FROM counters WHERE id = 1 FOR UPDATE; -- блокирует строку
-- T2 на этом же SELECT FOR UPDATE будет ждать COMMIT T1
UPDATE counters SET value = 110 WHERE id = 1;
COMMIT;

T2 после T1’s COMMIT перечитает свежее значение и посчитает по нему. Подробнее про варианты FOR UPDATE в разделе блокировок.

3. Repeatable Read + ловить ошибку (оптимистическая блокировка):

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT value FROM counters WHERE id = 1;
UPDATE counters SET value = 110 WHERE id = 1;
COMMIT;

На Repeatable Read Postgres увидит, что строка изменилась после начала транзакции, и кинет ERROR: could not serialize access due to concurrent update. Приложение ловит ошибку и перезапускает транзакцию. Это оптимистическая блокировка: дешёво в среднем, дорого в худшем.

Когда какой уровень брать

Простое правило: Read Committed по умолчанию, Repeatable Read для read-heavy транзакций со сложной логикой, Serializable для критичных инвариантов.

Сценарий	Уровень	Почему
API-эндпоинт с одним INSERT или UPDATE	Read Committed	дефолт, минимум блокировок
Отчёт по нескольким таблицам, который должен видеть консистентный снимок	Repeatable Read	snapshot isolation
Перевод денег между счётами, проверка лимита	Serializable	инвариант на нескольких строках
Импорт CSV с миллионом строк	Read Committed	блокировки коротко-живущие

Serializable стоит до 30% performance, поэтому на нагруженных эндпоинтах его не включают глобально. Включают точечно на критические транзакции.

SELECT ... FOR ..., четыре варианта

Конструкция	Что блокирует	Кейс
FOR UPDATE	строку полностью, ждут другие FOR UPDATE, FOR SHARE и UPDATE	модифицируешь строку
FOR NO KEY UPDATE	строку, но не блокирует FK-ссылающихся	UPDATE без изменения PK/уникальных колонок (Postgres использует это автоматически)
FOR SHARE	shared-блокировка, другие могут читать с FOR SHARE, но не FOR UPDATE	гарантия, что строка не изменится до конца транзакции
FOR KEY SHARE	ещё слабее, блокирует только изменение ключа	держит FK живым

В 99% случаев тебе нужен FOR UPDATE или ничего. Остальное, точечная оптимизация.

FOR UPDATE SKIP LOCKED дополнительно умеет пропускать заблокированные другими транзакциями строки. Это базовый паттерн для очередей задач прямо в Postgres:

SELECT id, payload
FROM job_queue
WHERE state = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

Десять воркеров одновременно вызывают этот запрос и получают непересекающиеся пачки задач. Без SKIP LOCKED девять воркеров стояли бы в очереди.