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

в 8:04, , рубрики: isolation levels, mysql, postgresql, sql, transactions, Администрирование баз данных, Программирование

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

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

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

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

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

Для примеров была выбрана СУБД 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

Шаг 2. Смотрим какая информация имеется у нас в начале.
Уровни изолированности транзакций для самых маленьких - 3

Шаг 3. Теперь выполняем операции CREATE, DELETE, UPDATE в Т1, и посмотрим, что теперь видит другая транзакция.
Уровни изолированности транзакций для самых маленьких - 4
Т2 видит данные другой транзакции, которые еще не были зафиксированы.

Шаг 4. И Т2 может получить какие-то данные.
Уровни изолированности транзакций для самых маленьких - 5

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

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

Read committed

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

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

Шаг 3. Также выполним 3 простейшие операции с таблицей accounts (Т1) и сделаем полную выборку из этих таблиц в обеих транзакциях.
Уровни изолированности транзакций для самых маленьких - 7
И увидим, что феномен грязного чтения в Т2 отсутствует.

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

Repeatable read

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

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

Шаг 3. В Т1 выполняем запросы CREATE, UPDATE и DELETE. После, в Т2 пытаемся обновить ту же самую строку, которую обновили в Т1.
Уровни изолированности транзакций для самых маленьких - 9
И получаем lock: T2 будет ждать, пока T1 не зафиксирует изменения или не откатится.

Шаг 4. Зафиксируем изменения, которые сделала Т1. И прочитаем снова данные из таблицы accounts в Т2.
Уровни изолированности транзакций для самых маленьких - 10
Как видно, феноменов повторяющегося чтения и чтения фантомов не наблюдается. Как же так, ведь по умолчанию, repeatable read позволяет нам предотвратить только феномен повторяющегося чтения?
На самом деле в MySQL отсутствует эффект чтения фантомов для уровня repeatable read. И в PostgreSQL от него тоже избавились для этого уровня. Хотя в классическом представлении этого уровня, мы должны наблюдать этот эффект.

Serializable

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

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

Шаг 2. Т2 читаем таблицу accounts, затем Т1 пытаемся обновить данные прочитанные Т2.
Уровни изолированности транзакций для самых маленьких - 11
Получаем lock: мы не можем изменить данные в одной транзакции, прочитанные в другой.

Шаг 3. И CREATE и DELETE ведет нас к lock'у в Т1.
Уровни изолированности транзакций для самых маленьких - 12

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

Выводы

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

Автор: slava157

Источник


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js