Уровни изоляции транзакций

2022-12-29T11:38:21.293Z

Проблематика

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

Потерянное обновление (англ. lost update)

При одновременном изменении одного блока данных разными транзакциями, одно из изменений теряется.

Имеются две транзакции, выполняемые одновременно:

-- Транзакция 1
UPDATE accounts SET balance=balance+20 WHERE id=1;
-- Транзакция 2
UPDATE accounts SET balance=balance+25 WHERE id=1;

В обеих транзакциях изменяется значение поля balance, при этом одно из изменений теряется. Так что, balance будет увеличено не на 45, а только на 20 или 25.

Причины:

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

«Грязное» чтение (англ. dirty read)

Чтение данных в одной транзакции, добавленных или изменённых другой транзакцией, которая впоследствии не зафиксируется (ROLLBACK);

-- Транзакция 1                   -- Транзакция 2
SELECT f FROM tbl1 WHERE id=1;
UPDATE tbl1 SET f=f+1 WHERE id=1;
                                  SELECT f FROM tbl1 WHERE id=1;
ROLLBACK;

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

Неповторяющееся чтение (англ. non-repeatable read)

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

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

-- Транзакция 1	                  -- Транзакция 2
SELECT f FROM tbl1 WHERE id=1;	  SELECT f FROM tbl1 WHERE id=1;
UPDATE tbl1 SET f=f+1 WHERE id=1;
COMMIT;
                                  SELECT f FROM tbl1 WHERE id=1;

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

Фантомное чтение (англ. phantom reads)

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

-- Транзакция 1	                         -- Транзакция 2
                                         SELECT SUM(f2) FROM tbl1;
INSERT INTO tbl1 (f1,f2) VALUES (15,20);
COMMIT;
                                         SELECT SUM(f2) FROM tbl1;

В транзакции 2 получаем суму всех значений в колонке f2. Затем в транзакции 1 выполняется вставка новой строки, приводящая к тому, что повторное выполнение запроса в транзакции 2 выдаст другой результат. Такая ситуация называется фантомным чтением. От неповторяющегося чтения оно отличается тем, что результат повторного обращения к данным изменился не из-за изменения/удаления самих этих данных, а из-за появления новых (фантомных) данных.

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

Read uncommitted (чтение незафиксированных данных)

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

Read committed (чтение фиксированных данных)

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

Repeatable read (повторяемость чтения)

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

Serializable

Изоляция уровня этого уровня обеспечивает беспрепятственный доступ к базе данных транзакциям с SELECT запросами. Но для транзакций с запросами UPDATE и DELETE, этот уровень изоляции не допускает модификации одной и той же строки в рамках разных транзакций. При попытке обновления данных мы получим lock и будем ждать пока не завершится транзакция в которой были запрошены строки которые обновляются в текущей транзакции. Так решается проблема фантомного чтения.

Таблица уровней на которых предотвращаются типы проблем

Уровень изоляции lost update | dirty read | unrepeadable read | phantom read
READ UNCOMMITTED     +              -                -                -
READ COMMITTED       +              +                -                -
REPEATABLE READ      +              +                +                -
SERIALIZABLE         +              +                +                +

Deadlocks (взаимная блокировка транзакций)

Первая транзакции блокирует выполнение второй транзакции, а вторая транзакция блокирует выполнение первой транзакции. В этом случае одна из транзакций падает с ошибкой «Deadlock found when trying to get lock; try restarting transaction».

START TRANSACTION;                START TRANSACTION;
                                  /*lock row 1*/
                                  UPDATE tbl1 SET f=f+1 WHERE id=1;
/*lock row 2*/
UPDATE tbl1 SET f=f+2 WHERE id=2;
                                  /*try to lock row 2, waiting*/
                                  UPDATE tbl1 SET f=f-1 WHERE id=2;
/*try to lock row 1, will end a deadlock*/
UPDATE tbl1 SET f=f-2 WHERE id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Полезные команды

выяснить текущий уровень изоляции

SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;
SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;

установить уровень изоляции для сессии

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- возможные уровни REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE

просмотреть список транзакций которые могли зависнуть

SELECT * FROM information_schema.innodb_trx\G;

Ссылки

https://habr.com/ru/post/469415/ базовая статья с примерами
https://medium.com/pseudo-blog/уровни-изоляции-транзакций-87cd2b129de1 более подробная статья с исторической справкой вначале
https://shurshun.ru/tranzaktsii-blokirovki-urovni-izoliovannosti-tranzaktsiy-v-mysql/ тут хорошо описана проблематика
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html дока mysql
https://habr.com/ru/post/555920/ про ACID для самых маленьких
https://stackoverflow.com/questions/2950676/difference-between-set-autocommit-1-and-start-transaction-in-mysql-have-i-misse разница между start transaction и set autocommit = 0

Роман Калинин