MVCC-1. Изоляция

    Привет, Хабр! Этой статьей я начинаю серию циклов (или цикл серий? в общем, задумка грандиозная) о внутреннем устройстве PostgreSQL.

    Материал будет основан на учебных курсах по администрированию, которые делаем мы с Павлом pluzanov. Смотреть видео не все любят (я точно не люблю), а читать слайды, пусть даже с комментариями, — совсем «не то».

    Конечно, статьи не будут повторять содержание курсов один в один. Я буду говорить только о том, как все устроено, опуская собственно администрирование, зато постараюсь делать это более подробно и обстоятельно. И я верю в то, что такие знания полезны прикладному разработчику не меньше, чем администратору.

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

    Вещи, о которых пойдет речь, не сильно меняются от версии к версии, но использовать я буду текущий, 11-й «ванильный» PostgreSQL.

    Первый цикл посвящен вопросам, связанным с изоляцией и многоверсионностью, и план его таков:

    1. Изоляция, как ее понимают стандарт и PostgreSQL (эта статья);
    2. Слои, файлы, страницы — что творится на физическом уровне;
    3. Версии строк, виртуальные и вложенные транзакции;
    4. Снимки данных и видимость версий строк, горизонт событий;
    5. Внутристраничная очистка и HOT-обновления;
    6. Обычная очистка (vacuum);
    7. Автоматическая очистка (autovacuum);
    8. Переполнение счетчика транзакций и заморозка.

    Ну, поехали.

    Что такое изоляция и почему это важно


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

    Вряд ли вы обрадуетесь, если приложение получит из базы некорректные данные, или если приложение запишет некорректные данные в базу.

    Но что такое “корректные” данные? Известно, что на уровне базы данных можно создавать ограничения целостности (integrity constraints, такие как NOT NULL или UNIQUE). Если данные всегда удовлетворяют ограничениям целостности (а это так, потому что СУБД это гарантирует), то они целостны.

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

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

    Дальше мы будет называть корректность термином согласованность (consistency).

    Давайте, однако, предположим, что приложение выполняет только корректные последовательности операторов. В чем тогда роль СУБД, если приложение и так корректно?

    Во-первых, оказывается, что корректная последовательность операторов может временно нарушать согласованность данных, и это — как ни странно — нормально. Заезженный, но понятный пример состоит в переводе средств с одного счета на другой. Правило согласованности может звучать так: перевод никогда не меняет общей суммы денег на счетах (такое правило довольно трудно записать на SQL в виде ограничения целостности, так что оно существует на уровне приложения и невидимо для СУБД). Перевод состоит из двух операций: первая уменьшает средства на одном счете, вторая — увеличивает на другом. Первая операция нарушает согласованность данных, вторая — восстанавливает.

    Хорошее упражнение — реализовать описанное выше правило на уровне ограничений целостности. А вам слабо? ©

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

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

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

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

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

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

    Если и другие, более сложные аномалии, с которыми мы разберемся чуть позже.

    Отказываться от одновременного выполнения, конечно, нельзя: иначе о какой производительности может идти речь? Но нельзя и работать с некорректными данными.

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

    Итак, мы подошли к определению:

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

    Это определение объединяет три первые буквы аббревиатуры ACID. Они настолько тесно связаны друг с другом, что рассматривать одно без другого просто нет смысла. На самом деле сложно оторвать и букву D (durability). Ведь при крахе системы в ней остаются изменения незафиксированных транзакций, с которыми приходится что-то делать, чтобы восстановить согласованность данных.

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

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


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

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

    Потерянное обновление


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

    Например, две транзакции собираются увеличить сумму на одном и том же счете на 100 ₽. Первая транзакция читает текущее значение (1000 ₽), затем вторая транзакция читает то же значение. Первая транзакция увеличивает сумму (получается 1100 ₽) и записывает это значение. Вторая транзакция поступает так же — получает те же 1100 ₽ и записывает их. В результате клиент потерял 100 ₽.

    Потерянное обновление не допускается стандартом ни на одном уровне изоляции.

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


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

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

    Грязное чтение допускается стандартом на уровне Read Uncommitted.

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


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

    Например, пусть правило согласованности запрещает отрицательные суммы на счетах клиентов. Первая транзакция собирается уменьшить сумму на счете на 100 ₽. Она проверяет текущее значение, получает 1000 ₽ и решает, что уменьшение возможно. В это время вторая транзакция уменьшает сумму на счете до нуля и фиксирует изменения. Если бы теперь первая транзакция повторно проверила сумму, она получила бы 0 ₽ (но она уже приняла решение уменьшить значение, и счет “уходит в минус”).

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

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


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

    Например, пусть правило согласованности запрещает клиенту иметь более 3 счетов. Первая транзакция собирается открыть новый счет, проверяет их текущее количество (скажем, 2) и решает, что открытие возможно. В это время вторая транзакция тоже открывает клиенту новый счет и фиксирует изменения. Если бы теперь первая транзакция перепроверила количество, она получила бы 3 (но она уже выполняет открытие еще одного счета и у клиента их оказывается 4).

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

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


    Стандарт определяет и еще один уровень — Serializable, — на котором не допускаются никакие аномалии. И это совсем не то же самое, что запрет на потерянное обновление и на грязное, неповторяющееся и фантомное чтения.

    Дело в том, что существует значительно больше известных аномалий, чем перечислено в стандарте, и еще неизвестное число пока неизвестных.

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

    Итоговая табличка


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

    Почему именно эти аномалии?


    Почему из множества возможных аномалий в стандарте перечислены только несколько и почему именно такие?

    Достоверно этого, видимо, никто не знает. Но практика здесь точно обогнала теорию, так что не исключено, что о других аномалиях тогда (речь о стандарте SQL:92) просто не задумывались.

    Кроме того, предполагалось, что изоляция должна быть построена на блокировках. Идея широко применявшегося протокола двухфазного блокирования (2PL) состоит в том, что в процессе выполнения транзакция блокирует строки, с которыми работает, а при завершении — освобождает блокировки. Сильно упрощая, чем больше блокировок захватывает транзакция, тем лучше она изолирована от других транзакций. Но и тем сильнее страдает производительность системы, поскольку вместо совместной работы транзакции начинают выстраиваться в очередь за одними и теми же строками.

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

    Если транзакция блокирует изменяемые строки от изменения, но не от чтения, получаем уровень Read Uncommitted: потерянные изменения не допускаются, но можно прочитать незафиксированные данные.

    Если транзакция блокирует изменяемые строки и от чтения, и от изменения, получаем уровень Read Committed: незафиксированные данные прочитать нельзя, но при повторном обращении к строке можно получить другое значение (неповторяющееся чтение).

    Если транзакция блокирует и читаемые, и изменяемые строки и от чтения, и от изменения, получаем уровень Repeatable Read: повторное чтение строки будет выдавать то же значение.

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

    Поэтому для реализации уровня Serializable обычных блокировок не хватает — нужно блокировать не строки, а условия (предикаты). Такие блокировки и были названы предикатными. Предложены они были еще в 1976 году, но их практическая применимость ограничена достаточно простыми условиями, для которых понятно, как объединять два разных предиката. До реализации таких блокировок в какой-либо системе дело, насколько мне известно, не дошло.

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


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

    Такая изоляция автоматически не допускает грязное чтение. Формально в PostgreSQL можно указать уровень Read Uncommitted, но работать она будет точно так же, как Read Committed. Поэтому про уровень Read Uncommitted мы дальше вообще не будем говорить.

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

    За счет использования снимков данных изоляция в PostgreSQL получается строже, чем того требует стандарт: уровень Repeatable Read не допускает не только неповторяющегося, но и фантомного чтения (хотя и не обеспечивает полную изоляцию). И достигается это без потери эффективности.
    потерянные изменения грязное чтение неповторяющееся чтение фантомное чтение другие аномалии
    Read Uncommitted да да да
    Read Committed да да да
    Repeatable Read да
    Serializable

    Как многоверсионность реализована “под капотом”, мы поговорим в следующих статьях, а сейчас подробно посмотрим на каждый из трех уровней глазами пользователя (как вы понимаете, самое интересное скрывается за «другими аномалиями»). Для этого создадим таблицу счетов. У Алисы и Боба по 1000 ₽, но у Боба открыто два счета:

    => CREATE TABLE accounts(
      id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
      number text UNIQUE,
      client text,
      amount numeric
    );
    => INSERT INTO accounts VALUES
      (1, '1001', 'alice', 1000.00),
      (2, '2001', 'bob', 100.00),
      (3, '2002', 'bob', 900.00);
    

    Read Committed


    Отсутствие грязного чтения


    Легко убедиться в том, что грязные данные прочитать невозможно. Начнем транзакцию. По умолчанию она будет использовать уровень изоляции Read Committed:

    => BEGIN;
    => SHOW transaction_isolation;
    
     transaction_isolation 
    -----------------------
     read committed
    (1 row)
    

    Говоря точнее, умолчательный уровень задается параметром, его при необходимости можно изменить:

    => SHOW default_transaction_isolation;
    
     default_transaction_isolation 
    -------------------------------
     read committed
    (1 row)
    

    Итак, в открытой транзакции снимаем средства со счета, но не фиксируем изменения. Свои собственные изменения транзакция видит:

    => UPDATE accounts SET amount = amount - 200 WHERE id = 1;
    => SELECT * FROM accounts WHERE client = 'alice';
    
     id | number | client | amount 
    ----+--------+--------+--------
      1 | 1001   | alice  | 800.00
    (1 row)
    

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

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

    |  => BEGIN;
    |  => SELECT * FROM accounts WHERE client = 'alice';
    
    |   id | number | client | amount  
    |  ----+--------+--------+---------
    |    1 | 1001   | alice  | 1000.00
    |  (1 row)
    

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

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


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

    => COMMIT;
    

    |  => SELECT * FROM accounts WHERE client = 'alice';
    
    |   id | number | client | amount 
    |  ----+--------+--------+--------
    |    1 | 1001   | alice  | 800.00
    |  (1 row)
    
    |  => COMMIT;
    

    Запрос получает уже новые данные — это и есть аномалия неповторяющегося чтения, которая допускается на уровне Read Committed.

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

          IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN
            UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
          END IF;
    

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

          IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN
           -----
          |   UPDATE accounts SET amount = amount - 200 WHERE id = 1;
          |   COMMIT;
           -----
            UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
          END IF;
    

    Если, переставив операторы, можно все испортить, значит код написан некорректно. И не стоит обманывать себя, что такого стечения обстоятельств не произойдет — произойдет обязательно.

    А как написать код корректно? Возможности, как правило, сводятся к следующему:

    • Не писать код.
      Это не шутка. Например, в данном случае проверка легко превращается в ограничение целостности:
      ALTER TABLE accounts ADD CHECK amount >= 0;
      Теперь никакие проверки не нужны: достаточно просто выполнить действие и при необходимости обработать исключение, которое возникнет в случае попытки нарушения целостности.
    • Использовать один SQL-оператор.
      Проблемы с согласованностью возникают из-за того, что в промежутке между операторами может завершиться другая транзакция и изменятся видимые данные. А если оператор один, то и промежутков никаких нет.
      В PostgreSQL достаточно средств, чтобы одним SQL-оператором решать сложные задачи. Отметим общие табличные выражения (CTE), в которых в том числе можно использовать операторы INSERT/UPDATE/DELETE, а также оператор INSERT ON CONFLICT, который реализует логику «вставить, а если строка уже есть, то обновить» в одном операторе.
    • Пользовательские блокировки.
      Последнее средство — вручную установить исключительную блокировку или на все нужные строки (SELECT FOR UPDATE), или вообще на всю таблицу (LOCK TABLE). Это всегда работает, но сводит на нет преимущества многоверсионности: вместо одновременного выполнения часть операций будет выполняться последовательно.

    Несогласованное чтение


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

    Допустим, первая транзакция начала перевод средств с одного счета Боба на другой:

    => BEGIN;
    => UPDATE accounts SET amount = amount - 100 WHERE id = 2;
    

    В это время другая транзакция подсчитывает баланс Боба, причем подсчет выполняется в цикле по всем счетам Боба. Фактически транзакция начинает с первого счета (и, очевидно, видит прежнее состояние):

    |  => BEGIN;
    |  => SELECT amount FROM accounts WHERE id = 2;
    
    |   amount 
    |  --------
    |   100.00
    |  (1 row)
    

    В этот момент первая транзакция успешно завершается:

    => UPDATE accounts SET amount = amount + 100 WHERE id = 3;
    => COMMIT;
    

    А другая читает состояние второго счета (и видит уже новое значение):

    |  => SELECT amount FROM accounts WHERE id = 3;
    
    |   amount  
    |  ---------
    |   1000.00
    |  (1 row)
    
    |  => COMMIT;
    

    Таким образом вторая транзакция получила в сумме 1100 ₽, то есть некорректные данные. Это и есть аномалия несогласованного чтения.

    Как избежать такой аномалии, оставаясь на уровне Read Committed? Конечно, использовать один оператор. Например так:

          SELECT sum(amount) FROM accounts WHERE client = 'bob';
    


    До сих пор я утверждал, что видимость данных может поменяться только между операторами, но так ли это очевидно? А если запрос выполняется долго, может ли он увидеть часть данных в одном состоянии, а часть — в другом?

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

    => SELECT amount, pg_sleep(2) FROM accounts WHERE client = 'bob';
    

    Пока эта конструкция выполняется, в другой транзакции выполняем перенос средств обратно:

    |  => BEGIN;
    |  => UPDATE accounts SET amount = amount + 100 WHERE id = 2;
    |  => UPDATE accounts SET amount = amount - 100 WHERE id = 3;
    |  => COMMIT;
    

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

     amount  | pg_sleep 
    ---------+----------
        0.00 | 
     1000.00 | 
    (2 rows)
    

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

    => 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';
    

    |  => BEGIN;
    |  => UPDATE accounts SET amount = amount + 100 WHERE id = 2;
    |  => UPDATE accounts SET amount = amount - 100 WHERE id = 3;
    |  => COMMIT;
    

    В этом случае получим некорректные данные — 100 ₽ потерялись:

     get_amount | pg_sleep 
    ------------+----------
         100.00 | 
         800.00 | 
    (2 rows)
    

    Подчеркну, что такой эффект возможен только на уровне изоляции Read Committed, и только с категорией изменчивость VOLATILE. Беда в том, что умолчанию используется именно этот уровень изоляции и именно эта категория изменчивости, так что надо признать — грабли лежат очень удачно. Не наступайте!

    Несогласованное чтение в обмен на потерянные изменения


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

    Посмотрим, что происходит при попытке изменения одной и той же строки двумя транзакциями. Сейчас у Боба 1000 ₽ на двух счетах:

    => SELECT * FROM accounts WHERE client = 'bob';
    
     id | number | client | amount 
    ----+--------+--------+--------
      2 | 2001   | bob    | 200.00
      3 | 2002   | bob    | 800.00
    (2 rows)
    

    Начинаем транзакцию, которая уменьшает баланс Боба:

    => BEGIN;
    => UPDATE accounts SET amount = amount - 100 WHERE id = 3;
    

    В это же время другая транзакция начисляет проценты на все счета клиентов с общим балансом, равным или превышающим 1000 ₽:

    |  => 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 уже заблокирована первой транзакцией.

    Между тем первая транзакция фиксирует изменения:

    => COMMIT;
    

    Что же получится в результате?

    => SELECT * FROM accounts WHERE client = 'bob';
    
     id | number | client |  amount  
    ----+--------+--------+----------
      2 | 2001   | bob    | 202.0000
      3 | 2002   | bob    | 707.0000
    (2 rows)
    

    Да, с одной стороны, команда UPDATE не должна видеть изменений второй транзакции. Но с другой — она не должна потерять изменения, зафиксированные во второй транзакции.

    После снятия блокировки UPDATE перечитывает строку, которую пытается обновить (но только ее одну!). В результате получается, что Бобу начислено 9 ₽, исходя из суммы 900 ₽. Но если бы у Боба было 900 ₽, его счета вообще не должны были попасть в выборку.

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

    Внимательные читатели отмечают, что при некоторой помощи со стороны приложения на уровне Read Committed можно получить и потерянное обновление. Например так:

          x := (SELECT amount FROM accounts WHERE id = 1);
          UPDATE accounts SET amount = x + 100 WHERE id = 1;
    

    База данных не виновата: она получает два SQL-оператора и ничего не знает о том, что значение x + 100 как-то связано с accounts.amount. Не пишите код таким образом.

    Repeatable Read


    Отсутствие неповторяющегося и фантомного чтений


    Само название уровня изоляции говорит о том, что чтение является повторяемым. Проверим это, а заодно убедимся и в отсутствии фантомных чтений. Для этого в первой транзакции вернем счета Боба в прежнее состояние и создадим новый счет для Чарли:

    => BEGIN;
    => UPDATE accounts SET amount = 200.00 WHERE id = 2;
    => UPDATE accounts SET amount = 800.00 WHERE id = 3;
    => INSERT INTO accounts VALUES
      (4, '3001', 'charlie', 100.00);
    => SELECT * FROM accounts ORDER BY id;
    
     id | number | client  | amount 
    ----+--------+---------+--------
      1 | 1001   | alice   | 800.00
      2 | 2001   | bob     | 200.00
      3 | 2002   | bob     | 800.00
      4 | 3001   | charlie | 100.00
    (4 rows)
    

    Во втором сеансе начнем транзакцию с уровнем Repeatable Read, указав его в команде BEGIN (уровень первой транзакции не важен).

    |  => BEGIN ISOLATION LEVEL REPEATABLE READ;
    |  => SELECT * FROM accounts ORDER BY id;
    
    |   id | number | client |  amount  
    |  ----+--------+--------+----------
    |    1 | 1001   | alice  |   800.00
    |    2 | 2001   | bob    | 202.0000
    |    3 | 2002   | bob    | 707.0000
    |  (3 rows)
    

    Теперь первая транзакция фиксирует изменения, а вторая повторно выполняет тот же самый запрос.

    => COMMIT;
    

    |  => SELECT * FROM accounts ORDER BY id;
    
    |   id | number | client |  amount  
    |  ----+--------+--------+----------
    |    1 | 1001   | alice  |   800.00
    |    2 | 2001   | bob    | 202.0000
    |    3 | 2002   | bob    | 707.0000
    |  (3 rows)
    
    |  => COMMIT;
    

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

    На таком уровне можно не беспокоиться о том, что между двумя операторами что-то поменяется.

    Ошибка сериализации в обмен на потерянные изменения


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

    На уровне Repeatable Read такая аномалия не допускается, но, если она все-таки возникает, сделать уже ничего нельзя — поэтому транзакция обрывается с ошибкой сериализации. Проверим, повторив тот же сценарий с процентами:

    => SELECT * FROM accounts WHERE client = 'bob';
    
     id | number | client | amount 
    ----+--------+--------+--------
      2 | 2001   | bob    | 200.00
      3 | 2002   | bob    | 800.00
    (2 rows)
    
    => BEGIN;
    => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
    

    |  => 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
    |  );
    

    => COMMIT;
    

    |  ERROR:  could not serialize access due to concurrent update
    
    |  => ROLLBACK;
    

    Данные остались согласованными:

    => SELECT * FROM accounts WHERE client = 'bob';
    
     id | number | client | amount 
    ----+--------+--------+--------
      2 | 2001   | bob    | 200.00
      3 | 2002   | bob    | 700.00
    (2 rows)
    

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

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

    Несогласованная запись


    Итак, в PostgreSQL на уровне изоляции Repeatable Read предотвращаются все аномалии, описанные в стандарте. Но не все вообще. Оказывается, есть ровно две аномалии, которые остаются возможными. (Это верно не только для PostgreSQL, но и для других реализаций изоляции на основе снимков.)

    Первая из этих аномалий — несогласованная запись.

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

    Первая транзакция получает сумму на счетах Боба: 900 ₽.

    => BEGIN ISOLATION LEVEL REPEATABLE READ;
    => SELECT sum(amount) FROM accounts WHERE client = 'bob';
    
      sum   
    --------
     900.00
    (1 row)
    

    Вторая транзакция получает ту же сумму.

    |  => BEGIN ISOLATION LEVEL REPEATABLE READ;
    |  => SELECT sum(amount) FROM accounts WHERE client = 'bob';
    
    |    sum   
    |  --------
    |   900.00
    |  (1 row)
    

    Первая транзакция справедливо полагает, что сумму одного из счетов можно уменьшить на 600 ₽.

    => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2;
    

    И вторая транзакция приходит к такому же выводу. Но уменьшает другой счет:

    |  => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3;
    |  => COMMIT;
    

    => COMMIT;
    => SELECT * FROM accounts WHERE client = 'bob';
    
     id | number | client | amount  
    ----+--------+--------+---------
      2 | 2001   | bob    | -400.00
      3 | 2002   | bob    |  100.00
    (2 rows)
    

    У нас получилось увести баланс Боба в минус, хотя каждая из транзакций работает корректно поодиночке.

    Аномалия только читающей транзакции


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

    Но сначала восстановим состояние счетов Боба:

    => UPDATE accounts SET amount = 900.00 WHERE id = 2;
    => SELECT * FROM accounts WHERE client = 'bob';
    
     id | number | client | amount
    ----+--------+--------+--------
      3 | 2002   | bob    | 100.00
      2 | 2001   | bob    | 900.00
    (2 rows)
    

    Первая транзакция начисляет Бобу проценты на сумму средств на всех счетах. Проценты зачисляются на один из его счетов:

    => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 1
    => UPDATE accounts SET amount = amount + (
      SELECT sum(amount) FROM accounts WHERE client = 'bob'
    ) * 0.01
    WHERE id = 2;
    

    Затем другая транзакция снимает деньги с другого счета Боба и фиксирует свои изменения:

    |  => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 2
    |  => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
    |  => COMMIT;
    

    Если в этот момент первая транзакция будет зафиксирована, никакой аномалии не возникнет: мы могли бы считать, что сначала выполнена первая транзакция, а затем вторая (но не наоборот, потому что первая транзакция увидела состояние счета id = 3 до того, как этот счет был изменен второй транзакцией).

    Но представим, что в этот момент начинается третья (только читающая) транзакция, которая читает состояние какого-нибудь счета, не затронутого первыми двумя транзакциями:

    |  => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 3
    |  => SELECT * FROM accounts WHERE client = 'alice';
    
    |   id | number | client | amount 
    |  ----+--------+--------+--------
    |    1 | 1001   | alice  | 800.00
    |  (1 row)
    

    И только после этого первая транзакция завершается:

    => COMMIT;
    

    Какое состояние теперь должна увидеть третья транзакция?

    |    SELECT * FROM accounts WHERE client = ‘bob’;
    

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

    |    id | number | client | amount
    |   ----+--------+--------+--------
    |     2 | 2001   | bob    | 900.00
    |     3 | 2002   | bob    |   0.00
    |   (2 rows)
    
    |   => COMMIT;
    

    Serializable


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

    Несогласованная запись


    Для иллюстрации повторим сценарий с аномалией несогласованной записи:

    => BEGIN ISOLATION LEVEL SERIALIZABLE;
    => SELECT sum(amount) FROM accounts WHERE client = 'bob';
    
       sum    
    ----------
     910.0000
    (1 row)
    

    |   => BEGIN ISOLATION LEVEL SERIALIZABLE;
    |   => SELECT sum(amount) FROM accounts WHERE client = 'bob';
    
    |      sum    
    |   ----------
    |    910.0000
    |   (1 row)
    

    => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2;
    

    |   => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3;
    |   => COMMIT;
    

    => 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.
    

    Так же, как и на уровне Repeatable Read, приложение, использующее уровень изоляции Serializable, должно повторять транзакции, завершившиеся ошибкой сериализации, о чем нам сообщает и подсказка в сообщении об ошибке.

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

    В действительности реализация PostgreSQL такова, что допускает ложноотрицательные срабатывания: будут обрываться и некоторые совершенно нормальные транзакции, которым просто «не повезло». Как мы увидим позже, это зависит от многих причин, например, от наличия подходящих индексов или доступного объема оперативной памяти. Кроме того, есть и некоторые другие (довольно серьезные) ограничения реализации, например, запросы на уровне Serializable не будут работать на репликах, для них не будут использоваться параллельные планы выполнения. И, хотя работа над улучшением реализации не прекращается, но имеющиеся ограничения снижают привлекательность такого уровня изоляции.
    Параллельные планы появятся уже в PostgreSQL 12 (патч). А запросы на репликах могут заработать в PostgreSQL 13 (другой патч).

    Аномалия только читающей транзакции


    Чтобы только читающая транзакция не могла привести к аномалии и не могла пострадать от нее, 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;
    
     id | number | client | amount 
    ----+--------+--------+--------
      2 | 2001   | bob    | 900.00
      3 | 2002   | bob    | 100.00
    (2 rows)
    

    => 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):

    |   => BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; -- 3
    |   => SELECT * FROM accounts WHERE client = 'alice';
    

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

    => COMMIT;
    

    И только после того, как первая транзакция зафиксирована, третья продолжает выполнение:

    |    id | number | client | amount
    |   ----+--------+--------+--------
    |     1 | 1001   | alice  | 800.00
    |   (1 row)
    
    |   => SELECT * FROM accounts WHERE client = 'bob';
    
    |    id | number | client |  amount  
    |   ----+--------+--------+----------
    |     2 | 2001   | bob    | 910.0000
    |     3 | 2002   | bob    |     0.00
    |   (2 rows)
    
    |   => COMMIT;
    

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

    Так что если решили использовать Serializble — лучше всего глобально установить умолчательный уровень (хотя это, конечно, не запретит указать неправильный уровень явно):

          ALTER SYSTEM SET default_transaction_isolation = 'serializable';
    

    Более строгое изложение вопросов, связанных с транзакциями, согласованностью и аномалиями, вы найдете в книге и курсе лекций Бориса Асеновича Новикова «Основы технологий баз данных».

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


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

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

    Уровень изоляции Repeatable Read снимает часть проблем несогласованности, но, увы, не все. Поэтому приходится не только помнить об оставшихся аномалиях, но и изменять приложение так, чтобы оно корректно обрабатывало ошибки сериализации. Это, конечно, неудобно. Но для только читающих транзакций этот уровень прекрасно дополняет Read Committed и очень удобен, например, для построения отчетов, использующих несколько SQL-запросов.

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

    Продолжение.
    Postgres Professional
    Разработчик СУБД Postgres Pro

    Похожие публикации

    Комментарии 27

      0
      Правило согласованности может звучать так: перевод никогда не меняет общей суммы денег на счетах (такое правило довольно трудно записать на SQL в виде ограничения целостности, так что оно существует на уровне приложения и невидимо для СУБД)


      Это не совсем так. Точнее совсем не так. Просто оно будет записано не в виде ограничения (CONSTRAINT), а в виде схемы данных, где сумма фигурирует один раз и к ней так или иначе «подвязаны» два счёта. Такой, классический «транзакционный» (не в смысле СУБД, а в смысле бухгалтерского учёта) вариант. Правда при такой схеме остаток (сальдо) по счёту не хранится, а требует вычисления каждый раз, когда он потребуется.
        +2
        Оно ж по-разному бывает. Если мы про бухучет, то наверное да, можно все на полупроводках аккуратно устроить. Но в буржуйских системах (OeBS как пример) в одной бухгалтерской транзакции может быть больше двух счетов — и опять получаем ту же самую проблему.
        А в статье пример игрушечный, специально упрощенный. Но на нем все довольно хорошо видно.
          +1
          Я прекрасно всё понимаю: и про игрушечность примера и про разницу между итальянским и англо-саксонским конто (там, кстати, может быть ещё веселее, когда по разным счетам суммы ещё и в разных валютах — сам такое делал :)). Просто начал читать и как-то резануло. Потом втянулся :)
        +3
        И да, спасибо за статью! Нечастый пример того, как важные, но довольно сложные вещи, объясняются простым и понятным языком. Жду продолжения!
          0
          Спасибо, рад, что понравилось.
          0
          > метки никто не читает

          Неправда )
            0
            Черт (:
            0
            Пользовательские блокировки.
            Последнее средство — вручную установить исключительную блокировку или на все нужные строки (SELECT FOR UPDATE), или вообще на всю таблицу (LOCK TABLE). Это всегда работает, но сводит на нет преимущества многоверсионности: вместо одновременного выполнения часть операций будет выполняться последовательно.


            Не совсем согласен.
            Ето минимальное зло. При правильном SELECT мы блокируем только нужние записи.(для примера одного клиента) И операции по другим клиентам проходят паралельно. Но следует учитивать что для всех изменений в даной таблице нужно предварительно использовать
            SELECT FOR UPDATE в идеале использовать одну сторедж процедуру.
            P.S. Хорошая статтья, На жаль многие разработчики не хотят вникать в такие детали БД.
              +1
              Когда минимальное, а когда и максимальное.
              По сути, выполняя заранее SELECT FOR UPDATE для изменяемых строк, вы эмулируете блокировочный протокол поверх изоляции на основе снимков. Иногда это просто не нужно, иногда это ок, а иногда это вообще не спасает. It depends.
              0

              Превосходно!
              Прошу вас, не останавливайтесь, пишите ещё!

                0

                Спасибо. Графомана не остановить!

                0
                Спасибо, отлично разложили всё с примерами
                Скину коллегам )
                  0

                  Буду рад, если пригодится!

                  0
                  Егор, спасибо! Ваши статьи (в частности цикл статей про индексы) читаю и перечитываю много раз, очень рад новой серии статей, очень жду продолжения

                  В PostgreSQL достаточно средств, чтобы одним SQL-оператором решать сложные задачи. Отметим общие табличные выражения (CTE), в которых в том числе можно использовать операторы INSERT/UPDATE/DELETE, а также оператор INSERT ON CONFLICT, который реализует логику «вставить, а если строка уже есть, то обновить» в одном операторе.


                  Существует еще конструкция CASE WHEN THEN END, но почему то я очень давно не встречал ее упоминания в статьях (а статей разных читаю много). Я использую периодически эту конструкцию. Почему ее не упоминают? Быть может у нее есть какой-то существенный недостаток?
                    0

                    Владимир, спасибо на добром слове.
                    Недостатков у CASE я не вижу (ну, кроме многословности), прекрасная и полезная конструкция. Я ее использую совершенно автоматически; наверное, потому и не упоминают — вроде как очевидная вещь. Но в любом учебнике по SQL она есть, например в этом.

                    0
                    Очень хорошая статья. Спасибо! Вот только я так и не понял какой уровень изоляции у одного «SQL-оператора» и являются ли запросы CTE (а также подзапросы) отдельными операторами, в том числе рекурсивные. Да и что будет с банальным JOIN? Например, если мы соединяем тяжелую таблицу с собой по первичному ключу, а параллельно пролетает транзакция на обновление записи в этой таблице, может ли получится так, что значения в левой и правой части результата будут разными?

                    К моему удивлению, не удается найти какой-либо авторитетной информации по этим вопросам. Может быть вы прольете свет?

                    Еще раз спасибо!
                      +1

                      Рад, что статья понравилась! Свет попробую пролить.


                      какой уровень изоляции у одного «SQL-оператора»

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


                      являются ли запросы CTE (а также подзапросы) отдельными операторами, в том числе рекурсивные

                      Нет, это составные части одного и того же запроса.


                      Да и что будет с банальным JOIN? Например, если мы соединяем тяжелую таблицу с собой по первичному ключу, а параллельно пролетает транзакция на обновление записи в этой таблице, может ли получится так, что значения в левой и правой части результата будут разными?

                      Ни в коем случае. Это один оператор, он всегда* видит только согласованные данные (на момент начала оператора или на момент начала транзакции, смотря по уровню изоляции).


                      * Ну, не совсем всегда. В статье я привожу два случая, когда в PostgreSQL внутри одного оператора можно увидеть несогласованные данные на уровне Read Committed:


                      • если в запросе вызывается volatile-функция, содержащая другой запрос;
                      • оператор UPDATE, который перечитывает только одну строку, а не весь набор.

                      Никакие другие ситуации мне неизвестны.


                      К моему удивлению, не удается найти какой-либо авторитетной информации по этим вопросам.

                      Зачем вам авторитеты — проверьте сами. Вдруг и я вас обманываю? (:
                      Вот скажем вас join интересует. Возьмите таблицу, соедините ее саму с собой, параллельно запустите обновление и сравните результат с известным правильным.

                        0
                        Спасибо вам за столь оперативный ответ!
                        Возьмите таблицу, соедините ее саму с собой, параллельно запустите обновление и сравните результат с известным правильным.

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

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

                        Вот и хочется понять, а что в запросе полностью атомарно (консистентно и изолированно). Ведь, если весь запрос, то таким образом с помощью CTE можно хорошенько «просадить» конкурентность. В то же время об уровнях изоляции думать не придется…
                          +1

                          Стандарт не слишком много чего определяет, поэтому ориентироваться всегда надо на особенности конкретной реализации. За MS SQL не скажу, а в Постгресе весь запрос целиком видит согласованные данные.


                          то таким образом с помощью CTE можно хорошенько «просадить» конкурентность

                          Не понимаю, почему? Запрос (если мы о SELECT говорим), выполняясь, никому не мешает.

                            0
                            Стандарт не слишком много чего определяет, поэтому ориентироваться всегда надо на особенности конкретной реализации.

                            Видимо, так. Получается, что глядя на бизнес-транзакцию и уровень изоляции не получится понять будет ли приложение работать корректно. Для меня это открытие.

                            Не понимаю, почему? Запрос (если мы о SELECT говорим), выполняясь, никому не мешает.

                            В CTE можно писать запросы на изменение. Они могут быть тяжелыми. Я вижу тут 2 варианта: 1. Запросы из WITH должны интерпретироваться как шаги транзакции со всеми вытекающими. 2. Внутри СУБД нужно что-то заблокировать (причем возможно целыми площадями), чтобы предотвратить возможные коллизии с параллельными запросами.
                              +1
                              Получается, что глядя на бизнес-транзакцию и уровень изоляции не получится понять будет ли приложение работать корректно.

                              Вне контекста конкретной СУБД — не получится. Ну, есть какие-то общие знаменатели, но в общем случае точно нет.


                              Внутри СУБД нужно что-то заблокировать

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

                                0
                                Но и если бы CTE выполнялся по шагам, тоже надо было бы блокировать.

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


                                Вот кстати есть формальный способ записи истории транзакций буквами r1(x), w1(x), c1, a1 (в книге у вас на сайте он тоже используется). Я сейчас подумал, что непонятно как записывать self-join, CTE, "set value = value + 1" с помощью такой нотации. Нет ли тут проблемы с формализацией?


                                Извините, что так много вопросов. Но раз уж начал спрашивать… Отдельный цикл про блокировки тоже буду читать, спасибо. Я, наверное, еще не скоро разберусь. :)

                                  0
                                  А в CTE, подозреваю, все блокировки будут браться сразу

                                  Нет, тоже постепенно.


                                  Вот кстати есть формальный способ записи истории транзакций буквами r1(x), w1(x), c1, a1

                                  Есть. Я его сознательно не стал тут приводить, чтобы не уводить разговор совсем в сторону теории. Почему? См. ниже.


                                  непонятно как записывать self-join, CTE, "set value = value + 1" с помощью такой нотации. Нет ли тут проблемы с формализацией?

                                  Есть такая проблема. Теория использует простую модель с элементарными операциями. Как эти операции сгруппированы по SQL-операторам, теорию не интересует.


                                  Вот давайте посмотрим на пример потерянного обновления, про который вы пишите ниже:


                                  r1(x) r2(x) w1(x) w2(x)

                                  Пара операций r1(x) w1(x) подразумевает, что вы читаете объект x (допустим, строку таблицы), а потом записываете что-то в этот же x. Что именно — теория никак не определяет.


                                  Если мы реализуем эти операции так, как в том примере (https://github.com/ept/hermitage/blob/master/postgres.md#lost-update-p4):


                                  begin; set transaction isolation level read committed; -- T1
                                  begin; set transaction isolation level read committed; -- T2
                                  select * from test where id = 1; -- T1
                                  select * from test where id = 1; -- T2
                                  update test set value = 11 where id = 1; -- T1
                                  update test set value = 11 where id = 1; -- T2, BLOCKS
                                  commit; -- T1. This unblocks T2, so T1's update is overwritten
                                  commit; -- T2

                                  то мне непонятно, что именно мы тут потеряли? Оба оператора UPDATE записывают в x фиксированное значение — их в принципе не интересует, что в x было до этого.


                                  Но если мы переделаем этот пример вот так:


                                  ...
                                  update test set value = value + 11 where id = 1; -- T1
                                  update test set value = value + 11 where id = 1; -- T2, BLOCKS
                                  ...

                                  то увидим, что потери не случится: второй оператор UPDATE перечитает строку после того, как разблокируется.


                                  Но вообще по-моему лучше по-другому интерпретировать эту запись. Я бы считал, что пара r1(x) w1(x) соответствует одному оператору


                                  update t set x = f(x);

                                  потому что такой оператор как раз сначала читает x, а потом записывает в него что-то на основе полученного значения.


                                  Но так или иначе — результат один. Если у нас Read Committed, то мы не теряем обновление, но (поскольку перечитываем только одну строку) можем получить несогласованное чтение. А если Repeatable Read (или Serializable), то транзакция просто обрывается во избежание проблем.


                                  Это два разных подхода.
                                  В случае Read Committed мы предпочитаем получить аномалию, и никогда не обрываем из-за этого транзакции. Задача обеспечения согласованности ложится на плечи разработчика приложения.
                                  В случае Serializable (и отчасть Repeatable Read) мы предотвращаем потенциальные проблемы тем, что обрываем транзакции, и приложение должно их просто повторять.


                                  Ну вот, много написал, но не уверен, что что-то прояснил (:

                                    0
                                    то мне непонятно, что именно мы тут потеряли? Оба оператора UPDATE записывают в x фиксированное значение — их в принципе не интересует, что в x было до этого.

                                    Почему не интересует? Это могут быть два человека-оператора, сидящих каждый за своим терминалом и вручную обновляющих записи. Как условный пример — инкрементируют счетчик. Сначала оба прочитали 10, записали 11. А должно быть в результате 12.


                                    Я бы считал, что пара r1(x) w1(x) соответствует одному оператору

                                    Мне кажется это уже вольная трактовка. Которая кстати автоматически означает, что операция инкремента "set value = value + 1" не атомарна (не изолирована).


                                    Ну вот, много написал, но не уверен, что что-то прояснил (:

                                    Да, прояснили. :) Пока вывод такой: раз есть проблема с формализованным представлением SQL-запроса в виде элементарных операций чтения и записи, то невозможно понять из скольки операций он состоит и где могут произойти коллизии. А из этого автоматически следует, что пытаться найти в нем аномалии бесполезно. Как и оперировать ими на практике. В принципе у меня было такое подозрение, но я всегда думал, что я что-то пропустил.


                                    Тем не менее остается целых три варианта:


                                    1. Использовать Serializable (если СУБД позволяет).
                                    2. Использовать Read Committed с ручными блокировками.
                                    3. Читать руководство по уровням изоляции конкретной СУБД, где написано что и как видят запросы и что в какой момент блокируется.

                                    (Лучше, наверное, в обратном порядке)

                                      0

                                      Добавлю еще немного про аномалию Lost Update. Я, конечно, уже запутался, перебирая разные источники в поисках ответов. Но кажется, что lost update — это самая аномальная аномалия — она то появляется то исчезает в зависимости от источника.


                                      Например, в документации PostgreSQL она не упоминается совсем, даже в таблице ссылающейся на стандарт:


                                      The SQL standard and PostgreSQL-implemented transaction isolation levels are described in Table 13.1.

                                      Вот что, собственно, пишет сам стандарт об уровнях изоляции (ISO/IEC 9075-1:2011(E)), в котором транзакциям посвящены целых пол-страницы:


                                      Every isolation level guarantees that no update is lost.

                                      Не уверен, что речь идет именно об аномалии Lost Update, потому что упоминания других аномалий, как и слов anomaly и phenomena в документе найти не удалось.


                                      Зато попалась на глаза статья от Microsoft. Вот выдержка из нее:


                                      A lost update can be interpreted in one of two ways. In the first scenario, a lost update is considered to have taken place when data that has been updated by one transaction is overwritten by another transaction, before the first transaction is either committed or rolled back. This type of lost update cannot occur in SQL Server 2005 because it is not allowed under any transaction isolation level.
                                      The other interpretation of a lost update is when one transaction (Transaction #1) reads data into its local memory, and then another transaction (Transaction #2) changes this data and commits its change. After this, Transaction #1 updates the same data based on what it read into memory before Transaction #2 was executed. In this case, the update performed by Transaction #2 can be considered a lost update.

                                      Думаю, на этом я свои изыскания в области аномалий закончу...


                                      Спасибо вам за ответы!

                                        0

                                        Ну, первый-то сценарий совсем клинический.
                                        А так мне кажется, что интерпретации зависят от того, считаем мы r и w одной SQL-операцией (UPDATE), или двумя (SELECT + UPDATE). Если одной, то второй сценарий в Постгресе вообще невозможен (транзакция 2 будет заблокирована до окончания 1). А если двумя, то увы, возможен, потому что в этом случае база ничего не знает о связи w с r. Речь про Read Committed, конечно.

                                    0

                                    Еще один момент: у вас в табличке написано, что Postgres не допускает аномалии потерянных обновлений ни на на одном из уровней изоляции. В оф. документации я про это почему-то ничего не нашел, что также наводит на мысль, что поведение соответствует стандарту.
                                    Однако вот в этой табличке https://github.com/ept/hermitage указано, что Postgres допускает данную аномалию (P4) на уровне Read Committed, что подтверждают мои собственные эксперименты. Сценарий приведен здесь: https://github.com/ept/hermitage/blob/master/postgres.md#lost-update-p4
                                    Вполне похоже на r1(x) r2(x) w1(x) w2(x).

                        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                        Самое читаемое