Вопрос 10) Обработка транзакций в SQL

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

Таблица Orders, которую мы использовали в последних 18-ти уроках, — хороший пример. Заказы хранятся в двух таблицах, в таблице Order Items хранится информация об отдельных предметах заказов. Эти две таблицы связаны (соотнесены) между собой с помощью уникального идентификатора, который называется первичный ключ (см. урок 1, "Что такое SQL")- Эти таблицы, кроме того, связаны и с другими таблицами, содержащими информацию о клиентах и продуктах.

Процесс добавления нового заказа состоит в выполнении следующих этапов.

1. Проверка, содержится ли информация о клиенте в базе данных. Если нет, такая информация добавляется.

2. Выборка идентификатора клиента.

3. Добавление строки в таблицу Orders, связывающую ее (строку) с идентификатором клиента.

4. Выборка идентификатора нового заказа, присвоенного ему в таблице Orders

5. Добавление одной строки в таблицу Order Items для каждого заказанного предмета, соотнесение его с таблицей Orders посредством выбранного идентификатора (и с таблицей Products посредством идентификатора продукта).

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

Что случится с данными?

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

Но что если ошибка произойдет после того, как была добавлена строка в таблицу Orders, но до того, как будут добавлены строки в таблицу Orderltems? Теперь в вашей базе данных будет присутствовать пустой заказ.

Еще хуже: что если система сделает ошибку в процессе добавления строк в таблицу Orderltems? В таком случае в вашу базу данных заказ будет внесен лишь частично, и вы даже не будете знать об этом.

Как можно решить эту проблему? Именно здесь в игру вступает транзактная организация обработки данных, которую мы ради краткости будем называть обработка транзакций. Обработка транзакций — это механизм, используемый для управления наборами операций SQL, которые должны быть выполнены в пакете, т.е. таким образом, чтобы в базу данных не могли попасть результаты частичного выполнения этого пакета операций. При обработке транзакций вы можете быть уверенными в том, что выполнение набора операций не было прервано на середине — они или были выполнены все, или не была выполнена ни одна из них (если только не было явно указано иное). Если ошибки не произошло, результаты работы всего набора операторов фиксируются (записываются) в таблицах базы данных. Если произошла ошибка, должна быть выполнена отмена (аннулирование) всех операций, чтобы возвратить базу данных в известное и безопасное состояние.

Итак, если вернуться к нашему примеру, то вот как должен на самом деле выполняться процесс.

1. Проверка, содержится ли информация о клиенте в базе данных. Если нет, такая информация добавляется.

2. Фиксация информации о клиенте.

3. Выборка идентификатора клиента.

4. Добавление строки в таблицу Orders.

5. Если во время добавления строки в таблицу Orders происходит ошибка, операция отменяется.

6. Выборка идентификатора нового заказа, присвоенного ему в таблице Orders

7. Добавление одной строки в таблицу Orderltems для каждого заказанного предмета.

8. Если в процессе добавления строк в таблицу Orderltems происходит ошибка, добавление всех строк в таблицу Orderltems отменяется.

В п.4.4.4 рассматривался пример, в котором требовалось изменить номер продукта ПР = 13 на ПР = 20 и для этого пришлось проводить последовательное изменение четырех таблиц:

UPDATE Продукты UPDATE Состав

SET ПР = 20 SET ПР = 20

WHERE ПР = 13; WHERE ПР = 13;

UPDATE Поставки UPDATE Наличие

SET ПР = 20 SET ПР = 20

WHERE ПР = 13; WHERE ПР = 13;

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

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

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

COMMIT (фиксировать), превращающее все предварительные обновления в окончательные ("зафиксированные");

ROLLBACK (откат), аннулирующее все предварительные обновления.

Таким образом, транзакцией можно назвать последовательность SQL-предложений, расположенных между "точками синхронизации", учреждаемых в начале выполнения программы и издании COMMIT или ROLLBACK и только в этих случаях. При этом следует иметь в виду, что возможен неявный COMMIT (существует режим AUTOCOMMIT, в котором система издает COMMIT после выполнения каждого SQL-предложения) и ROLLBACK (выполняемый при аварийном завершении программы).

Ясно теперь, что пользователь должен сам решать, включать ли механизм обработки транзакций и если включать, то где издавать COMMIT (ROLLEBACK), т.е. какие последовательности SQL-предложений являются транзакциями.

Теперь о проблемах, связанных с параллельным использованием базы данных множеством разнообразных пользователей.

Большинство СУБД позволяют любому числу транзакций одновременно осуществлять доступ к одной и той же базе данных и в них существуют те или иные механизмы управления параллельными процессами, предотвращающие нежелательные воздействия одних транзакций на другие. По сути это механизм блокирования, главная идея которого достаточно проста. Если транзакции нужны гарантии, что некоторый объект (база данных, таблица, строка или поле), в котором она заинтересована, не будет изменен каким-либо непредсказуемым образом в течение требуемого промежутка времени, она устанавливает блокировку этого объекта. Результат блокировки заключается в том, чтобы изолировать этот объект от других транзакций и, в частности, предотвратить его изменение средствами этих транзакций. Для первой транзакции, таким образом, имеется возможность выполнять предусмотренную в ней обработку, располагая определенными знаниями о том, что объект в запросе будет оставаться в стабильном состоянии до тех пор, пока данная транзакция этого пожелает.

Ограниченный объем книги заставляет нас завершить на самом интересном месте обсуждение чрезвычайно важного вопроса об управлении транзакциями и параллельном их исполнении. С этим материалом мы познакомим вас в следующей книге, посвященной прикладному программированию в среде СУБД. А о средствах SQL, используемых в прикладных программах, будет кратко рассказано далее.

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

Чтобы сделать транзакцию управляемой, нужно разбить ее SQL-операторы на логические части и явно указать, когда может быть выполнена отмена, а когда нет. В некоторых СУБД требуется, чтобы вы явно отметили начало и конец каждого блока операторов транзакции. Например, в SQL Server нужно сделать следующее:

BEGIN TRANSACTION COMMIT TRANSACTION

Эквивалентный код для MySQL таков:

START TRANSACTION

Использование операторов ROLLBACK и COMMIT

Оператор ROLLBACK используется для отмены (аннулирования) операторов SQL, как показано ниже:

DELETE FROM Orders;

ROLLBACK;

В этом примере выполняется и сразу же, посредством оператора ROLLBACK, аннулируется операция DELETE. Хотя это и не самый полезный пример, он все равно показывает, что, будучи включенными в блок транзакции, операции DELETE (а также INSERT и UPDATE) не являются окончательными.

Обычно после выполнения операторов SQL результаты записываются непосредственно к таблицы баз данных. Это называется неявная фиксация — операция фиксации (сохранения или записи) выполняется автоматически.

Однако внутри блока транзакции фиксация неявно может и не проводиться. Это зависит от того, с какой СУБД вы работаете. Некоторые СУБД трактуют завершение транзакции как неявную фиксацию.

Для безусловного выполнения неявной фиксации используется оператор COMMIT. Вот соответствующий пример для SQL Server:

BEGIN TRANSACTION

DELETE OrderItems WHERE order_num = 12345

DELETE Orders WHERE order_num = 12345

COMMIT TRANSACTION

В этом примере для SQL Server заказ номер 12345 полностью удаляется из системы. Поскольку это приводит к обновлению двух таблиц базы данных, Orders и Orderlteras, блок транзакции применяется для того, чтобы заказ не мог быть удален лишь частично. Конечный оператор COMMIT записывает изменения только в случае, если не произошло ошибки. Если первый оператор будет выполнен, а второй, из-за ошибки, не выполнен, удаление не будет зафиксировано.

Чтобы выполнить то же самое в СУБД Oracle, нужно сделать следующее:

DELETE OrderItems WHERE order_num = 12345;

DELETE Orders WHERE order_num = 12345;

COMMIT;

Использование точек сохранения

Простые операторы ROLLBACK и COMMIT позволяют записывать или отменять транзакции в целом. Хотя это вполне применимо по отношению к простым транзакциям, для более сложных могут понадобиться частичные фиксации или отмены. Например, процесс добавления заказа, описанный выше, представляет собой одну транзакцию. Если произойдет ошибка, вы просто вернетесь в состояние, когда строка в таблицу Orders еще не была добавлена. Но вы вряд ли захотите отменить добавление данных в таблицу Customers (если оно было сделано).

Для отмены части транзакции вы должны иметь возможность размещения меток в стратегически важных точках блока транзакции. Потом, если понадобится отмена, вы сможете вернуть базу данных в состояние, соответствующее одной из меток. В языке SQL эти метки называются точками сохранения (savepoints). Для создания такой точки в СУБД MySQL и Oracle применяется оператор SAVEPOINT:

SAVEPOINT deletel;

В SQL Server и Sybase нужно сделать следующее:

SAVE TRANSACTION deletel;

Каждая точка сохранения должна иметь уникальное имя, идентифицирующее ее таким образом, чтобы, когда вы выполняете отмену, СУБД "знала", в какую точку она должна вернуться. Чтобы выполнить отмену действия всех операторов после этой точки, в СУБД SQL Server нужно выполнить следующее:

ROLLBACK TRANSACTION deletel;

В MySQL и Oracle можно сделать так:

ROLLBACK TO deletel;

А вот полный пример для SQL Server:

BEGIN TRANSACTION

INSERT INTO Customers(cust_id, cust_name)

VALUES('1000000010', 'Toys Emporium');

SAVE TRANSACTION StartOrder;

INSERT INTO Orders(order_num, order_date, cust_id)

VALUES(2 0100,'2001/12/1','1000000010');

IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;

INSERT INTO Orderltems(order_num, order_item,

lbprod_id, quantity, item_price)

VALUES(20010, 1, 'BROl', 100, 5.49);

IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;

INSERT INTO Orderltems(order_num, order_item,

%prod_id, quantity, item_price)

VALUES(20010, 2, 'BR03', 100, 10.99);

IF @@ERR0R о 0 ROLLBACK TRANSACTION StartOrder;

COMMIT TRANSACTION

Здесь имеется набор, состоящий из четырех операторов INSERT, включенных в блок транзакции. Точка сохранения определена после первого оператора INSERT, так что если какая-то из последующих операций INSERT закончится неудачей, отмена транзакции произойдет лишь до этой точки. В SQL Server для контроля успешности завершения какой-либо операции может быть использована переменная с именем @@ERROR. (В других СУБД используются иные функции или переменные для возвращения такой информации.) Если переменная @@ERROR возвращает значение, отличное от О, значит, произошла ошибка и транзакция отменяется до точки сохранения. Если обработка транзакции в целом завершается успешно, выполняется операция COMMIT для сохранения данных.