Уровень изолированности транзакций

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

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

При параллельном выполнении транзакций возможны следующие проблемы:

• потерянное обновление (англ. lost update) — при одновременном изменении одного блока данных разными транзакциями, одно из изменений теряется;
• «грязное» чтение (англ. dirty read) — чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится (откатится);
• неповторяющееся чтение (англ. non-repeatable read) — при повторном чтении в рамках одной транзакции, ранее прочитанные данные оказываются изменёнными или удалёнными;
• фантомное чтение (англ. phantom reads) — при повторном чтении в рамках одной транзакции прочитаны данные (новые «фантомные» строки), которых при предыдущих чтениях не было, хотя они удовлетворяли условиям отбора предыдущего чтения.

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

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

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

Транзакция 1	Транзакция 2
UPDATE tbl1 SET f2=f2+20 WHERE f1=1;	UPDATE tbl1 SET f2=f2+25 WHERE f1=1;

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

«Грязное» чтение

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

Транзакция 1	Транзакция 2
SELECT f2 FROM tbl1 WHERE f1=1;
UPDATE tbl1 SET f2=f2+1 WHERE f1=1;
	SELECT f2 FROM tbl1 WHERE f1=1;
ROLLBACK WORK;

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

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

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

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

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

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

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

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

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

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

Стандарт SQL-92 определяет уровни изоляции, установка которых предотвращает определенные конфликтные ситуации. Введены следующие четыре уровня изоляции:

Serializable (упорядочиваемость)

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

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

Уровень, при котором чтение одной и той же строки или строк в транзакции дает одинаковый результат. (Пока транзакция не завершена, никакие другие транзакции не могут модифицировать эти данные.)

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

Принятый по умолчанию уровень для Microsoft SQL Server. Завершенное чтение, при котором отсутствует черновое, "грязное" чтение (т.е. чтение одним пользователем данных, которые не были зафиксированы в БД командой COMMIT). Тем не менее в процессе работы одной транзакции другая может быть успешно завершена и сделанные ею изменения зафиксированы. В итоге первая транзакция будет работать с другим набором данных. Это проблема неповторяемого чтения.

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

В Informix можно предотвратить конфликты между читающими и пишущими транзакциями, установив параметр конфигурации USELASTCOMMITTED (начиная с версии 11.1), при этом читающая транзакция будет получать последние подтвержденные данные

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

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

Поведение при различных уровнях изолированности

«+» — предотвращает, «–» — не предотвращает.

Уровень изоляции	Фантомная вставка	Неповторяющееся чтение	«Грязное» чтение	Потерянное обновление
SERIALIZABLE	+	+	+	+
REPEATABLE READ	–	+	+	+
READ COMMITTED	–	–	+	+
READ UNCOMMITTED	–	–	–	+

Ссылки

• Транзакции и блокировка транзакций