Обработка транзакций в SQL

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

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

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

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

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

Обработка транзакций обеспечивает сохранение целост­ности базы данных за счет того, что пакеты операций SQL выполняются полностью или не выполняются вовсе.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При работе с транзакциями вы часто будете сталкивать­ся с одними и теми же терминами:

■ Транзакция (Transaction). Блок операторов SQL.

■ Отмена (Rollback). Процесс аннулирования указан­ных операторов SQL (такой процесс иногда называют "откат").

■ Фиксация (Commit). Запись несохраненных операто­ров SQL в таблицы базы данных.

■ Точка сохранения (Savepoint). Временное состояние в ходе выполнения транзакции, в которое можно вернуться после отмены части операций пакета (в от­личие от отмены всей транзакции). Иногда это со­стояние называют "точка отката".

Действие каких операторов можно отменить?

Обработка транзакций используется в ходе управле­ния действием операторов insert, update и delete. Вы не можете отменить действие оператора select. (В выполнении такой отмены вообще нет смысла.) Вы не можете отменить операции create или drop. Эти операторы можно использовать в блоке операторов транзакции, но если вам понадобится выполнить от­мену (откат), действие этих операторов аннулировано не будет.

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

Теперь, когда вы знаете, что такое обработка транзак­ций, перейдем к управляемым транзакциям.

о_ Различия в реализациях

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

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

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

ВВОД

BEGIN TRANSACTION COMMIT TRANSACTION

Анализ

В этом примере все операторы, заключенные между BEGIN TRANSACTION И COMMIT TRANSACTION, должны быть или выполнены, или не выполнены.

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

ВВОД

START TRANSACTION

ВВОД

В СУБД PostgreSQL используется синтаксис ANSI SQL:

ввод

B3GIN;

В других СУБД используются вариации на указанную

тему.

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

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

ввод

DELETE FROM Orders; ROLLBACK;

Анализ

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

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

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

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