Транзакции в SQL: BEGIN, COMMIT и ROLLBACK на примерах

Коротко

Транзакция — это несколько команд, которые база либо применяет все вместе, либо не применяет совсем. Аналитику она нужна как страховка: изменение можно посмотреть и отменить, пока оно не зафиксировано.

• begin открывает транзакцию, commit фиксирует изменения, rollback отменяет всё сделанное после begin.
• Без begin каждая команда — отдельная транзакция, которая фиксируется сразу. Это автокоммит, и отменить такую команду нельзя.
• В PostgreSQL ошибка внутри транзакции блокирует её целиком: до rollback база отвечает «current transaction is aborted».
• savepoint ставит точку внутри транзакции, rollback to возвращает к ней, не теряя сделанного раньше.
• До commit ваши изменения видите только вы. Зато изменённые строки заблокированы для чужих UPDATE и DELETE, поэтому открытую транзакцию не оставляют висеть.
• В PostgreSQL откатываются и CREATE TABLE, и DROP TABLE, и TRUNCATE.

Что такое транзакция простыми словами

Обычный пример — перевод денег: списать со счёта и зачислить на другой нужно либо вместе, либо никак. У аналитика задачи скромнее, но устроены так же. Перенести строки в архив — это INSERT и DELETE. Исправить справочник и пересчитать зависящую от него витрину — два UPDATE. Если между командами что-то упадёт, данные останутся наполовину переделанными, и следующий отчёт посчитается по ним.

Транзакция снимает эту проблему: команды между begin и commit база считает одним действием. Пока commit не выполнен, изменения остаются черновиком. Его видит только ваше подключение, и его можно выбросить целиком.

Свойства, которые за этим стоят, — атомарность, согласованность, изоляция и долговечность — и уровни изоляции разобраны в отдельной статье про ACID. Здесь только практика: команды, ответы базы и привычки, которые спасают данные.

Примеры выполнены в PostgreSQL 14 на таблице subscriptions учебной базы «Маяк»: 912 подписок, три тарифа, три статуса. Песочница симулятора SQL-аналитика принимает только SELECT и WITH, так что блоки с begin и update запускайте в своём PostgreSQL. И не на рабочей таблице, а на копии: create table subscriptions_copy as select * from subscriptions. Запросы на чтение из статьи работают в песочнице как есть. Первый показывает исходное состояние — с ним удобно сверяться после каждого отката.

select status,
       count(*) as subs
from subscriptions
group by status
order by status;   -- active 515, churned 169, past_due 228

BEGIN, COMMIT и ROLLBACK: как это выглядит на примере

Команд три. begin открывает транзакцию (в стандарте SQL она называется start transaction, PostgreSQL понимает обе записи). commit делает изменения постоянными и видимыми всем. rollback возвращает данные в состояние на момент begin.

Вернёмся к задаче из начала. Сначала считаем, сколько строк должно измениться: это обычный SELECT с тем же условием, что пойдёт в UPDATE. Ответ — 29.

Теперь та самая ошибка: UPDATE без условия про тариф. База отвечает UPDATE 228, и это число уже расходится с предпросмотром. Запрос по статусам внутри транзакции подтверждает: просроченных подписок не осталось, в оттоке 397 вместо 169. После rollback тот же запрос возвращает прежние 515, 169 и 228 — как будто UPDATE не было.

Число после слова UPDATE — первое, на что стоит смотреть. Это количество строк, которые команда изменила. Если оно не совпало с тем, что показал SELECT, дальше можно не разбираться: откат, потом поиск причины.

select count(*) as will_change   -- 29
from subscriptions
where status = 'past_due'
  and plan = 'team';

begin;

update subscriptions
set status = 'churned'
where status = 'past_due';        -- UPDATE 228, а ждали 29

select status, count(*) as subs
from subscriptions
group by status
order by status;                  -- active 515, churned 397

rollback;                         -- снова 515 / 169 / 228

Что такое автокоммит и почему ROLLBACK без BEGIN не помогает

Транзакция есть всегда, даже если вы её не открывали. Команда без begin выполняется в собственной транзакции из одной команды, и база фиксирует её сразу после успешного выполнения. Этот режим называется автокоммитом; в psql и в большинстве клиентов он включён по умолчанию.

Отсюда главная неприятность: rollback после такой команды ничего не отменяет. PostgreSQL выводит предупреждение WARNING: there is no transaction in progress — откатывать нечего, изменение уже зафиксировано. В примере ниже подписка 2 так и осталась в статусе churned.

В графических клиентах режим обычно переключается: вместо автокоммита можно выбрать ручной, и тогда клиент сам открывает транзакцию перед первой командой. Удобно, но у ручного режима своя цена — про неё раздел о незакрытых транзакциях. Проверьте, какой режим стоит у вас, прежде чем полагаться на откат.

update subscriptions
set status = 'churned'
where subscription_id = 2;        -- UPDATE 1

rollback;
-- WARNING:  there is no transaction in progress

select subscription_id, status
from subscriptions
where subscription_id = 2;        -- churned: откат не сработал

Как править данные со страховкой: BEGIN, UPDATE, проверка, COMMIT

Из этих команд складывается порядок, которым стоит выполнять любую правку данных руками. Он занимает минуту и работает одинаково для UPDATE и DELETE.

Первый шаг делается до транзакции и доступен даже в песочнице: посмотреть, что попадает под условие. Одного count(*) мало — полезно разложить строки по признаку, который вы собираетесь ограничить. Запрос ниже показывает просроченные подписки по тарифам: 127, 72 и 29. Сразу видно, что «все просроченные» и «просроченные тарифа team» — разные множества, 228 строк против 29.

Дальше — сама правка. После UPDATE сверяем число из ответа с предпросмотром и смотрим на результат запросом внутри транзакции: в тарифе team должно остаться 56 активных подписок и стать 46 ушедших, а статуса past_due быть не должно. Сошлось — commit. Не сошлось или что-то смущает — rollback, и данные не пострадали.

select plan,
       count(*) as subs,               -- basic 127, pro 72, team 29
       sum(monthly_price) as mrr       -- 2413, 2088, 1131
from subscriptions
where status = 'past_due'
group by plan
order by plan;

begin;

update subscriptions
set status = 'churned'
where status = 'past_due'
  and plan = 'team';              -- UPDATE 29: совпало с предпросмотром

select plan, status, count(*) as subs
from subscriptions
where plan = 'team'
group by plan, status
order by status;                  -- team active 56, team churned 46

commit;                           -- или rollback, если числа не сошлись

• SELECT с тем же WHERE — запомнить число строк.
• begin.
• UPDATE или DELETE — сравнить число в ответе с предпросмотром.
• SELECT по изменённым строкам — убедиться, что результат такой, как задумано.
• commit или rollback. Сразу, не откладывая.

Что происходит при ошибке внутри транзакции

В PostgreSQL ошибка в любой команде переводит всю транзакцию в состояние «прервана». В примере UPDATE прошёл успешно, а в проверочном запросе опечатка: state вместо status. База сообщает об ошибке и после этого перестаёт выполнять что-либо, включая правильные запросы. На каждую команду ответ один: ERROR: current transaction is aborted, commands ignored until end of transaction block.

Выход единственный — закончить транзакцию. rollback делает это явно. commit тоже сработает, но зафиксировать прерванную транзакцию нельзя: PostgreSQL выполнит откат и в ответ напишет ROLLBACK, а не COMMIT. После этого в оттоке снова 169 подписок: успешный UPDATE пропал вместе со всем остальным.

Это и есть атомарность в действии: либо все команды, либо ни одной. Неудобство в том, что опечатка в проверочном SELECT стоит всей проделанной работы. Обходится это точкой сохранения.

В других СУБД поведение отличается: например, MySQL после ошибки в одной команде обычно оставляет транзакцию открытой, и решать, откатывать ли её, приходится вам. Не переносите привычку из одной базы в другую без проверки.

begin;

update subscriptions
set status = 'churned'
where status = 'past_due'
  and plan = 'team';              -- UPDATE 29

select count(*) from subscriptions where state = 'churned';
-- ERROR:  column "state" does not exist
-- HINT:  Perhaps you meant to reference the column "subscriptions.status".

select count(*) from subscriptions where status = 'churned';
-- ERROR:  current transaction is aborted, commands ignored until end of transaction block

commit;                           -- ответ: ROLLBACK

SAVEPOINT и ROLLBACK TO: как откатить часть транзакции

savepoint имя ставит внутри транзакции отметку. rollback to savepoint имя отменяет всё, что сделано после отметки, и оставляет сделанное до неё. Транзакция при этом продолжается.

Задача из двух шагов: перевести просроченные подписки team в отток, а затем обнулить им ежемесячную цену. Первый шаг прошёл — UPDATE 29, ставим точку. Во втором шаге снова забыто условие про тариф: UPDATE 198, цена обнулилась у всех ушедших подписок базы. rollback to возвращает к точке: статусы на месте, цены прежние. Исправленный UPDATE отвечает UPDATE 46 — это 17 подписок team, которые ушли раньше, и 29 только что переведённых.

Та же команда выручает после ошибки. Если перед рискованной командой стоит точка сохранения, то после ERROR достаточно rollback to — транзакция выходит из прерванного состояния, и первый шаг не теряется. Мы проверили это на делении на ноль: после отката к точке запрос снова выполнялся и показывал 198 подписок в оттоке.

release savepoint имя убирает точку, когда она больше не нужна; сами изменения при этом остаются в транзакции. Точек может быть несколько, откат к ранней удаляет все более поздние.

begin;

update subscriptions
set status = 'churned'
where status = 'past_due'
  and plan = 'team';              -- UPDATE 29

savepoint statuses_done;

update subscriptions
set monthly_price = 0
where status = 'churned';         -- UPDATE 198: забыли тариф

rollback to savepoint statuses_done;   -- статусы целы, цены прежние

update subscriptions
set monthly_price = 0
where status = 'churned'
  and plan = 'team';              -- UPDATE 46

commit;

Что видит другая сессия до COMMIT

Сессия — это одно подключение к базе: ваше окно psql, вкладка клиента, отчёт в BI. Мы открыли два подключения к одной базе и выполнили команды по очереди. Сессия A начала транзакцию и перевела 29 подписок в отток. Сессия B в это время читала ту же таблицу.

Пока A не выполнила commit, B видит старые данные: 169 подписок в оттоке, подписка 2 в статусе past_due. Никакой ошибки и никакого ожидания — чтение не блокируется, B просто получает последнюю зафиксированную версию строк. Сама A при этом видит свои изменения: 198. После commit сессия B тем же запросом получает 198.

Практический вывод двойной. Во-первых, недоделанную правку никто не увидит: дашборд не покажет промежуточное состояние. Во-вторых, если коллега говорит «я уже обновил, а у тебя старые цифры» — скорее всего, он не выполнил commit. Так ведёт себя уровень изоляции Read Committed, который стоит в PostgreSQL по умолчанию; про остальные уровни — в статье об ACID.

Почему нельзя оставлять транзакцию открытой

Читать изменённые строки другим сессиям можно, а менять — нет. Строка, которую обновила незавершённая транзакция, заблокирована до commit или rollback. В том же опыте сессия B попыталась изменить цену подписки 2, пока A держала транзакцию открытой. Команда не упала и не выполнилась — она зависла.

Мы запускали её дважды. С ограничением set lock_timeout = '2s' база через две секунды отменила команду с ошибкой canceling statement due to lock timeout. Без ограничения UPDATE ждал почти три секунды — ровно до того момента, когда A выполнила commit, — и только потом ответил UPDATE 1. Если бы A ушла на обед, B ждала бы до конца обеда.

Вот почему пятый пункт порядка звучит «сразу». Транзакция, открытая в забытой вкладке, держит блокировки: загрузчик данных, который обновляет те же строки, встанет в очередь, за ним — следующие. Кроме того, долго открытая транзакция мешает PostgreSQL очищать устаревшие версии строк, и таблицы разрастаются. Администраторы находят таких «висящих» по состоянию idle in transaction в представлении pg_stat_activity — в нашем опыте сессия A выглядела именно так.

Правило простое: открыли транзакцию — закончите её до того, как отвлечётесь. Нужно подумать над результатом дольше пары минут — rollback, подумать, повторить.

set lock_timeout = '2s';

update subscriptions
set monthly_price = 35
where subscription_id = 2;
-- ERROR:  canceling statement due to lock timeout
-- CONTEXT:  while updating tuple (8,80) in relation "subscriptions"

-- кто держит транзакцию открытой и как давно
select pid, state, now() - xact_start as tx_age, query
from pg_stat_activity
where state = 'idle in transaction';

Откатываются ли CREATE TABLE и DROP TABLE

В PostgreSQL — да. Команды, которые меняют структуру базы, участвуют в транзакции наравне с UPDATE. В примере внутри begin создаётся резервная копия таблицы, а сама таблица удаляется. Запрос к subscriptions после этого падает с ошибкой relation "subscriptions" does not exist — и заодно прерывает транзакцию, как любая ошибка. После rollback всё возвращается: в subscriptions снова 912 строк, а таблицы subscriptions_backup нет, потому что её создание тоже откатилось.

То же с TRUNCATE: внутри транзакции таблица показывала 0 строк, после rollback — 912. Это позволяет пересобирать витрину целиком внутри одной транзакции: удалили старую таблицу, создали новую, проверили — и только потом зафиксировали. Читатели витрины не увидят момента, когда её нет.

Рассчитывать на это в другой СУБД нельзя. В MySQL, например, команды изменения структуры неявно фиксируют текущую транзакцию: после DROP TABLE откатывать уже нечего. Прежде чем удалять таблицу «под защитой» транзакции, проверьте документацию своей базы.

begin;

create table subscriptions_backup as
select * from subscriptions;      -- SELECT 912

drop table subscriptions;         -- DROP TABLE

select count(*) from subscriptions;
-- ERROR:  relation "subscriptions" does not exist

rollback;

select count(*) from subscriptions;          -- 912: таблица на месте
select to_regclass('subscriptions_backup');  -- пусто: копии нет

Транзакция только для чтения: защита от случайной записи

Иногда задача обратная: вы точно не собираетесь ничего менять и хотите, чтобы база за этим проследила. Например, вы подключены к рабочей базе под учётной записью с правами на запись и разбираетесь в данных перед правкой. Транзакция, открытая командой begin read only, выполняет SELECT как обычно, а на любую попытку записи отвечает ошибкой cannot execute UPDATE in a read-only transaction.

Второй способ — set transaction read only первой командой после обычного begin. Результат тот же: DELETE в такой транзакции получил cannot execute DELETE in a read-only transaction. А set default_transaction_read_only = on включает режим для всех следующих транзакций сессии, в том числе для команд без begin: одиночный UPDATE после этой настройки тоже был отклонён.

Это защита от собственной невнимательности, а не разграничение доступа: настройку сессии можно выключить той же командой. Надёжный способ запретить запись — отдельная учётная запись только с правом SELECT. Но если такой нет, одна строка в начале сеанса избавляет от худшего сценария.

begin read only;

select count(*)
from subscriptions
where status = 'past_due';        -- 228: чтение работает

update subscriptions
set status = 'churned'
where status = 'past_due';
-- ERROR:  cannot execute UPDATE in a read-only transaction

rollback;

Частые вопросы

Что почитать дальше

Запросы на чтение из статьи — распределение статусов, предпросмотр и разбивку по тарифам — можно выполнить в песочнице. Для begin и update поднимите локальный PostgreSQL, сделайте копию таблицы и повторите опыт с двумя окнами psql: он занимает пять минут и запоминается лучше любого объяснения.