Уровни изолированности транзакций для самых маленьких



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

    Немного теории


    Сами транзакции особых объяснений не требуют, транзакция — это N (N≥1) запросов к БД, которые выполнятся успешно все вместе или не выполнятся вовсе. Изолированность же транзакции показывает то, насколько сильно влияют друг на друга параллельно выполняющиеся транзакции.
    Выбирая уровень транзакции, мы пытаемся прийти к консенсусу в выборе между высокой согласованностью данных между транзакциями и скоростью выполнения этих самых транзакций.
    Стоит отметить, что самую высокую скорость выполнения и самую низкую согласованность имеет уровень read uncommitted. Самую низкую скорость выполнения и самую высокую согласованность — serializable.

    Подготовка окружения


    Для примеров была выбрана СУБД MySQL. PostgreSQL мог бы тоже использоваться, но он не поддерживает уровень изоляции read uncommitted, и использует вместо него уровень read committed. Да и как оказалось, разные СУБД по-разному воспринимают уровни изолированности. Могут иметь разнообразные нюансы в обеспечении изоляции, иметь дополнительные уровни или не иметь общеизвестных.

    Создадим окружение с помощью готового образа MySQL с Docker Hub. И заполним базу данными.

    docker-compose.yaml
    version: '3.4'
    services:
      db:
        image: mysql:8
        environment:
            - MYSQL_ROOT_PASSWORD=12345
        command: --init-file /init.sql
        volumes:
            - data:/var/lib/mysql
            - ./init.sql:/init.sql
        expose:
            - "3306"
        ports:
            - "3309:3306"
    
    volumes:
      data:
    


    Заполнение базы данных
    create database if not exists bank;
    
    use bank;
    
    create table if not exists accounts
    (
    	id int unsigned auto_increment
    		primary key,
    	login varchar(255) not null,
    	balance bigint default 0 not null,
    	created_at timestamp default now()
    ) collate=utf8mb4_unicode_ci;
    
    insert into accounts (login, balance) values ('petya', 1000);
    insert into accounts (login, balance) values ('vasya', 2000);
    insert into accounts (login, balance) values ('mark', 500);
    


    Рассмотрим как работают уровни и их особенности.
    Примеры будем выполнять на 2 параллельно исполняющихся транзакциях. Условно транзакция в левом окне будем называть транзакция 1 (Т1), в правом окне — транзакция 2 (Т2).

    Read uncommitted


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

    Шаг 1. Начинаем 2 параллельные транзакции.



    Шаг 2. Смотрим какая информация имеется у нас в начале.



    Шаг 3. Теперь выполняем операции INSERT, DELETE, UPDATE в Т1, и посмотрим, что теперь видит другая транзакция.



    Т2 видит данные другой транзакции, которые еще не были зафиксированы.

    Шаг 4. И Т2 может получить какие-то данные.



    Шаг 5. При откате изменений Т1, данные полученные Т2 окажутся ошибочными.



    На данном уровне нельзя использовать данные, на основе которых делаются важные для приложения выводы и критические решения т.к выводы эти могут быть далеки от реальности.
    Данный уровень можно использовать, например, для примерных расчетов чего-либо. Результат COUNT(*) или MAX(*) можно использовать в каких-нибудь нестрогих отчетах.
    Другой пример это режим отладки. Когда во время транзакции, вы хотите видеть, что происходит с базой.

    Read committed


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

    Шаг 1 и Шаг 2 аналогичны предыдущему примеру.

    Шаг 3. Также выполним 3 простейшие операции с таблицей accounts (Т1) и сделаем полную выборку из этих таблиц в обеих транзакциях.



    И увидим, что феномен грязного чтения в Т2 отсутствует.

    Шаг 4. Зафиксируем изменения Т1 и проверим, что теперь видит Т2.



    Теперь Т2 видит все, что сделала Т1. Это так называемые феномен неповторяющегося чтения, когда мы видим обновленные и удаленные строки (UPDATE, DELETE), и феномен чтения фантомов, когда мы видим добавленные записи (INSERT).

    Repeatable read


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

    Снова повторяем Шаг 1 и Шаг 2.

    Шаг 3. В Т1 выполняем запросы INSERT, UPDATE и DELETE. После, в Т2 пытаемся обновить ту же самую строку, которую обновили в Т1.



    И получаем lock: T2 будет ждать, пока T1 не зафиксирует изменения или не откатится.

    Шаг 4. Зафиксируем изменения, которые сделала Т1. И прочитаем снова данные из таблицы accounts в Т2.



    Как видно, феноменов неповторяющегося чтения и чтения фантомов не наблюдается. Как же так, ведь по умолчанию, repeatable read позволяет нам предотвратить только феномен неповторяющегося чтения?

    На самом деле в MySQL отсутствует эффект чтения фантомов для уровня repeatable read. И в PostgreSQL от него тоже избавились для этого уровня. Хотя в классическом представлении этого уровня, мы должны наблюдать этот эффект.

    Небольшой абстрактный пример — сервис генерации подарочных сертификатов (кодов) и их использования. Например, злоумышленник сгенерировал себе код сертификата и пытается его активировать, пытаясь послать несколько запросов подряд на активацию купона. В таком случае у нас запустится несколько параллельно исполняемых транзакций, работающих с одним и тем же купоном. И в некоторых ситуациях может возникнуть двойная или даже тройная активация купона (пользователь получит 2x/3x бонусов). При repeatable read в данном случае возникнет lock и активация пройдет единожды, а в предыдущих 2 уровнях возможна многократная активация. Подобную проблему можно также решить с помощью запроса SELECT FOR UPDATE, который также заблокирует обновляемую запись (купон).

    Serializable


    Уровень, при котором транзакции ведут себя как будто ничего более не существует, никакого влияния друг на друга нет. В классическом представлении этот уровень избавляет от эффекта чтения фантомов.

    Шаг 1. Начинаем транзакции.

    Шаг 2. Т2 читаем таблицу accounts, затем Т1 пытаемся обновить данные прочитанные Т2.



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

    Шаг 3. И INSERT и DELETE ведет нас к lock'у в Т1.



    Пока Т2 не завершит свою работу, мы не сможем работать с данными, которые она прочитала. Мы получаем максимальную согласованность данных, никакие лишние данные не зафиксируются. Цена за это медленная скорость транзакций из-за частых lock'ов поэтому при плохой архитектуре приложения это может сыграть с Вами злую шутку.

    Выводы


    В большинстве приложений уровень изолированности редко меняется и используется значение по умолчанию (например, в MySQL это repeatable read, в PostgreSQL — read committed).

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

    Подробнее
    Реклама

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

      +2
      Во всех статьях такого типа ОЧЕНЬ не хватает описания в каких случаях какой тип изоляции выбирать. А-то складывается впечатление, что можно включить serializable и не париться. Не пишут о производительности на разных режимах.
        +2
        Спасибо
        Пример для read uncommitted есть, для repeatable read дописал.

        Про производительность добавил предложение вначале. Если Вы конечно не имели ввиду конкретные бенчмарки :)
          0
          Спасибо
          +5

          Алгоритм примерно такой.
          Сначала надо выбрать минимальный уровень изоляции, который гарантирует корректность поведения в данной транзакции (включая корректность данных с одной стороны и отсутствие взаимоблокировок с другой). Замечание про "в данной транзакции" весьма важное.
          Обычно это решение достаточно легко следует из того, что вы делаете в транзакции.
          Например:


          • Вывод списка в грид/на форму — обычно read committed, изредка read uncommitted (только в некоторых СУБД имеет смысл, в частности в старых версиях MS SQL, или если не используете RCSI)
          • Отчёты/формы, состоящие из одного запроса с соединениями/объединениями (join или union) — read committed
          • Отчёты/формы, состоящие из нескольких последовательных запросов или использующие временные таблицы — надо оценить, насколько тут нужен repeatable read (и действия должны быть в одной транзакции, иначе repeatable read не имеет смысла). Если не нужен, то read committed, если нужен, то repeatable read.
          • Пишущие транзакции (с проверкой условий/остатков, из нескольких запросов) — используйте по умолчанию не ниже repeatable read.
          • В MS SQL лучше (с точки зрения корректности данных и отсутствия взаимоблокировок) в пишущей транзакции для тех таблиц, которые меняются в данной транзакции использовать как можно раньше serializable. В других СУБД в зависимости от того, можете ли нарваться на фантомы.

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


          После того, как определились с базовым уровнем изоляции начинаем смотреть (правильность, производительность одного потока, производительность параллельных соединений с сервером, взаимоблокировки и блокировки/race condition горячих мест) и искать компромиссы. Тут уже парой абзацев не отделаться (статья превратится в книгу) — ситуаций и компромиссов даже в одной СУБД, даже типовых быстро становятся десятки. Цикл типичный: сбор информации, гипотеза, проверка, изменение, проверка применимости, внедрение и так по кругу.

          0
          Феномен не «повторяющегося», а неповторяющегося (но повторного) чтения.
            +1
            Да, спасибо
            Поправил
            0
            Пожалуй, ваша заметка больше вредна чем полезна, легко может запутать начинающих разработчиков, особенно пример с Repeatable read. Да к чему здесь docker? Вы так сильно ненавидите ваш пакетный менеджер?;)
              0
              Ну уж извините, пытался максимально просто объяснить, как разбирался сам на самых простых примерах.

              Docker здесь для того, чтобы любой желающий, скопировав пару файлов мог сам повторить примеры в том же самом окружении, где это делал я.
                0
                Чисто из любопытства, т.к. казалось, что данная статья помогла понять некоторые важные моменты. Что в статье не так/запутывает?
                  +2

                  Да хотя бы тем, что феномены не объяснены. А значит, что из этой статьи человек не поймёт, как минимум разницу serializable/repeatable read. Это, кстати, весьма индикативный вопрос, например, часто кандидат на собеседовании говорит "я ваще крут в СУБД и уровни изоляции вдоль и поперёк", а на вопрос разницы serializable/repeatable read хотя и говорит "фантомы", но привести пример и объяснить что это за фантомы не может.
                  Ну и то, что уровни изоляции надо объяснять либо без СУБД вообще, либо с одной СУБД. Потому что реализации НАСТОЛЬКО разные, что потом у человека, которому объясняли, каша в голове.


                  ЗЫ: Это только моё мнение, на основании моего опыта объяснения уровней изоляций лет около 15.

                +1

                Спасибо. Для меня было полезно.

                  +2

                  Стоит отметить, что разные СУБД имеют разную [b]реализацию[/b] этих уровней изоляции. Сами-то уровни будут работать одинаково (как им предписано стандартом), но вот процесс выполнения запроса с клиентской стороны будет разным (например, будет ли блокироваться и висеть в ожидании запрос, выполняющий SELECT, если есть другая незавершенная транзакция, выполнившая UPDATE над данной таблицей? ). Причём даже в рамках одной СУБД существуют опции, меняющие поведение в рамках одних и тех уровней изоляции. Например, в MS SQL SERVER есть замечательная опция READ_COMMITTED_SNAPSHOT, включив которую активируется режим "версионирования" строк, в котором в приведённом мной выше примере SELECT не блокируется даже при уровне изоляции READ_COMMITTED, потому что незакомиченный UPDATE создаёт внутри СУБД "отдельные версии" обновлённых строк.


                  Да что я вам тут пишу, вон кто-то уже всё расписал:
                  https://habr.com/ru/post/305600/

                    0
                    Вы в разделе про Read committed на шаге 4 говорите, что удаление строки, также как и изменение строки приводит к unrepeatable read:
                    «Теперь Т2 видит все, что сделала Т1. Это так называемые феномен неповторяющегося чтения, когда мы видим обновленные и удаленные строки (UPDATE, DELETE), и феномен чтения фантомов, когда мы видим добавленные записи (INSERT).»

                    На самом деле это чтение фантомов.
                    Другое дело, что Delete можно поймать на уровне изоляции ReadComitted, и нельзя на Repeatable Read.
                      0
                      Хм, а что же тогда такое феномен неповторящегося чтения для уровня read committed? Если DELETE — это чтение фантомов

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

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