SQL в PostgreSQL: транзакции

Транзакции - фундаментальная концепция всех систем баз данных. Существенным моментом транзакции является то, что она объединяет несколько шагов в одну операцию по принципу "все или ничего". Промежуточные состояния между шагами не видны другим параллельным транзакциям, и если произойдет какой-либо сбой, который препятствует завершению транзакции, то ни один из шагов не повлияет на базу данных вообще.

Например, рассмотрим банковскую базу данных, которая содержит остатки по различным счетам клиентов, а также общие остатки по депозитам для филиалов. Предположим, мы хотим записать платеж в размере 100 долларов США со счета Алисы на счет Боба. Предельно упрощая, команды SQL для этого могут выглядеть так:

UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');

Детали этих команд здесь не важны. Важным моментом является то, что для выполнения этой довольно простой операции требуется несколько отдельных обновлений. Сотрудники нашего банка захотят быть уверены, что либо все эти обновления происходят, либо ничего не происходит. Это определенно не годится для сбоя системы, когда Боб получит 100 долларов, которые не были списаны с Алисы. Алиса также не могла бы долго оставаться счастливым покупателем, если бы деньги были списаны с ее счета, но не поступили бы к Бобу. Нам нужна гарантия, что если что-то пойдет не так в ходе операции, ни один из выполненных шагов не вступит в силу. Группировка обновлений в транзакцию дает нам эту гарантию. Транзакция называется атомарной (atomic): с точки зрения других транзакций она либо происходит полностью, либо не происходит вовсе.

Нам также нужна гарантия того, что после завершения транзакции и ее подтверждения системой базы данных она действительно будет постоянно записана и не будет потеряна, даже если вскоре после этого произойдет сбой. Например, если мы регистрируем снятие наличных Бобом, мы не хотим никаких шансов, что дебет его счета исчезнет в результате сбоя сразу после того, как он выйдет из двери банка. База данных транзакций гарантирует, что все обновления, сделанные транзакцией, регистрируются в постоянном хранилище (т. е. на диске) до того, как транзакция будет объявлена ​​завершенной.

Другое важное свойство транзакционных баз данных тесно связано с понятием атомарных обновлений: когда несколько транзакций выполняются одновременно, каждая из них не должна видеть неполные изменения, сделанные другими. Например, если одна транзакция занята подсчетом всех балансов в филиалах, то в нее не будет включаться дебет из филиала Алисы, но не кредит в филиал Боба, и наоборот. Таким образом, транзакции должны осуществляться по принципу "все или ничего" не только с точки зрения их постоянного воздействия на базу данных, но и с точки зрения их видимости, когда они происходят. Обновления, сделанные на данный момент открытой транзакцией, невидимы для других транзакций, пока транзакция не завершится, после чего все обновления становятся видимыми одновременно.

В PostgreSQL транзакция настраивается путем окружения команд SQL транзакции командами BEGIN и COMMIT. Итак, наша банковская транзакция на самом деле будет выглядеть так:

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
COMMIT;

Если в ходе транзакции мы решаем, что не хотим фиксировать (возможно, мы только что заметили, что баланс Алисы стал отрицательным), мы можем выполнить команду ROLLBACK вместо COMMIT, и все наши обновления на данный момент будут отменены.

PostgreSQL фактически рассматривает каждый оператор SQL как выполняемый в рамках транзакции. Если вы не вводите команду BEGIN, тогда каждый отдельный оператор имеет неявные BEGIN и (в случае успеха) COMMIT, обернутые вокруг него. Группа операторов, окруженная BEGIN и COMMIT, иногда называется блоком транзакции.

Некоторые клиентские библиотеки выдают команды BEGIN и COMMIT автоматически, так что вы можете получить эффект блоков транзакций, не спрашивая.

Можно управлять операторами в транзакции более детально с помощью точек сохранения. Точки сохранения позволяют выборочно отбрасывать части транзакции с фиксацией остальных. После определения точки сохранения с помощью SAVEPOINT вы можете при необходимости откатиться к точке сохранения с помощью ROLLBACK TO. Все изменения в базе данных транзакции между определением точки сохранения и откатом к ней отменяются, но изменения до точки сохранения сохраняются.

После отката к точке сохранения она продолжает существовать, поэтому вы можете вернуться к ней несколько раз. И наоборот, если вы уверены, что вам не нужно будет снова откатываться к определенной точке сохранения, ее можно освободить, чтобы система могла освободить некоторые ресурсы. Имейте в виду, что либо освобождение, либо откат к точке сохранения автоматически освобождают все точки сохранения, которые были определены после нее.

Все это происходит в блоке транзакции, поэтому ничего из этого не видно другим сеансам базы данных. Когда и если вы фиксируете блок транзакции, зафиксированные действия становятся видимыми как единое целое для других сеансов, в то время как действия отката никогда не становятся видимыми вообще.

Вспоминая банковскую базу данных, предположим, что мы списываем 100,00 долларов со счета Алисы и кредитуем счет Боба, но позже обнаруживаем, что мы должны были пополнить счет Уолли. Мы могли бы сделать это, используя такие точки сохранения:

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- забыть об этом и использовать аккаунт Уолли
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;

Этот пример, конечно, слишком упрощен, но в блоке транзакции возможен большой контроль за счет использования точек сохранения. Более того, ROLLBACK TO - единственный способ восстановить контроль над блоком транзакции, который был переведен системой в состояние прерывания из-за ошибки, за исключением его полного отката и повторного запуска.

---

Читайте также:

• SQL в PostgreSQL: представления (Views)
• SQL в PostgreSQL: внешние ключи (Foreign Keys)
• SQL в PostgreSQL: запрос к таблице

Alex tools в Телеграм