Пошук уроків, статей та іншого контенту
Зрозумієте, як рівень ізоляції визначає видимість даних між паралельними транзакціями.
Коли кілька транзакцій працюють одночасно, вони можуть читати й змінювати одні й ті самі рядки. Рівень ізоляції визначає, які зміни інших транзакцій поточна транзакція може бачити.
У PostgreSQL транзакції використовують механізм MVCC — багатоверсійність рядків. Замість блокування кожного читання PostgreSQL зберігає версії рядків і визначає, чи має конкретна транзакція бачити кожну з них.
Ізоляція потрібна, щоб керувати такими ситуаціями:
транзакція читає незбережені зміни іншої транзакції;
один і той самий запит у межах транзакції повертає різні результати;
дві транзакції одночасно змінюють пов’язані дані;
результат залежить від порядку виконання паралельних операцій.
PostgreSQL підтримує такі рівні:
READ UNCOMMITTED;
READ COMMITTED;
REPEATABLE READ;
SERIALIZABLE.
У PostgreSQL READ UNCOMMITTED фактично працює як READ COMMITTED. Брудні читання PostgreSQL не дозволяє навіть на цьому рівні.
За замовчуванням використовується:
READ COMMITTEDРівень ізоляції можна встановити під час початку транзакції:
BEGIN ISOLATION LEVEL REPEATABLE READ;Або перевірити поточне значення:
SHOW transaction_isolation;Рівень ізоляції діє для всієї транзакції. Його не можна змінити після того, як транзакція вже виконала SQL-команду.
На рівні READ COMMITTED кожна SQL-команда бачить дані, які були зафіксовані до початку саме цієї команди.
Тому два SELECT в одній транзакції можуть побачити різні результати:
транзакція A виконує перший SELECT;
транзакція B змінює дані та виконує COMMIT;
транзакція A виконує другий SELECT;
другий запит транзакції A бачить зміни транзакції B.
Це називають неповторюваним читанням.
Створимо таблицю:
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
id integer PRIMARY KEY,
owner text NOT NULL,
balance integer NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts (id, owner, balance)
VALUES (1, 'Olena', 100);Відкрийте два підключення до PostgreSQL. Команди з позначкою Сесія A виконуйте в першому, а команди з позначкою Сесія B — у другому.
У сесії A:
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance
FROM accounts
WHERE id = 1;Результат:
balance
---------
100Не завершуючи транзакцію в сесії A, виконайте в сесії B:
BEGIN;
UPDATE accounts
SET balance = 150
WHERE id = 1;
COMMIT;Тепер повторіть запит у сесії A:
SELECT balance
FROM accounts
WHERE id = 1;
COMMIT;Результат другого запиту — 150. Перший і другий запити були виконані в межах однієї транзакції, але кожен отримав власний знімок даних.
UPDATE на READ COMMITTEDНа цьому рівні PostgreSQL не читає незбережені зміни іншої транзакції. Якщо рядок уже змінюється паралельною транзакцією, UPDATE може зачекати, доки та транзакція завершиться.
Після очікування PostgreSQL повторно перевіряє рядок і працює з актуальною зафіксованою версією. Це означає, що умова WHERE може бути перевірена вже після завершення іншої транзакції.
На рівні REPEATABLE READ транзакція працює зі знімком даних, створеним на початку першого SQL-запиту транзакції.
Усі наступні запити цієї транзакції бачать той самий знімок. Тому:
незбережені зміни інших транзакцій не видно;
повторне читання повертає той самий результат;
нові рядки, додані іншими транзакціями, не з’являються в наступних запитах;
конфліктні зміни можуть призвести до помилки серіалізації.
У сесії A:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance
FROM accounts
WHERE id = 1;Результат:
balance
---------
150У сесії B змініть баланс:
BEGIN;
UPDATE accounts
SET balance = 200
WHERE id = 1;
COMMIT;Знову виконайте запит у сесії A:
SELECT balance
FROM accounts
WHERE id = 1;
COMMIT;Сесія A продовжить бачити 150, хоча сесія B уже зафіксувала значення 200.
Транзакція на REPEATABLE READ має узгоджений знімок даних протягом усього свого виконання.
SERIALIZABLE — найсуворіший рівень ізоляції. PostgreSQL гарантує, що результат паралельного виконання транзакцій буде таким, ніби вони виконувалися послідовно одна за одною.
Для цього PostgreSQL відстежує залежності між транзакціями. Якщо виявлено небезпечну комбінацію операцій, одна з транзакцій може завершитися помилкою:
ERROR: could not serialize access due to read/write dependencies among transactionsЦе не означає, що PostgreSQL автоматично повторить транзакцію. Повторний запуск має виконати застосунок.
Припустімо, у лікарні завжди має залишатися щонайменше один черговий лікар. Дві транзакції можуть одночасно перевірити це правило та спробувати зняти з чергування різних лікарів.
Підготовка даних:
DROP TABLE IF EXISTS doctors;
CREATE TABLE doctors (
id integer PRIMARY KEY,
name text NOT NULL,
on_call boolean NOT NULL
);
INSERT INTO doctors (id, name, on_call)
VALUES
(1, 'Iryna', true),
(2, 'Andrii', true);У сесії A:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*)
FROM doctors
WHERE on_call = true;Результат:
count
-------
2Тепер у сесії B:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*)
FROM doctors
WHERE on_call = true;Сесія B також побачить 2.
Далі сесія A знімає з чергування першого лікаря:
UPDATE doctors
SET on_call = false
WHERE id = 1;
COMMIT;Сесія B намагається зняти з чергування другого:
UPDATE doctors
SET on_call = false
WHERE id = 2;
COMMIT;Одна з транзакцій може отримати помилку серіалізації. PostgreSQL не дозволить обом транзакціям успішно завершитися, якщо їх сукупний результат порушує логіку послідовного виконання.
У застосунку таку транзакцію потрібно повторити:
async function runSerializable(pool, operation) {
for (let attempt = 1; attempt <= 3; attempt += 1) {
const client = await pool.connect();
try {
await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
const result = await operation(client);
await client.query('COMMIT');
return result;
} catch (error) {
await client.query('ROLLBACK');
// Повторюємо транзакцію лише для помилки серіалізації.
if (error.code !== '40001' || attempt === 3) {
throw error;
}
} finally {
client.release();
}
}
}Код помилки 40001 означає serialization failure. Повторення має охоплювати всю транзакцію, а не лише окремий SQL-запит.
Підходить для більшості звичайних операцій:
кожен запит бачить останні зафіксовані дані;
два запити в одній транзакції можуть побачити різні результати;
транзакція зазвичай рідше блокується;
це стандартний рівень PostgreSQL.
Потрібен, коли всі читання в межах транзакції мають використовувати один знімок:
результати повторних читань стабільні;
нові зафіксовані зміни інших транзакцій не видно;
конфліктні оновлення можуть завершити транзакцію помилкою.
Потрібен для складних бізнес-правил, які мають залишатися коректними за паралельного виконання:
забезпечує найсильнішу гарантію узгодженості;
може частіше завершувати транзакції помилкою 40001;
застосунок повинен уміти повторювати транзакції.
Рівень ізоляції визначає видимість версій даних, але іноді потрібно явно повідомити PostgreSQL, що рядок буде змінено.
Для цього використовують SELECT ... FOR UPDATE:
BEGIN;
SELECT id, balance
FROM accounts
WHERE id = 1
FOR UPDATE;
UPDATE accounts
SET balance = balance - 20
WHERE id = 1;
COMMIT;FOR UPDATE блокує вибраний рядок для зміни іншими транзакціями до завершення поточної транзакції. Інша транзакція, яка намагатиметься змінити цей рядок, чекатиме завершення першої.
Це корисно, коли операція має послідовно:
прочитати рядок;
перевірити його значення;
змінити цей самий рядок.
Під час вибору рівня ізоляції потрібно враховувати:
чи можуть дані змінюватися між двома читаннями;
чи має вся транзакція бачити один знімок;
чи існують бізнес-правила між кількома рядками;
чи готовий застосунок повторювати транзакції після помилки серіалізації;
наскільки критичною є узгодженість порівняно з кількістю очікувань і повторів.
Зазвичай починають із READ COMMITTED. Якщо потрібен стабільний знімок для всієї транзакції, використовують REPEATABLE READ. Якщо паралельні транзакції не повинні створювати жодного результату, який неможливий за послідовного виконання, обирають SERIALIZABLE.
У READ COMMITTED знімок створюється для кожної команди. Тому два SELECT можуть повернути різні результати.
Якщо потрібен один знімок на всю транзакцію, використовуйте REPEATABLE READ або SERIALIZABLE.
PostgreSQL не показує незбережені зміни іншої транзакції. Навіть READ UNCOMMITTED у PostgreSQL поводиться як READ COMMITTED.
Помилка 40001 означає, що транзакція не може безпечно завершитися. Не слід просто повторювати окремий запит або ігнорувати помилку. Потрібно:
виконати ROLLBACK;
почати нову транзакцію;
повторити всі її операції.
Чим довше триває транзакція, тим довше вона утримує свої ресурси та тим вища ймовірність конфліктів. Транзакції варто робити короткими й не включати до них зайві операції.
Поведінку конкурентного доступу неможливо повноцінно перевірити одним послідовним набором команд. Для тестування потрібно відкрити щонайменше дві паралельні сесії та контролювати порядок виконання команд.
PostgreSQL використовує MVCC і визначає видимість даних за знімками транзакцій.
READ COMMITTED є типовим рівнем і створює новий знімок для кожної SQL-команди.
REPEATABLE READ зберігає один знімок протягом усієї транзакції.
SERIALIZABLE захищає від результатів, які неможливі за послідовного виконання, але може вимагати повтору транзакції.
Незбережені зміни інших транзакцій не видно на жодному рівні PostgreSQL.
Для операцій читання з подальшим оновленням можна використовувати SELECT ... FOR UPDATE.
Обраний рівень ізоляції має відповідати вимогам узгодженості та способу обробки конфліктів у застосунку.