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

Мониторинг блокировок

Чтобы отслеживать блокировки, существующие в базе данных, можно выполнить запрос к динамическому административному представлению sys.dm_tran_locks. Это представление предоставляет строку для каждой отдельной блокировки, существующей в базе данных в настоящий момент.

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

   USE AdventureWorks;
   GO
   BEGIN TRAN
   SELECT FirstName,LastName,EmailAddress
     FROM Person.Contact WITH (HOLDLOCK)
     WHERE ContactID = 15
3. Чтобы узнать, какие блокировки используются этой транзакцией, можно воспользоваться динамическим административным представлением sys.dm_tran_locks. Чтобы получить сведения только о блокировках, которые принадлежат к конкретной транзакции, можно соединить это представление с другим административным представлением, которое называется sys.dm_tran_current_transaction и возвращает информацию о текущей транзакции, выполняемой в текущем соединении. В окне запроса введите и выполните следующую инструкцию SELECT, чтобы получить информацию о блокировках и зафиксировать транзакцию.

   SELECT resource_type, resource_associated_entity_id,
          request_mode,request_status 
     FROM sys.dm_tran_locks dml 
     INNER JOIN sys.dm_tran_current_transaction dmt
     ON dml.request_owner_id = dmt.transaction_id;
   
   COMMIT TRAN
4. На следующем рисунке представлен результат этого запроса. Он показывает, что в ключе, представляющем строку в кластеризованном индексе, существует разделяемая блокировка ( request_mode= S ), а в соответствующей странице и таблице Person.Contact существует блокировка разделяемого намерения ( request_mode = IS ). Значение GRANT в столбце request_status означает, что запрашиваемые блокировки предоставлены транзакции.
   

   
   
5. Теперь посмотрим, что произойдет, если мы изменим предложение WHERE таким образом, чтобы запрос возвращал больше строк. Внесите в предложение WHERE следующие изменения и выполните всю транзакцию.

   BEGIN TRAN
   SELECT FirstName,LastName,EmailAddress
     FROM Person.Contact WITH (HOLDLOCK)
     WHERE ContactID <7000;
6. Далее изучим блокировки, выполнив следующий код:

   SELECT resource_type, resource_associated_entity_id,request_mode,request_status 
     FROM sys.dm_tran_locks dml 
     INNER JOIN sys.dm_tran_current_transaction dmt
     ON dml.request_owner_id = dmt.transaction_id; 
   COMMIT TRAN

   Мы видим, что разделяемая блокировка определена для объектного типа ресурсов, в этом случае, для таблицы Person.Contact. SQL Server определяет, что для этой транзакции удержание блокировки на уровне таблицы будет проще и быстрее, чем удержание 7000 блокировок ключей со всеми зависимыми блокировками намерений. Поскольку SQL Server использовал уровень блокировки ТАБЛИЦА, ему не придется использовать блокировки намерений, ведь блокировка таблицы – самый верхний уровень в иерархии блокировок для данных. Чтобы узнать, какой объект блокируется, можно использовать функцию OBJECT_NAME. OBJECT_NAME в качестве аргумента принимает идентификатор объекта и возвращает имя объекта. (Столбец resource_associated_entity_id содержит идентификатор блокируемого объекта Object ID, если OBJECT представляет собой resource_type.)
7. Чтобы увидеть, как SQL Server блокирует данные в процессе изменения, введите и выполните следующую транзакцию для обновления ( UPDATE ) данных в таблице Person.Contact и запросите связанные блокировки. В завершение выполняется инструкция ROLLBACK TRAN для отмены этих изменений.

   USE AdventureWorks;
   GO
   BEGIN TRAN
   UPDATE Person.Contact
   SET Phone ='+43 555 333 222'
     WHERE ContactID =25;
   SELECT resource_type, resource_associated_entity_id,request_mode,request_status 
     FROM sys.dm_tran_locks dml 
     INNER JOIN sys.dm_tran_current_transaction dmt
     ON dml.request_owner_id = dmt.transaction_id; 
   ROLLBACK TRAN

   Результат показан на следующем рисунке. Мы видим, что SQL Server блокирует ключ при помощи монопольной блокировки ( request_mode = X ). При любом изменении данных SQL Server выполняет монопольную блокировку и удерживает ее до тех пор, пока не завершится транзакция. Как уже говорилось ранее, SQL Server также использует блокировки изменений на первом этапе выполнения инструкций UPDATE. Поскольку мы запросили информацию о блокировках после инструкции UPDATE, блокировка строки уже была повышена до монопольной блокировки. Мы видим также, что для страницы и таблицы снова существуют две блокировки монопольного намерения ( request_mode = IX ); кроме того, существует также блокировка под названием Sch-S для типа ресурсов METADATA. Блокировка Sch-S – это блокировка схемы, которая используется для того, чтобы запретить изменение схемы таблицы другими транзакциями в процессе обновления данных, потому что такие изменения не разрешаются в пр оцессе изменения данных.

   

   
   

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

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

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

Стандарт ANSI 99 определяет четыре уровня изоляции транзакций; все они поддерживаются в SQL Server 2005:

• READ UNCOMMITTED (чтение незафиксированных данных) не использует или не проверяет наличие блокировок при чтении данных.

  Следовательно, на этом уровне изоляции возможно чтение незафиксированных данных.
• READ COMMITTED (чтение зафиксированных данных) - читает только зафиксированные данные и ожидает снятия монопольной блокировки другой транзакцией. Разделяемые блокировки, используемые для чтения данных, снимаются сразу после завершения операции чтения. READ COMMITTED - уровень изоляции по умолчанию для SQL Server.
• REPEATABLE READ (повторяющееся чтение) читает данные так же, как уровень READ COMMITTED, но удерживает разделяемую блокировку до окончания транзакции.
• SERIALIZABLE (упорядочиваемое чтение) аналогично уровню REPEATABLE READ. Этот уровень блокирует не только вовлеченные данные, но и весь диапазон данных. Это не допускает вставки новых данных в диапазон, используемый в запросе, что могло бы привести к фантомному чтению (см. тему "Эффекты параллелизма" в Электронной документации SQL Server 2005).

Кроме того, в SQL Server есть два дополнительных уровня изоляции, которые используют для чтения данных версии строк (об этих уровнях изоляции мы подробно поговорим далее в этой лекции). Использование версий строк позволяет транзакции читать последнюю зафиксированную версию данных, если на эти данные распространяется монопольная блокировка. Это может способствовать существенному увеличению производительности запроса, поскольку операциям чтения не приходится ожидать снятия блокировок. Это такие два уровня, как:

• READ COMMITTED SNAPSHOT (зафиксированное чтение, моментальный снимок) - это новая реализация уровня READ COMMITTED (зафиксированное чтение). В отличие от обычного уровня READ COMMITED, SQL Server читает последнюю версию зафиксированных данных, а, значит, при выполнении операций чтения ему не приходится ждать снятия блокировок. Этот уровень можно использовать вместо уровня READ COMMITED.
• Уровень SNAPSHOT (моментальный снимок) использует для предоставления устойчивости транзакционного чтения управление версиями строк. Это означает, что в течение транзакции одни и те же данные всегда читаются с уровнем изоляции SERIALIZABLE, но при этом нет необходимости в блокировке данных для предотвращения изменения их другими транзакциями, поскольку благодаря управлению версиями строк обеспечивается согласованность чтения.

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

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

Используем только чтение зафиксированных данных

В SQL Server 2005 уровень изоляции READ COMMITED – это уровень изоляции по умолчанию при установлении нового соединения. Этот уровень существует в двух разновидностях: READ COMMITTED (чтение зафиксированных данных) и READ COMMITTED SNAPSHOT (чтение зафиксированных данных, мгновенный снимок). Применяемый тип определяется в параметрах базы данных. Уровень изоляции READ COMMITED ожидает снятия блокировок перед чтением данных, тогда как уровень изоляции READ COMMITTED SNAPSHOT использует управление версиями строк и читает последнюю версию зафиксированных данных, когда данные заблокированы другой транзакцией.

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

1. Запустите SQL Server Management Studio и откройте окно New Query (Новый запрос).
2. Введите и выполните следующие инструкции, чтобы прочитать поля Name и Email.Address в таблице Person.Contact. если ContactID = 1. Код для этого примера включен в файлы примеров под именами ReadCommitted1.sql и ReadCommitted2.sql.

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

   Возвращена строка из столбца Email.Address gustavo0@adventure-works.com, которая соответствует контактному лицу Gustavo Achong.
3. Далее представим, что другая транзакция изменяет столбец Email.Address, в то время как первая транзакция все еще открыта. Откройте еще одно окно запроса и выполните следующий пакет, чтобы обновить ( UPDATE ) столбец Email.Address без фиксации транзакции.

   USE AdventureWorks; BEGIN TRAN
   UPDATE Person.Contact
   SET EmailAddress = "uncommitted@email.at"
     WHERE ContactID = 1;
4. Инструкция UPDATE выполняется без каких-либо проблем. Она оказывает действие на одну строку данных, хотя сначала эти данные были считаны транзакцией из первого окна запроса, и эта транзакция еще не завершилась. Это происходит потому, что уровень изоляции READ COMMITED не удерживает разделяемые блокировки, которые используются инструкцией SELECT, до окончания транзакции. Блокировки снимаются сразу после того, как данные прочитаны SQL Server. Это может оказаться проблемой, если вам нужны согласованные операции считывания данных в процессе вашей транзакции. Как это осуществить, мы рассмотрим позже в разделе "Получаем согласованные повторяющиеся операции чтения" данной лекции.
5. А сейчас вернемся к окну запроса 1 и снова попробуем выполнить чтение данных.

   SELECT FirstName, LastName, EmailAddress 
     FROM Person.Contact 
     WHERE ContactID = 1;

   Запрос не завершается, поскольку инструкция SELECT заблокирована. SQL Server пытается получить разделяемую блокировку на ключ столбца ContactID 1, но это невозможно, ведь транзакция UPDATE в окне запроса Query Window 2 владеет монопольной блокировкой на этот ключ. Хотя окно запроса Query Windows 2 находится в режиме READ COMMITED (поскольку мы не изменяли уровень по умолчанию), монопольная блокировка все еще не снята. Эта блокировка существует потому, что монопольные блокировки для изменений данных всегда удерживаются до окончания транзакции.
6. Перейдите в окно запроса Query Window 2, не закрывая окно запросов Query Window 1. Введите и выполните следующую инструкцию SELECT, чтобы получить информацию о предоставленных и ожидающих блокировках в базе данных.

   SELECT resource_type, resource_associated_entity_id,
          request_mode, request_status 
     FROM sys.dm_tran_locks

   Мы видим, что одна из разделяемых блокировок имеет статус WAIT. Это запрос, выполняемый в окне Query Window 1. Он ожидает завершения запроса в окне Query Window 2, который владеет монопольной блокировкой, предоставленной на тот же ресурс.
7. Выполните инструкцию ROLLBACK TRAN в окне запроса Query Window 2, чтобы отменить инструкцию UPDATE, и перейдите в окно Query Window 1. Вы видите, что запрос в этом окне запроса завершился, и его результат не отличается от полученного до этого. Когда транзакция в окне запроса Query Window 2 завершилась, блокировки были сняты, и запрос в окне Query Window 1 больше не был блокирован. Поскольку транзакция в окне Query Window 2 подверглась откату, мы получили в результате в окне запроса Query Window 1 исходные данных. Если бы транзакция в окне запроса Query Window 2 была зафиксирована, то в результате в окне запроса Query Window 1 мы получили бы новые данные.
8. Выполните инструкцию COMMIT TRAN в окне запроса Query Window 1, после чего закройте все окна запросов.

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