Базовый курс SQL. Транзакции

[к оглавлению]

Что это такое

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

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

Давайте рассмотрим на примере. Чтобы принять студентов на первый курс, нам необходимо для каждого из них выполнить действия:

1. Проверить, содержат ли таблицы faculties и teachers необходимые нам данные о факультете и кураторе группы будущего студента
2. Если данных не достаточно, внести их в соответствующие таблицы
3. Получить данные о группе, в которой будет обучаться студент из таблицы groups
4. Или добавить в таблицу новую строку, если такой группы ещё нет
5. Добавить данные о студенте в таблицу students

Все эти таблицы связаны друг с другом внешними ключами. Что произойдёт с данными, если на каком-либо этапе выполнения кода возникнет ошибка? Причин может быть много: нехватка места на диске, блокировка таблицы, вопросы к безопасности данных, - а результат один: цепочка будет разорвана, и последующие её звенья не будут выполнены. А значит, данные в базе данных будут неполными или вовсе ложными.

Как же транзакции решают эту проблему? SQL-запросы, хранящиеся внутри транзакции , могут быть выполнены только все вместе, или будут полностью отменены. Если ошибок не возникало, результаты будут записаны в базу данных, и мы можем быть уверенными, что все необходимые опреации выполнены. Если же произошла ошибка, база данных вернётся к последнему корректному состоянию.

Немного терминологии:

• ТРАНЗАКЦИЯ - полный набор SQL-запросов, ограниченных контрольными точками
• ОТКАТ - процесс возврата к исходному состоянию БД
• ФИКСАЦИЯ - запись данных в БД
• ТОЧКА СОХРАНЕНИЯ - промежуточное состояние внутри транзакции, к которому можно вернуться, не отменяя всех запросов транзакции

Что можно отменить, а что нельзя

Обратите внимание на то, что команда ROLEBACK не отменяет действие CREATE и DROP. Будьте осторожны, их использование даже внутри транзакции может повлечь потерю данных. А вот результаты INSERT, UPDATE и DELETE можно откатить.

Как управлять транзакцией

Начинается транзакция с ключевого слова BEGIN. Концом транзакции считается точка, в которой происходит сохранение данных COMMIT, или когда следует откатить изменения ROLLBACK.

Различия в синтаксисе

В различных СУБД синтаксис транзакций может отличаться. Для уточнения, обратитесь к додкументации своей СУБД.

Пример простейшей транзакции для СУБД Oracle:

BEGIN TRANSACTION
DELETE Students WHERE group_id = 56
DELETE Groups WHERE group_id = 56
COMMIT TRANSACTION

В данном примере из базы данных полностью удаляются данные о группе. Чтобы не допустить частичного удаления, два запроса обёрнуты в транзакцию гарантирующую, что будут выполнены оба, или данные полностью сохранятся.

Тот же пример для MySQL и MariaDB:

START TRANSACTION;
DELETE * FROM Students WHERE group_id = 56;
DELETE * FROM Groups WHERE group_id = 56;
COMMIT;

Для PostgreSQL:

BEGIN;
DELETE * FROM Students WHERE group_id = 56;
DELETE * FROM Groups WHERE group_id = 56;
COMMIT;

Как и COMMIT, ROLLBACK влияет на транзакцию полностью. Но не сохраняет данные, а отменяет изменения. Для PostgreSQL:

BEGIN;
DELETE * FROM Students WHERE group_id = 56;
DELETE * FROM Groups WHERE group_id = 56;
ROLLBACK;

В реальных программах ROLLBACK обычно вызывается:

• Неявно: выполняется автоматически при возникновении ошибки ДО команды COMMIT
• Явно: указывается при соблюдении неких условий, устанавливаемых программой

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

Дополнительные точки сохранения

Также существует возможность добавлять дополнительные точки сохранения данных. Это позволяет реализовывать более сложные логические цепочки, которые требуют частичного сохранения или отката данных.
Например, мы создали данные о факультете faculties, группе groups и начали добавлять студентов students. Ошибка произошла при добавлении данных об одном из учащихся. В этом случае, мы хотели бы сохранить уже внесённые корректные данные: о факультете и группе.
Для этого нам следует разместить контрольные точки в местах транзакции, где изменение данных можно приостановить без потери их целостности. И далее, с помощью ROLLBACK, вернуться к любой из меток.

В PostgreSQL, MySQL, MariaDB, Oracle контрольные точки можно создать с помощью оператора SAVEPOINT:

SAVEPOINT insert_group;

Обязательно следует давать контрольным точкам имена, чтобы СУБД понимала, до какого момента следует произвести откат данных:

ROLLBACK TO insert_group;

Итак, транзакции - это мощный инструмент работы со связанными блоками SQL-запросов, предоставляющий возможность выполнить весь пакет целиком или откатить данные к изначальному виду. Для этого используются команды COMMIT, ROLLBACK, а также дополнительные точки сохранения SAVEPOINT. Мы рассмотрели только основные понятия в теме транзакций, для более полного изучения этого инструмента, обратитесь к документации своей СУБД.

Следующая страница. Курсоры.
Оглавление.