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

Неявные транзакции

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

ALTER TABLE     GRANT     FETCH      DELETE
CREATE          REVOKE    INSERT     SELECT
DROP            OPEN      UPDATE     TRUNCATE TABLE

Применяем неявные транзакции

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

   SET IMPLICIT_TRANSACTIONS ON; GO
3. Выполните следующий код, чтобы создать таблицу для проверки запуска транзакции.

   CREATE TABLE T1 (i int PRIMARY KEY);
4. Чтобы проверить, открыта ли транзакция, можно использовать функцию @@TRANCOUNT ; выполните следующую инструкцию SELECT:

   SELECT @@TRANCOUNT AS [Transaction Count];
5. Результат равен 1 ; это означает, что соединение имеет открытую транзакцию. Значение 0 означало бы, что в данный момент не открыто ни одной транзакции, а число больше 1 - что имеют место вложенные транзакции (о которых речь пойдет позже).
6. Теперь вставьте в таблицу запись и снова проверьте значение @@TRANCOUNT, выполнив следующие инструкции:

   INSERT INTO T1 VALUES(5);
   GO
   SELECT @@TRANCOUNT AS [Transaction Count];

   Значение функции @@TRANCOUNT, как и прежде, равно 1. SQL Server не запустил новой транзакции, потому что уже существует одна открытая транзакция.
7. Теперь выполним откат транзакции и снова проверим значение функции @@TRANCOUNT, выполнив следующий код. Вы увидите, что значение @@TRANCOUNT будет равно 0 после выполнения инструкции ROLLBACK TRAN.

ROLLBACK TRAN
GO
SELECT @@TRANCOUNT AS [Transaction Count];

1. Попробуем сделать выборку из таблицы T1.

   SELECT * FROM T1;
2. Мы получим сообщение об ошибке, потому что таблицы больше не существует. Неявная транзакция была запущена при помощи инструкции CREATE TABLE, а инструкция ROLLBACK TRAN отменила результаты работы, выполненной первой инструкцией.
3. Отключим неявный режим, выполнив следующий код:

   SET IMPLICIT_TRANSACTIONS OFF;

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

Вложенные транзакции

Явные транзакции могут быть вложенными; это означает, что можно запускать явные транзакции в других явных транзакциях. Одна из основных причин поддержки этого механизма – это разрешение транзакций внутри хранимых процедур, независимо от того, была ли сама процедура вызвана из транзакции. Как же вложенные транзакции обрабатываются в SQL Server? Давайте изучим вложенные транзакции на двух простых примерах.

Изучаем вложенные транзакции

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

   PRINT "Trancount before transaction: " + 
         CAST(@@trancount as char(1)) 
   BEGIN TRAN
     PRINT "After first BEGIN TRAN: " + 
         CAST(@@trancount as char(1))
     BEGIN TRAN
       PRINT "After second BEGIN TRAN: " + 
             CAST(@@trancount as char(1))
     COMMIT TRAN
     PRINT "After first COMMIT TRAN: " + 
         CAST(@@trancount as char(1)) 
   COMMIT TRAN 
   PRINT "After second COMMIT TRAN: " + 
         CAST(@@trancount as char(1))
3. Из результата видно, что каждая инструкция BEGIN TRAN увеличивает значение @@TRANCOUNT на 1, а каждая инструкция COMMIT TRAN уменьшает значение на 1. Как мы уже наблюдали ранее, значение 0 означает, что не открыто ни одной транзакции. Следовательно, транзакция завершается, когда значение функции @@TRANCOUNT уменьшается от 1 до 0, что происходит при фиксации самой внешней транзакции. Таким образом, каждая внутренняя транзакция требует фиксации. Самая внешняя транзакция определяет, будут ли внутренние транзакции полностью фиксироваться, поскольку эта транзакция запускается первой инструкцией BEGIN TRAN и фиксируется только последней инструкцией COMMIT TRAN. Если эта самая внешняя транзакция не зафиксирована, то вложенные в нее транзакции также не будут зафиксированы.
4. Введите и выполните следующий пакет, чтобы проверить, что произойдет в случае отката транзакции.

   USE AdventureWorks 
   BEGIN TRAN
     PRINT "After 1st BEGIN TRAN: " + 
           CAST(@@trancount as char(1)) 
     BEGIN TRAN
       PRINT "After 2nd BEGIN TRAN: " + 
             CAST(@@trancount as char(1)) 
       BEGIN TRAN 
         PRINT "After 3rd BEGIN TRAN: " + 
               CAST(@@trancount as char(1))
         UPDATE Person.Contact
         SET EmailAddress = "test@test.at"
           WHERE ContactID = 20
       COMMIT TRAN 
       PRINT "After first COMMIT TRAN: " + 
             CAST(@@trancount as char(1)) 
   ROLLBACK TRAN 
   PRINT "After ROLLBACK TRAN: " + 
         CAST(@@trancount as char(1))
   
   SELECT EmailAddress FROM Person.Contact 
     WHERE ContactID = 20;
5. В этом примере адрес электронной почты контактного лица обновляется в процессе вложенной транзакции, которая сразу же фиксируется. Затем выполняется инструкция ROLLBACK TRAN. Инструкция ROLLBACK TRAN уменьшает значение функции @@TRANCOUNT с 0 до 1, и выполняет откат всей транзакции вместе со всеми вложенными транзакциями, независимо от того, были ли они до этого зафиксированы. Следовательно, обновление данных, выполненное в течение вложенной транзакции, подвергается откату, и данные не изменяются.

Всегда имейте в виду, что при использовании вложенных транзакций только самая внешняя транзакция определяет, будут ли зафиксированы внутренние транзакции. Каждая инструкция COMMIT TRAN всегда применяется к инструкции BEGIN TRAN, которая выполнялась последней. Следовательно, чтобы зафиксировать транзакцию, нужно вызывать инструкцию COMMIT TRAN для каждой выполненной инструкции BEGIN TRAN. Инструкция ROLLBACK TRAN всегда принадлежит самой внешней транзакции и поэтому всегда вызывает откат всей транзакции, независимо от того, сколько вложенных транзакций открыто. По этой причине управление вложенными транзакциями может быть непростым. Как утверждалось в начале этого раздела, вложенные транзакции чаще всего случаются во вложенных хранимых процедурах, где каждая процедура сама по себе запускает транзакцию. Вложенных транзакций можно избежать, если перед решением о том, нужно ли запускать транзакцию, проверять значение функции @@TRANCOUNT в начале этой процедуры. Если значение, возвращаемое функцией @@TRANCOUNT, больше 0, то не обязательно запускать новую транзакцию, поскольку процедура уже находится в состоянии транзакции, и вызывающий экземпляр может вызвать откат этой транзакции, если произойдет ошибка.

Управление изоляцией транзакций

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

• ИДЕНТИФИКАТОР СТРОКИ. Идентификатор строки используется для блокировки определенной строки в куче, если не существует кластеризованный индекс.
• КЛЮЧ. Блокируется ключ индекса или индекс. Если для таблицы существует кластеризованный индекс, то этот тип блокировки также используется для блокировки строки таблицы, потому что в кластеризованном индексе данные являются частью индекса. Дополнительную информацию о свойствах индексов можно найти в "Повышение производительности запроса" .
• СТРАНИЦА. Блокируется 8-килобайтная страница базы данных, которая может быть как страницей данных, так и страницей индекса.
• ТАБЛИЦА. Блокировка таблицы используется для блокировки всей таблицы для совершения какой-либо операции.

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

• Разделяемая блокировка ( Shared , S ).Разделяемые блокировки используются для блокировки данных от доступа для записи. Они предотвращают изменение данных другими транзакциями, но разрешают чтение данных. Разделяемые блокировки совместимы с другими разделяемыми блокировками, которые разрешают более чем одной транзакции иметь разделяемую блокировку на блокируемый ресурс. Следовательно, транзакции могут читать одни и те же данные параллельно.
• Монопольная блокировка ( Exclusive , X ).Монопольные блокировки используются для всех изменений данных. Они предотвращают доступ к данным со стороны других транзакций. Следовательно, монопольные блокировки не совместимы с другими блокировками.
• Блокировка изменений ( Update , U ).Блокировки изменений - это частный случай разделяемых блокировок. Они используются, главным образом, для поддержки инструкций UPDATE. В инструкции UPDATE данные должны считываться перед изменением. Следовательно, необходим такой тип блокировки, который не предотвращает считывания данных другими транзакциями во время считывания их данной транзакцией. Кроме того, когда SQL Server приступает к изменению данных, ему приходится повышать тип блокировки до монопольной. Для этой операции чтения SQL Server использует блокировки изменений, которые совместимы с разделяемыми блокировками, но не совместимы с другими блокировками изменений. Следовательно, другие транзакции могут считывать данные в то время, когда эти данные считываются для инструкции UPDATE, но другим инструкциям UPDATE приходится ждать, пока не будет освобождена блокировка изменений.
• Блокировка намерения ( Intent , I ).Блокировки намерения существуют как варианты предыдущих типов блокировки, среди которых блокировка разделяемого намерения, блокировка монопольного намерения и т. д. Они используются для защиты блокировок на нижних уровнях иерархии от запрещающих блокировок на более высоких уровнях иерархии. Рассмотрим ситуацию, при которой транзакция владеет монопольной блокировкой на строку таблицы. В этом случае другие транзакции не могут получить монопольную блокировку на всю таблицу. Чтобы управлять такими ситуациями, используются блокировки намерения на более высоких уровнях иерархии, чтобы уведомить другие транзакции о том, что некоторые ресурсы блокированы на более низких уровнях иерархии. В этом случае, транзакция, удерживающая монопольную блокировку на строку, также блокирует страницу и таблицу блокировкой монопольного намерения.

Полный список типов блокировки и блокируемых ресурсов можно найти в Электронной документации SQL Server 2005, тема "Режимы блокировки".