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) и не на репликах.
Аномалии и уровни: стандарт против PostgreSQL
Заголовок раздела «Аномалии и уровни: стандарт против PostgreSQL»| lost update | dirty read | non-repeatable | phantom | другие | |
|---|---|---|---|---|---|
| Стандарт Read Uncommitted | — | да | да | да | да |
| Стандарт Read Committed | — | — | да | да | да |
| Стандарт Repeatable Read | — | — | — | да | да |
| Стандарт Serializable | — | — | — | — | — |
| PostgreSQL Read Committed | да | — | да | да | да |
| PostgreSQL Repeatable Read | — | — | — | — | да |
| PostgreSQL Serializable | — | — | — | — | — |
Почему стандарт перечисляет именно эти аномалии? Он писался в расчете на блокировки (2PL): уровни отличаются тем, какие строки и как долго блокируются. Фантомы на блокировках не убрать — нельзя заблокировать строку, которой еще нет; нужны предикатные блокировки.
Попробуйте сами
Заголовок раздела «Попробуйте сами»Сводка для PostgreSQL
| аномалия | Read Committed | Repeatable Read | Serializable |
|---|---|---|---|
| Грязное чтение | нет | нет | нет |
| Неповторяющееся чтение | да | нет | нет |
| Фантомное чтение | да | нет | нет |
| Потерянное обновление | да | нет | нет |
| Несогласованное чтение | да | нет | нет |
| Несогласованная запись | да | да | нет |
| Аномалия только читающей транзакции | да | да | нет |
Подводные камни Read Committed
Заголовок раздела «Подводные камни Read Committed»- Антипаттерн «проверка + действие» двумя операторами. Между
SELECTиUPDATEможет «вклиниться» что угодно:Лечение:IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THENUPDATE 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 ₽ он вообще не должен был попасть в выборку.
Serializable в PostgreSQL (SSI)
Заголовок раздела «Serializable в PostgreSQL (SSI)»- Надстройка над снимками: отслеживает зависимости «чтение → запись» между транзакциями (SIREAD-блокировки) и обрывает транзакцию-«пивот» опасной структуры:
ERROR: could not serialize access due to read/write dependencies among transactions. - Возможны ложные срабатывания (зависят от индексов, памяти, гранулярности блокировок) — приложение обязано уметь повторять транзакции.
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE— читающая транзакция ждет безопасного момента и после этого не может ни вызвать аномалию, ни пострадать от нее. Единственный случай, когдаSELECTблокируется обновлениями.- Объявляйте читающие транзакции
READ ONLY— это снижает накладные расходы.
Проверить на живом PostgreSQL
Заголовок раздела «Проверить на живом PostgreSQL»Конкурентные сценарии требуют двух сеансов 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 committedBEGIN 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?средний
- Сделать проверку декларативной — ограничение целостности (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.