Перейти к содержимому

2. Изоляция

Суть

  • Транзакция переводит БД из одного согласованного состояния в другое при условии, что выполнена целиком (Atomicity) и без помех от других (Isolation). СУБД не знает правил согласованности приложения — она гарантирует атомарность и изоляцию.
  • Стандарт SQL определяет 4 уровня через запрещенные аномалии: dirty read, non-repeatable read, phantom read; lost update запрещено везде; Serializable должен исключать любые аномалии, а не только перечисленные.
  • PostgreSQL использует Snapshot Isolation (многоверсионный вариант): грязного чтения нет вообще (Read Uncommitted = Read Committed), Repeatable Read не допускает фантомов, но в Read Committed возможна потеря обновлений, если значение вычисляется в приложении.
  • SI оставляет ровно две аномалии: write skew (несогласованная запись) и read-only transaction anomaly. Их ловит Serializable (SSI) — ценой ошибок сериализации, в том числе ложноположительных.
  • Read Committed: снимок на каждый оператор; ждущий UPDATE после снятия блокировки перечитывает только одну строку → read skew внутри оператора. RR/Serializable: снимок на транзакцию; конфликт → could not serialize access, транзакцию нужно повторить.
  • Практика: в RC — делать проверку и изменение одним оператором (CHECK, amount = amount - x, CTE, INSERT ON CONFLICT) или явно блокировать (SELECT FOR UPDATE); RR удобен для отчетов из нескольких запросов; Serializable — только если все транзакции на нем (иначе он молча работает как RR) и не на репликах.
lost update dirty read non-repeatable phantom другие
Стандарт Read Uncommitted — да да да да
Стандарт Read Committed — — да да да
Стандарт Repeatable Read — — — да да
Стандарт Serializable — — — — —
PostgreSQL Read Committed да — да да да
PostgreSQL Repeatable Read — — — — да
PostgreSQL Serializable — — — — —

Почему стандарт перечисляет именно эти аномалии? Он писался в расчете на блокировки (2PL): уровни отличаются тем, какие строки и как долго блокируются. Фантомы на блокировках не убрать — нельзя заблокировать строку, которой еще нет; нужны предикатные блокировки.

симулятор Аномалии × уровни изоляции
Выберите аномалию и уровень, затем проходите сценарий по шагам. Сценарии — из главы 2 (таблица accounts: alice 1000; bob 100 + 900).
Неповторяющееся чтение (non-repeatable read) — Повторное чтение той же строки дает другой результат, потому что между чтениями другая транзакция ее изменила и зафиксировала.
Транзакция T1
Транзакция T2
Сводка для PostgreSQL
аномалияRead CommittedRepeatable ReadSerializable
Грязное чтениенетнетнет
Неповторяющееся чтениеданетнет
Фантомное чтениеданетнет
Потерянное обновлениеданетнет
Несогласованное чтениеданетнет
Несогласованная записьдаданет
Аномалия только читающей транзакциидаданет
  • Антипаттерн «проверка + действие» двумя операторами. Между SELECT и UPDATE может «вклиниться» что угодно:
    IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN
    UPDATE accounts SET amount = amount - 1000 WHERE id = 1; -- проверка уже устарела
    END IF;
    Лечение: ALTER TABLE accounts ADD CHECK (amount >= 0) и просто выполнять действие; или один оператор; или SELECT … FOR UPDATE.
  • Долгий оператор видит данные на момент своего начала (проверяется через pg_sleep). Но VOLATILE-функция внутри запроса выполняет свои запросы со своими снимками → несогласованные данные внутри одного оператора.
  • UPDATE после ожидания блокировки перечитывает только заблокированную строку (EvalPlanQual), а остальные строки выборки уже отобраны по старому снимку. Пример из книги: начисление процентов клиентам с балансом ≥ 1000 начисляет Бобу 1 % от 900 ₽, хотя при 900 ₽ он вообще не должен был попасть в выборку.
  • Надстройка над снимками: отслеживает зависимости «чтение → запись» между транзакциями (SIREAD-блокировки) и обрывает транзакцию-«пивот» опасной структуры: ERROR: could not serialize access due to read/write dependencies among transactions.
  • Возможны ложные срабатывания (зависят от индексов, памяти, гранулярности блокировок) — приложение обязано уметь повторять транзакции.
  • BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE — читающая транзакция ждет безопасного момента и после этого не может ни вызвать аномалию, ни пострадать от нее. Единственный случай, когда SELECT блокируется обновлениями.
  • Объявляйте читающие транзакции READ ONLY — это снижает накладные расходы.

Конкурентные сценарии требуют двух сеансов psql. Минимальный набор для повторения примеров книги:

CREATE TABLE accounts(
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
client text,
amount numeric
);
INSERT INTO accounts VALUES (1,'alice',1000.00), (2,'bob',100.00), (3,'bob',900.00);
SHOW default_transaction_isolation; -- read committed
BEGIN ISOLATION LEVEL REPEATABLE READ; -- уровень для одной транзакции
SHOW transaction_isolation;
Какие уровни изоляции есть в PostgreSQL и чем они отличаются от стандарта?базовый

Можно указать все четыре, но фактически их три: Read Uncommitted работает как Read Committed (грязного чтения нет из-за снимков). Repeatable Read в PostgreSQL строже стандарта — не допускает фантомов. Read Committed, наоборот, допускает потерянное обновление (которое стандарт запрещает везде), если новое значение вычисляет приложение. Serializable реализован через SSI и предотвращает все аномалии.

Чем отличается снимок в Read Committed и Repeatable Read?базовый

В Read Committed снимок строится в начале каждого оператора, поэтому разные операторы одной транзакции видят разные данные. В Repeatable Read и Serializable снимок строится один раз — на первом операторе транзакции (не на BEGIN!) — и используется до конца.

Что произойдет, если две транзакции одновременно обновляют одну строку в RC и в RR?средний

Вторая ждет завершения первой (блокировка строки — это xmax старой версии плюс блокировка номера транзакции). Если первая откатилась — вторая просто продолжает. Если зафиксировалась: в RC вторая перечитывает самую новую версию этой строки и применяет изменение к ней (поэтому amount = amount + 100 не теряет обновлений); в RR/Serializable — ERROR: could not serialize access due to concurrent update, транзакцию нужно повторить.

Что такое write skew? Приведите пример. На каком уровне он предотвращается?средний

Две транзакции читают пересекающиеся данные, принимают решение и меняют разные строки, так что конфликта записи нет, но вместе они нарушают инвариант. Пример: правило «сумма по счетам Боба ≥ 0»; обе видят 900 и списывают по 600 с разных счетов → −300. Возможна в RC и RR; предотвращается только на Serializable (или явными блокировками SELECT … FOR UPDATE проверяемых строк в RR/RC).

Почему нельзя смешивать Serializable с другими уровнями?глубокий

SSI отслеживает зависимости только между сериализуемыми транзакциями. Если часть транзакций работает на RC/RR, их чтения и записи не учитываются, и Serializable молча вырождается в Repeatable Read. Поэтому при использовании Serializable имеет смысл задать default_transaction_isolation = serializable. Еще ограничение: Serializable нельзя использовать на репликах.

Как избежать гонки «проверить и изменить» на уровне Read Committed?средний
  1. Сделать проверку декларативной — ограничение целостности (CHECK, UNIQUE, EXCLUDE). 2) Уложить логику в один SQL-оператор (CTE с DML, INSERT … ON CONFLICT, UPDATE … WHERE amount >= x). 3) Явно заблокировать строки SELECT … FOR UPDATE (или таблицу LOCK TABLE) — работает всегда, но сериализует часть работы.
Что такое READ ONLY DEFERRABLE и зачем?глубокий

Режим для сериализуемых читающих транзакций: транзакция ждет момента, когда получение снимка гарантированно безопасно (нет конкурентных транзакций, способных создать аномалию с ее участием), и после этого работает без накладных расходов SSI и без риска ошибки сериализации. Удобно для длинных отчетов и pg_dump --serializable-deferrable.