Транзакції в sql та Transaction isolation levels (рівні ізоляції транзакцій)

Dmytro Tus

Full Stack Web developer

Transaction isolation levels

Transaction isolation levels (рівні ізоляції транзакцій) визначають, як транзакції взаємодіють одна з одною та які побічні ефекти можуть виникати при їх паралельному виконанні. Рівні ізоляції допомагають балансувати між продуктивністю системи та вимогами до точності і консистентності даних. У стандарті SQL визначені чотири рівні ізоляції:

1. Read Uncommitted (Читання незавершених даних):

   • Найнижчий рівень ізоляції.
   • Транзакція може бачити незавершені зміни, зроблені іншими транзакціями.
   • Можливі побічні ефекти:
     • Dirty Read: Читання незавершених змін іншої транзакції.

2. Read Committed (Читання завершених даних):

   • Транзакція бачить тільки ті зміни, які були завершені іншими транзакціями (після COMMIT).
   • Транзакція не бачить незавершених змін.
   • Можливі побічні ефекти:
     • Non-repeatable Read: Повторне читання того ж рядка в межах однієї транзакції може повернути різні результати, якщо інша транзакція оновить цей рядок і завершить його зміни.

3. Repeatable Read (Повторюване читання):

   • Транзакція бачить ті ж дані при кожному читанні, навіть якщо інші транзакції змінюють ці дані.
   • Рядки, які були прочитані на початку транзакції, залишаються незмінними для цієї транзакції.
   • Можливі побічні ефекти:
     • Phantom Read: Вставка нових рядків іншою транзакцією може призвести до того, що повторне читання набору рядків поверне додаткові результати.

4. Serializable (Серіалізоване):

   • Найвищий рівень ізоляції.
   • Транзакції виконуються послідовно, якби вони виконувалися одна за одною, що виключає всі побічні ефекти паралельного виконання.
   • Всі проблеми, такі як dirty read, non-repeatable read і phantom read, виключені.
   • Найнижча продуктивність через значні накладні витрати на блокування ресурсів.

Ось приклад, як встановити різні рівні ізоляції для транзакцій в MySQL:

-- Встановити рівень ізоляції Read Uncommitted
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Встановити рівень ізоляції Read Committed
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Встановити рівень ізоляції Repeatable Read (за замовчуванням в MySQL)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Встановити рівень ізоляції Serializable
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Щоб дізнатись поточний рівень ізоляції:

SELECT @@transaction_isolation;

Приклади

Read Uncommitted

Наведемо приклад використання рівня ізоляції Read Uncommitted, щоб показати, як транзакція може читати незавершені дані (dirty reads).

Припустимо, у нас є дві транзакції, T1 і T2, що виконуються одночасно.

• Таблиця users:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    amount INT
);

INSERT INTO users (id, name, amount) VALUES (1, 'Jon', 100);
INSERT INTO users (id, name, amount) VALUES (2, 'Jane', 50);

• Транзакція T1:

START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE users SET amount = amount - 100 WHERE id = 1;
-- Не робимо COMMIT або ROLLBACK, залишаємо транзакцію незавершеною

• Транзакція T2:

START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Читаємо дані до завершення T1
SELECT * FROM users WHERE id = 1;

Давайте розглянемо, що відбувається:

• Транзакція T1 починається і зменшує amount у користувача з id = 1 на 100, але не завершує транзакцію (COMMIT або ROLLBACK не виконується).
• Транзакція T2, працюючи на рівні ізоляції Read Uncommitted, читає значення amount для користувача з id = 1.

На рівні ізоляції Read Uncommitted, T2 може побачити незавершені зміни, зроблені T1. Отже, запит у T2 може повернути amount = 0 для користувача з id = 1, хоча ці зміни ще не були підтверджені.

 

Read Сommitted

Наведемо приклад використання рівня ізоляції Read Committed, щоб показати, як транзакція може бачити лише завершені зміни інших транзакцій.

Припустимо, у нас є дві транзакції, T1 і T2, що виконуються одночасно.

1. Таблиця users:

   CREATE TABLE users (
       id INT PRIMARY KEY,
       name VARCHAR(100),
       amount INT
   );
   
   INSERT INTO users (id, name, amount) VALUES (1, 'Jon', 100);
   INSERT INTO users (id, name, amount) VALUES (2, 'Jane', 50);
   

   
   

2. Транзакція T1:
   

   START TRANSACTION;
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
   
   UPDATE users SET amount = amount - 100 WHERE id = 1;
   -- Не робимо COMMIT або ROLLBACK, залишаємо транзакцію незавершеною
   

   
   

3. Транзакція T2:

START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Читаємо дані до завершення T1
SELECT * FROM users WHERE id = 1;

Давайте розглянемо, що відбувається:

• Транзакція T1 починається і зменшує amount у користувача з id = 1 на 100, але не завершує транзакцію (COMMIT або ROLLBACK не виконується).
• Транзакція T2, працюючи на рівні ізоляції Read Committed, читає значення amount для користувача з id = 1.

На рівні ізоляції Read Committed, T2 не побачить незавершених змін, зроблених T1. Отже, запит у T2 поверне amount = 100 для користувача з id = 1, оскільки зміни, зроблені T1, ще не підтверджені (COMMIT не було виконано).

Repeatable Read

Рівень ізоляції Repeatable Read забезпечує, що дані, прочитані однією транзакцією, залишатимуться незмінними протягом всієї її тривалості, навіть якщо інші транзакції змінюють ці дані. Це захищає від проблеми "неповторюваних читань" (non-repeatable read), але не гарантує захисту від "привидів" (phantom reads), коли нові рядки можуть бути додані іншими транзакціями.

1. Початок T1: Транзакція T1 починається з рівнем ізоляції Repeatable Read і читає значення amount для користувача з id = 1. Значення становить 100.
2. Початок T2: Транзакція T2 починається і змінює значення amount для користувача з id = 1, зменшуючи його на 50. Після цього транзакція T2 завершується (COMMIT).
3. Повторне читання в T1: Навіть після завершення T2, повторне читання значення amount для користувача з id = 1 в T1 повертає 100, оскільки рівень ізоляції Repeatable Read гарантує, що дані, прочитані на початку транзакції, залишатимуться незмінними протягом всієї її тривалості.

Це гарантує, що T1 бачить консистентний набір даних протягом всієї транзакції, незважаючи на зміни, зроблені іншими транзакціями.

Сценарій з привидами 

1. Початок T1: Транзакція T1 починається з рівнем ізоляції Repeatable Read і читає всіх користувачів, у яких amount > 25. На цей момент у таблиці є два рядки, які задовольняють цю умову: Jon і Jane.
2. Початок T2: Транзакція T2 починається, додає нового користувача з amount = 30 і завершується (COMMIT).
3. Повторне читання в T1: Після завершення T2, T1 повторно виконує той самий запит SELECT * FROM users WHERE amount > 25 і бачить нового користувача (Jack, 30). Це є прикладом "привида", оскільки новий рядок з'явився в результаті запиту під час виконання транзакції T1.

Таким чином, хоча рівень ізоляції Repeatable Read гарантує, що дані, прочитані на початку транзакції, залишатимуться незмінними протягом всієї її тривалості, він не захищає від додавання нових рядків іншими транзакціями, що може призвести до появи "привидів" під час повторних запитів.

Приклад рівня ізоляції Serializable

Рівень ізоляції Serializable є найвищим рівнем ізоляції транзакцій, що забезпечує повну ізоляцію між транзакціями. Він гарантує, що транзакції виконуються послідовно, якби вони виконувалися одна за одною, виключаючи всі можливі аномалії паралельного виконання, такі як dirty reads, non-repeatable reads та phantom reads.

Як це працює

При рівні ізоляції Serializable база даних забезпечує, що:

• Dirty Reads: Не відбуваються. Транзакції не можуть бачити незавершені зміни інших транзакцій.
• Non-repeatable Reads: Не відбуваються. Якщо транзакція читає дані кілька разів, вона завжди отримає ті самі результати.
• Phantom Reads: Не відбуваються. Жодна нова транзакція не може вставити рядки, які відповідатимуть умовам запиту, виконаного іншою транзакцією, доки ця транзакція не завершиться.

Це досягається за допомогою блокувань, які можуть значно зменшити продуктивність, оскільки блокуються не тільки окремі рядки, але й цілі діапазони значень.

 

1. Початок T1: Транзакція T1 починається з рівнем ізоляції Serializable і читає всіх користувачів, у яких amount > 25. Вона отримує два рядки: Jon і Jane.
2. Початок T2: Транзакція T2 намагається додати нового користувача з amount = 30, але ця операція блокується, оскільки T1 ще не завершилася.
3. Завершення T1: Транзакція T1 завершується (COMMIT), звільняючи блокування.
4. Завершення T2: Транзакція T2 продовжує виконання і завершує вставку нового рядка після того, як T1 завершиться.

Рівень ізоляції Serializable забезпечує, що навіть додавання нових рядків не змінює результати запитів, виконаних іншими транзакціями. Це досягається шляхом блокування необхідних ресурсів, що може впливати на продуктивність, але гарантує повну ізоляцію транзакцій.

---