Пошук уроків, статей та іншого контенту
Дослідите, які операції належать до транзакції та як PostgreSQL поводиться з помилками й незавершеними змінами.
Транзакція — це група операцій, яку PostgreSQL розглядає як одну логічну зміну даних.
Транзакція має чіткі межі:
початок — команда BEGIN;
успішне завершення — команда COMMIT;
скасування змін — команда ROLLBACK.
Якщо транзакція завершується через COMMIT, її зміни стають постійними. Якщо виконується ROLLBACK, зміни, зроблені в межах транзакції, скасовуються.
BEGIN;
INSERT INTO accounts (owner, balance)
VALUES ('Олена', 1000);
UPDATE accounts
SET balance = balance - 100
WHERE owner = 'Олена';
COMMIT;У цьому прикладі обидві операції належать одній транзакції. Після COMMIT результат збережено.
У клієнтах PostgreSQL, наприклад у psql, зазвичай увімкнено режим автоматичної фіксації — autocommit.
У такому режимі кожна окрема команда автоматично є транзакцією:
INSERT INTO accounts (owner, balance)
VALUES ('Іван', 500);Ця команда приблизно еквівалентна такій послідовності:
BEGIN;
INSERT INTO accounts (owner, balance)
VALUES ('Іван', 500);
COMMIT;Якщо потрібно об'єднати кілька операцій в одну транзакцію, використовуйте BEGIN:
BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE owner = 'Олена';
UPDATE accounts
SET balance = balance + 200
WHERE owner = 'Іван';
COMMIT;Тепер або обидві операції будуть зафіксовані, або обидві можна буде скасувати.
Поки транзакція не завершена, її зміни:
видимі поточному підключенню;
не є остаточно збереженими;
зазвичай не видимі іншим підключенням;
можуть бути скасовані командою ROLLBACK.
Приклад:
BEGIN;
INSERT INTO accounts (owner, balance)
VALUES ('Марія', 750);
SELECT owner, balance
FROM accounts
WHERE owner = 'Марія';
ROLLBACK;
SELECT owner, balance
FROM accounts
WHERE owner = 'Марія';Перший SELECT побачить рядок, оскільки він виконується в тій самій транзакції.
Після ROLLBACK вставлений рядок зникає. Другий SELECT його не поверне.
Щоб побачити різницю між поточним і іншим підключенням:
Відкрийте два вікна psql.
У першому виконайте:
BEGIN;
INSERT INTO accounts (owner, balance)
VALUES ('Петро', 300);
SELECT *
FROM accounts
WHERE owner = 'Петро';У другому підключенні виконайте:
SELECT *
FROM accounts
WHERE owner = 'Петро';Перше підключення побачить незавершену вставку. Друге зазвичай не побачить її, доки в першому підключенні не буде виконано:
COMMIT;Якщо замість цього виконати:
ROLLBACK;рядок не стане доступним для інших підключень.
До однієї транзакції можуть належати різні операції:
INSERT, UPDATE, DELETE;
SELECT, якщо важливо виконувати кілька перевірок в одному контексті;
зміни структури таблиць, наприклад CREATE TABLE або ALTER TABLE;
операції, які блокують рядки або об'єкти бази даних.
Наприклад, створення таблиці також можна виконати всередині транзакції:
BEGIN;
CREATE TABLE transaction_demo (
id integer PRIMARY KEY,
description text
);
ROLLBACK;Після ROLLBACK таблиця transaction_demo не залишиться в базі.
Більшість звичайних операцій зі схемою в PostgreSQL підтримують транзакційність. Однак окремі операції мають спеціальні обмеження, тому для початку важливо запам'ятати основне правило: перевіряйте документацію конкретної команди, якщо вона змінює структуру або стан бази.
Команда ROLLBACK скасовує всі зміни від початку поточної транзакції:
BEGIN;
INSERT INTO accounts (owner, balance)
VALUES ('Софія', 1200);
UPDATE accounts
SET balance = balance + 300
WHERE owner = 'Софія';
ROLLBACK;У цьому випадку скасовуються і INSERT, і UPDATE.
ROLLBACK корисний, коли:
перевірка даних показала помилку;
одна з операцій не вдалася;
користувач скасував дію;
застосунок не зміг завершити весь сценарій;
потрібно безпечно протестувати зміни.
Якщо команда всередині транзакції завершується помилкою, PostgreSQL не продовжує виконувати наступні команди цієї транзакції.
Розглянемо приклад із порушенням унікальності:
BEGIN;
INSERT INTO accounts (id, owner, balance)
VALUES (1, 'Олена', 1000);
INSERT INTO accounts (id, owner, balance)
VALUES (1, 'Іван', 500);
SELECT *
FROM accounts;
COMMIT;Друга команда INSERT завершиться помилкою, тому що значення id = 1 уже існує.
Після цього транзакція переходить у стан помилки. Наступний SELECT також не буде виконано. PostgreSQL повідомить, що поточна транзакція перервана і команди ігноруються до завершення транзакції.
Щоб вийти з такого стану, потрібно виконати:
ROLLBACK;Після ROLLBACK можна почати нову транзакцію.
-- Підготовка даних виконується поза демонстраційною транзакцією
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
owner text NOT NULL UNIQUE,
balance numeric(12, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts (owner, balance)
VALUES ('Олена', 1000);
BEGIN;
-- Ця команда успішна
INSERT INTO accounts (owner, balance)
VALUES ('Іван', 500);
-- Ця команда викликає помилку: owner має бути унікальним
INSERT INTO accounts (owner, balance)
VALUES ('Олена', 700);
-- До цього місця PostgreSQL не дійде в межах цієї транзакції
SELECT *
FROM accounts;
-- Скасовуємо всю транзакцію, включно з вставкою Івана
ROLLBACK;
-- Іван не буде збережений
SELECT *
FROM accounts;У psql після помилки можна побачити запрошення, яке сигналізує про перервану транзакцію. Не намагайтеся продовжувати роботу звичайними SQL-командами — спочатку виконайте ROLLBACK.
Іноді потрібно скасувати лише частину транзакції, а не всю транзакцію. Для цього використовують точку збереження — SAVEPOINT.
BEGIN;
INSERT INTO accounts (owner, balance)
VALUES ('Марія', 750);
SAVEPOINT before_second_insert;
INSERT INTO accounts (owner, balance)
VALUES ('Петро', 300);
ROLLBACK TO SAVEPOINT before_second_insert;
COMMIT;Після ROLLBACK TO SAVEPOINT скасовуються операції після точки збереження. Вставка Марії залишається, а вставка Петра скасовується.
Точка збереження також допомагає відновитися після помилки в межах більшої транзакції:
BEGIN;
INSERT INTO accounts (owner, balance)
VALUES ('Наталя', 900);
SAVEPOINT before_risky_operation;
-- Якщо наступна операція завершиться помилкою,
-- можна повернутися до цієї точки
INSERT INTO accounts (owner, balance)
VALUES ('Олена', 400);
ROLLBACK TO SAVEPOINT before_risky_operation;
-- Транзакція все ще активна
INSERT INTO accounts (owner, balance)
VALUES ('Петро', 400);
COMMIT;У прикладі помилкова вставка скасовується, але вставка Наталі та Петра фіксується.
Значення, які PostgreSQL отримує з послідовностей, наприклад через nextval, не повертаються назад після ROLLBACK.
Тому після скасування транзакції в ідентифікаторах можуть залишатися пропуски. Це нормальна поведінка і не означає, що дані пошкоджено.
BEGIN;
INSERT INTO accounts (owner, balance)
VALUES ('Оксана', 600);
ROLLBACK;
INSERT INTO accounts (owner, balance)
VALUES ('Леся', 650);
SELECT *
FROM accounts;Якщо id створюється автоматично, ідентифікатор, використаний для скасованої вставки, може більше не повторитися.
Автоматичні ідентифікатори призначені для унікальності, а не для гарантування послідовності без пропусків.
Якщо застосунок відкрив транзакцію, але не виконав ні COMMIT, ні ROLLBACK, транзакція залишається відкритою.
Це може призвести до проблем:
зміни не стають видимими іншим підключенням;
утримуються блокування;
інші запити можуть чекати;
з'єднання зайняте незавершеною роботою.
Тому застосунок повинен гарантовано завершувати транзакцію:
COMMIT після успішного виконання всіх операцій;
ROLLBACK, якщо сталася помилка.
Типовий алгоритм:
почати транзакцію
спробувати виконати всі операції
якщо все успішно:
зафіксувати транзакцію
якщо сталася помилка:
скасувати транзакціюПісля помилки PostgreSQL не продовжує нормальне виконання транзакції.
Неправильно:
BEGIN;
-- Тут виникла помилка
INSERT INTO accounts (owner, balance)
VALUES ('Олена', 100);
-- Ця команда не виправить стан транзакції
UPDATE accounts
SET balance = balance + 100
WHERE owner = 'Петро';Правильно:
ROLLBACK;
BEGIN;
UPDATE accounts
SET balance = balance + 100
WHERE owner = 'Петро';
COMMIT;SELECT відкочує зміниSELECT лише читає дані. Він не скасовує попередні операції.
Щоб скасувати транзакцію, потрібно явно виконати:
ROLLBACK;COMMIT після невдалого запитуЯкщо транзакція вже перебуває у стані помилки, спочатку слід виконати ROLLBACK або повернутися до відповідного SAVEPOINT.
Не можна вважати, що наступний COMMIT автоматично виправить помилкову транзакцію.
COMMITЯкщо виконати BEGIN і завершити сесію без COMMIT, незбережені зміни не повинні залишитися зафіксованими. Проте в реальному застосунку не варто покладатися на закриття з'єднання. Транзакцію потрібно завершувати явно.
Якщо переказ складається зі зняття коштів і їх зарахування, небезпечно виконувати ці дії як незалежні транзакції:
-- Небезпечна схема
UPDATE accounts
SET balance = balance - 100
WHERE owner = 'Олена';
-- Між двома операціями може статися помилка
UPDATE accounts
SET balance = balance + 100
WHERE owner = 'Іван';Краще об'єднати їх:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE owner = 'Олена';
UPDATE accounts
SET balance = balance + 100
WHERE owner = 'Іван';
COMMIT;Тоді можна скасувати обидві зміни, якщо друга операція не вдалася.
Транзакція об'єднує кілька операцій в одну логічну зміну.
BEGIN починає явну транзакцію.
COMMIT фіксує всі її зміни.
ROLLBACK скасовує всі зміни від початку транзакції.
До COMMIT зміни зазвичай видимі лише поточному підключенню.
Помилка переводить транзакцію в стан переривання.
Після помилки потрібно виконати ROLLBACK або використати ROLLBACK TO SAVEPOINT.
Незавершена транзакція може утримувати ресурси та блокування.
Пов'язані операції, які мають виконатися разом, потрібно розміщувати в одній транзакції.