Пошук уроків, статей та іншого контенту
Дослідите стандартний рівень ізоляції PostgreSQL і правила видимості зафіксованих даних.
Read Committed — стандартний рівень ізоляції транзакцій у PostgreSQL.
Він гарантує:
транзакція не читає незaфіксовані зміни інших транзакцій;
кожна SQL-команда бачить лише дані, зафіксовані до початку цієї команди;
зміни, виконані поточною транзакцією, видимі їй самій;
різні команди в межах однієї транзакції можуть бачити різний стан бази даних.
Останній пункт є ключовим: у Read Committed знімок даних створюється для кожної команди окремо, а не один раз на всю транзакцію.
Перевірити поточний рівень ізоляції можна так:
SHOW transaction_isolation;Типовий результат:
read committedДля окремої транзакції рівень можна вказати явно:
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Запити транзакції
COMMIT;SET TRANSACTION потрібно виконати до першої команди, яка звертається до даних у цій транзакції.
Також рівень можна встановити для поточного з'єднання:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;Уявімо, що транзакція виконує запит:
SELECT balance
FROM accounts
WHERE id = 1;Запит бачить:
усі зміни, зафіксовані до початку виконання цього SELECT;
зміни, виконані поточною транзакцією, навіть якщо вона ще не завершилася;
не бачить незaфіксовані зміни інших транзакцій.
Якщо інша транзакція зафіксує зміни вже після початку SELECT, поточний запит їх не побачить. Але наступний SELECT побачить, оскільки він отримає новий знімок даних.
Створимо тестову таблицю:
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
id integer PRIMARY KEY,
owner text NOT NULL,
balance integer NOT NULL
);
INSERT INTO accounts (id, owner, balance)
VALUES (1, 'Anna', 100);Відкрийте два підключення до PostgreSQL. Позначимо їх як Сесія A і Сесія B.
У сесії A виконайте:
BEGIN;
UPDATE accounts
SET balance = balance + 50
WHERE id = 1;На цьому етапі зміна ще не зафіксована.
У сесії B виконайте:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 1;Результат:
balance
---------
100Сесія B бачить попереднє зафіксоване значення 100, а не незaфіксоване значення 150.
Це означає, що Read Committed не допускає «брудного читання».
У сесії A:
COMMIT;Тепер зміна стала видимою для інших транзакцій.
Якщо в сесії B виконати новий запит:
SELECT balance
FROM accounts
WHERE id = 1;Результат буде:
balance
---------
150Попередній SELECT у сесії B побачив 100, тому що був виконаний до COMMIT у сесії A. Новий SELECT отримав новий знімок і побачив 150.
Завершіть транзакцію в сесії B:
COMMIT;У межах однієї транзакції результати двох однакових запитів можуть відрізнятися.
У сесії A:
BEGIN;
SELECT count(*)
FROM accounts;В іншій сесії вставте рядок і зафіксуйте його:
BEGIN;
INSERT INTO accounts (id, owner, balance)
VALUES (2, 'Oleh', 200);
COMMIT;Тепер знову виконайте в сесії A:
SELECT count(*)
FROM accounts;Другий SELECT побачить новий рядок, хоча транзакція в сесії A ще не завершилася.
Це називають неповторюваним читанням: той самий запит у межах однієї транзакції може повернути різний результат.
У Read Committed це нормальна і дозволена поведінка.
UPDATE і DELETEДля команд, які змінюють дані, PostgreSQL використовує дещо складніше правило.
Розглянемо ситуацію:
транзакція A почала UPDATE;
транзакція B намагається змінити той самий рядок;
транзакція B чекає, поки транзакція A завершиться;
після цього PostgreSQL перевіряє рядок ще раз.
Приклад.
У сесії A:
BEGIN;
UPDATE accounts
SET balance = balance + 100
WHERE id = 1;Не виконуйте COMMIT одразу.
У сесії B:
BEGIN;
UPDATE accounts
SET balance = balance - 30
WHERE id = 1;Ця команда чекатиме завершення транзакції A, оскільки обидві транзакції змінюють один рядок.
Тепер у сесії A:
COMMIT;Після цього команда в сесії B продовжить виконання. PostgreSQL застосує її до актуальної версії рядка:
100 + 100 - 30 = 170Завершіть транзакцію в сесії B:
COMMIT;Перевірка:
SELECT id, owner, balance
FROM accounts
WHERE id = 1;Результат:
id | owner | balance
----+-------+---------
1 | Anna | 170Якщо після очікування умова WHERE більше не виконується, PostgreSQL не змінює рядок.
Наприклад:
UPDATE accounts
SET balance = balance - 100
WHERE id = 1
AND balance >= 100;Якщо інша транзакція перед цим зменшила balance до значення меншого за 100, PostgreSQL повторно перевірить умову. Такий рядок не буде оновлено.
Звичайний SELECT не чекає блокування рядка для читання. Він може повернути останню доступну зафіксовану версію.
Для явного блокування рядків використовують SELECT ... FOR UPDATE:
BEGIN;
SELECT id, owner, balance
FROM accounts
WHERE id = 1
FOR UPDATE;Якщо інша транзакція вже змінює цей рядок, SELECT FOR UPDATE чекатиме її завершення. Після цього PostgreSQL прочитає актуальну версію рядка.
Це корисно, коли рядок потрібно прочитати, перевірити й потім змінити в межах однієї транзакції.
Наприклад:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;
UPDATE accounts
SET balance = balance - 20
WHERE id = 1;
COMMIT;Блокування діє до завершення транзакції через COMMIT або ROLLBACK.
Рівень Read Committed запобігає:
читанню незaфіксованих даних;
читанню частково виконаної зміни іншої транзакції;
використанню даних транзакції, яка завершилася через ROLLBACK.
Наприклад, якщо транзакція змінила кілька рядків, але потім виконала ROLLBACK, інші транзакції не побачать жодної з цих змін.
Read Committed не створює єдиного незмінного знімка для всієї транзакції.
Тому можливі:
неповторювані читання — повторний запит бачить інше значення;
фантомні рядки — повторний запит бачить нові рядки, додані іншою транзакцією;
змішане читання — різні запити в одній транзакції бачать різні моменти часу.
Наприклад, така транзакція:
BEGIN;
SELECT sum(balance)
FROM accounts;
-- Інша транзакція змінює дані та виконує COMMIT
SELECT sum(balance)
FROM accounts;
COMMIT;може отримати різні суми.
Якщо бізнес-логіка потребує, щоб усі запити транзакції бачили один і той самий стан даних, Read Committed може бути недостатнім. У такому випадку розглядають інші рівні ізоляції або явні блокування.
Помилкове припущення:
Якщо транзакція вже почалася, усі її запити бачать стан бази на момент
BEGIN.
У Read Committed це не так. Знімок створюється на початку кожної команди.
PostgreSQL не дозволяє отримати брудне читання, навіть якщо явно вказати:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;У PostgreSQL цей режим фактично поводиться як READ COMMITTED.
SELECT перед критичною зміноюТакий код може мати проблему:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 1;
-- На цьому місці інша транзакція може змінити balance
UPDATE accounts
SET balance = balance - 20
WHERE id = 1;
COMMIT;Якщо потрібно гарантувати, що рядок не зміниться між читанням і наступною операцією, використовуйте блокування:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;
UPDATE accounts
SET balance = balance - 20
WHERE id = 1;
COMMIT;UPDATE, DELETE і SELECT FOR UPDATE можуть чекати завершення іншої транзакції. Транзакції потрібно завершувати через COMMIT або ROLLBACK, інакше блокування залишатимуться активними.
Read Committed — стандартний рівень ізоляції PostgreSQL.
Незaфіксовані зміни інших транзакцій не видимі.
Кожна SQL-команда отримує власний знімок даних.
Повторний запит у тій самій транзакції може побачити нові зафіксовані зміни.
UPDATE і DELETE можуть чекати блокування та повторно перевіряти умову WHERE.
SELECT FOR UPDATE дає змогу заблокувати рядки для подальшої безпечної зміни.
Read Committed не гарантує повторюваного читання або єдиного стану даних для всієї транзакції.