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

Урок про MVCC показал, что читатели и писатели в PostgreSQL друг друга не ждут. Остался вопрос, который мы отложили там: что происходит, когда две транзакции меняют одно и то же. Теория уровней изоляции разобрана в уроке про уровни изоляции, а вопросы с собеседований в уроке про транзакции. Здесь практика: как ведёт себя PostgreSQL, какие ошибки он выдаёт и что с ними делать в приложении.

Окно и фотография

Представь, что ты считаешь машины на парковке. Можно перед каждым подсчётом выглядывать в окно: каждый раз видишь свежую картину, но два подсчёта подряд могут не сойтись. А можно один раз сфотографировать парковку и считать по снимку: все подсчёты сойдутся, но новых машин ты не увидишь, пока не сделаешь новое фото.

Уровни изоляции в PostgreSQL отличаются ровно этим: когда транзакция делает снимок.

Уровень	Когда делается снимок	Что даёт
read committed	перед каждой командой	свежие данные, но два SELECT подряд могут разойтись
repeatable read	один раз, перед первой командой транзакции	вся транзакция видит одну картину
serializable	как в repeatable read, плюс слежка за зависимостями	результат как при выполнении транзакций по очереди

По умолчанию работает read committed:

SHOW transaction_isolation;

 transaction_isolation
-----------------------
 read committed
(1 row)

Уровень выбирают при открытии транзакции:

BEGIN ISOLATION LEVEL REPEATABLE READ;

Поменять его после первого запроса нельзя:

ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query

Три особенности PostgreSQL, которыми он отличается от таблицы из учебника:

• Грязного чтения нет вообще. Уровень READ UNCOMMITTED принимается, но работает как read committed: чужие незафиксированные версии MVCC не показывает никогда.
• Repeatable read строже стандарта. Снимок один на всю транзакцию, поэтому фантомы, новые строки от чужих транзакций, тоже не видны.
• Строгость оплачивается ошибками, а не ожиданием. На repeatable read и serializable PostgreSQL не блокирует конфликт заранее, а отменяет одну из транзакций. Приложение должно уметь её повторить.

Read committed: две команды, две картины

Сессия A (отчёт)                       Сессия B (менеджер)
------------------------------------  ------------------------------------
BEGIN;
SELECT sum(price) FROM books;
-- 6690.00
                                      UPDATE books SET price = price + 100
                                        WHERE id = 8;
                                      -- автофиксация
SELECT sum(price) FROM books;
-- 6790.00: новый снимок, изменение B
-- уже видно
COMMIT;

Для большинства веб-запросов это нормально: каждая команда видит свежие зафиксированные данные. Но отчёт, который считает итоги несколькими запросами, на read committed может разойтись сам с собой. Ему нужен repeatable read.

Repeatable read: конфликт обновлений

Если A работает на repeatable read, второй SELECT вернёт те же 6690.00. Но что будет, если A сама захочет поменять строку, которую B уже поменяла после снимка A?

Сессия A (repeatable read)             Сессия B
------------------------------------  ------------------------------------
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT price FROM books WHERE id = 8;
-- 2490.00
                                      BEGIN;
                                      UPDATE books SET price = 2590
                                        WHERE id = 8;
UPDATE books SET price = price * 0.9
  WHERE id = 8;
-- ждёт: строку держит B
                                      COMMIT;
ERROR:  could not serialize access due to concurrent update

A посчитала скидку от цены, которую видела в снимке, а цена уже другая. Молча перезаписать её значило бы потерять изменение B. PostgreSQL отменяет A. На read committed тот же UPDATE дождался бы COMMIT у B, перечитал строку и применил скидку к новой цене 2590.

Serializable: аномалия, которую не видно по строкам

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

Сессия A (serializable)                Сессия B (serializable)
------------------------------------  ------------------------------------
BEGIN ISOLATION LEVEL SERIALIZABLE;   BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(qty) FROM stock            SELECT sum(qty) FROM stock
  WHERE book_id = 8;                    WHERE book_id = 8;
-- 2, можно продать один              -- 2, можно продать один
UPDATE stock SET qty = qty - 1        UPDATE stock SET qty = qty - 1
  WHERE book_id = 8 AND shelf = 1;      WHERE book_id = 8 AND shelf = 2;
COMMIT;
                                      COMMIT;
                                      ERROR:  could not serialize access due to read/write dependencies among transactions
                                      HINT:  The transaction might succeed if retried.

Транзакции меняли разные строки, поэтому repeatable read пропустил бы обе, и на складе стало бы ноль экземпляров. Такая аномалия называется перекосом записи. Serializable следит, кто что читал, видит, что результат невозможен ни при каком порядке выполнения по очереди, и отменяет одну транзакцию. В сообщении есть ещё строка DETAIL с технической причиной, её текст зависит от ситуации.

Подсказка в HINT главная: ошибку сериализации лечат повтором. У таких ошибок код 40001, и приложение, которое включает repeatable read или serializable, ловит его и запускает транзакцию заново с начала.