Транзакции SQL: BEGIN, COMMIT, ROLLBACK, ACID и уровни изоляции

Транзакции SQL объединяют несколько операций в одно целое: база данных фиксирует либо все изменения, либо ни одного. Транзакция пишется тремя командами: BEGIN открывает транзакцию, COMMIT сохраняет результат, ROLLBACK отменяет всё, что было сделано после BEGIN.

Примеры ниже прогнаны в PostgreSQL 18.6 через psql, вывод приведён дословно. Синтаксис MS SQL и MySQL, где он отличается, дан по документации и помечен названием СУБД.

Что такое транзакция в базе данных

Классический пример транзакции: перевод денег. Нужно списать сумму с одного счёта и зачислить на другой. Если между этими операциями случится сбой системы, деньги исчезнут. Транзакция гарантирует, что списание без зачисления не останется.

Создадим в базе данных таблицу счетов. Ограничение CHECK запрещает отрицательный баланс, оно пригодится дальше:

CREATE TABLE accounts (
    id      integer PRIMARY KEY,
    owner   text NOT NULL,
    balance numeric(10, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Анна', 1000), (2, 'Борис', 500);

CREATE TABLE
INSERT 0 2

Выполним перевод 300 со счёта 1 на счёт 2:

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;
SELECT * FROM accounts ORDER BY id;

BEGIN
UPDATE 1
UPDATE 1
COMMIT
 id | owner | balance 
----+-------+---------
  1 | Анна  |  700.00
  2 | Борис |  800.00
(2 rows)

В PostgreSQL до COMMIT другие сеансы видят старые данные: балансы 1000 и 500. После фиксации изменения видны всем.

Свойства ACID

ACID называют четыре свойства, которые СУБД гарантирует для каждой транзакции в базе данных:

Изолированность в ACID настраивается уровнями изоляции транзакций. За долговечность отвечает журнал: в PostgreSQL это WAL, в MS SQL журнал транзакций.

BEGIN, COMMIT и ROLLBACK

ROLLBACK отменяет все изменения данных с момента BEGIN. Пример:

BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
ROLLBACK;
SELECT balance FROM accounts WHERE id = 1;

BEGIN
UPDATE 1
ROLLBACK
 balance 
---------
  700.00
(1 row)

В разных СУБД команды транзакций пишутся по-разному:

В хранимых процедурах, функциях и триггерах MySQL слово BEGIN открывает блок BEGIN ... END. Там транзакцию начинают только через START TRANSACTION.

Ошибка внутри транзакции

Что произойдёт, если одна из операций упадёт? Попробуем перевести 1000 со счёта, где всего 700. Зачисление пройдёт, списание нарушит CHECK:

BEGIN;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
SELECT * FROM accounts ORDER BY id;
COMMIT;
SELECT * FROM accounts ORDER BY id;

BEGIN
UPDATE 1
ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (1, Анна, -300.00).
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
 id | owner | balance 
----+-------+---------
  1 | Анна  |  700.00
  2 | Борис |  800.00
(2 rows)

После ошибки PostgreSQL переводит транзакцию в состояние «прервана» и до её конца не выполняет ни одной операции, кроме ROLLBACK и ROLLBACK TO SAVEPOINT, даже SELECT. На COMMIT сервер ответил ROLLBACK, и это означает, что транзакция откатилась полностью: зачисление 1000 на счёт Бориса тоже отменено.

SQL Server ведёт себя иначе. При выключенном SET XACT_ABORT (так по умолчанию) после части ошибок откатывается только упавшая инструкция, транзакция продолжается, и COMMIT может зафиксировать половину перевода. Поэтому в процедурах с явной транзакцией ставьте SET XACT_ABORT ON, а ошибки ловите через TRY CATCH в SQL Server.

SAVEPOINT: частичный откат транзакции

С помощью точки сохранения можно отменить часть транзакции и продолжить работу. Здесь перевод 100 нужно сохранить, а лишнее списание отменить:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
SAVEPOINT before_fee;
UPDATE accounts SET balance = balance - 5000 WHERE id = 2;
ROLLBACK TO SAVEPOINT before_fee;
COMMIT;
SELECT * FROM accounts ORDER BY id;

BEGIN
UPDATE 1
UPDATE 1
SAVEPOINT
ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (2, Борис, -4100.00).
ROLLBACK
COMMIT
 id | owner | balance 
----+-------+---------
  1 | Анна  |  600.00
  2 | Борис |  900.00
(2 rows)

ROLLBACK TO SAVEPOINT вернул транзакцию из состояния ошибки, и COMMIT на этот раз зафиксировал перевод. В PostgreSQL это единственный способ продолжить прерванную транзакцию без полного отката. RELEASE SAVEPOINT удаляет точку и сохраняет изменения, сделанные после неё.

Автокоммит, явные и неявные транзакции

Если BEGIN не написан, каждая операция выполняется в отдельной транзакции и фиксируется автоматически. Это режим автокоммита (autocommit), он включён по умолчанию в PostgreSQL, SQL Server и MySQL, а SQLite сам открывает и закрывает транзакцию вокруг каждой команды.

Явная транзакция начинается с BEGIN. Неявные транзакции бывают такие:

• в psql после \set AUTOCOMMIT off клиент сам отправляет BEGIN перед первой командой, а фиксировать нужно руками. Если выйти из psql без COMMIT, изменения пропадут;
• в MS SQL после SET IMPLICIT_TRANSACTIONS ON транзакцию открывают INSERT, UPDATE, DELETE, SELECT из таблицы, CREATE и ряд других команд.

Вложенные транзакции

Настоящих вложенных транзакций нет ни в PostgreSQL, ни в MS SQL. PostgreSQL на второй BEGIN отвечает WARNING: there is already a transaction in progress и продолжает ту же транзакцию, SQLite выдаёт ошибку. В MS SQL внутренний BEGIN TRANSACTION увеличивает счётчик @@TRANCOUNT, внутренний COMMIT только уменьшает его и не освобождает ресурсы, а ROLLBACK TRANSACTION без имени точки откатывает всё до внешнего BEGIN. Чтобы отменить часть работы, используйте точки сохранения.

Аномалии параллельных транзакций

Когда параллельные транзакции одновременно работают с одними данными, возможны такие эффекты:

• грязное чтение (dirty read): транзакция видит данные, которые другая записала, но ещё не зафиксировала;
• неповторяющееся чтение (non-repeatable read): повторный SELECT той же строки внутри транзакции возвращает другое значение, потому что другая транзакция успела его изменить и зафиксировать;
• фантомное чтение (phantom read): повторный запрос с тем же условием возвращает другой набор строк, потому что другая транзакция добавила или удалила подходящие;
• аномалия сериализации: итог нескольких зафиксированных транзакций не совпадает ни с одним порядком их выполнения по очереди;
• потерянное обновление (lost update): обе транзакции прочитали одно значение, каждая записала своё, и одно изменение затёрло другое.

С аномалиями из нормализации баз данных их путать не стоит: там причина в дублировании данных, здесь в параллельной работе.

Уровни изоляции транзакций

Стандарт SQL задаёт четыре уровня изоляции транзакций. Чем строже уровень, тем меньше аномалий:

SERIALIZABLE считается самым строгим уровнем: он даёт результат такой, как если бы транзакции выполнялись по очереди.

Уровень по умолчанию:

• PostgreSQL: READ COMMITTED;
• SQL Server: READ COMMITTED; по умолчанию он работает на блокировках (READ_COMMITTED_SNAPSHOT OFF), в Azure SQL Database на версиях строк;
• MySQL с InnoDB: REPEATABLE READ.

Уровень изоляции задают при открытии транзакции. В PostgreSQL прямо в BEGIN:

BEGIN ISOLATION LEVEL REPEATABLE READ;

В SQL Server и MySQL используется отдельная команда перед началом:

-- SQL Server, по документации
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

В MS SQL уровень действует для соединения, пока его не поменяют, в MySQL без слова SESSION только для следующей транзакции.

Проверка в двух сеансах PostgreSQL

Мы открыли пару соединений с базой данных и выполняли команды по очереди. Перед каждым опытом балансы возвращали к 600 и 900 и удаляли добавленные строки.

Первый опыт: транзакция 1 выполнила BEGIN и UPDATE accounts SET balance = 0 WHERE id = 1 без фиксации. Второе соединение открыло транзакцию с ISOLATION LEVEL READ UNCOMMITTED, прочитало баланс счёта 1 и получило 600, а не 0: в PostgreSQL READ UNCOMMITTED работает как READ COMMITTED, грязного чтения нет.

Второй опыт: один и тот же сценарий на уровнях READ COMMITTED и REPEATABLE READ.

В READ COMMITTED каждый запрос видит данные, зафиксированные к его началу: шаг 5 показал неповторяющееся чтение, шаг 6 фантом. В REPEATABLE READ транзакция работает со снимком на момент первого запроса и не видит ни изменения, ни новую строку.

Потерянное обновление и ошибка сериализации

Пример частой ошибки в коде приложения: прочитать баланс, вычесть сумму в программе и записать готовое число. Транзакции 1 и 2 одновременно списывают по 100 со счёта, где 600. Обе прочитали 600 и обе выполняют UPDATE accounts SET balance = 500 WHERE id = 1.

• В READ COMMITTED второй UPDATE ждёт, пока первая транзакция зафиксируется, а потом записывает своё значение. Итог 500, хотя списали 200: одно списание потеряно.
• В REPEATABLE READ транзакция 2 получает ERROR: could not serialize access due to concurrent update с кодом SQLSTATE 40001. Транзакцию нужно откатить и повторить целиком, с нового чтения.
• Запись UPDATE accounts SET balance = balance - 100 WHERE id = 1 в обеих транзакциях при READ COMMITTED дала правильные 400: второй UPDATE дождался фиксации первого и вычел 100 из уже обновлённой строки.

Какой уровень изоляции использовать? Не поднимайте его «на всякий случай». Для балансов и счётчиков хватает READ COMMITTED и вычисления в самом UPDATE. Если перед записью нужна проверка в коде, заблокируйте строку через SELECT ... FOR UPDATE: другие транзакции не смогут её изменить до вашего COMMIT. REPEATABLE READ и SERIALIZABLE берите, когда нужен согласованный снимок данных или правило, охватывающее несколько строк, и только вместе с повтором транзакции при ошибке 40001.

Взаимная блокировка (deadlock)

Блокировки изменённых строк держатся до завершения транзакции. Если транзакции захватили по строке и ждут друг друга, возникает взаимная блокировка. В нашем примере транзакция 1 обновила счёт 1, транзакция 2 счёт 2, затем каждая попыталась обновить строку, занятую другой. PostgreSQL автоматически нашёл взаимную блокировку и прервал транзакцию 1 с ошибкой deadlock detected (SQLSTATE 40P01), её COMMIT превратился в ROLLBACK. Транзакция 2 перевела 100 от Бориса Анне: 600 и 900 стали 700 у Анны и 800 у Бориса.

Какую из транзакций прервёт PostgreSQL, заранее не угадать. MS SQL в такой ситуации выбирает жертву и возвращает ей ошибку 1205. Как защититься:

1. Обновляйте строки во всех транзакциях в одном порядке, например по возрастанию id.
2. Держите транзакции короткими, без лишних действий внутри.
3. Ловите ошибки 40P01 и 1205 и повторяйте транзакцию целиком.

Журнал транзакций SQL Server

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

Журнал растёт, пока его не усекают. В простой модели восстановления (SIMPLE) место освобождается после контрольной точки. В полной (FULL) и BULK_LOGGED только после резервной копии журнала. Когда место кончается, сервер выдаёт ошибку 9002, и работающая база остаётся доступной только для чтения.

Причину смотрят в sys.databases (по документации, мы не запускали):

-- SQL Server
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

Два частых значения log_reuse_wait_desc:

• LOG_BACKUP: база в модели FULL, а резервные копии журнала не делаются. Решение: настроить BACKUP LOG по расписанию. Если копий журнала не было никогда, понадобятся две подряд;
• ACTIVE_TRANSACTION: из-за долгой открытой транзакции журнал не усекается даже в модели SIMPLE. Её ищут через DBCC OPENTRAN.

Типичные ошибки с транзакциями

Забытый COMMIT в коде приложения

Модуль sqlite3 в Python 3.14 по умолчанию работает в старом режиме: перед INSERT, UPDATE, DELETE он сам открывает транзакцию, но фиксировать её должен ваш код. Запустите в пустой папке:

import sqlite3

con = sqlite3.connect("shop.db")
con.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT)")
con.execute("INSERT INTO orders (item) VALUES (?)", ("книга",))
con.close()  # commit() забыли

con = sqlite3.connect("shop.db")
print("без commit:", con.execute("SELECT count(*) FROM orders").fetchone()[0])

with con:  # при выходе commit, при исключении rollback
    con.execute("INSERT INTO orders (item) VALUES (?)", ("лампа",))
con.close()

con = sqlite3.connect("shop.db")
print("через with:", con.execute("SELECT count(*) FROM orders").fetchone()[0])
con.close()

без commit: 0
через with: 1

Первая вставка пропала молча, без ошибки. Блок with con: фиксирует транзакцию при выходе и откатывает при исключении, но соединение не закрывает. Значения передаются через ?: так запрос защищён от SQL-инъекции.

Долгая транзакция

Если транзакция ждёт ввода пользователя, всё это время держатся блокировки строк, другие транзакции ждут, а в MS SQL не усекается журнал. Сначала соберите все данные, потом открывайте транзакцию.

DDL внутри транзакции MySQL

CREATE TABLE, ALTER TABLE, DROP TABLE и другие команды DDL в MySQL неявно фиксируют текущую транзакцию, и ROLLBACK после них уже ничего не вернёт. В PostgreSQL CREATE TABLE внутри BEGIN откатывается вместе с остальным, мы это проверили. Исключение в PostgreSQL: CREATE INDEX CONCURRENTLY внутри транзакции не работает, подробнее в статье про индексы в SQL.

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

Чем COMMIT отличается от ROLLBACK?

Транзакция завершается либо COMMIT, либо ROLLBACK. COMMIT фиксирует все изменения: они становятся видны другим сеансам и сохраняются после сбоя. ROLLBACK отменяет все изменения с начала транзакции. После COMMIT откатить транзакцию уже нельзя, только выполнить новую с обратными изменениями.

Можно ли откатить только часть транзакции?

Да, с помощью точки сохранения: SAVEPOINT с именем точки, затем ROLLBACK TO SAVEPOINT с тем же именем (в MS SQL SAVE TRANSACTION и ROLLBACK TRANSACTION). Изменения до точки останутся, транзакция продолжится.

Почему в PostgreSQL READ UNCOMMITTED не даёт грязного чтения?

PostgreSQL принимает все четыре уровня из стандарта, но внутри реализует три: READ UNCOMMITTED работает как READ COMMITTED. Стандарт задаёт только минимум защиты, более строгое поведение он допускает.

Когда использовать уровень изоляции SERIALIZABLE?

Когда правило охватывает несколько строк и его нельзя выразить ограничением таблицы, например «общий баланс всех счетов клиента не уходит в минус». SERIALIZABLE гарантирует результат, как при выполнении транзакций по очереди, но часть транзакций может завершаться ошибкой 40001. Код обязан ловить её и повторять транзакцию с начала.