PostgreSQL 16. Изоляция транзакций. Часть 2

Введение

Данная статья является продолжением первой части: "PostgreSQL 16. Организация данных. Часть 1".

Как и первая часть, эта является объединением книги и официальной документации с моими рисунками, объясняющими написанное в более наглядном (надеюсь, простом) варианте.

Информация взята из книги Егора Рогова PostgreSQL 16 изнутри, а также из документации PostgreSQL 16.2.

2.1. Согласованность

Важная особенность реляционных СУБД — обеспечение согласованности (consistency), то есть корректности данных.

На уровне базы данных можно создавать ограничения целостности (integrity constraints). Ссылка на документацию.

Ограничения дают вам возможность управлять данными в таблицах так, как вы захотите. Если пользователь попытается сохранить в столбце значение, нарушающее ограничения, возникнет ошибка. Самое банальное — это тип данных. Они сами по себе ограничивают значения, которые можно сохранить в таблице.

Однако для многих приложений такие ограничения слишком грубые. Например, столбец, содержащий цену продукта, должен, вероятно, принимать только положительные значения. Но такого стандартного типа данных нет.
Для этого существуют ограничения-проверки:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

Ещё может потребоваться, чтобы в таблице с информацией о товаре была только одна строка с определённым кодом товара. Для такого варианта можно задать уникальность определенного поля для всей таблицы UNIQUE. Для определенных полей всегда должно быть указано значение, тут поможет NOT NULL.

Возможно, вы также захотите ограничить данные столбца по отношению к другим столбцам или строкам. Например, если вы храните обычную цену и цену со скидкой, так вы можете гарантировать, что цена со скидкой будет всегда меньше обычной.

CREATE TABLE products (
    product_no integer UNIQUE,
    name text NOT NULL,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

Рекомендую подробнее почитать про constraints в статье, чтобы мы могли двигаться дальше.

⚠️ Внимание!
В PostgreSQL предполагается, что условия ограничений CHECK являются постоянными, то есть при одинаковых данных в строке они всегда выдают одинаковый результат.
Однако это предположение может нарушаться, как часто бывает, когда в выражении CHECK используется пользовательская функция, поведение которой впоследствии меняется. PostgreSQL не запрещает этого, и если строки в таблице перестанут удовлетворять ограничению CHECK, это останется незамеченным.

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

❗Согласованность строже, чем целостность! И что конкретно под ней понимается, СУБД не знает.

Далее будем рассматривать на самом популярном и понятном примере - перевод денег. Перевод средств с одного счета на другой состоит из двух операций:

1. Уменьшает средства на одном счете;

2. Увеличивает на другом.

Первая операция нарушает согласованность данных, вторая — восстанавливает. Если первая операция выполнится, а вторая по причине какого-то сбоя нет, то согласованность нарушится.

Рис. 2.1. Неудачный перевод денег на другой счет.

Это недопустимо! Ценой неимоверных усилий такие ситуации можно обрабатывать на уровне приложения, но, к счастью, это не требуется.

Задачу полностью решает СУБД, если знает, что две операции составляют неделимое целое, то есть транзакцию ссылка на документацию.

Рис. 2.2. Транзакция по переводу денег с одного счета на другой.

В таком случае либо две операции выполнятся, либо ни одна из них.

Рис. 2.3. Отмена транзакции при ошибке.

⚠️ Внимание!
Транзакции, абсолютно правильные сами по себе, при одновременном выполнении могут начать работать некорректно.

Это происходит из-за того, что перемешивается порядок выполнения операций разных транзакций. Если бы СУБД сначала выполняла все операции одной транзакции, а только потом все операции другой, такой проблемы не возникало бы, но без распараллеливания работы производительность была бы невообразимо низкой.

Рис. 2.4 Сравнение времени выполнения транзакций при параллельном и последовательном режимах.

Ситуации, когда корректные транзакции некорректно работают вместе, называются аномалиями одновременного выполнения.

Роль СУБД состоит в том, чтобы выполнять транзакции параллельно и при этом гарантировать, что результат такого одновременного выполнения будет совпадать с результатом одного из возможных последовательных выполнений. Иными словами —изолировать транзакции друг от друга, устранив любые возможные аномалии.

Транзакцией называется множество операций, которые переводят базу данных из одного корректного состояния в другое корректное состояние (согласованность) при условии, что транзакция выполнена полностью (атомарность) и без помех со стороны других транзакций (изоляция).

Это определение объединяет требования, стоящие за первыми тремя буквами акронима ACID: Atomicity, Consistency, Isolation. Подробнее про ACID.

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

2.2. Уровни изоляции и аномалии в стандарте SQL

В стандарте SQL от ANSI/ISO, сформулированном еще в 1992 году, описаны четыре уровня:

• Read uncommitted — чтение незафиксированных данных;

• Read committed — чтение зафиксированных данных;

• Repeatable read — повторяющееся чтение;

• Serializable — упорядочиваемость.

Наиболее строгий из них — serializable он не допускает никаких аномалий и гарантирует, что при параллельном запуске транзакций мы получим такой же результат, как если бы они запускались по очереди в некотором порядке.

Рассмотрим остальные уровни через аномалии, которые возможны при взаимодействии параллельных транзакций, но не допускаются на определённом уровне.

Грязное чтение и Read Uncommitted

Минимальный уровень изоляции. Гарантирует только физическую целостность при записи данных. Транзакция читает данные, записанные параллельной незавершённой транзакцией.

Рассмотрим пример (рис. 2.5). "Транзакция 1" создает учетную запись пользователя и добавляет ему карту. "Транзакция 2" запрашивает всех пользователей для email рассылки.
Две транзакции выполняются в двух разных сессиях. Таким образом, происходит перемешивание операций разных транзакций во времени.

Рис. 2.5. Изоляция read uncommitted

Так как уровень изоляции позволяет читать изменения, которые незавершенная транзакция создала, то "Транзакция 2" вытянет пользователей вместе с "new". Однако в момент создания карты произошла ошибка (допустим, неверное название карты), и "Транзакция 1" начинает откатывать все изменения. Аккаунт пользователя не был создан, но письмо на почту он уже получил.

Ещё один пример (рис. 2.6). Две операции перевода денег с одного счета на другой. "Транзакция 1" уменьшает счет пользователя на 100 (теперь он равен нулю). "Транзакция 2" также уменьшает счет пользователя, однако он уже равен нулю и не может быть отрицательным, что вызывает ошибку. Далее в "Транзакции 1" происходит ошибка (сессия обрывается или что-то ещё), и она откатывается, что отменяет изменение счета пользователя и у него снова 100. "Транзакция 2" откатывается уже независимо от первой.

Рис. 2.6. Пример чтения незафиксированных изменений.

Таким образом, "Транзакция 2" не выполнилась из-за влияния незафиксированных изменений от "Транзакции 1", чего бы не произошло на следующем уровне изоляции.

Неповторяющееся чтение и Read Committed

Ссылка на документацию

В транзакции, работающей на этом уровне, запрос SELECT видит только те данные, которые были зафиксированы до начала запроса; он никогда не увидит незафиксированных данных или изменений, внесённых параллельными транзакциями в процессе выполнения запроса.

Также заметьте, что два последовательных оператора SELECT могут видеть разные данные даже в рамках одной транзакции, если какие-то другие транзакции зафиксируют изменения после запуска первого SELECT, но до запуска второго.

На данном уровне транзакции не возникает "грязного чтения" рассмотренного на рис. 2.5.

Команды UPDATE, DELETE, SELECT FOR UPDATE и SELECT FOR SHARE ведут себя подобно SELECT при поиске целевых строк: они найдут только те целевые строки, которые были зафиксированы на момент начала команды. Однако к моменту, когда они будут найдены, эти целевые строки могут быть уже изменены (а также удалены или заблокированы) другой параллельной транзакцией. В этом случае запланированное изменение будет отложено до фиксирования или отката первой изменяющей данные транзакции (если она ещё выполняется).

"Транзакция 1" уменьшает баланс на 100 (рис. 2.7). Далее "Транзакция 2" тоже пытается уменьшить баланс на 100, однако данная строка уже изменилась в "Транзакции 1" и не была зафиксирована, поэтому для "Транзакции 2" откладывается операция UPDATE до завершения "Транзакции 1" (той, которая изменила строку).

Рис. 2.7. Ожидание завершения первой транзакции при уровне изоляции read committed.

Если "Транзакция 1" откатывается, её результат отбрасывается и "Транзакция 2" может продолжить изменение изначально полученной строки (рис. 2.7).

Рис. 2.8. Ожидание завершения первой транзакции при уровне изоляции read committed.

Если "Транзакция 1" зафиксировалась, но в результате обновила (удалила) эту строку, "Транзакция 2" попытается выполнить свою операцию с изменённой версией строки (либо проигнорирует её).

Если имеется условие поиска в команде (WHERE), то оно вычисляется повторно для выяснения, соответствует ли по-прежнему этому условию изменённая версия строки. Если да, вторая изменяющая транзакция продолжает свою работу с изменённой версией строки.

В более сложных ситуациях уровень Read Committed может приводить к нежелательным результатам.

Например, рассмотрим команду DELETE, работающую со строками, которые параллельно изменяются в другой транзакции (рис. 2.9).

Рис. 2.9. Невозможность удаления при параллельном изменении значений таблицы.

Команда DELETE не сделает ничего, даже несмотря на то, что строка с website.hits = 10 была в таблице и до, и после выполнения UPDATE. Это происходит потому, что строка со значением 9 до изменения пропускается, а когда команда UPDATE завершается и DELETE получает освободившуюся блокировку, строка с 10 теперь содержит 11, а это значение уже не соответствует условию.

Неповторяющееся чтение допускается стандартом на уровнях Read Uncommitted и Read Committed.

Фантомное чтение и Repeatable Read

Ссылка на документацию.

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

Этот уровень отличается от Read Committed тем, что запрос в транзакции данного уровня видит снимок данных на момент начала первого оператора в транзакции (не считая команд управления транзакциями), а не начала текущего оператора. Таким образом, последовательные команды SELECT в одной транзакции видят одни и те же данные; они не видят изменений, внесённых и зафиксированных другими транзакциями после начала их текущей транзакции.

Команды UPDATE, DELETE, MERGE, SELECT FOR UPDATE и SELECT FOR SHARE ведут себя подобно SELECT при поиске целевых строк: они найдут только те целевые строки, которые были зафиксированы на момент начала транзакции.

Однако к моменту, когда они будут найдены, эти целевые строки могут быть уже изменены (а также удалены или заблокированы) другой параллельной транзакцией.

Рассмотрим пример на рисунке 2.10.
"Транзакция 1" начинает обновление строк (hits += 1), далее начинается "Транзакция 2" и в режиме Repeatable Read в момент выполнения операции DELETE будет ожидать фиксирования или отката "Транзакции 1" (если она ещё выполняется). Когда "Транзакция 1" откатывается, её результат отбрасывается и "Транзакция 2" может продолжить изменение изначально полученных строк (рис 2.10).

Рис. 2.10. При изоляции Reapeatable Read транзакция продолжит выполнение после блокировки, если блокирующая транзакция откатилась.

Если же "Транзакция 1" зафиксировалась и в результате изменила или удалила эту строку, а не просто заблокировала её, произойдёт откат "Транзакции 2" (рис. 2.11) с сообщением:

ОШИБКА: не удалось сериализовать доступ из-за параллельного изменения.

Так как транзакция уровня Repeatable Read не может изменять или блокировать строки, изменённые другими транзакциями с момента её начала (рис. 2.11).

Рис. 2.11. Reapeatable Read транзакции не могут выполниться одновременно, если влияют на одни и те же строки.

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

Заметьте, что потребность в повторении транзакции может возникнуть, только если эта транзакция изменяет данные; в транзакциях, которые только читают данные, конфликтов сериализации не бывает.

В некоторых СУБД могут существовать даже два отдельных уровня Repeatable Read и Snapshot Isolation с различным поведением. Допускаемые особые условия, представляющие отличия двух этих подходов, не были формализованы разработчиками теории БД до развития стандарта SQL.

Рассмотрим пример с перекрестным добавлением данных (рис. 2.12).

Рис. 2.12. Создание новых строк в одной таблице одновременно в Reapetable Read не вызывает ошибки, даже если бизнес логика нарушена.

1. "Транзакция 1" начинается.

2. "Транзакция 2" начинается.

3. "Транзакция 1" вычисляет сумму для класса 1 (получает 30).

4. "Транзакция 2" вычисляет сумму для класса 2 (получает 300).

5. "Транзакция 1" создает новую запись для класса 2 с вычисленной суммой равной 30.

6. "Транзакция 2" создает новую запись для класса 1 с вычисленной суммой равной 300.

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

Если операции этих транзакций будут выполняться немного в другом порядке (но не последовательно), можно будет натолкнуться на "фантомное чтение" (рис. 2.13).

Аномалия фантомного чтения (phantom read) возникает, когда одна транзакция читает набор строк в которых присутствуют новые строки, которые другая транзакция добавила (и зафиксировала изменения) уже после начала первой транзакции, но перед чтением строк.

Рис. 2.13. "Фантомное чтение" при уровне изоляции Repeatable Read.

1. "Транзакция 3" начинается.

2. "Транзакция 3" вычисляет сумму для класса 2.

3. "Транзакция 4" начинается, вычисляет сумму для класса 1 (получает 30) и создает новую запись для класса 2 с вычисленной суммой равной 30.

4. "Транзакция 4" фиксирует изменения.

5. "Транзакция 3" вычисляет сумму для класса 2 вместе с новой строкой, которая добавила "Транзакция 4" и получаем 330.

6. "Транзакция 3" создает новую запись для класса 1 с вычисленной суммой равной 330.

Отсутствие аномалий и Serializable

Ссылка на документацию.

Стандарт определяет и уровень, на котором не допускаются никакие аномалии.

Это означает, что на таком уровне разработчику приложения не надо думать об изоляции. Если транзакции выполняются корректно последовательно, то данные останутся согласованными и при параллельной работе этих транзакций.

Рис. 2.14. Уровень изоляций Serializable.

1. "Транзакция 1" начинается.

2. "Транзакция 2" начинается.

3. "Транзакция 1" вычисляет сумму для класса 1 (получает 30).

4. "Транзакция 2" вычисляет сумму для класса 2 (получает 300).

5. "Транзакция 1" создает новую запись для класса 2 с вычисленной суммой равной 30.

6. "Транзакция 2" создает новую запись для класса 1 с вычисленной суммой равной 300.

7. "Транзакция 1" фиксирует изменения.

8. "Транзакция 2" при фиксации изменений получает ошибку:

   ОШИБКА: не удалось сериализовать доступ из-за зависимостей чтения/записи между транзакциями

Это объясняется тем, что при выполнении "Транзакции 1" перед "Транзакцией 2" сумма получилась бы 330, а не 300.

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

Ниже представлены уровни изоляций транзакций и допустимые аномалии.

Рис. 2.15. Таблица уровней изоляций и допустимых аномалий для стандарта SQL.

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

Изоляция на основе снимков (Snapshot Isolation, SI) позволяет обходиться минимумом блокировок. Фактически блокируются операции, связанные с изменением одной и той же строки, а также операции, затрагивающие уникальные индексы и внешние ключи. Читающие транзакции не блокируют другие транзакции, но различные виды блокировок могут ограничивать выполнение одновременно других операций.

В PostgreSQL реализован многоверсионный вариант протокола SI. Многоверсионность подразумевает, что в СУБД в один момент времени могут сосуществовать несколько версий одной и той же строки. Это позволяет включать в снимок подходящую версию, а не обрывать транзакции, пытающиеся прочитать устаревшие данные.

Уровни изоляции в PostgreSQL строже чем в стандарте SQL.

Рис. 2.16. Таблица уровней изоляций и допустимых аномалий для PostgreSQL.

В PostgreSQL вы можете запросить любой из четырёх уровней изоляции транзакций, однако внутри реализованы только три различных уровня, то есть режим Read Uncommitted в PostgreSQL действует как Read Committed.

Причина этого в том, что только так можно сопоставить стандартные уровни изоляции с реализованной в PostgreSQL архитектурой многоверсионного управления конкурентным доступом.

2.4. Какой уровень изоляции использовать?

Уровень изоляции Read Committed используется в PostgreSQL по умолчанию, и, по всей видимости, именно этот уровень применяется в абсолютном большинстве приложений. Он удобен тем, что на нем обрыв транзакции возможен только в случае сбоя, но для предотвращения несогласованности обрыв не применяется. Иными словами, ошибка сериализации возникнуть не может, и о повторении транзакций заботиться не надо.

Различные СУБД могут не иметь описанных выше уровней изоляций, а также отличаться уровнем изоляции по умолчанию (Рис. 2.17).

Рис. 2.17. Различные уровни изоляций СУБД. Ярко-зеленым отмечены уровни изоляции по умолчанию.

Выбор уровня изоляции транзакций зависит от требований приложения к целостности данных и производительности. Ниже приведены рекомендации по использованию различных уровней изоляции, их плюсы и минусы, а также примеры ситуаций, в которых каждый уровень может быть наиболее подходящим.

Read Uncommitted

Описание: Позволяет читать данные, которые еще не зафиксированы (грязное чтение). Это самый низкий уровень изоляции.

✅ Преимущества:

• Высокая производительность, минимальные блокировки.

⛔️ Недостатки:

• Возможны грязные чтения, когда транзакция читает данные, которые могут быть откатаны.

Использование:

• Редко используется в практике. Подходит для чтения данных, которые не критичны по целостности, например, для временных отчетов, где допустимы некоторые неточности.

Read Committed

Описание: Позволяет читать только те данные, которые уже зафиксированы другими транзакциями.

✅ Преимущества:

• Избегает грязных чтений.

• Хороший баланс между производительностью и целостностью данных.

⛔️ Недостатки:

• Возможны неповторяющиеся чтения, когда повторный запрос в рамках одной транзакции возвращает разные результаты.

Использование:

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

Repeatable Read

Описание: Гарантирует, что если транзакция читает данные, эти данные не изменятся и не будут удалены до завершения транзакции.

✅ Преимущества:

• Избегает грязных и неповторяющихся чтений.

⛔️ Недостатки:

• Возможны фантомные чтения, когда другие транзакции добавляют новые строки, удовлетворяющие условиям запроса.

Использование:

• Подходит для приложений, где важна консистентность данных при повторных чтениях в рамках одной транзакции, например, для финансовых операций.

Serializable

Описание: Самый высокий уровень изоляции, который делает так, что транзакции выполняются последовательно, как если бы они были выполнены одна за другой.

✅ Преимущества:

• Избегает грязных, неповторяющихся и фантомных чтений.

• Гарантирует максимальную консистентность данных.

⛔️ Недостатки:

• Самый медленный уровень изоляции.

• Высокий риск блокировок и взаимоблокировок.

Использование:

• Подходит для критически важных приложений, где требуется абсолютная консистентность данных, таких как банковские системы или системы управления запасами на складе.

Рекомендации по выбору уровня изоляции

1. 🐤 Read Uncommitted: Используйте только для операций, где не важна точность данных.

2. 🐱 Read Committed: Хороший выбор для большинства приложений, обеспечивает баланс между производительностью и целостностью.

3. 🦁 Repeatable Read: Подходит для приложений, где необходима консистентность данных при повторных чтениях.

4. 🤖 Serializable: Используйте для критически важных приложений, требующих максимальной целостности данных.

Выбор уровня изоляции всегда зависит от конкретных требований вашего приложения к консистентности данных и производительности.

Заключение

В данной статье была рассмотрена «Глава 2» из книги PostgreSQL 16 изнутри.

Если эта публикация окажется полезной, то в дальнейшем будет продемонстрировано внутреннее устройство страниц таблицы из главы 3 «Страницы и версии строк» этой же книги.