# Изоляция Индекс LLMS: [llms.txt](/ru/llms.txt) --- ## 2.1. Согласованность {#internal-ru-section-2-1} Важная особенность реляционных СУБД — обеспечение *согласованности* (consistency), то есть *корректности* данных. Известно, что на уровне базы данных можно создавать *ограничения целостности* (integrity constraints), такие как `NOT NULL` или `UNIQUE`. СУБД следит за тем, чтобы данные никогда не нарушали эти ограничения, то есть оставались целостными. Если бы все ограничения были сформулированы на уровне базы данных, согласованность была бы гарантирована. Но некоторые условия слишком сложны для этого, например охватывают сразу несколько таблиц. И даже если ограничение в принципе можно было бы определить в базе данных, но из каких-то соображений оно не определено, это не означает, что его можно нарушать. Итак, согласованность строже, чем целостность, но что конкретно под ней понимается, СУБД не знает. Если приложение нарушит согласованность, не нарушая целостности, у СУБД не будет способа узнать об этом. Получается, что гарантом согласованности выступает приложение, и остается верить, что оно написано корректно и никогда не ошибается. Но если приложение выполняет только корректные последовательности операторов, в чем тогда роль СУБД? Во-первых, корректная последовательность операторов может временно нарушать согласованность данных, и это, как ни странно, нормально. Заезженный, но понятный пример состоит в переводе средств с одного счета на другой. Правило согласованности может звучать так: *перевод никогда не* *меняет общей суммы денег на счетах*. Такое правило довольно трудно (хотя и возможно) записать на SQL в виде ограничения целостности, так что пусть оно существует на уровне приложения и остается невидимым для СУБД. Перевод состоит из двух операций: первая уменьшает средства на одном счете, вторая — увеличивает на другом. Первая операция нарушает согласованность данных, вторая — восстанавливает. Если первая операция выполнится, а вторая — по причине какого-то сбоя — нет, то согласованность нарушится. А это недопустимо. Ценой неимоверных усилий такие ситуации можно обрабатывать на уровне приложения, но, к счастью, это не требуется. Задачу полностью решает СУБД, если знает, что две операции составляют неделимое целое, то есть *транзакцию*. Но есть и второй, более тонкий момент. Транзакции, абсолютно правильные сами по себе, при одновременном выполнении могут начать работать некорректно. Это происходит из-за того, что перемешивается порядок выполнения операций разных транзакций. Если бы СУБД сначала выполняла все операции одной транзакции, а только потом — все операции другой, такой проблемы не возникало бы, но без распараллеливания работы производительность была бы невообразимо низкой. > [!NOTE] > Действительно одновременно транзакции работают в системах, где это позволяет аппаратура: многоядерный процессор, дисковый массив. Но все те же рассуждения справедливы и для сервера, который выполняет команды последовательно в режиме разделения времени. Иногда для обобщения используют термин *конкурентное вы-* *полнение*. Ситуации, когда корректные транзакции некорректно работают вместе, называются *аномалиями* одновременного выполнения. Простой пример: если приложение хочет получить из базы согласованные данные, то оно как минимум не должно видеть изменения других незафиксированных транзакций. Иначе (если какая-либо транзакция будет отменена) можно увидеть состояние, в котором база данных никогда не находилась. Такая аномалия называется *грязным чтением*. Есть множество других, более сложных аномалий. Роль СУБД состоит в том, чтобы выполнять транзакции параллельно и при этом гарантировать, что результат такого одновременного выполнения будет совпадать с результатом одного из возможных последовательных выполнений. Иными словами — *изолировать* транзакции друг от друга, устранив любые возможные аномалии. Таким образом, транзакцией называется множество операций, которые переводят базу данных из одного корректного состояния в другое корректное состояние (*согласованность*) при условии, что транзакция выполнена полностью (*атомарность*) и без помех со стороны других транзакций (*изоляция*). Это определение объединяет требования, стоящие за первыми тремя буквами акронима ACID: Atomicity, Consistency, Isolation. Они настолько тесно связаны друг с другом, что рассматривать их по отдельности просто нет смысла. На самом деле сложно отделить и требование долговечности (Durability), ведь при крахе системы в ней остаются изменения незафиксированных транзакций, а с ними приходится что-то делать, чтобы восстановить согласованность данных. (*с.* [211](/ru/10-write-ahead-log/#internal-ru-page-212)) Получается, что СУБД помогает приложению поддерживать согласованность, учитывая состав транзакции, но не имея при этом понятия о подразумеваемых правилах согласованности. К сожалению, реализация полной изоляции — технически сложная задача, сопряженная с уменьшением производительности системы. Поэтому на практике почти всегда применяется ослабленная изоляция, которая предотвращает некоторые, но не все аномалии. А это означает, что часть работы по обеспечению согласованности данных ложится на приложение. Именно поэтому очень важно понимать, какой уровень изоляции используется в системе, какие гарантии он дает, а какие — нет, и как в таких условиях писать корректный код. ## 2.2. Уровни изоляции и аномалии в стандарте SQL {#internal-ru-section-2-2} Стандарт SQL описывает четыре уровня изоляции[^internal-ru-50-1]. Эти уровни определяются перечислением аномалий, которые допускаются или не допускаются при одновременном выполнении транзакций. Поэтому разговор об уровнях придется начать с аномалий. Стоит иметь в виду, что стандарт — некое теоретическое построение, которое влияет на практику, но с которым практика в то же время сильно расходится. Поэтому все примеры здесь умозрительные. Они будут использовать операции над счетами клиентов: это довольно наглядно, хотя, надо признать, не имеет ни малейшего отношения к тому, как банковские операции устроены в действительности. Интересно, что со стандартом SQL расходится и настоящая теория баз данных[^internal-ru-51-1], развившаяся уже после того, как стандарт был принят, а практика успела уйти вперед. ### Потерянное обновление {#internal-ru-subsection-star-28} Аномалия *потерянного обновления* (lost update) возникает, когда две транзакции читают одну и ту же строку таблицы, затем одна из них обновляет эту строку, после чего вторая обновляет эту же строку, не учитывая изменений, сделанных первой транзакцией. Например, две транзакции собираются увеличить сумму на одном и том же счете на 100 ₽. Первая транзакция читает текущее значение (1000 ₽), затем вторая транзакция читает то же самое значение. Первая увеличивает сумму (получается 1100 ₽) и записывает в базу это новое значение. Вторая поступает так же: получает те же 1100 ₽ и записывает их. В результате клиент потерял 100 ₽. Потерянное обновление не допускается стандартом ни на одном уровне изоляции. ### Грязное чтение и Read Uncommitted {#internal-ru-subsection-star-30} Аномалия *грязного чтения* (dirty read) возникает, когда транзакция читает еще не зафиксированные изменения, сделанные другой транзакцией. Например, первая транзакция переводит 100 ₽ на пустой счет клиента, но не фиксирует изменение. Другая транзакция читает состояние счета (обновленное, но не зафиксированное) и позволяет клиенту снять наличные — несмотря на то, что первая транзакция прерывается и отменяет свои изменения, так что никаких денег на счете клиента нет. Грязное чтение допускается стандартом на уровне Read Uncommitted. ### Неповторяющееся чтение и Read Committed {#internal-ru-subsection-star-32} Аномалия *неповторяющегося чтения* (non-repeatable read) возникает, когда транзакция читает одну и ту же строку два раза, а в промежутке между чтениями вторая транзакция изменяет (или удаляет) эту строку и фиксирует изменения. Тогда первая транзакция получит разные результаты. Например, пусть правило согласованности *запрещает отрицательные суммы на счетах клиентов*. Первая транзакция собирается уменьшить сумму на счете на 100 ₽. Она проверяет текущее значение, получает 1000 ₽ и решает, что уменьшение возможно. В это время вторая транзакция уменьшает сумму на счете до нуля и фиксирует изменения. Если бы теперь первая транзакция повторно проверила сумму, она получила бы 0 ₽ (но она уже приняла решение уменьшить значение, и счет «уходит в минус»). Неповторяющееся чтение допускается стандартом на уровнях Read Uncommitted и Read Committed. ### Фантомное чтение и Repeatable Read {#internal-ru-subsection-star-34} Аномалия *фантомного чтения* (phantom read) возникает, когда одна транзакция два раза читает набор строк по одинаковому условию, а в промежутке между чтениями другая транзакция добавляет строки, удовлетворяющие этому условию, и фиксирует изменения. Тогда первая транзакция получит разные наборы строк. Например, пусть правило согласованности *запрещает клиенту иметь более* *трех счетов*. Первая транзакция собирается открыть новый счет, проверяет их текущее количество (скажем, два) и решает, что открытие возможно. В это время вторая транзакция тоже открывает клиенту новый счет и фиксирует изменения. Если бы теперь первая транзакция перепроверила количество, она получила бы три (но она уже выполняет открытие еще одного счета, и у клиента их оказывается четыре). Фантомное чтение допускается стандартом на уровнях Read Uncommitted, Read Committed и Repeatable Read. ### Отсутствие аномалий и Serializable {#internal-ru-subsection-star-36} Стандарт определяет и уровень, на котором не допускаются никакие аномалии, — Serializable. И это совсем не то же самое, что запрет на потерянное обновление и на грязное, неповторяющееся и фантомное чтение. Дело в том, что существует значительно больше известных аномалий, чем перечислено в стандарте, и еще неизвестное число пока неизвестных. Уровень Serializable должен предотвращать *любые* аномалии. Это означает, что на таком уровне разработчику приложения не надо думать об изоляции. Если транзакции выполняют корректные последовательности операторов, работая в одиночку, данные останутся согласованными и при одновременной работе этих транзакций. В качестве иллюстрации приведу известную всем таблицу из стандарта, к которой для ясности добавлен последний столбец: | Уровень изоляции | потерянные изменения | грязное чтение | неповторяющееся чтение | фантомное чтение | другие аномалии | | --- | --- | --- | --- | --- | --- | | Read Uncommitted | — | да | да | да | да | | Read Committed | — | — | да | да | да | | Repeatable Read | — | — | — | да | да | | Serializable | — | — | — | — | — | {#internal-ru-table-2-1 num="2.1" caption="Уровни изоляции и аномалии в стандарте SQL" class="wide"} ### Почему именно эти аномалии? {#internal-ru-subsection-star-38} Почему из множества возможных аномалий в стандарте перечислены только несколько, и почему именно такие? Достоверно этого, видимо, никто не знает. Но не исключено, что о других аномалиях во времена принятия первых версий стандарта просто не задумывались, поскольку теория заметно отставала от практики. Кроме того, предполагалось, что изоляция должна быть построена на блокировках. Идея широко применявшегося *протокола двухфазного блокирования* (2PL) состоит в том, что в процессе выполнения транзакция блокирует затронутые строки, а при завершении — освобождает блокировки. Сильно упрощая: чем больше блокировок захватывает транзакция, тем лучше она изолирована от других транзакций. Но и тем сильнее страдает производительность системы, поскольку вместо совместной работы транзакции начинают выстраиваться в очередь за одними и теми же строками. Как мне представляется, разница между стандартными уровнями изоляции в значительной степени объясняется как раз количеством необходимых для их реализации блокировок. Если транзакция блокирует изменяемые строки для изменений, но не для чтения, получаем уровень Read Uncommitted с возможностью прочитать незафиксированные данные. Если изменяемые строки блокируются и для чтения, и для изменений, получаем уровень Read Committed: незафиксированные данные прочитать нельзя, но при повторном обращении к строке можно получить другое значение (неповторяющееся чтение). Если для всех операций блокируются и читаемые, и изменяемые строки, получаем уровень Repeatable Read: повторное чтение строки будет выдавать то же значение. Но с Serializable проблема: невозможно заблокировать строку, которой еще нет. Из-за этого остается возможность фантомного чтения: другая транзакция может добавить строку, попадающую под условия выполненного ранее запроса, и эта строка окажется в повторной выборке. Поэтому для полной изоляции обычных блокировок не хватает — нужно блокировать не строки, а условия (предикаты). Такие *предикатные* блокировки были предложены еще в 1976 году при работе над System R, но их практическая применимость ограничена достаточно простыми условиями, для которых понятно, конфликтуют ли два разных предиката. До реализации предикатных блокировок в задуманном виде дело, насколько мне известно, не дошло ни в одной системе. (*с.* [298](/ru/14-miscellaneous-locks/#internal-ru-page-299)) ## 2.3. Уровни изоляции в PostgreSQL {#internal-ru-section-2-3} Со временем на смену блокировочным протоколам управления транзакциями пришел *протокол изоляции на основе снимков* (Snapshot Isolation, SI). Его идея состоит в том, что каждая транзакция работает с согласованным снимком данных на определенный момент времени. В снимок попадают все актуальные изменения, зафиксированные до момента его создания. Изоляция на основе снимков позволяет обходиться минимумом блокировок. Фактически блокируется только повторное изменение одной и той же строки. Все остальные операции могут выполняться одновременно: пишущие транзакции никогда не блокируют читающие транзакции, а читающие вообще никогда никого не блокируют. (*с.* [268](/ru/13-row-level-locks/#internal-ru-page-269)) В PostgreSQL реализован *многоверсионный* вариант протокола SI. Многоверсионность подразумевает, что в СУБД в один момент времени могут сосуществовать несколько версий одной и той же строки. Это позволяет включать в снимок подходящую версию, а не обрывать транзакции, пытающиеся прочитать устаревшие данные. За счет использования снимков данных изоляция в PostgreSQL отличается от той, что требует стандарт, и в целом она строже. Грязное чтение не допускается по определению. Формально в PostgreSQL можно указать уровень Read Uncommitted, но работать он будет точно так же, как Read Committed, поэтому дальше я вообще не буду говорить про этот уровень. Уровень Repeatable Read не допускает не только неповторяющегося, но и фантомного чтения (хотя и не обеспечивает полную изоляцию). Правда, на уровне Read Committed можно *в ряде случаев* потерять изменения. (*с.* [161](/ru/07-autovacuum/#internal-ru-page-162)) | Уровень изоляции | потерянные изменения | грязное чтение | неповторяющееся чтение | фантомное чтение | другие аномалии | | --- | --- | --- | --- | --- | --- | | Read Committed | да | — | да | да | да | | Repeatable Read | — | — | — | — | да | | Serializable | — | — | — | — | — | {#internal-ru-table-2-2 num="2.2" caption="Уровни изоляции и аномалии в PostgreSQL" class="wide"} Перед тем как исследовать внутреннее устройство изоляции, давайте подробно рассмотрим каждый из трех уровней с точки зрения пользователя. (*с.* [97](/ru/04-snapshots/#internal-ru-page-98)) Для этого создадим таблицу счетов. У Алисы и Боба по 1000 ₽, но у Боба открыто два счета: ```text {label="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); ``` ### Read Committed {#internal-ru-subsection-star-40} **Отсутствие грязного чтения.** Легко убедиться в том, что грязные данные прочитать невозможно. Начнем транзакцию. По умолчанию она использует уровень изоляции Read Committed [^internal-ru-56-1]: ```text {label="psql"} => BEGIN; => SHOW transaction_isolation; transaction_isolation ----------------------- read committed (1 row) ``` Говоря точнее, умолчательный уровень задается параметром, который при необходимости можно изменить: ```text {label="psql"} => SHOW default_transaction_isolation; default_transaction_isolation ------------------------------- read committed (1 row) ``` Итак, в открытой транзакции снимаем средства со счета, но не фиксируем изменения. Свои собственные изменения транзакция всегда видит: ```text {label="psql"} => UPDATE accounts SET amount = amount - 200 WHERE id = 1; => SELECT * FROM accounts WHERE client = 'alice'; id | client | amount ----+--------+-------- 1 | alice | 800.00 (1 row) ``` Во втором сеансе начинаем еще одну транзакцию с тем же уровнем Read Committed: ```text {label="psql"} => BEGIN; => SELECT * FROM accounts WHERE client = 'alice'; id | client | amount ----+--------+--------- 1 | alice | 1000.00 (1 row) ``` Как и ожидалось, другая транзакция не видит незафиксированные изменения — грязное чтение не допускается. **Неповторяющееся чтение.** Пусть теперь первая транзакция зафиксирует изменения, а вторая повторно выполнит тот же самый запрос: ```text {label="psql"} => COMMIT; ``` ```text {label="psql"} => SELECT * FROM accounts WHERE client = 'alice'; id | client | amount ----+--------+-------- 1 | alice | 800.00 (1 row) => COMMIT; ``` Запрос получает уже новые данные — это и есть аномалия *неповторяющегося* *чтения*, которая допускается на уровне Read Committed. Практический вывод: в транзакции нельзя принимать решения на основании данных, прочитанных предыдущим оператором, ведь за время между выполнением операторов все может измениться. Вот пример, вариации которого встречаются в прикладном коде так часто, что он является классическим антипаттерном: ```sql IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN UPDATE accounts SET amount = amount - 1000 WHERE id = 1; END IF; ``` За время, которое проходит между проверкой и обновлением, другие транзакции могут как угодно изменить состояние счета, так что такая «проверка» ни от чего не спасает. Удобно представлять себе, что между операторами одной транзакции могут «вклиниться» произвольные операторы других транзакций, например вот так: ```sql IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN ``` ```sql UPDATE accounts SET amount = amount - 200 WHERE id = 1; COMMIT; ``` ```sql UPDATE accounts SET amount = amount - 1000 WHERE id = 1; END IF; ``` Если, переставив операторы, можно все испортить, значит, код написан некорректно. Не стоит обманывать себя, что с таким стечением обстоятельств мы не столкнемся: если неприятность может случиться, она произойдет обязательно. А вот воспроизводить и, следовательно, исправлять такие ошибки очень сложно. Как написать код корректно? Есть несколько возможностей: - Заменить процедурный код декларативным. Например, в данном случае проверка легко превращается в ограничение целостности: ```sql ALTER TABLE accounts ADD CHECK amount >= 0; ``` Теперь никакие проверки в коде не нужны: достаточно просто выполнить действие и при необходимости обработать исключение, которое возникнет в случае попытки нарушения целостности. - Использовать один оператор SQL. Проблемы с согласованностью возникают из-за того, что в промежутке между операторами может завершиться другая транзакция, и видимые данные изменятся. Если оператор один, то и промежутков никаких нет. В PostgreSQL достаточно средств, чтобы одним SQL-оператором решать сложные задачи. Отмечу общие табличные выражения (CTE), в которых в том числе можно использовать операторы `INSERT`, `UPDATE`, `DELETE`, `MERGE`, а также оператор `INSERT ON CONFLICT`, который атомарно реализует логику «вставить, а если строка уже есть, то обновить». (v. 15) - Задействовать пользовательские блокировки. Последнее средство — вручную установить исключительную блокировку на все нужные строки (`SELECT FOR UPDATE`) или вообще на всю таблицу (`LOCK TABLE`). Это всегда работает, но сводит на нет преимущества многоверсионности: вместо одновременного выполнения часть операций будет выполняться последовательно. (*с.* [268](/ru/13-row-level-locks/#internal-ru-page-269)) (*с.* [261](/ru/12-relation-level-locks/#internal-ru-page-262)) **Несогласованное чтение.** Однако не все так просто. Реализация PostgreSQL такова, что допускает другие, менее известные аномалии, которые не регламентируются стандартом. Допустим, первая транзакция начала перевод средств с одного счета Боба на другой: ```text {label="psql"} => BEGIN; => UPDATE accounts SET amount = amount - 100 WHERE id = 2; ``` В это время другая транзакция подсчитывает баланс Боба, причем подсчет выполняется в цикле по всем счетам Боба. Фактически транзакция начинает с первого счета (и, разумеется, видит прежнее состояние): ```text {label="psql"} => BEGIN; => SELECT amount FROM accounts WHERE id = 2; amount -------- 100.00 (1 row) ``` В этот момент первая транзакция успешно завершается: ```text {label="psql"} => UPDATE accounts SET amount = amount + 100 WHERE id = 3; => COMMIT; ``` А другая читает состояние второго счета (и видит уже новое значение): ```text {label="psql"} => SELECT amount FROM accounts WHERE id = 3; amount --------- 1000.00 (1 row) => COMMIT; ``` В итоге вторая транзакция получила в сумме 1100 ₽, то есть прочитала некорректные данные. Такая аномалия называется *несогласованным чтением* (read skew). Как избежать этой аномалии, оставаясь на уровне Read Committed? Конечно, использовать один оператор. Например, так: ```sql SELECT sum(amount) FROM accounts WHERE client = 'bob'; ``` До сих пор я утверждал, что видимость данных может поменяться только между операторами, но так ли это очевидно? А если запрос выполняется долго, может ли он увидеть часть данных в одном состоянии, а часть — уже в другом? Проверим. Удобный способ для этого — вставить в оператор искусственную задержку, вызвав функцию `pg_sleep`. Первая строка будет прочитана сразу, а вторая — через две секунды: ```text {label="psql"} => SELECT amount, pg_sleep(2) -- 2 секунды FROM accounts WHERE client = 'bob'; ``` Пока эта конструкция выполняется, в другой транзакции переводим средства обратно: ```text {label="psql"} => BEGIN; => UPDATE accounts SET amount = amount + 100 WHERE id = 2; => UPDATE accounts SET amount = amount - 100 WHERE id = 3; => COMMIT; ``` Результат показывает, что оператор видит данные в таком состоянии, в каком они находились на момент начала его выполнения, что, безусловно, правильно: ```text amount | pg_sleep ---------+---------- 0.00 | 1000.00 | (2 rows) ``` Но и тут не все так просто. Если в запросе вызывается *изменчивая* функция (с категорией изменчивости `VOLATILE`) и в этой функции выполняется другой запрос, то этот вложенный запрос будет видеть данные, не согласованные с данными основного запроса. Проверим состояние счетов Боба, используя функцию: ```text {label="psql"} => CREATE FUNCTION get_amount(id integer) RETURNS numeric AS $$ SELECT amount FROM accounts a WHERE a.id = get_amount.id; $$ VOLATILE LANGUAGE sql; => SELECT get_amount(id), pg_sleep(2) FROM accounts WHERE client = 'bob'; ``` И снова переведем деньги между счетами, пока запрос с задержкой выполняется: ```text {label="psql"} => BEGIN; => UPDATE accounts SET amount = amount + 100 WHERE id = 2; => UPDATE accounts SET amount = amount - 100 WHERE id = 3; => COMMIT; ``` В этом случае получим несогласованные данные — 100 ₽ пропали: ```text get_amount | pg_sleep ------------+---------- 100.00 | 800.00 | (2 rows) ``` Подчеркну, что такой эффект возможен только на уровне изоляции Read Committed и только с категорией изменчивости `VOLATILE`. Беда в том, что по умолчанию используется именно этот уровень изоляции и именно эта категория изменчивости, так что остается признать — грабли лежат очень удачно. **Несогласованное чтение вместо потерянного обновления.** Аномалию несогласованного чтения в рамках одного оператора можно — несколько неожиданным образом — получить и при обновлении. Посмотрим, что происходит при попытке изменения одной и той же строки двумя транзакциями. Сейчас у Боба 1000 ₽ на двух счетах: ```text {label="psql"} => SELECT * FROM accounts WHERE client = 'bob'; id | client | amount ----+--------+-------- 2 | bob | 200.00 3 | bob | 800.00 (2 rows) ``` Начинаем транзакцию, которая уменьшает баланс Боба: ```text {label="psql"} => BEGIN; => UPDATE accounts SET amount = amount - 100 WHERE id = 3; ``` В это же время другая транзакция начисляет проценты на все счета с общим балансом, равным или превышающим 1000 ₽: ```text {label="psql"} => UPDATE accounts SET amount = amount * 1.01 WHERE client IN ( SELECT client FROM accounts GROUP BY client HAVING sum(amount) >= 1000 ); ``` Выполнение оператора `UPDATE` состоит из двух частей. Сначала фактически выполняется оператор `SELECT`, который отбирает для обновления строки, соответствующие условию. Поскольку изменение первой транзакции не зафиксировано, вторая транзакция не может его видеть, и оно никак не влияет на выбор строк для начисления процентов. Таким образом, счета Боба попадают под условие, и после выполнения обновления его баланс должен увеличиться на 10 ₽. На втором этапе выполнения выбранные строки обновляются одна за другой. Вторая транзакция вынуждена подождать, поскольку строка `id = 3` в настоящий момент изменяется первой транзакцией и поэтому заблокирована. Между тем первая транзакция фиксирует изменения: ```text {label="psql"} => COMMIT; => SELECT * FROM accounts WHERE client = 'bob'; id | client | amount ----+--------+---------- 2 | bob | 202.0000 3 | bob | 707.0000 (2 rows) ``` Да, с одной стороны, команда `UPDATE` не должна видеть изменений второй транзакции. Но с другой — она не должна потерять изменения, зафиксированные во второй транзакции. После снятия блокировки оператор `UPDATE` *перечитывает* строку, которую пытается обновить (но только ее одну!). В результате получается, что Бобу начислено 9 ₽, исходя из суммы 900 ₽. Но если бы у Боба было 900 ₽, его счета вообще не должны были попасть в выборку. (*с.* [279](/ru/13-row-level-locks/#internal-ru-page-280)) Таким образом, транзакция прочитала некорректные данные: часть строк — на один момент времени, часть — на другой. Взамен потерянного обновления мы снова получаем аномалию несогласованного чтения. **Потерянное обновление.** Впрочем, хитрость с перечитыванием заблокированной строки не спасает от потери изменений, если обновление происходит не в одном операторе SQL. Вот пример, который уже был. Приложение читает и запоминает (вне базы данных) текущий баланс счета Алисы: (*с.* [50](/ru/02-isolation/#internal-ru-page-51)) ```text {label="psql"} => BEGIN; => SELECT amount FROM accounts WHERE id = 1; amount -------- 800.00 (1 row) ``` В это время другая транзакция действует так же: ```text {label="psql"} => BEGIN; => SELECT amount FROM accounts WHERE id = 1; amount -------- 800.00 (1 row) ``` Первая транзакция увеличивает запомненное ранее значение на 100 ₽ и записывает в базу: ```text {label="psql"} => UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1 RETURNING amount; amount -------- 900.00 (1 row) UPDATE 1 => COMMIT; ``` И вторая транзакция тоже: ```text {label="psql"} => UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1 RETURNING amount; amount -------- 900.00 (1 row) UPDATE 1 => COMMIT; ``` К сожалению, Алиса недосчиталась 100 ₽. СУБД ничего не знает о том, что запомненное значение 800 ₽ как-то связано с `accounts.amount`, и допускает аномалию потерянного изменения. На уровне изоляции Read Committed такой код некорректен. ### Repeatable Read {#internal-ru-subsection-star-52} **Отсутствие неповторяющегося и фантомного чтения.** Само название уровня изоляции Repeatable Read [^internal-ru-64-1] говорит о повторяемости чтения. Проверим это, а заодно убедимся и в отсутствии фантомов. Для этого в первой транзакции вернем счета Боба в прежнее состояние и создадим новый счет для Чарли: ```text {label="psql"} => BEGIN; => UPDATE accounts SET amount = 200.00 WHERE id = 2; => UPDATE accounts SET amount = 800.00 WHERE id = 3; => INSERT INTO accounts VALUES (4, 'charlie', 100.00); => SELECT * FROM accounts ORDER BY id; id | client | amount ----+---------+-------- 1 | alice | 900.00 2 | bob | 200.00 3 | bob | 800.00 4 | charlie | 100.00 (4 rows) ``` Во втором сеансе начнем транзакцию с уровнем Repeatable Read, указав его в команде `BEGIN` (уровень первой транзакции не важен): ```text {label="psql"} => BEGIN ISOLATION LEVEL REPEATABLE READ; => SELECT * FROM accounts ORDER BY id; id | client | amount ----+--------+---------- 1 | alice | 900.00 2 | bob | 202.0000 3 | bob | 707.0000 (3 rows) ``` Теперь первая транзакция фиксирует изменения, а вторая повторно выполняет тот же самый запрос: ```text {label="psql"} => COMMIT; ``` ```text {label="psql"} => SELECT * FROM accounts ORDER BY id; id | client | amount ----+--------+---------- 1 | alice | 900.00 2 | bob | 202.0000 3 | bob | 707.0000 (3 rows) => COMMIT; ``` Вторая транзакция продолжает видеть ровно те же данные, что и в начале: не видно ни изменений в существующих строках, ни новых строк. На таком уровне можно не беспокоиться о том, что между двумя операторами что-то поменяется. **Ошибка сериализации вместо потерянных изменений.** Как мы уже видели, на уровне изоляции Read Committed при обновлении одной и той же строки двумя транзакциями может возникнуть аномалия несогласованного чтения. Это происходит из-за того, что ожидающая транзакция перечитывает заблокированную строку и видит ее на один момент времени, а остальные строки выборки — на другой. (*с.* [61](/ru/02-isolation/#internal-ru-page-62)) На уровне Repeatable Read такая аномалия не допускается, но, если она все-таки возникает, сделать уже ничего нельзя — поэтому транзакция обрывается с ошибкой сериализации. Проверим, повторив тот же сценарий с процентами: ```text {label="psql"} => SELECT * FROM accounts WHERE client = 'bob'; id | client | amount ----+--------+-------- 2 | bob | 200.00 3 | bob | 800.00 (2 rows) ``` ```text {label="psql"} => BEGIN; => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; ``` ```text {label="psql"} => BEGIN ISOLATION LEVEL REPEATABLE READ; => UPDATE accounts SET amount = amount * 1.01 WHERE client IN ( SELECT client FROM accounts GROUP BY client HAVING sum(amount) >= 1000 ); ``` ```text {label="psql"} => COMMIT; ERROR: could not serialize access due to concurrent update => ROLLBACK; ``` Данные остались согласованными: ```text {label="psql"} => SELECT * FROM accounts WHERE client = 'bob'; id | client | amount ----+--------+-------- 2 | bob | 200.00 3 | bob | 700.00 (2 rows) ``` Такая же ошибка будет и в случае любого другого конкурентного изменения строки, даже если оно не затрагивает интересующие нас столбцы. Можно проверить, что и сценарий с приложениями, обновляющими баланс на основе запомненного значения, приводит к той же ошибке: ```text {label="psql"} => BEGIN ISOLATION LEVEL REPEATABLE READ; => SELECT amount FROM accounts WHERE id = 1; amount -------- 900.00 (1 row) ``` ```text {label="psql"} => BEGIN ISOLATION LEVEL REPEATABLE READ; => SELECT amount FROM accounts WHERE id = 1; amount -------- 900.00 (1 row) ``` ```text {label="psql"} => UPDATE accounts SET amount = 900.00 + 100.00 WHERE id = 1 RETURNING amount; amount --------- 1000.00 (1 row) UPDATE 1 => COMMIT; => UPDATE accounts SET amount = 900.00 + 100.00 WHERE id = 1 RETURNING amount; ERROR: could not serialize access due to concurrent update => ROLLBACK; ``` Практический вывод: если приложение использует уровень изоляции Repeatable Read для пишущих транзакций, оно должно быть готово повторять транзакции, завершившиеся ошибкой сериализации. Для только читающих транзакций такой исход невозможен. **Несогласованная запись.** Итак, в PostgreSQL на уровне изоляции Repeatable Read предотвращаются все аномалии, описанные в стандарте. Но не все вообще — науке до сих пор неизвестно, сколько их существует. Однако доказан важный факт: изоляция на основе снимков оставляет возможными *ровно две* аномалии, сколько бы их ни было всего. Первая из этих аномалий — *несогласованная запись* (write skew). Пусть действует такое правило согласованности: *допускаются отрицательные суммы на отдельных счетах клиента, если общая сумма на всех счетах* *этого клиента остается неотрицательной*. Первая транзакция получает сумму на счетах Боба: ```text {label="psql"} => BEGIN ISOLATION LEVEL REPEATABLE READ; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; sum -------- 900.00 (1 row) ``` Вторая транзакция получает ту же сумму: ```text {label="psql"} => BEGIN ISOLATION LEVEL REPEATABLE READ; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; sum -------- 900.00 (1 row) ``` Первая транзакция справедливо полагает, что сумму одного из счетов можно уменьшить на 600 ₽: ```text {label="psql"} => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2; ``` И вторая транзакция приходит к такому же выводу. Но уменьшает другой счет: ```text {label="psql"} => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3; => COMMIT; ``` ```text {label="psql"} => COMMIT; => SELECT * FROM accounts WHERE client = 'bob'; id | client | amount ----+--------+--------- 2 | bob | -400.00 3 | bob | 100.00 (2 rows) ``` У нас получилось увести баланс Боба в минус, хотя поодиночке каждая из транзакций отработала бы корректно. **Аномалия только читающей транзакции.** Аномалия *только читающей транзакции* — вторая и последняя из аномалий, возможных на уровне Repeatable Read. Чтобы продемонстрировать ее, потребуются три транзакции, две из которых будут изменять данные, а третья — только читать. Но сначала восстановим состояние счетов Боба: ```text {label="psql"} => UPDATE accounts SET amount = 900.00 WHERE id = 2; => SELECT * FROM accounts WHERE client = 'bob'; ``` ```text id | client | amount ----+--------+-------- 3 | bob | 100.00 2 | bob | 900.00 (2 rows) ``` Первая транзакция начисляет Бобу проценты на сумму средств на всех счетах. Проценты зачисляются на один из его счетов: ```text {label="psql"} => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 1 => UPDATE accounts SET amount = amount + ( SELECT sum(amount) FROM accounts WHERE client = 'bob' ) * 0.01 WHERE id = 2; ``` Затем другая транзакция снимает деньги с другого счета Боба и фиксирует свои изменения: ```text {label="psql"} => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 2 => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; => COMMIT; ``` Если в этот момент первая транзакция будет зафиксирована, никакой аномалии не возникнет: мы могли бы считать, что сначала выполнена первая транзакция, а затем вторая (но не наоборот, потому что первая транзакция увидела состояние счета `id = 3` до того, как этот счет был изменен второй транзакцией). Но представим, что в этот момент третья (только читающая) транзакция запрашивает суммы на счетах Боба. Она может видеть изменения второй транзакции (которая уже была зафиксирована), но не первой (которая еще не была зафиксирована): ```text {label="psql"} => SELECT * FROM accounts WHERE client = 'bob'; -- 3 id | client | amount ----+--------+-------- 2 | bob | 900.00 3 | bob | 0.00 (2 rows) ``` С другой стороны, мы уже установили выше, что вторую транзакцию следует считать начавшейся после первой. Последствия аномалии становятся понятными после завершения первой транзакции: ```text {label="psql"} => COMMIT; ``` У Боба на счетах всего 900 ₽ (что показала только читающая транзакция), а проценты оказались начисленными на все 1000 ₽: ```text {label="psql"} => SELECT * FROM accounts WHERE client = 'bob'; id | client | amount ----+--------+---------- 2 | bob | 910.0000 3 | bob | 0.00 (2 rows) ``` ### Serializable {#internal-ru-subsection-star-62} На уровне Serializable [^internal-ru-71-1] предотвращаются все возможные аномалии. Фактически Serializable реализован как надстройка над изоляцией на основе снимков данных. Те аномалии, которые не возникают при Repeatable Read (такие как грязное, неповторяемое, фантомное чтение), не возникают и на уровне Serializable. А те две аномалии, которые возникают (несогласованная запись и аномалия только читающей транзакции), специальным образом обнаруживаются, и в этом случае транзакция обрывается: возникает уже знакомая нам ошибка сериализации. **Отсутствие аномалий.** Можно убедиться, что сценарий с аномалией несогласованной записи приведет к ошибке сериализации: (*с.* [67](/ru/02-isolation/#internal-ru-page-68)) ```text {label="psql"} => BEGIN ISOLATION LEVEL SERIALIZABLE; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; sum ---------- 910.0000 (1 row) ``` ```text {label="psql"} => BEGIN ISOLATION LEVEL SERIALIZABLE; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; sum ---------- 910.0000 (1 row) ``` ```text {label="psql"} => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2; => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3; => COMMIT; COMMIT ``` ```text {label="psql"} => COMMIT; ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt. HINT: The transaction might succeed if retried. ``` К такой же ошибке приведет и сценарий аномалии только читающей транзакции. **Откладывание читающей транзакции.** Чтобы только читающая транзакция не приводила к аномалии и не могла пострадать от нее, PostgreSQL предлагает интересный механизм: такая транзакция может быть отложена до тех пор, пока ее выполнение не станет безопасным. Это единственный случай, когда оператор `SELECT` может быть заблокирован обновлениями строк. Посмотрим на примере сценария аномалии только читающей транзакции: ```text {label="psql"} => 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; id | client | amount ----+--------+-------- 2 | bob | 900.00 3 | bob | 100.00 (2 rows) ``` ```text {label="psql"} => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 1 => UPDATE accounts SET amount = amount + ( SELECT sum(amount) FROM accounts WHERE client = 'bob' ) * 0.01 WHERE id = 2; => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 2 => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; => COMMIT; ``` Третью транзакцию явно объявляем только читающий (`READ ONLY`) и откладываемой (`DEFERRABLE`): ```text {label="psql"} => BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; -- 3 => SELECT * FROM accounts WHERE client = 'alice'; ``` При попытке выполнить запрос транзакция блокируется, потому что иначе она приведет к аномалии. И только после того, как первая транзакция будет зафиксирована, третья продолжит выполнение: ```text {label="psql"} => COMMIT; ``` ```text {label="psql"} id | client | amount ----+--------+--------- 1 | alice | 1000.00 (1 row) => SELECT * FROM accounts WHERE client = 'bob'; id | client | amount ----+--------+---------- 2 | bob | 910.0000 3 | bob | 0.00 (2 rows) => COMMIT; ``` Таким образом, приложение, использующее уровень изоляции Serializable, должно повторять транзакции, завершившиеся ошибкой сериализации. (Точно так же следует поступать и на уровне Repeatable Read, если не ограничиваться только читающими транзакциями.) Уровень Serializable дает простоту программирования, но цена за нее — накладные расходы на обнаружение возможных аномалий и обрыв некоторой доли транзакций. Снизить накладные расходы можно, явно обозначая только читающие транзакции как `READ ONLY`. Но главный вопрос, конечно, в том, насколько велика доля оборванных транзакций, ведь их придется выполнять повторно. Если бы обрывались только те транзакции, которые действительно несовместимо пересекаются по данным, все было бы неплохо. Но такая реализация неизбежно оказалась бы слишком ресурсоемкой, поскольку пришлось бы отслеживать операции с каждой строкой. В действительности реализация такова, что допускает ложноположительные срабатывания: будут обрываться и некоторые совершенно нормальные транзакции, которым «просто не повезло». Везение зависит от многих причин, например от наличия подходящих индексов или доступного объема оперативной памяти, и поведение сложно предсказать заранее. (*с.* [298](/ru/14-miscellaneous-locks/#internal-ru-page-299)) Если используется уровень изоляции Serializable, он должен применяться для всех транзакций приложения. При смешении транзакций разного уровня изоляции уровень Serializable будет (без всяких предупреждений) вести себя как Repeatable Read. Поэтому при использовании Serializable имеет смысл изменить значение параметра *default\_transaction\_isolation*, хотя, конечно, это не помешает указать неправильный уровень явно. (*по умолчанию:* `read committed`) Есть и другие ограничения реализации, например запросы на уровне Serializable не будут работать на репликах. И хотя работа над улучшением функциональности не прекращается, имеющиеся ограничения и накладные расходы снижают привлекательность такого уровня изоляции. (v. 12) ## 2.4. Какой уровень изоляции использовать? {#internal-ru-section-2-4} Уровень изоляции Read Committed используется в PostgreSQL по умолчанию, и, по всей видимости, именно этот уровень применяется в абсолютном большинстве приложений. Он удобен тем, что на нем обрыв транзакции возможен только в случае сбоя, но для предотвращения несогласованности обрыв не применяется. Иными словами, ошибка сериализации возникнуть не может, и о повторении транзакций заботиться не надо. Обратная сторона медали — большое число возможных аномалий, подробно рассмотренных выше. Разработчик вынужден постоянно иметь их в виду и писать код так, чтобы не допускать их появления. Если не получается сформулировать нужные действия в одном SQL-операторе, приходится прибегать к явной установке блокировок. Самое неприятное то, что код сложно тестировать на наличие ошибок, связанных с получением несогласованных данных, а сами ошибки могут возникать непредсказуемым и невоспроизводимым образом и поэтому сложны в исправлении. Уровень изоляции Repeatable Read снимает часть проблем несогласованности, но, увы, не все. Поэтому приходится не только помнить об оставшихся аномалиях, но и изменять приложение так, чтобы оно корректно обрабатывало ошибки сериализации. Это, конечно, неудобно. Но для только читающих транзакций этот уровень прекрасно дополняет Read Committed и полезен, например, для построения отчетов, использующих несколько SQL-запросов. Наконец, уровень Serializable позволяет вообще не заботиться о несогласованности, что значительно упрощает написание кода. Единственное, что требуется от приложения, — уметь повторять любую транзакцию при получении ошибки сериализации. Но доля прерываемых транзакций и дополнительные накладные расходы могут существенно снизить пропускную способность. Также следует учитывать, что уровень Serializable не применим на репликах и что его нельзя смешивать с другими уровнями изоляции. [^internal-ru-50-1]: [postgrespro.ru/docs/postgresql/18/transaction-iso](). [^internal-ru-51-1]: [postgrespro.ru/education/books/dbtech](). [^internal-ru-56-1]: [postgrespro.ru/docs/postgresql/18/transaction-iso#XACT-READ-COMMITTED](). [^internal-ru-64-1]: [postgrespro.ru/docs/postgresql/18/transaction-iso#XACT-REPEATABLE-READ](). [^internal-ru-71-1]: [postgrespro.ru/docs/postgresql/18/transaction-iso#XACT-SERIALIZABLE]().