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

В этом руководстве мы рассмотрим фиксации, откаты и сбои в небольшой таблице учетных записей. Мы запускали эти примеры в SQLite, и показанные балансы получены в результате этого запуска. Основные команды одинаковы для разных баз данных, но поведение блокировки и изоляции сильно различается, поэтому мы указываем, где можно проверить документацию вашей собственной базы данных.

Что такое транзакция?

Транзакция — это последовательность одного или нескольких операторов SQL, которую база данных обрабатывает как одну логическую операцию. Вот наша таблица:

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  owner TEXT,
  balance INT CHECK (balance >= 0)
);

INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ben', 200);

Ограничение CHECK — это правило, согласно которому баланс никогда не может опускаться ниже нуля, которое мы будем использовать позже. Обратите внимание, что синтаксис начала транзакции различается: SQLite и PostgreSQL принимают BEGIN, MySQL также имеет START TRANSACTION, а SQL Server использует BEGIN TRANSACTION.

За пределами явной транзакции большинство баз данных используют autocommit, где каждый оператор сохраняется сразу после его завершения. Oracle является заметным исключением, поскольку сеанс там остается в транзакции до тех пор, пока вы не зафиксируете ее.

COMMIT: сделать это постоянным

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;

-- Asha | 700
-- Ben  | 500

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

ОТКАТ: отменить все

ROLLBACK отменяет все с момента начала транзакции:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT owner, balance FROM accounts;
-- Asha | 600   (visible inside this transaction)
-- Ben  | 500

ROLLBACK;
SELECT owner, balance FROM accounts;
-- Asha | 700   (back to where it was)
-- Ben  | 500

Откат также происходит автоматически, если соединение разрывается или сервер выходит из строя до того, как вы зафиксируете транзакцию, поэтому незавершенная транзакция не сохраняется.

Когда утверждение терпит неудачу

Вот более реалистичный случай. Аша пытается отправить 5000, которых нет на счету. Сначала идет кредит Бену, а затем дебет, что нарушает правило CHECK:

BEGIN;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
-- error: CHECK constraint failed: balance >= 0

ROLLBACK;

-- Asha | 700
-- Ben  | 500

Без этой сделки Бен оставил бы себе 5000, и бухгалтерские книги были бы неправильными. Помните об одной важной детали: неудачный оператор не всегда отменяет транзакцию сам по себе. Например, в SQLite и MySQL более ранние инструкции могут оставаться ожидающими, поэтому ваш код должен выполнить откат. Вместо этого PostgreSQL помечает всю транзакцию как прерванную и отказывается от дальнейших операторов, пока вы не откатитесь.

В коде приложения всегда связывайте транзакцию с обработкой ошибок. Вот идея Python, и наше руководство по обработке исключений Python объясняет синтаксис:

try:
    conn.execute("BEGIN")
    conn.execute(debit_sql, values)
    conn.execute(credit_sql, values)
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

Точки сохранения

Точка сохранения отмечает позицию внутри транзакции, поэтому вы можете отменить только часть работы:

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 999 WHERE id = 2;
ROLLBACK TO bonus;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Asha | 650
-- Ben  | 550

Бонус 999 был отменен, а остальная часть транзакции была продолжена и зафиксирована. Это полезно для дополнительных шагов в длительном процессе.

Объяснение свойств ACID

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

Что происходит с параллельными транзакциями?

Мы открыли два подключения к одному файлу SQLite. Первый запустил транзакцию и обновил строку с 1 до 2, еще не зафиксировав ее. Вот что увидело второе соединение:

Connection B reads:   1                     -- no uncommitted data
Connection B writes:  database is locked    -- it has to wait
(A commits)
Connection B reads:   2                     -- now it sees the change

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

Чтение аномалий

Когда транзакции перекрываются, могут возникнуть определенные проблемы. Стандарт SQL определяет их с точки зрения того, что может наблюдать транзакция:

Другая проблема — потеря обновления. Две сессии считывают баланс 100, каждая добавляет 10 и каждая записывает 110. Одно обновление фактически исчезло, когда ответ должен был быть 120.

Уровни изоляции SQL

Уровни изоляции позволяют вам обменивать строгость на параллелизм. Вот что стандарт SQL позволяет на каждом уровне:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Точный синтаксис и поведение зависят от вашей базы данных. Значения по умолчанию также различаются: PostgreSQL и SQL Server используют фиксированное чтение, тогда как InnoDB MySQL использует повторяемое чтение. Некоторые базы данных дают более строгие гарантии, чем того требует стандарт, или рассматривают один уровень как другой. Например, в PostgreSQL чтение без фиксации ведет себя как чтение с фиксированием. Всегда читайте документацию по вашей базе данных, прежде чем полагаться на определенное поведение.

Тупики

Взаимная блокировка возникает, когда каждая из двух транзакций удерживает блокировку, необходимую другой. Транзакция A блокирует строку 1 и хочет получить строку 2, тогда как транзакция B блокирует строку 2 и хочет получить строку 1. Ни одна из них не может продолжаться.

Такие базы данных, как PostgreSQL, MySQL и SQL Server, обнаруживают это и отменяют одну транзакцию с ошибкой. Это нормальное поведение, и ваше приложение должно быть к этому готово. Чтобы уменьшить количество взаимоблокировок, всегда обновляйте строки в последовательном порядке, например сначала наименьший идентификатор учетной записи, сохраняйте короткие транзакции и повторяйте попытку, когда одна из них выбрана в качестве жертвы.

Лучшие практики

• Сделайте транзакции короткими. Длинные транзакции удерживают блокировки и блокируют других пользователей.
• Никогда не ждите пользователя или медленного API внутри транзакции. Делайте это до начала или после него.
• Всегда обрабатывайте ошибки. Каждый путь должен заканчиваться фиксацией или откатом.
• Добавьте ограничения. Они задают базе данных реальные правила, которые необходимо соблюдать. Хороший дизайн таблицы тоже помогает.
• Используйте хорошие индексы. Более быстрый поиск означает, что блокировки удерживаются меньше времени.
• Будьте осторожны с повторными попытками. Повторяйте попытки в случае взаимоблокировок, но убедитесь, что операция не может быть применена дважды.
• Протестируйте параллелизм нарочно. Многие из этих ошибок появляются только под нагрузкой, поэтому одни только базовые интеграционные тесты могут их не выявить.

Итоги

Транзакции превращают несколько рискованных шагов в одну безопасную операцию, а ACID описывает гарантии, которые делают эту операцию заслуживающей доверия. Всякий раз, когда деловое действие затрагивает более одной строки или таблицы, спросите себя, что произойдет, если оно остановится на полпути. Если ответ вас беспокоит, оберните его в транзакцию. Для практики откройте два сеанса в локальной базе данных и попытайтесь создать аномалии самостоятельно.

Часто задаваемые вопросы

Продолжайте учиться