Модуль: Базы данных · Уровень: Senior

TL;DR#

• Стандарт SQL определяет 4 уровня изоляции: Read Uncommitted, Read Committed, Repeatable Read, Serializable — и описывает их через 3 запрещённые аномалии (dirty read, non-repeatable read, phantom read).
• Стандарт определяет уровни через аномалии, а не через механизм. PostgreSQL реализует их через MVCC (snapshot isolation), поэтому реальная защита от аномалий строже, чем минимально требует стандарт.
• В PostgreSQL фактически три уровня: READ UNCOMMITTED ведёт себя как READ COMMITTED (грязного чтения не существует никогда — MVCC отдаёт только закоммиченные версии строк).
• Read Committed (дефолт) — снимок берётся на каждый отдельный statement. Защищает от dirty read, но допускает non-repeatable read, phantom read и lost update между statements.
• Repeatable Read в PG — это полноценный snapshot isolation: один снимок на всю транзакцию. Предотвращает dirty/non-repeatable/phantom read (в отличие от стандарта, где RR допускает phantom). Но допускает write skew и при конфликте на запись бросает 40001.
• Serializable в PG реализован через SSI (Serializable Snapshot Isolation): отслеживает зависимости чтения/записи через predicate locks (SIReadLock) и откатывает транзакции с serialization_failure (SQLSTATE 40001), нарушающие сериализуемость, включая write skew.
• На уровнях RR и Serializable код обязан иметь retry-цикл на 40001 (и 40P01 deadlock). Без него приложение будет случайно падать под нагрузкой.

Теория#

Стандарт SQL: уровни через аномалии#

Стандарт ANSI/ISO SQL не описывает как СУБД должна изолировать транзакции, а только какие аномалии запрещены на каждом уровне. Аномалии:

• Dirty read — транзакция читает данные, записанные другой ещё не закоммиченной транзакцией. Если та откатится — мы прочитали то, чего «не было».
• Non-repeatable read — транзакция дважды читает одну и ту же строку и получает разные значения, потому что между чтениями другая транзакция закоммитила UPDATE/DELETE этой строки.
• Phantom read — транзакция дважды выполняет один и тот же запрос по диапазону (предикату), и во второй раз появляются новые строки (INSERT другой транзакции), удовлетворяющие предикату.

Классическая таблица стандарта:

Важно: эта таблица — минимальные требования. СУБД вправе быть строже. Также в стандарте есть «дыра»: он определён через локинг-ориентированную модель и не покрывает аномалии snapshot isolation, такие как write skew (описана в статье Berenson et al. «A Critique of ANSI SQL Isolation Levels», 1995). PostgreSQL использует MVCC, а не двухфазный локинг, поэтому привязка к этой таблице неполна.

Аномалии, которых нет в стандартной таблице#

• Lost update — две транзакции читают одно значение, обе вычисляют новое на его основе и записывают; одна перезаписывает другую, обновление теряется. Пример: balance = balance + 100 через read-modify-write в коде приложения.
• Write skew — две транзакции читают пересекающийся набор данных, принимают решение на основе прочитанного и пишут в разные строки. Каждая по отдельности корректна, но вместе нарушают инвариант. Snapshot isolation (PG Repeatable Read) её не ловит.
• Read-only transaction anomaly — даже транзакция, которая только читает, может увидеть несериализуемое состояние при snapshot isolation. SSI это учитывает.

Что РЕАЛЬНО реализовано в PostgreSQL#

PostgreSQL построен на MVCC: каждая строка имеет версии (xmin/xmax), и транзакция видит снимок (snapshot) — набор версий, закоммиченных на определённый момент. Из этого следуют ключевые отличия от стандарта:

Read Uncommitted == Read Committed#

В PG грязное чтение невозможно физически: снимок никогда не включает незакоммиченные версии чужих транзакций. Запрос SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED принимается, но движок трактует его как READ COMMITTED. Это легально по стандарту (СУБД может быть строже).

Read Committed (дефолт)#

• Снимок берётся в начале каждого SQL-statement, а не транзакции.
• Каждая новая команда видит данные, закоммиченные до её старта. Поэтому два SELECT в одной транзакции могут вернуть разное — non-repeatable и phantom read возможны.
• Особенность для пишущих команд: если UPDATE/DELETE/SELECT FOR UPDATE натыкается на строку, заблокированную другой транзакцией, он ждёт; после её коммита перечитывает строку (re-evaluates) уже с новой версией и применяет условие WHERE заново. Это так называемый «EvalPlanQual» — источник тонких багов.

Repeatable Read == Snapshot Isolation#

• Снимок берётся один раз на первую команду транзакции (точнее, на первый запрос, читающий/пишущий данные) и используется до конца.
• Предотвращает dirty read, non-repeatable read и phantom read — то есть строже стандарта (стандарт допускает phantom на RR).
• НЕ предотвращает write skew и read-only anomaly.
• При попытке обновить строку, изменённую и закоммиченную другой транзакцией после начала снимка, бросает: ERROR: could not serialize access due to concurrent update — SQLSTATE 40001. Транзакцию нужно повторить.

Serializable == SSI (Serializable Snapshot Isolation)#

PostgreSQL (с 9.1) реализует настоящую сериализуемость поверх snapshot isolation через алгоритм SSI (Cahill et al.):

• Поверх обычного snapshot isolation движок отслеживает зависимости между транзакциями через predicate locks (видны как SIReadLock в pg_locks). Они не блокируют — это «отметки» о том, какие диапазоны/строки транзакция прочитала.
• SSI ищет паттерн «опасной структуры» — два последовательных rw-конфликта (rw-antidependency), образующих цикл, который делает расписание несериализуемым.
• Если обнаружен потенциально несериализуемый порядок, одна из транзакций откатывается с: ERROR: could not serialize access due to read/write dependencies among transactions — SQLSTATE 40001.
• Гарантирует, что любой успешно закоммиченный набор транзакций эквивалентен какому-то последовательному их выполнению. Это ловит и write skew, и read-only anomaly.

Важно: predicate locks потребляют память (max_pred_locks_per_transaction), могут эскалировать с уровня строки до страницы/отношения, что увеличивает число ложных конфликтов и 40001.

Сводная таблица: стандарт vs PostgreSQL#

* На RR lost update «теряется» в виде тихой перезаписи нет — вместо этого PG бросает 40001 при конфликте записи (first-updater-wins с откатом второго). На RC lost update в чистом read-modify-write через приложение возможен.

SET TRANSACTION ISOLATION LEVEL#

-- Установка уровня для текущей транзакции (только сразу после BEGIN, до первого запроса)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... запросы ...
COMMIT;

-- Компактная форма
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Read-only транзакция (на SERIALIZABLE даёт оптимизацию SSI + DEFERRABLE)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

-- Дефолт уровня для всех новых транзакций сессии
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Глобально (postgresql.conf / ALTER SYSTEM / ALTER DATABASE)
-- default_transaction_isolation = 'read committed'

READ ONLY DEFERRABLE на Serializable: транзакция дождётся «безопасного» снимка и тогда никогда не получит 40001 и никого не заставит откатиться — полезно для длинных аналитических read-only запросов.

Пример write skew#

Инвариант: «хотя бы один врач должен быть on-call». Два врача одновременно снимают себя с дежурства.

-- Таблица
CREATE TABLE doctors (id int PRIMARY KEY, name text, on_call boolean);
INSERT INTO doctors VALUES (1, 'Alice', true), (2, 'Bob', true);

-- Транзакция A (Alice)            -- Транзакция B (Bob)
BEGIN ISOLATION LEVEL REPEATABLE READ;
                                   BEGIN ISOLATION LEVEL REPEATABLE READ;
-- A видит: on_call = 2
SELECT count(*) FROM doctors WHERE on_call; -- 2
                                   -- B видит свой снимок: on_call = 2
                                   SELECT count(*) FROM doctors WHERE on_call; -- 2
-- 2 >= 1, можно снять Alice
UPDATE doctors SET on_call=false WHERE id=1;
                                   -- 2 >= 1, можно снять Bob (по СВОЕМУ снимку)
                                   UPDATE doctors SET on_call=false WHERE id=2;
COMMIT;                            COMMIT;
-- Итог: НИ ОДНОГО on-call врача. Инвариант нарушен.

На REPEATABLE READ обе транзакции коммитятся успешно — пишут в разные строки, конфликта записи нет. Write skew проходит.

На SERIALIZABLE SSI замечает rw-зависимость (обе читали один диапазон WHERE on_call, и обе его изменили), и одна из транзакций при COMMIT (или раньше) получит:

ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot ...
HINT: The transaction might succeed if retried.

Альтернатива без Serializable — явно материализовать конфликт через блокировку читаемых строк:

-- На RC/RR это закрывает write skew: SELECT FOR UPDATE блокирует прочитанные строки,
-- вторая транзакция ждёт и перечитывает актуальное состояние.
SELECT count(*) FROM doctors WHERE on_call FOR UPDATE;

Но FOR UPDATE не блокирует фантомные INSERT’ы (нет строки — нечего блокировать), поэтому от write skew с появлением новых строк спасает только Serializable или явные advisory/предметные блокировки.

Lost update: демонстрация#

-- READ COMMITTED, read-modify-write в приложении (АНТИПАТТЕРН)
-- A: SELECT balance FROM accounts WHERE id=1;  -- 100
-- B: SELECT balance FROM accounts WHERE id=1;  -- 100
-- A: UPDATE accounts SET balance=200 WHERE id=1; (100+100)
-- B: UPDATE accounts SET balance=200 WHERE id=1; (100+100) -- потеряли +100 от A

-- ПРАВИЛЬНО: атомарно в БД
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

-- Либо оптимистично через версию
UPDATE accounts SET balance = $1, version = version + 1
WHERE id = 1 AND version = $2; -- если 0 строк затронуто — кто-то опередил, retry

На RR второй UPDATE balance=balance+100 той же строки после коммита первого даст 40001, а не тихую потерю.

Обработка serialization_failure (40001) с retry в Go#

Ключевой паттерн: на RR/Serializable любая транзакция может легально упасть с 40001, и это не ошибка приложения, а сигнал «повтори». Нужен идемпотентный retry с backoff.

database/sql + lib/pq:

import (
	"context"
	"database/sql"
	"errors"
	"math/rand"
	"time"

	"github.com/lib/pq"
)

// isRetryable: 40001 (serialization_failure) и 40P01 (deadlock_detected)
func isRetryable(err error) bool {
	var pqErr *pq.Error
	if errors.As(err, &pqErr) {
		switch pqErr.Code {
		case "40001", "40P01":
			return true
		}
	}
	return false
}

func withRetry(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) error {
	const maxAttempts = 5
	var lastErr error
	for attempt := 0; attempt < maxAttempts; attempt++ {
		tx, err := db.BeginTx(ctx, &sql.TxOptions{
			Isolation: sql.LevelSerializable, // или LevelRepeatableRead
		})
		if err != nil {
			return err
		}
		if err = fn(tx); err != nil {
			_ = tx.Rollback()
			if isRetryable(err) {
				lastErr = err
				// экспоненциальный backoff с джиттером
				backoff := time.Duration(1<<attempt)*time.Millisecond +
					time.Duration(rand.Intn(5))*time.Millisecond
				select {
				case <-ctx.Done():
					return ctx.Err()
				case <-time.After(backoff):
				}
				continue
			}
			return err
		}
		if err = tx.Commit(); err != nil {
			// ВАЖНО: 40001 может прийти именно на Commit (особенно на Serializable)
			if isRetryable(err) {
				lastErr = err
				continue
			}
			return err
		}
		return nil
	}
	return lastErr
}

Аналогично через pgx/v5 (коды через pgconn.PgError):

import (
	"context"
	"errors"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

func isRetryablePgx(err error) bool {
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) {
		return pgErr.Code == "40001" || pgErr.Code == "40P01"
	}
	return false
}

func withRetryPgx(ctx context.Context, pool *pgxpool.Pool, fn func(pgx.Tx) error) error {
	for attempt := 0; attempt < 5; attempt++ {
		err := pgx.BeginTxFunc(ctx, pool, pgx.TxOptions{
			IsoLevel: pgx.Serializable, // pgx.RepeatableRead и т.д.
		}, fn) // BeginTxFunc сам делает Commit/Rollback
		if err == nil {
			return nil
		}
		if !isRetryablePgx(err) {
			return err
		}
		// backoff опущен для краткости
	}
	return errors.New("serialization retry exhausted")
}

Подводные камни / gotchas#

• Retry на Commit(), а не только внутри транзакции. На Serializable SSI часто детектирует конфликт именно в момент коммита. Если ловить 40001 только на промежуточных запросах — пропустите половину случаев.
• Идемпотентность функции retry. Тело транзакции выполнится несколько раз. Нельзя делать побочные эффекты вне БД (отправка письма, HTTP-вызов) внутри ретраемого блока — только после успешного Commit.
• Read Committed берёт новый снимок на КАЖДЫЙ statement. Распространённая ошибка senior-кандидата — сказать «снимок на транзакцию». На RC это неверно; снимок на транзакцию только начиная с RR.
• EvalPlanQual на RC. При UPDATE ... WHERE с конкурентным изменением строки PG перечитывает строку после ожидания и заново применяет WHERE к новой версии — а не к версии из снимка. Это может привести к тому, что обновится строка, которую вы по снимку «не видели бы», или наоборот — пропустится. Тонкий источник логических багов в счётчиках/очередях.
• SELECT FOR UPDATE на RR может бросить 40001. В отличие от RC (где он ждёт и перечитывает), на RR блокировка строки, изменённой после снимка, немедленно даёт serialization failure.
• Serializable не бесплатен. Predicate locks занимают память; при их эскалации (max_pred_locks_per_transaction, max_pred_locks_per_relation) растёт число ложных 40001. Длинные транзакции на Serializable резко повышают конфликтность.
• READ UNCOMMITTED — ложное чувство. Кандидаты иногда ставят его «для скорости грязного чтения» — в PG это no-op, ведёт себя как RC, никакой грязи и никакого выигрыша.
• Уровень нельзя менять после первого запроса транзакции. SET TRANSACTION ISOLATION LEVEL сработает только до первой команды, читающей/пишущей данные, иначе ошибка.
• Снимок RR/Serializable фиксируется на первой команде, а не на BEGIN. BEGIN; ... SELECT now(); — снимок до первого реального запроса. Длинная пауза между BEGIN и первым SELECT не «замораживает» данные.
• Pgx vs lib/pq коды ошибок. Разные типы (*pgconn.PgError vs *pq.Error); errors.As обязателен, потому что драйверы оборачивают ошибки.
• Connection pool + уровень изоляции. Если выставлять уровень через SET SESSION CHARACTERISTICS, он «прилипнет» к соединению в пуле и протечёт на следующие запросы. Лучше задавать per-transaction через TxOptions.

Вопросы на собеседовании#

В: Чем уровни изоляции в PostgreSQL отличаются от стандарта SQL? О: Стандарт определяет 4 уровня через 3 запрещённые аномалии и минимальные гарантии. PG реализован на MVCC и строже: READ UNCOMMITTED = READ COMMITTED (грязного чтения нет физически); REPEATABLE READ дополнительно предотвращает phantom read (стандарт его допускает), будучи полноценным snapshot isolation; SERIALIZABLE реализован через SSI и даёт настоящую сериализуемость, ловя write skew.

В: Когда берётся снимок данных на Read Committed и на Repeatable Read? О: На Read Committed — в начале каждого отдельного statement (поэтому два SELECT в одной транзакции могут вернуть разное). На Repeatable Read — один раз, на первой команде транзакции, и держится до конца.

В: Что такое write skew и какой уровень от него защищает? О: Две транзакции читают пересекающийся набор данных, принимают решение и пишут в разные строки; по отдельности корректно, вместе нарушают инвариант. Snapshot isolation (PG RR) её не ловит, потому что нет конфликта записи. Защищает только SERIALIZABLE (SSI) или явная материализация конфликта через SELECT FOR UPDATE/предметные блокировки.

В: Как PostgreSQL реализует Serializable? О: Через SSI — Serializable Snapshot Isolation. Поверх snapshot isolation движок отслеживает rw-зависимости через ненастоящие predicate locks (SIReadLock), ищет «опасную структуру» из двух rw-antidependency, образующих несериализуемый цикл, и откатывает одну из транзакций с 40001. Не блокирует читателей, но потребляет память на predicate locks.

В: Что такое SQLSTATE 40001 и как с ним работать? О: serialization_failure — легальный сигнал, что транзакция не может быть сериализована и должна быть повторена. Обработка: retry-цикл с экспоненциальным backoff и джиттером, идемпотентное тело транзакции, обязательно ловить 40001 ещё и на Commit(). Рядом обычно обрабатывают 40P01 (deadlock).

В: Как предотвратить lost update? О: Не делать read-modify-write в приложении. Варианты: атомарный UPDATE ... SET x = x + n; оптимистичная блокировка через колонку version с WHERE version = $old и проверкой числа затронутых строк; пессимистичная SELECT ... FOR UPDATE; или уровень RR/Serializable, где конкурентное обновление даст 40001.

В: Почему READ UNCOMMITTED в PG бесполезен? О: MVCC никогда не показывает незакоммиченные версии, поэтому грязное чтение невозможно физически. PG принимает запрос, но трактует уровень как READ COMMITTED. Никакого выигрыша по скорости/видимости это не даёт.

В: В чём опасность SELECT FOR UPDATE на разных уровнях? О: На RC он ждёт конкурентную блокировку и перечитывает актуальную версию строки (EvalPlanQual). На RR/Serializable, если строка изменена после снимка, он немедленно бросает 40001. Кроме того, FOR UPDATE не защищает от фантомных INSERT — несуществующую строку заблокировать нельзя, поэтому от write skew с появлением новых строк он не спасает.

В: Какой уровень выбрать по умолчанию для сервиса и почему? О: Чаще всего Read Committed (дефолт) — компромисс производительности и достаточной корректности при условии атомарных UPDATE и явных блокировок там, где нужны инварианты. RR/Serializable выбирают точечно для транзакций с инвариантами по нескольким строкам (балансы, бронирования), всегда с retry на 40001. Serializable — когда инварианты сложно выразить блокировками и важна корректность важнее throughput.

На что копают на senior+#

• Анатомия снимка MVCC. xmin/xmax, pg_snapshot (xmin/xmax/xip_list), как visibility map и hint bits ускоряют проверку видимости, при чём тут txid_current() и wraparound.
• EvalPlanQual в деталях. Что именно происходит при конкурентном UPDATE на Read Committed, почему это может «пропустить» строки в наивной реализации очереди задач, и как SELECT ... FOR UPDATE SKIP LOCKED решает проблему work-queue.
• Алгоритм SSI и теория. Граф конфликтов, rw-antidependency, понятие «pivot»-транзакции, почему достаточно двух последовательных rw-рёбер; цена predicate lock escalation и тюнинг max_pred_locks_*.
• Read-only anomaly. Умение привести пример, где даже read-only транзакция видит несериализуемое состояние, и зачем нужен DEFERRABLE.
• Поведение в пуле соединений. Утечка уровня изоляции/SET через переиспользуемые соединения, почему предпочтительнее per-transaction TxOptions, как ведёт себя pgbouncer в transaction pooling режиме относительно SET/prepared statements.
• Цена и стратегия retry. Бэкофф, ограничение попыток, идемпотентность, метрики по частоте 40001, как высокий уровень конфликтности сигнализирует о горячих строках и плохой схеме доступа.
• Сравнение с другими СУБД. Чем PG snapshot isolation отличается от MySQL/InnoDB (gap locks, next-key locks на RR предотвращают phantom иначе — через локинг, а не SSI), и от Oracle (нет настоящего Serializable, только snapshot).
• Взаимодействие с логической репликацией/триггерами/constraint’ами. Когда уникальный constraint ловит то, что snapshot isolation пропустил бы, и почему уникальный индекс — простейший способ закрыть часть write skew/phantom.