Уровни изоляции в PostgreSQL

Стандарт SQL описывает четыре уровня изоляции через аномалии, которые они запрещают. PostgreSQL реализует их строже стандарта: грязного чтения нет нигде, а Read Uncommitted ведёт себя как Read Committed. Реально различимы три уровня, и отличает их одно - когда берётся снимок (см. snapshot).

Три уровня и их снимки

Read Committed - уровень по умолчанию. Каждый оператор берёт свежий снимок, поэтому соседние SELECT в одной транзакции могут увидеть чужие свежие коммиты. Грязного (незафиксированного) он не покажет никогда.

Repeatable Read фиксирует снимок один раз, в начале первого запроса, и держит до конца транзакции. Сколько раз ни перечитывай - картина та же. В PostgreSQL этот уровень заодно убирает фантомные строки, чего стандарт не требует.

Serializable добавляет к снимку Repeatable Read проверку зависимостей между транзакциями. Подробности - в serializable-ssi.

Какие аномалии где возможны

Грязного чтения нет нигде - это особенность реализации PostgreSQL. Неповторяющееся и фантомное чтение отсекаются уже на Repeatable Read, потому что снимок заморожен. А вот аномалия сериализации (например, две транзакции, каждая читает то, что меняет другая) проходит даже на Repeatable Read - и ловится только на Serializable.

Цена за строгость: ошибка сериализации

За изоляцию выше Read Committed приходится платить. На Repeatable Read и Serializable конфликтующая запись не блокируется и не ждёт - транзакция откатывается с ошибкой 40001:

ERROR:  could not serialize access due to concurrent update

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

Как переключать

SHOW transaction_isolation;                          -- текущий уровень

BEGIN ISOLATION LEVEL REPEATABLE READ;               -- на одну транзакцию

SET default_transaction_isolation = 'repeatable read';  -- на сессию