Транзакция в SQL

Транзакция в SQL — это логически завершённая последовательность операций с данными в базе данных, которая выполняется как единое целое. Транзакция обеспечивает атомарность, согласованность, изолированность и долговечность (свойства ACID) изменений, гарантируя, что либо все операции внутри транзакции будут успешно применены, либо ни одна из них не окажет влияния на состояние базы данных. Транзакции являются фундаментальным механизмом реляционных систем управления базами данных (СУБД), позволяющим поддерживать целостность данных при параллельном доступе и в случае сбоев.

История и развитие

Концепция транзакций возникла в 1970-х годах в связи с развитием реляционных баз данных и необходимостью обработки одновременных запросов. Ранние работы Джима Грея, Андреаса Рейтера и других исследователей заложили основы теории транзакций. В 1983 году Тео Хардер и Андреас Рейтер ввели аббревиатуру ACID (Atomicity, Consistency, Isolation, Durability), которая стала стандартом требований к транзакционным системам.

В языке SQL поддержка транзакций была стандартизирована в спецификации SQL-92 (ISO/IEC 9075). До этого разные СУБД (например, Oracle, IBM DB2, Microsoft SQL Server) реализовывали транзакции по-своему. Современные стандарты SQL (SQL:2016, SQL:2023) включают расширенные возможности управления транзакциями, такие как уровни изоляции, точки сохранения (savepoints) и распределённые транзакции.

Свойства ACID

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

• Атомарность (Atomicity). Транзакция выполняется полностью или не выполняется вовсе. Если в процессе выполнения происходит сбой (например, отказ сервера или нарушение ограничений целостности), все уже выполненные операции откатываются (rollback). Атомарность гарантирует, что база данных не остаётся в промежуточном, несогласованном состоянии.

• Согласованность (Consistency). Транзакция переводит базу данных из одного согласованного состояния в другое согласованное состояние. Все ограничения целостности (первичные ключи, внешние ключи, уникальность, проверочные ограничения) должны быть соблюдены как до начала, так и после завершения транзакции. Если какое-либо ограничение нарушается, транзакция откатывается.

• Изолированность (Isolation). Результаты выполнения одной транзакции не видны другим транзакциям до её полного завершения. Уровень изолированности определяет, насколько одна транзакция защищена от изменений, вносимых параллельно выполняющимися транзакциями. Стандарт SQL определяет четыре уровня изолированности (см. ниже).

• Долговечность (Durability). После успешного завершения транзакции (коммита) все внесённые изменения становятся постоянными и сохраняются даже в случае последующего сбоя системы. Обычно это реализуется через журнал транзакций (write-ahead log, WAL), который позволяет восстановить данные после аварии.

Жизненный цикл транзакции

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

1. Начало (BEGIN). Транзакция явно или неявно начинается. В большинстве СУБД первая команда DML (INSERT, UPDATE, DELETE) автоматически начинает транзакцию, если она не была начата явно. Для явного начала используется команда BEGIN TRANSACTION (или BEGIN / START TRANSACTION в разных диалектах).

1. Активное выполнение. Внутри транзакции выполняются SQL-операторы. Все изменения временно фиксируются в памяти и в журнале транзакций, но не становятся окончательными до коммита.

1. Фиксация (COMMIT). Команда COMMIT завершает транзакцию успешно. Все изменения, сделанные в рамках транзакции, становятся постоянными и видимыми для других транзакций.

1. Откат (ROLLBACK). Команда ROLLBACK отменяет все изменения, сделанные с момента начала транзакции (или с последней точки сохранения). База данных возвращается в состояние, предшествовавшее началу транзакции.

1. Точка сохранения (SAVEPOINT). Промежуточная метка внутри транзакции, к которой можно частично откатиться, не отменяя всю транзакцию. Это полезно для сложных операций, где часть изменений уже можно зафиксировать, а часть — отменить.

Уровни изолированности

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

• READ UNCOMMITTED (чтение незафиксированных данных). Самый низкий уровень. Транзакция может читать данные, изменённые другими транзакциями, даже если те ещё не зафиксированы (грязное чтение). Этот уровень редко используется из-за риска получения несогласованных данных.

• READ COMMITTED (чтение зафиксированных данных). Транзакция видит только те изменения, которые были зафиксированы другими транзакциями до начала её выполнения. Грязное чтение исключено, но возможны неповторяемое чтение (когда повторное чтение одной и той же строки в рамках транзакции даёт разные результаты) и фантомное чтение (появление новых строк, удовлетворяющих условию запроса).

• REPEATABLE READ (повторяемое чтение). Гарантирует, что если транзакция прочитала строку, то при повторном чтении в рамках той же транзакции строка не изменится (неповторяемое чтение исключено). Однако фантомное чтение возможно: другие транзакции могут добавлять новые строки, соответствующие условию.

• SERIALIZABLE (сериализуемый). Самый строгий уровень. Транзакции выполняются так, как если бы они были последовательными (одна за другой). Исключены грязное чтение, неповторяемое чтение и фантомное чтение. Достигается за счёт блокировок или механизмов многоверсионности (MVCC).

На практике уровень изолированности по умолчанию различается в разных СУБД: PostgreSQL и Oracle используют READ COMMITTED, MySQL (InnoDB) — REPEATABLE READ, Microsoft SQL Server — READ COMMITTED.

Проблемы параллельного доступа

При одновременном выполнении нескольких транзакций могут возникать следующие аномалии:

• Грязное чтение (dirty read). Транзакция A читает данные, изменённые транзакцией B, но ещё не зафиксированные. Если B откатывается, A оказывается с некорректными данными.

• Неповторяемое чтение (non-repeatable read). Транзакция A читает строку, затем транзакция B изменяет эту строку и фиксирует изменения. При повторном чтении A видит другое значение.

• Фантомное чтение (phantom read). Транзакция A выполняет запрос с условием (например, WHERE status = 'active'), затем транзакция B вставляет новую строку, удовлетворяющую условию, и фиксирует. При повторном выполнении запроса A видит дополнительную строку.

• Потерянное обновление (lost update). Две транзакции одновременно читают одни и те же данные, изменяют их и фиксируют. Изменения одной транзакции перезаписывают изменения другой.

Управление транзакциями в SQL

Явное управление

Большинство СУБД позволяют явно начинать, фиксировать и откатывать транзакции:

``sql BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; COMMIT; ``

Если в процессе выполнения возникает ошибка, транзакция откатывается:

``sql BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; -- Ошибка: недостаточно средств ROLLBACK; ``

Автоматические транзакции

В некоторых СУБД (например, MySQL с движком MyISAM, который не поддерживает транзакции) каждая отдельная SQL-команда выполняется как отдельная транзакция (автокоммит). В транзакционных движках (InnoDB, PostgreSQL) автокоммит может быть включён или выключен.

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

Точки сохранения позволяют частично откатить транзакцию:

``sql BEGIN TRANSACTION; INSERT INTO logs VALUES ('Start process'); SAVEPOINT after_insert; UPDATE data SET value = 100 WHERE id = 1; -- Если что-то пошло не так, откатываемся к точке сохранения ROLLBACK TO SAVEPOINT after_insert; COMMIT; ``

Транзакции в распределённых системах

В распределённых базах данных (например, в системах с репликацией или шардированием) транзакции могут затрагивать несколько узлов. Для обеспечения атомарности в таких сценариях используется протокол двухфазной фиксации (Two-Phase Commit, 2PC). Он включает фазу подготовки (когда все участники подтверждают готовность зафиксировать изменения) и фазу фиксации (когда координатор даёт команду на фиксацию). Однако 2PC может приводить к блокировкам при сбоях, поэтому в современных распределённых системах (например, в NewSQL-решениях) применяются более сложные протоколы, такие как Paxos или Raft.

Реализация в популярных СУБД

• PostgreSQL. Поддерживает все четыре уровня изолированности, реализованные через механизм многоверсионности (MVCC). Уровень по умолчанию — READ COMMITTED. Поддерживает точки сохранения и распределённые транзакции через двухфазную фиксацию.

• MySQL (InnoDB). Полностью транзакционный движок. Уровень изолированности по умолчанию — REPEATABLE READ. Использует MVCC и блокировки на уровне строк. Поддерживает точки сохранения.

• Microsoft SQL Server. Поддерживает уровни изолированности READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE, а также дополнительный уровень SNAPSHOT (моментальный снимок), основанный на версионировании строк. По умолчанию — READ COMMITTED.

• Oracle Database. Использует многоверсионность для обеспечения уровня READ COMMITTED по умолчанию. Поддерживает SERIALIZABLE и READ ONLY транзакции. Не поддерживает READ UNCOMMITTED (считается избыточным из-за MVCC).

Практические рекомендации

• Использовать транзакции для всех операций, которые должны быть атомарными (например, перевод денег между счетами).
• Выбирать минимально необходимый уровень изолированности для баланса между производительностью и согласованностью.
• Избегать длительных транзакций, так как они могут блокировать другие операции и увеличивать нагрузку на систему.
• Применять точки сохранения для сложных многошаговых операций, чтобы избежать полного отката при частичных ошибках.
• В веб-приложениях транзакции обычно управляются на уровне бизнес-логики (например, в рамках одного HTTP-запроса).

Источники

• ISO/IEC 9075:2023 — Information technology — Database languages — SQL
• Gray, J., & Reuter, A. (1993). Transaction Processing: Concepts and Techniques. Morgan Kaufmann.
• Date, C. J. (2003). An Introduction to Database Systems (8th ed.). Addison-Wesley.
• Документация PostgreSQL: «Transaction Isolation»
• Документация MySQL: «InnoDB Transaction Model»
• Документация Microsoft SQL Server: «Transactions (Database Engine)»