Пошук уроків, статей та іншого контенту
Навчитеся запускати транзакції, фіксувати зміни та скасовувати їх у PostgreSQL.
Транзакція — це група SQL-операцій, яку PostgreSQL обробляє як одну логічну дію.
Транзакція має завершитися одним із двох способів:
COMMIT — успішно зберегти всі зміни;
ROLLBACK — скасувати всі зміни, виконані в межах транзакції.
Це дає змогу не залишити базу даних у неповністю зміненому стані. Наприклад, під час переказу коштів потрібно одночасно:
зменшити баланс одного рахунку;
збільшити баланс іншого рахунку.
Якщо друга операція не виконалася, першу теж потрібно скасувати.
Для явного запуску транзакції використовується команда:
BEGIN;Також можна використовувати повніший запис:
START TRANSACTION;У PostgreSQL ці команди мають однакове призначення.
Після BEGIN усі наступні зміни належать до цієї транзакції, доки не буде виконано COMMIT або ROLLBACK.
Команда COMMIT завершує транзакцію та зберігає всі її зміни:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
COMMIT;Після виконання COMMIT зміна балансу стає постійною для бази даних. Звичайною командою ROLLBACK скасувати її вже не можна.
Команда ROLLBACK завершує транзакцію та скасовує всі зміни, зроблені після BEGIN:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
ROLLBACK;Після ROLLBACK баланс повернеться до значення, яке був до початку транзакції.
Нижче наведений повний приклад. Він створює таблицю рахунків, спочатку демонструє скасування зміни, а потім — її фіксацію.
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
id integer PRIMARY KEY,
owner text NOT NULL,
balance numeric(12, 2) NOT NULL
);
INSERT INTO accounts (id, owner, balance)
VALUES
(1, 'Олена', 1000.00),
(2, 'Андрій', 500.00);
SELECT * FROM accounts ORDER BY id;
BEGIN;
UPDATE accounts
SET balance = balance - 200.00
WHERE id = 1;
UPDATE accounts
SET balance = balance + 200.00
WHERE id = 2;
SELECT * FROM accounts ORDER BY id;
ROLLBACK;
SELECT * FROM accounts ORDER BY id;
BEGIN;
UPDATE accounts
SET balance = balance - 200.00
WHERE id = 1;
UPDATE accounts
SET balance = balance + 200.00
WHERE id = 2;
COMMIT;
SELECT * FROM accounts ORDER BY id;Що відбувається в цьому прикладі:
Створюється таблиця accounts із двома рахунками.
Починається перша транзакція.
З рахунку Олени переказується 200,00 на рахунок Андрія.
ROLLBACK скасовує обидві зміни.
Починається друга транзакція.
Виконується такий самий переказ.
COMMIT зберігає зміни.
Після першого ROLLBACK баланси знову дорівнюють:
Олена 1000.00
Андрій 500.00Після другого COMMIT вони стають такими:
Олена 800.00
Андрій 700.00Якщо одна з команд у транзакції завершується помилкою, PostgreSQL переводить транзакцію в стан помилки. Наступні SQL-команди в такій транзакції не виконуються, доки транзакцію не завершити через ROLLBACK.
BEGIN;
INSERT INTO accounts (id, owner, balance)
VALUES (3, 'Марія', 700.00);
INSERT INTO accounts (id, owner, balance)
VALUES (1, 'Інший користувач', 300.00);
ROLLBACK;Друга команда спричиняє помилку, оскільки id = 1 уже існує. ROLLBACK скасовує всю транзакцію, тому рахунок Марії також не буде доданий.
Це важлива властивість транзакцій: або зберігаються всі зміни, або не зберігається жодна з них.
Багато клієнтів PostgreSQL, зокрема psql, за замовчуванням працюють у режимі автоматичної фіксації.
У такому режимі окрема SQL-команда автоматично стає окремою транзакцією:
INSERT INTO accounts (id, owner, balance)
VALUES (3, 'Марія', 700.00);Якщо команда виконалася успішно, зміна автоматично фіксується. Якщо сталася помилка, команда скасовується.
Щоб об’єднати кілька команд в одну транзакцію, потрібно явно використати BEGIN:
BEGIN;
INSERT INTO accounts (id, owner, balance)
VALUES (3, 'Марія', 700.00);
UPDATE accounts
SET balance = balance + 50.00
WHERE id = 1;
COMMIT;Тепер обидві зміни будуть частиною однієї транзакції.
Транзакція починається з:
BEGIN;і завершується однією з команд:
COMMIT;або:
ROLLBACK;Після завершення транзакції наступна SQL-команда в автоматичному режимі виконуватиметься в новій транзакції.
Не слід залишати транзакцію відкритою надовго. Поки транзакція триває, PostgreSQL може утримувати блокування та інші ресурси. Завжди завершуйте транзакцію після виконання потрібних операцій.
Якщо виконати BEGIN і зміни, але не виконати COMMIT, зміни ще не будуть остаточно зафіксовані.
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;У такому випадку потрібно свідомо вирішити, що робити далі:
COMMIT;або:
ROLLBACK;ROLLBACK працює лише для поточної незавершеної транзакції:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
COMMIT;
ROLLBACK;У цьому прикладі другий ROLLBACK не скасує оновлення, оскільки воно вже було збережене через COMMIT.
Якщо кілька команд разом утворюють одну логічну дію, їх потрібно помістити в одну транзакцію:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;
COMMIT;Без транзакції може зберегтися лише перша зміна, якщо друга завершиться помилкою.
Після помилки транзакцію потрібно завершити через ROLLBACK, а вже потім виконувати нові команди:
BEGIN;
INSERT INTO accounts (id, owner, balance)
VALUES (1, 'Дубль', 100.00);
ROLLBACK;
SELECT * FROM accounts;Транзакція об’єднує кілька SQL-операцій в одну логічну дію.
BEGIN запускає транзакцію.
COMMIT зберігає всі зміни транзакції.
ROLLBACK скасовує всі незбережені зміни транзакції.
Якщо одна команда в транзакції завершується помилкою, зазвичай потрібно виконати ROLLBACK.
Пов’язані зміни, які мають виконатися разом, слід розміщувати в одній транзакції.
Не залишайте транзакції відкритими без потреби.