serializable_1.sql

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

-- 1. Отсутствие аномалий.

-- Шаг 1.1 =>
-- Можно убедиться, что сценарий с аномалией несогласованной записи приведет к ошибке сериализации:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT sum(amount) FROM accounts WHERE client = 'bob';

-- Шаг 1.3 =>
UPDATE accounts SET amount = amount - 600.00 WHERE id = 2;

-- Шаг 1.5
COMMIT;
-- К такой же ошибке приведет и сценарий аномалии только читающей транзакции.

------------------------------------------------------------------------------------------------------------------------

-- 2. Откладывание читающей транзакции.
-- Чтобы только читающая транзакция не приводила к аномалии и не могла пострадать от нее,
-- PostgreSQL предлагает интересный механизм: такая транзакция может быть отложена до тех пор,
-- пока ее выполнение не станет безопасным. Это единственный случай, когда оператор SELECT
-- может быть заблокирован обновлениями строк.
-- Посмотрим на примере сценария аномалии только читающей транзакции:

-- Приводим данные в порядок
UPDATE accounts SET amount = 900.00 WHERE id = 2;
UPDATE accounts SET amount = 100.00 WHERE id = 3;
SELECT * FROM accounts WHERE client = 'bob' ORDER BY id;

-- Шаг 2.1
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET amount = amount + (
SELECT sum(amount) FROM accounts WHERE client = 'bob' ) * 0.01
WHERE id = 2;

-- И только после того, как первая транзакция будет зафиксирована, третья продолжит выполнение:
COMMIT;
-- => 3

-- Таким образом, приложение, использующее уровень изоляции Serializable, должно повторять транзакции,
-- завершившиеся ошибкой сериализации. (Точно так же следует поступать и на уровне Repeatable Read,
-- если не ограничиваться только читающими транзакциями.)
-- Уровень Serializable дает простоту программирования, но цена за нее — накладные расходы
-- на обнаружение возможных аномалий и обрыв некоторой доли транзакций.
-- Снизить накладные расходы можно, явно обозначая только читающие транзакции как READ ONLY.
-- Но главный вопрос, конечно, в том, насколько велика доля оборванных транзакций, ведь их придется выполнять повторно.
-- Если бы обрывались только те транзакции, которые действительно несовместимо пересекаются по данным,
-- все было бы неплохо.
-- Но такая реализация неизбежно оказалась бы слишком ресурсоёмкой,
-- поскольку пришлось бы отслеживать операции с каждой строкой.

-- В действительности реализация такова, что допускает ложноположительные срабатывания:
-- будут обрываться и некоторые совершенно нормальные транзакции, которым «просто не повезло».
-- Везение зависит от многих причин, например от наличия подходящих индексов или доступного объема оперативной памяти,
-- и поведение сложно предсказать заранее.

-- Если используется уровень изоляции Serializable, он должен применяться для всех транзакций приложения.
-- При смешении транзакций разного уровня изоляции уровень Serializable будет (без всяких предупреждений)
-- вести себя как Repeatable Read. Поэтому при использовании Serializable имеет смысл
-- изменить значение параметра default_transaction_isolation, хотя, конечно, это не помешает
-- указать неправильный уровень явно.

-- Есть и другие ограничения реализации, например запросы на уровне Serializable не будут работать на репликах.
-- И хотя работа над улучшением функциональности не прекращается, имеющиеся ограничения и накладные расходы
-- снижают привлекательность такого уровня изоляции.