Транзакция в PostgreSQL
Резюме: в данной инструкции Вы узнаете, как проводить транзакции в PostgreSQL, используя BEGIN
, COMMIT
и ROLLBACK
.
Что такое транзакция в базе данных?
Транзакция в базе данных - единый блок работы, состоящий из одной или нескольких операций.
Классическим примером транзакции является банковский перевод средств с одного счет на другой. Завершенная транзакция должна обеспечивать баланс между счетами отправителя и получателя. Это означает то, что если отправитель отправляет сумму X
, получатель получает сумму X
, не больше и не меньше.
Транзакция в PostgreSQL атомарная, согласованная, изолированная и устойчивая. Эти характеристики относятся к понятию ACID:
- Атомарность (Atomicity) Атомарность гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Будут либо выполнены все её подоперации, либо не выполнено ни одной;
- Согласованность (Consistency) гарантирует то, что любые изменения данных в базе данных обоснованные и следуют предопределенным правилам;
- Изоляция (Isolation) определяет то, насколько целостность транзакции вина другим транзакциям;
- Устойчивость (Durability) гарантирует то, что все проведенные транзакции будут сохранены в базе данных навсегда.
Настройка образца таблицы
Давайте создадим новую таблицу под названием Счета (Accounts
) в качестве образца:
DROP TABLE IF EXISTSaccounts;
CREATE TABLEaccounts (
id INT GENERATED BY DEFAULT AS IDENTITY,
name VARCHAR(
100)
NOT NULL,
balance
DEC(
15,
2)
NOT NULL,
PRIMARY
KEY(
id)
);
Начало транзакции
Выполните команду INSERT :
INSERT INTOaccounts(
name,balance)
VALUES(
'Bob',
10000);
PostgreSQL сразу же добавил новый ряд в нашу таблицу Accounts
. В этом случае Вы не знаете, когда начинается транзакция, и не можете перехватить модификацию, например, откатить ее назад.
Для того, чтобы начать транзакцию, используйте следующую команду:
BEGIN TRANSACTION;
или
BEGIN WORK;
или просто:
BEGIN;
Например, следующие команды начинают новую транзакцию и добавляют новый счет в таблицу Accounts
:
BEGIN;
INSERT INTOaccounts(
name,balance)
VALUES(
'Alice',
10000);
Из текущего сеанса Вы можете увидеть изменение, запросив таблицу Accounts:
SELECT id,
name,
balance
FROMaccounts;
Однако, если Вы начнете новую сессию и выполните запрос, описанный выше, Вы не увидите изменение:
SELECT i,
name,
balance
FROMaccounts;
Как совершить транзакцию
Для того, чтобы изменение стало видно другим пользователям, необходимо совершить транзакцию, используя команду COMMIT
WORK
:
COMMIT WORK;
или
COMMIT TRANSACTION;
или просто:
COMMIT;
Следующая команда COMMIT
вставит счет Элис в таблицу Accounts
:
COMMIT;
Вы сможете увидеть изменение, запросив таблицу Accounts
:
SELECT id,
name,
balance
FROMaccounts;
После выполнения команды COMMIT
PostgreSQL также гарантирует, что оно останется в таблице даже в том случае, если произойдет какой-либо сбой.
Соедините их вместе.
-- start a transaction BEGIN;
-- insert a new row into the accounts table INSERT INTOaccounts(
name,balance)
VALUES(
'Alice',
10000);
-- commit the change (or roll it back later) COMMIT;
COMMIT в PostgreSQL: пример банковской транзакции
В этом разделе мы покажем Вам, как перевести 1000 USD со счета Боба на счет Элис. Мы используем две сессии для того, чтобы увидеть изменения после каждой операции.
В первой сессии начните новую транзакцию:
BEGIN;
И вычтите 1000 USD со счета Боба (id 1):
UPDATEaccounts
SETbalance = balance -
1000 WHERE id=
1;
Во второй сессии проверьте баланс на обоих счетах:
SELECT id,
name,
balance
FROMaccounts;
Как Вы можете видеть, изменение не обнаруживается в других сессиях.
Затем, добавьте ту же сумму (1000 USD ) на счет Элис:
UPDATEaccounts
SETbalance = balance +
1000 WHERE id=
2;
Во второй сессии изменение также не обнаруживается, пока мы его не совершим:
COMMIT;
Сейчас Вы сможете увидеть изменение из любой сессии:
SELECT id,
name,
balance
FROMaccounts;
Соедините их вместе.
-- start a transaction BEGIN;
-- deduct 1000 from account 1 UPDATEaccounts
SETbalance = balance -
1000 WHERE id=
1;
-- add 1000 to account 2 UPDATEaccounts
SETbalance = balance +
1000 WHERE id=
2;
-- select the data from accounts SELECT id,
name, balance
FROMaccounts;
-- commit the transaction COMMIT;
Откат транзакции
Для того, чтобы откатать назад или отменить изменение текущей транзакции, используйте следующую команду:
ROLLBACK WORK;
или
ROLLBACK TRANSACTION;
или просто:
ROLLBACK;
Допустим, Вы хотите перевести 1500 USD со счета Боба на счет Элис. Но совершенно случайно Вы отправили деньги Джеку вместо Элис. И Вы хотите полностью откатить транзакцию.
Для начала добавьте счет Джека в таблицу Accounts:
INSERT INTOaccounts(
name, balance)
VALUES(
'Jack',
0);
Затем, вычтите сумму со счета Боба: BEGIN;
UPDATEaccounts
SETbalance = balance -
1500 WHERE id=
1;
Затем, добавьте ту же сумму на счет Элис:
UPDATEaccounts
SETbalance = balance +
1500 WHERE id=
3;
Но, у счета Элис id 2. Таким образом, данное действие было ошибочным.
Для того, чтобы отменить изменение, используйте команду ROLLBACK
:
ROLLBACK;
Наконец, проверьте баланс всех счетов: SELECT id,
name,
balance
FROMaccounts;
Как видно из данных выше, остатки на счетах остаются такими же, какими они были до транзакции.
Объедините все вместе.
-- begin the transaction BEGIN;
-- deduct the amount from the account 1 UPDATEaccounts
SETbalance = balance -
1500 WHERE id=
1;
-- add the amount from the account 3 (instead of 2) UPDATEaccounts
SETbalance = balance +
1500 WHERE id=
3;
-- roll back the transaction ROLLBACK;
Благодаря данной инструкции Вы научились обращаться с транзакциями в PostgreSQL с помощью команд BEGIN
, COMMIT
и ROLLBACK
.