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

Применяем уровень изоляции READ COMMITED SNAPSHOT

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

   USE master;
   ALTER DATABASE AdventureWorks
   SET READ_COMMITTED_SNAPSHOT ON
3. Теперь запустим транзакцию и изменим Email.Address как в описанной выше процедуре (но оставим транзакцию открытой), выполнив следующий код:

   USE AdventureWorks; 
   BEGIN TRAN
   UPDATE Person.Contact
   SET EmailAddress = "uncommitted@email.at"
     WHERE ContactID = 1;
4. Откройте еще одно окно запроса и выполните следующие инструкции для чтения данных из столбцов Name и EmailAddress строки ContactID 1.

   USE AdventureWorks;
   BEGIN TRAN
   SELECT FirstName, LastName, EmailAddress 
     FROM Person.Contact 
     WHERE ContactID = 1;

   Возвращается адрес Email.Address gustavo0@adventure-works.com контактного лица Густаво Ахонга, поскольку это - самая последняя зафиксированная версия этой строки. В отличие от уровня READ COMMITED без параметра SNAPSHOT, запрос больше не блокируется.
5. Закройте окно запроса Query Window 2 и перейдите в окно запроса Query Window 1.
6. Выполните следующие инструкции для отката транзакции и переключения обратно на уровень READ COMMITED (этот запрос будет ожидать, пока вы не закроете окно запроса Query Window 2).

   ROLLBACK TRAN
   GO
   USE master;
   ALTER DATABASE AdventureWorks
   SET READ_COMMITTED_SNAPSHOT OFF

   Важно. Этот уровень изоляции можно использовать для уменьшения количества блокировок, но имейте в виду, что это – параметр уровня базы данных. Когда он изменяется, все транзакции, использующие уровень READ COMMITTED в этой базе данных, тоже изменяют свое поведение. Следовательно, такой прием можно рекомендовать использовать только в тех случаях, если все эти транзакции остаются логически правильными, когда считывают последнюю зафиксированную версию данных вместо актуальной зафиксированной версии данных.

Как добиться согласованных повторяющихся операций чтения

Один из недостатков уровня READ COMMITED заключается в том, что данные, считанные одной транзакцией, могут быть изменены другой транзакцией, пока выполняется первая транзакция. Следовательно, ни одна из версий уровня READ COMMITED мы не гарантируем согласованных чтений. Получение согласованных чтений означает, что в пределах одной транзакции всегда будут считываться одни и те же данные.

• Уровень READ COMMITED использует разделяемые блокировки при считывании данных, но снимает их после завершения операции чтения. Следовательно, другие транзакции могут изменять данные.
• READ COMMITTED SNAPSHOT считывает последнюю зафиксированную версию данных. Когда данные считываются во второй раз, последняя зафиксированная версия может оказаться более свежей, чем предыдущая, если вторая транзакция зафиксировала изменение данных.

Несогласованность чтений может вызвать проблемы, если необходимы согласованные чтения, например, для создания отчета. Представьте себе, что транзакция вычисляет некоторые бизнес-показатели на основе ваших данных. В процессе выполнения этого вычисления с уровнем изоляции READ COMMITED существует возможность того, что эти значения будут вычислены неправильно, потому что базовые данные будут изменены, пока транзакция производит вычисления. Чтобы успешно выполнить расчет, можно использовать уровень изоляции SNAPSHOT. Этот уровень использует управление версиями, чтобы предоставить зафиксированную версию данных, но, в отличие от уровня READ COMMITED SNAPSHOT, он всегда предоставляет самую последнюю зафиксированную версию от начала транзакции. Следовательно, SQL Server всегда читает одни и те же данные на протяжении всей транзакции.

Применяем уровень изоляции SNAPSHOT

1. Запустите SQL Server Management Studio и откройте окно New Query (Новый запрос).
2. Уровень изоляции SNAPSHOT (мгновенный снимок) необходимо активировать для базы данных однократно. После активации этот уровень может использоваться при необходимости любым соединением. Чтобы разрешить уровень изоляции SNAPSHOT в базе данных AdventureWorks, выполните следующую инструкцию. Код этого примера можно найти среди файлов примеров под именем SnapshotIsolation1.sql и SnapshotIsolation2.sql.

   USE master;
   ALTER DATABASE AdventureWorks
   SET ALLOW_SNAPSHOT_ISOLATION ON;
3. Теперь представьте себе, что нужно создать несколько отчетов по таблице Sales.SalesOrderDetail, но необходимым условием является согласование операций чтения. Выполните следующие инструкции, чтобы активировать изоляцию SNAPSHOT для транзакций и запустите транзакцию, которая возвращает сумму линейных итогов для одного заказа. Запомните значение OrderTotal.

   USE AdventureWorks;
   SET TRANSACTION ISOLATION LEVEL SNAPSHOT
   BEGIN TRAN
   SELECT SUM(LineTotal) as OrderTotal 
     FROM Sales.SalesOrderDetail 
     WHERE SalesOrderID = 43659
4. Откройте еще одно окно запроса и обновите таблицу SalesOrderDetail, чтобы изменить базовые данные для запроса в окне запроса Query Window 1. (Если вы захотите повторить этот пример, замените OrderQty 5 на какое-либо другое число, чтобы следующий код действительно изменил данные в базе данных).

   USE AdventureWorks;
   UPDATE Sales.SalesOrderDetail 
   SET OrderQty = 5 
     WHERE SalesOrderID = 43659 AND ProductID = 777
5. Закройте окно запроса Query Window 2, перейдите обратно в окно Query Window 1 и повторите инструкцию SELECT.

   SELECT SUM(LineTotal) as OrderTotal 
     FROM Sales.SalesOrderDetail 
     WHERE SalesOrderID = 43659

   Как видите, результат тот же, что был до этого, поскольку изоляция SNAPSHOT игнорирует изменения данных в процессе выполнения транзакции. Она всегда предоставляет последние зафиксированные с начала транзакции значения.
6. Зафиксируйте все транзакции и повторите чтение, выполнив следующий код. Вы увидите, что этот результат будет другим, потому что транзакция завершилась.

   COMMIT TRAN
   SELECT SUM(LineTotal) as OrderTotal 
     FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659
7. Выполните следующий код, чтобы отключить изоляцию SNAPSHOT в базе данных Adventure Works.

   ALTER DATABASE AdventureWorks
   SET ALLOW_SNAPSHOT_ISOLATION OFF;

Как предотвратить параллельные изменения данных

Как мы наблюдали в предыдущих примерах, изоляция SNAPSHOT не блокирует данные в процессе чтения, но обеспечивает согласованное представление на протяжении всей транзакции. Но в некоторых ситуациях необходимо заблокировать данные на протяжении всей транзакции, чтобы избежать обновления данных другими транзакциями. Предположим, что нужно выписать счет за заказ. Сначала нужно извлечь данные и проверить их, а затем сгенерировать счет на их основе. В такой транзакции необходимо заблокировать данные с самого начала, чтобы предотвратить изменения, которые могут сделать другие транзакции. В этом случае ни уровень SNAPSHOT, ни уровень READ COMMITED не будут подходящими вариантами. В такой ситуации можно использовать уровень изоляции REPEATABLE READ (повторяющиеся чтения). Этот уровень аналогичен уровню READ COMMITED без SNAPSHOT, но удерживает разделяемые блокировки до окончания транзакции. Следовательно, он предотвратит изменения данных.

Применяем уровень изоляции REPEATABLE READ

1. Запустите SQL Server Management Studio и откройте окно New Query (Новый запрос).
2. Допустим, что нам нужно обработать заказ с идентификатором OrderID 43659. Сначала нужно выполнить выборку данных. Чтобы не допустить изменения данных другими транзакциями в процессе чтения, используйте уровень изоляции REPEATABLE READ. Выполните следующий код (код для этого примера можно найти среди файлов примеров под именем RepeatableReadIsolation1.sql и RepeatableReadIsolation2.sql ).

   USE AdventureWorks;
   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
   BEGIN TRAN
     SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty
       FROM Sales.SalesOrderDetail
       WHERE SalesOrderID = 43659
3. Откройте еще одно окно запроса и попробуйте обновить таблицу SalesOrderDetail, чтобы изменить базовые данные запроса в окне Query Window 1; для этого выполните следующий код:

   USE AdventureWorks; 
   UPDATE Sales.SalesOrderDetail 
   SET OrderQty = 5 
     WHERE SalesOrderID = 43659 AND ProductID = 777

   Запрос ожидает. В отличие от уровня изоляции SNAPSHOT, невозможно обновить данные, поскольку разделяемые блокировки удерживаются для предотвращения изменения данных другими транзакциями. Эти блокировки можно увидеть через административное представление sys.dm_tran_locks, которое мы использовали ранее.
4. Нажмите кнопку Cancel Executing Query (Отменить выполнение запроса) на панели инструментов (она показана на следующем рисунке), чтобы отменить запрос в окне запроса Query Window 2; вместо этого запроса выполните инструкцию INSERT, чтобы добавить в заказ новую строку:

   INSERT INTO Sales.SalesOrderDetail
     (SalesOrderID,CarrierTrackingNumber,
      OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount) 
      VALUES(43659,'4911-403C-98',1,758,1,874,0)

   

   
   
5. Обратите внимание на то, что эта инструкция успешно выполняется, хотя используется уровень изоляции REPEATABLE READ. Так происходит потому, что REPETABLE READ блокирует данные для предотвращения изменения данных, а инструкция INSERT вставляет новые данные в базу данных, а это не запрещено. Поскольку новая строка попадает в диапазон инструкции SELECT транзакции из окна Window 1, она будет считана при следующем извлечении транзакцией этих данных. Такие строки называются фантомными чтениями.
6. Повторите инструкцию SELECT и зафиксируйте ( COMMIT ) транзакцию, как показано ниже.

   SELECT SalesOrderID, SalesOrderDetailID, 
          ProductID, OrderQty 
     FROM Sales.SalesOrderDetail 
     WHERE SalesOrderID = 43659
   COMMIT TRAN

   Обратите внимание, что новая строка была считана инструкцией SELECT, потому что она попала в диапазон инструкции. Уровень REPEATABLE READ предотвращает изменение существующих данных, но не предотвращает вставки новых данных в диапазон инструкции SELECT.
7. Закройте окно среды SQL Server Management Studio.