Обработка транзакций
20. Обработка транзакций
20.1. Что такое транзакция
Транзакции представляют собой блоки из операторов SQL, которые должны выполняться в пакетном режиме.
Обработка транзакций обеспечивает сохранение целостности базы данных за счет того, что пакеты операций SQL полностью или не выполняются вовсе.
Рассмотрим процесс добавления нового заказа по этапам.
1. Проверка, содержится ли информация о клиенте в базе данных. Если нет, такая информация добавляется.
2. Выборка идентификатора клиента.
3. Добавление строки в таблицу Orders, связывающую строку с идентификатором клиента.
4. Выборка идентификатора нового заказа, присвоенного ему в таблице Orders.
Рекомендуемые материалы
5. Добавление одной строки в таблицу OrderItems для каждого заказанного предмета, соотнесение его с таблицей Orders посредством выбранного идентификатора и с таблицей Products посредством идентификатора продукта.
Теперь предположим, что какая-то ошибка в базе данных, например, нехватка места на диске, ограничения, связанные с безопасностью, блокировка таблицы, помешала завершить эту последовательность действий.
Что в этом случае случится с данными?
Хорошо, если ошибка произойдет после добавления информации о клиенте в таблицу, но до того как она будет добавлена в таблицу Orders – в этом случае проблем не будет, так как можно иметь данные о клиентах без заказов. При повторном выполнении последовательности добавленная запись о клиенте будет возвращена и использована. Можно будет продолжить работу с того места, на котором остановились.
На что будет, если ошибка произойдет после того, как была добавлена строка в таблицу Orders, но до того, как будут добавлены строки в таблицу OrderItems? Теперь в базе данных будет присутствовать пустой заказ.
Еще будет хуже, если система сделает ошибку в процессе добавления строк в таблицу OrderItems? В этом случае в базу данных заказ будет внесен лишь частично, и мы даже не будем знать об этом.
Как решается эта проблема? Необходимо применить транзактную организацию обработки данных, т.е. обработку транзакций.
Обработка транзакций – это механизм, используемый для управления наборами операций SQL, которые должны быть выполнены в пакете, т.е. таким образом, чтобы в базу данных не могли попасть результаты частичного выполнения этого пакета операций. При обработке транзакций можно быть уверенным в том, что выполнение набора операций не было прервано на середине – они были выполнены все, или не была выполнена ни одна из них. Если ошибки не произошло, результаты работы всего набора операторов записываются в таблицах базы данных. Если произошла ошибка, должна быть выполнена отмена всех операций, чтобы возвратить базу данных в известное и безопасное состояние.
Возвращаясь к примеру, процесс должен выполняться так.
1. Проверка, содержится ли информация о клиенте в базе данных. Если нет, такая информация добавляется.
2. Фиксация информации о клиенте.
3. Выборка идентификатора клиента.
4. Добавление строки в таблицу Orders, связывающую строку с идентификатором клиента.
5. Если во время добавления строки в таблицу Orders происходит ошибка, операция отменяется.
6. Выборка идентификатора нового заказа, присвоенного ему в таблице Orders.
7. Добавление одной строки в таблицу OrderItems для каждого заказанного предмета, соотнесение его с таблицей Orders посредством выбранного идентификатора и с таблицей Products посредством идентификатора продукта.
8. Если в процессе добавления строк в таблицу OrderItems происходит ошибка, добавление всех строк в таблицу OrderItems отменяется.
При работе с транзакциями определяются термины:
· Транзакция (Transaction). Блок операторов SQL.
· Отмена (Rollback). Процесс аннулирования указанных операторов SQL. (Другое название - откат).
· Фиксация (Commit). Запись несохраненных операторов SQL в таблицы базы данных.
· Точка сохранения (Savepoint). Временное состояние в ходе выполнения транзакции, в которое можно вернуться после отмены части операций пакета, в отличие от отмены всей транзакции. Иногда это состояние называют «точка отката».
Обработка транзакций используется в ходе управления действием операторов INSERT, UPDATE и DELETE, а также CREATE, DROP. Нет смысла отменять действие оператора SELECT.
20.2. Управляемые транзакции
Точный синтаксис, используемый для обработки транзакций, для разных СУБД различен.
Чтобы сделать транзакцию управляемой, нужно разбить ее SQL-операторы на логические части и явно указать, когда может быть выполнена отмена, а когда нет.
В некоторых СУБД требуется, чтобы явно отметили начало и конец каждого блока операторов транзакции. Например, в SQL Server нужно сделать следующее:
BEGIN TRANSACTION
. . .
COMMIT TRANSACTION
Все операторы, заключенные между BEGIN TRANSACTION и COMMIT TRANSACTION, должны быть выполнены, или не выполнены.
В СУБД MySQL это выглядит так:
START TRANSACTION
. . .
20.3. Использование оператора ROLLBACK
Оператор ROLLBACK используется для отмены операторов SQL. Это записывается так:
DELETE FROM Orders;
ROLLBACK;
В примере выполняется и сразу же, посредством оператора ROLLBACK, аннулируется операция DELETE. Это не самый полезный пример, он показывает, что будучи включенными в блок транзакции, операции DELETE (INSERT и UPDATE) не являются окончательными.
20.4. Использование оператора COMMIT
Обычно после выполнения операторов SQL результаты записываются непосредственно в таблицы баз данных. Это называется неявная фиксация – операция фиксации (сохранения или записи) выполняется автоматически.
Однако внутри блока транзакции фиксация неявно может и не проводиться. Это зависит от того, с какой СУБД вы работаете. Некоторые СУБД трактуют завершение транзакции как неявную фиксацию.
Для безусловного выполнения неявной фиксации используется оператор COMMIT. Пример для SQL Server:
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION
В примере заказ номер 12345 полностью удаляется из системы. Поскольку это приводит к обновлению двух таблиц базы данных, Orders и OrderItems, блок транзакции применяется для того, чтобы заказ не мог быть удален лишь частично. Конечный оператор COMMIT записывает изменения только в случае, если не произошло ошибки. Если первый оператор будет выполнен, а второй, из=за ошибки, не выполнен, удаление не будет зафиксировано.
Чтобы выполнить то же самое в СУБД Oracle, нужно сделать следующее:
DELETE OrderItems WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT;
20.5. Использование точек сохранения
Простые операторы ROLLBACK и COMMIT позволяют записывать или отменять транзакции в целом. Хотя это вполне применимо по отношению к простым транзакциям, для более сложных могут понадобиться частичные фиксации или отмены.
Например, процесс добавления заказа, описанный выше, представляет собой одну транзакцию. Если произойдет ошибка, база данных вернется в состояние, когда строка в таблицу Orders еще не была добавлена. Но вряд ли нужно отменить добавление данных в таблицу Customers, если оно было сделано.
Для отмены части транзакции необходимо иметь возможность размещение метое в важных точках блока транзакции. Потом, если понадобится отмена, можно вернуть базу данных в состояние, соответствующее одной из меток.
В языке SQL эти метки называются точками сохранения (savepoints). Для создания такой точки в СУБД MySQL и Oracle применяется оператор SAVEPOINT:
SAVEPOINT deletel;
В SQL Server это будет так:
Лекция 11 - Разработка системы адаптивного управления - лекция, которая пользуется популярностью у тех, кто читал эту лекцию.
SAVE TRANSACTION deletel;
Каждая точка сохранения должна иметь уникальное имя, идентифицирующее ее таким образом, чтобы, когда выполняется отмена, СУБД знала, в какую точку она должна вернуться. Чтобы выполнить отмену действия всех операторов после этой точки, в СУБД SQL Server нужно выполнить:
ROLLBACK TRANSACTIOM delete;
В MySQL и Oracle это так:
ROLLBACK TO delete;
Чем больше точек сохранения, тем лучше.