Немного о транзакциях БД и уровнях их изолированности

Здесь я повторю кратко то, что было на лекции 20.10. касательно механизма транзакций и кое-что добавлю от себя.

Транзакция (Transaction) - некоторая последовательность действий, выполняемая над объектами БД (в первую очередь над таблицами), которая рассматривается как одно целое и поэтому либо должна быть корректно завершена (Commit), либо полностью игнорирована (Rollback).
Основные требования к транзакциям сформулировал Джим Грей. Это свойства Atomicity (Атомарность), Consistency (Согласованность) и Durability (Долговечность). Позднее к ним присоединилось свойство Isolation (Изолированность), образуя четверку свойств ACID.

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

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

Transactions: ACID Compliance

Уровни изолированности транзакций (Transaction Isolation Levels).

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

Но для начала опишу 4 основные проблемные ситуации:

Допустим имеется таблица:

       users
id     name     age
1      Mat      28
2      Steave   22

1. Проблема потерянного изменения (Lost Update)

Транзакция 1	Транзакция 2
UPDATE users SET age = 21 WHERE id = 1;
	UPDATE users SET age = 25 WHERE id = 1;
SELECT * FROM users WHERE id = 1;
	COMMIT;

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

2. Проблема грязного чтения (Dirty Reads)

Транзакция 1	Транзакция 2
	SELECT * FROM users WHERE id = 1;
UPDATE users SET age = 21 WHERE id = 1;
	SELECT * FROM users WHERE id = 1;
ROLLBACK;

При этом транзакция 2 может неоднократно делать выборки, всякий раз с некорректным результатом.

3. Проблема неповторяемого чтения (Non-Repeatable Reads)

Транзакция 1	Транзакция 2
	SELECT * FROM users WHERE id = 1;
UPDATE users SET age = 21 WHERE id = 1; COMMIT;
	SELECT * FROM users WHERE id = 1;

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

4. Проблема "записей-фантомов" (Phantom Reads)

Транзакция 1	Транзакция 2
	SELECT * FROM users WHERE age BETWEEN 10 AND 30;
INSERT INTO users VALUES ( 3, 'Bob', 27 ); COMMIT;
	SELECT * FROM users WHERE age BETWEEN 10 AND 30;

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

Уровни изоляции [0-3]:

0 - чтение неподтвержденных данных (Read Uncommited)
Первый из возможных уровней изоляции, при котором обеспечивается разрешение первой из вышеназванных ситуаций, но при этом возможны три остальные.

1 - чтение подтвержденных данных (Read Commited)
На этом уровне исключаются проблемы потерянного изменения и грязного чтения, но при этом возможно неповторяемое чтение и чтение фантомов.

2 - повторяемость чтения (Repeatable Read)
На этом уровне корректно разрешаются ситуации 1-3, остается проблема появления "записей-фантомов".

3 - упорядоченность (Serializable)
Уровень, на котором результаты параллельного выполнения транзакций для БД совпадают с результатом последовательного выполнения тех же транзакций. Фантомы невозможны.

Isolation Levels and Concurrency