Использование транзакций для обеспечения безопасности параллелизма в работе с базой данных

В двух предыдущих лекциях рассказывалось о том, как установить соединение с удаленными источниками данных. Для приложений, использующих распределенные клиенты, особенно важным является вопрос исключения перезаписи ввода одного пользователя другим пользователем. Как разработчик приложений, вы должны также гарантировать, что набор связанных изменений не будет нарушен, если эти изменения завершены лишь частично. Эти проблемы настолько важны для приложений баз данных в целом, что в данном курсе целая лекция посвящена тому, как SQL Server способствует их решению. В этой лекции объясняется, как упаковка изменений данных в транзакцию одновременно и препятствует тому, чтобы пользователи наступали друг другу на пятки, и дает разработчику приложений возможность обеспечить целостность данных.

Бизнес-транзакции и транзакции SQL Server

Любой бизнес-процесс состоит из одной или нескольких транзакций. Представьте себе, что вы являетесь владельцем интернет-магазина. Когда клиент заказывает изделие, то, чтобы гарантировать своевременную доставку, должен запуститься предварительно заданный процесс. Этот процесс должен также предусматривать обработку кредитной карты, чтобы гарантировать получение оплаты вашей компанией. Если при выполнении одной из этих задач произойдет непоправимый сбой, то весь процесс необходимо отменить, чтобы не оказаться в ситуации, когда клиент получит счет, но не получит товар, и наоборот. В большинстве случаев такие процессы обрабатываются вычислительными системами, в которых все данные хранятся в базах данных. Данные, относящиеся к одной бизнес-транзакции, должны изменяться, чтобы оставаться доступными, согласованными и полными и отражать бизнес-процессы. Этого можно добиться, используя транзакции на уровне базы данных. Транзакцией называется последовательность операций, выполняемых, как один логический блок, и имеющая следующие свойства (так называемые свойства ACID):

Атомарность (Atomicity).Каждая транзакция представляет собой единицу работы. Она не может быть разбита на меньшие части. Это свойство означает, что выполняются либо все изменения данных, определенные в данной транзакции, либо не выполняется ни одно из них.

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

Изолированность (Isolation). Транзакции следует изолировать от изменений данных другими транзакциями. Это означает, что ни одна другая операция не должна изменять данные в промежуточном (незафиксированном) состоянии. Чтобы предохранить промежуточные данные от изменения, транзакция должна либо подождать подтверждения изменений данных другой транзакцией, либо видеть данные в подтвержденном ранее состоянии.

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

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

1. Определите границы транзакции. Разработчик должен определить, где начинается и где заканчивается транзакция. Транзакция всегда должна быть настолько короткой, насколько это возможно, но ее продолжительность должна быть достаточной, чтобы транзакцию можно было сопоставить требованиям бизнес-процесса.
2. Разработайте механизм управления ошибками. Не все ошибки вызывают автоматический откат транзакций. Разработка управления ошибками является обязанностью разработчика.
3. Определите уровень изолированности транзакции. Изолированность различных транзакций всегда имеет негативные последствия для параллелизма. Если вы полностью изолируете какую-либо транзакцию, а другой транзакции нужно будет прочитать те же данные, но в предыдущем состоянии, то вторая транзакция будет блокирована до завершения первой. Это может стать большой проблемой для систем баз данных с множеством параллельных подключений. SQL Server реализует различные варианты уровней изолированности, которые следует выбирать очень тщательно. По общему правилу, выбирать следует тот уровень, который блокирует наименьший объем данных на кратчайший промежуток времени, но при этом обеспечивает необходимый уровень защищенности транзакции.

Весь остальной материал данной лекции посвящен разработке и реализации транзакций в SQL Server.

Определение транзакций в SQL Server

Давайте посмотрим, как можно определить и обработать транзакции в SQL Server 2005. SQL Server предоставляет различные способы обработки транзакций, которые можно определить для каждого соединения с базой данных. Любое соединение может использовать тот режим, который необходим для выполнения специфических для этого соединения требований. Вот эти режимы:

• Автофиксация транзакций
• Явные транзакции
• Неявные транзакции

Режим автофиксации

SQL Server обрабатывает все изменения как транзакции. Никакое изменение данных не может произойти иначе, как в процессе транзакции. Следовательно, SQL Server приходится самому определять транзакцию, если она не определена разработчиком. Транзакции, определяемые SQL Server, называются также транзакцией с автофиксацией. Режим автофиксации используется SQL Server по умолчанию.

Изучаем транзакции с автофиксацией

1. В меню Start (Пуск) выберите All Programs,. Microsoft SQL Server 2005, SQL Server Management Studio (Все программы, Microsoft SQL Server 2005, Среда SQL Server Management Studio). Откройте окно нового запроса, нажав кнопку New Query (Новый запрос) на панели инструментов.
2. Введите и выполните следующую инструкцию CREATE TABLE, чтобы создать небольшую таблицу, которую мы будем использовать в следующем опыте для изучения поведения транзакции. Код этого примера можно найти среди файлов примеров под именем ExploringAutoCommit.sql.

   USE tempdb;
   GO
   CREATE TABLE table1 (
   i int NOT NULL PRIMARY KEY,
   col1 varchar(20) NOT NULL,
   col2 varchar(20) NULL);
3. Теперь давайте вставим в таблицу table 1 три новых строки. Для этого введите следующие инструкции в окне запроса и выполните все три инструкции вместе.

   USE tempdb;
   GO
   INSERT INTO table1 (i,col1,col2)
   VALUES (1,'First row','First row');
   INSERT INTO table1 (i,col1,col2) 
   VALUES (2,NULL,'Second row');
   INSERT INTO table1 (i,col1,col2) 
   VALUES (3,'Third row','Third row');
4. Вы получите показанное на рисунке сообщение, которое информирует вас о том, что SQL Server не разрешает вставку значения NULL в столбец col1, потому что для этого столбца задано условие NOT NULL.
   

   
   
5. Введите и выполните следующую инструкцию SELECT, чтобы проверить, были ли записи успешно вставлены в таблицу.

   USE tempdb;
   GO
   SELECT i,col1,col2
   FROM table1;
6. Как видите, вставка второй строки не выполнена, но первая и третья строки успешно вставлены. Когда SQL Server использует транзакции с автофиксацией, каждая инструкция рассматривается как транзакция. Если одна инструкция генерирует ошибку, соответствующая ей транзакция автоматически подвергается откату. Если инструкция успешно и без ошибок выполняется, то транзакция автоматически фиксируется. Следовательно, инструкции 1 и 3 были зафиксированы, а инструкция 2, вызвавшая ошибку, была отменена. Обратите внимание на то, что такое поведение имеет место даже в том случае, если три инструкции передаются вместе в виде пакета. Пакетное выполнение не определяет, следует ли обрабатывать все инструкции в пакете как единую транзакцию.

Явные транзакции

Для явной транзакции разработчик определяет начало транзакции и момент, в который она должна быть зафиксирована или подвергнута откату. Это достигается при помощи инструкций T-SQL BEGIN TRANSACTION, COMMIT TRANSACTION и ROLLBACK TRANSACTION. Явные транзакции независимы от пакета. Явная транзакция может объединять несколько пакетов; в одном пакете может быть задано несколько явных транзакций.

Примечание. Вместо ключевого слова TRANSACTION можно использовать сокращение TRAN.

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

1. Запустите SQL Server Management Studio и откройте окно New Query (Новый запрос).
2. Введите и выполните следующую инструкцию, чтобы выполнить усечение таблицы table1. Код этого примера можно найти среди файлов примеров под именем DefineExplicitTransactions.sql.

   USE tempdb;
   GO
   TRUNCATE TABLE table1;
3. Теперь вставьте те же три записи в таблицу table1. На этот раз сгруппируйте инструкции в явную транзакцию, поскольку необходимо, чтобы в таблицу были вставлены все записи, или не было вставлено ни одной из них. В окне запроса введите следующие инструкции и выполните все эти инструкции как одну.

   USE tempdb;
   GO
   BEGIN TRAN
   INSERT INTO table1 (i,col1,col2) VALUES (1,'First row','First row');
   INSERT INTO table1 (i,col1,col2) VALUES (2,NULL,'Second row');
   INSERT INTO table1 (i,col1,col2) VALUES (3,'Third row','Third row');
   COMMIT TRAN;
4. Вы получите такое же сообщение, как и раньше, в котором сообщается, что SQL Server не разрешает вставку значения NULL в столбец col1, потому что для этого столбца задано условие NOT NULL.
5. Введите и выполните следующую инструкцию SELECT, чтобы проверить, были ли вставлены записи.

   USE tempdb;
   GO
   SELECT i,col1,col2
   FROM table1;
6. Вы видите, что результат тот же, что и в режиме автофиксации. Две из трех записей вставлены в таблицу, а одна, нарушающая ограничение NULL, не вставлена. Что произошло? Как отмечалось ранее, обязанностью разработчика является не только определение длины транзакции, но и то, должен ли выполняться откат. Поэтому в транзакцию необходимо добавить обработчик ошибок. Без обработчика ошибок SQL Server после ошибки просто обработает следующую инструкцию, потому что пакет не отменяется. В предыдущем пакете SQL Server просто обрабатывает каждую инструкцию INSERT и после этого обрабатывает инструкцию COMMIT TRAN. Следовательно, у нас тот же результат, что и в режиме автофиксации.
7. Чтобы добавить обработчик ошибок, можно использовать новые блоки языка T-SQL SQL Server 2005 TRY и CATCH. Снова выполните усечение таблицы, а затем запустите транзакцию с обработчиком ошибок, как показано ниже.

   —выполняем усечение таблицы 
   TRUNCATE TABLE table1
   —транзакция с обработчиком ошибок 
   BEGIN TRY 
    BEGIN TRAN
     INSERT INTO table1 (i,col1,col2) VALUES (1,'First row','First row'); 
     INSERT INTO table1 (i,col1,col2) VALUES (2,NULL,'Second row'); 
     INSERT INTO table1 (i,col1,col2) VALUES (3,'Third row','Third row');
    COMMIT TRAN; 
   END TRY 
   BEGIN CATCH
    ROLLBACK TRAN 
   END CATCH;

   В этом случае вы не получите сообщения об ошибке, поскольку ошибка была захвачена блоком CATCH.
8. Введите и выполните следующую инструкцию SELECT, чтобы проверить, был ли выполнен откат транзакции.

   USE tempdb;
   GO
   SELECT i,col1,col2
     FROM table1;

   Эта инструкция не возвратила ни одной записи. Как видите, произошел откат всей транзакции. Когда во второй инструкции INSERT произошло нарушение, SQL Server перешел к блоку CATCH и выполнил откат транзакции.
9. Остается только одна проблема – это код не возвращает каких-либо сообщений, которые информировали бы о том, что произошла ошибка. Это поведение управляется в блоке CATCH, в котором можно использовать особые функции для возвращения ошибок; можно также использовать функцию RAISERROR для задания пользовательского текста сообщения об ошибке. Измените блок CATCH как показано ниже.

   BEGIN CATCH
   SELECT ERROR_NUMBER() AS ErrorNumber,
   ERROR_SEVERITY() AS ErrorSeverity,
   ERROR_STATE() as ErrorState,
   ERROR_PROCEDURE() as ErrorProcedure,
   ERROR_LINE() as ErrorLine,
     ERROR_MESSAGE() as ErrorMessage; 
     RAISERROR("Error in Transaction!",14,1) 
     ROLLBACK TRAN 
   END CATCH;
10. Еще раз выполните всю транзакцию из пункта 9. Теперь будет возвращена запись со всей информацией, имеющей отношение к ошибке, и пользовательский текст сообщения, в котором говорится о том, что произошла ошибка. Конечно, в инструкцию RAISERROR. можно также включить реальное сообщение об ошибке. В этом случае блок CATCH будет выглядеть следующим образом:

    BEGIN CATCH
    DECLARE @er nvarchar(max)
    SET @er = "Error: "+ ERROR_MESSAGE();
    RAISERROR(@er,14,1);
    ROLLBACK TRAN END CATCH;