Рівні ізольованості транзакцій
Рівень ізольованості транзакцій — значення, що задає рівень, при якому в транзакції дозволяються неузгоджені дані, тобто ступінь ізольованості однієї транзакції від іншої. Більш високий рівень ізольованості підвищує точність даних, але при цьому може знижуватись кількість транзакцій, що виконуються паралельно. З іншого боку, більш низький рівень ізольованості дозволяє виконувати більше паралельних транзакцій, але знижує точність даних.
При паралельному виконанні транзакцій можливі такі проблеми:
- втрачене оновлення (англ. 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;
|
В транзакції 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 визначає рівні ізоляції, установка яких запобігає деяким конфліктним ситуаціям. Введені наступні чотири рівні ізоляції:
Найбільш високий рівень ізольованості; транзакції повністю ізолюються одна від одної. На цьому рівні результати паралельного виконання транзакцій для бази даних у більшості випадків можна вважати такими, що збігаються з послідовним виконанням тих же транзакцій (по черзі в будь-якому порядку).
Рівень, при якому читання одного і того ж рядку чи рядків в транзакції дає однаковий результат. (Поки транзакція не закінчена, ніякі інші транзакції не можуть змінити ці дані).
Прийнятий за замовчуванням рівень для Microsoft SQL Server. Закінчене читання, при якому відсутнє «брудне» читання (тобто, читання одним користувачем даних, що не були зафіксовані в БД командою COMMIT). Проте, в процесі роботи однієї транзакції інша може бути успішно закінчена, і зроблені нею зміни зафіксовані. В підсумку, перша транзакція буде працювати з іншим набором даних. Це проблема неповторюваного читання.
В Oracle блокування на читання немає, замість цього транзакція, що «читає», отримує ту версію даних, яка була актуальна в базі до початку тієї, що «пише».
В Informix можна запобігти конфліктам між транзакціями, що читають та пишуть, встановивши параметр конфігурації USELASTCOMMITTED (починаючи з версії 11.1), при цьому транзакція, що читає, буде отримувати останні підтверджені дані[1]
Найнижчий рівень ізоляції, який відповідає рівню 0. Він гарантує тільки відсутність втрачених оновлень[2]. Якщо декілька транзакцій одночасно намагались змінювати один і той же рядок, то в кінцевому варіанті рядок буде мати значення, визначений останньою успішно виконаною транзакцією.
«+» — запобігає, «-» — не запобігає.
| Рівень ізоляції | Фантомне читання | Неповторюване читання | «Брудне» читання | Втрачене оновлення[3] |
|---|---|---|---|---|
| SERIALIZABLE | + | + | + | + |
| REPEATABLE READ | - | + | + | + |
| READ COMMITTED | - | - | + | + |
| READ UNCOMMITTED | - | - | - | + |