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

Блокирование и взаимоблокировки

Чтобы гарантировать, что транзакция не будет читать фантомные данные, можно заблокировать данные, задав блокировку диапазона исследуемых данных. Однако это может вызвать проблему взаимоблокировок.

Блокировка последовательных блоков связанных данных

Чтобы предотвратить фантомные чтения, можно использовать уровень изоляции SERIALIZABLE (упорядоченное чтение). Этот уровень является более строгим, чем уровень REPEATABLE READ ; он блокирует не только данные, которые считываются в процессе транзакции, но и диапазон, который читает транзакция. Это достигается использованием особых видов блокировок, которые называются блокировками диапазона ключей, в индексах. Блокировки диапазона ключей блокируют диапазоны, заданные в предложении WHERE инструкции SELECT. Эти блокировки могут использоваться только в том случае, если существует соответствующий индекс. Если индекс не существует, то SQL Server должен использовать блокировку на уровне таблицы, чтобы предотвратить вставки данных в этот диапазон. Следовательно, уровень изоляции SERIALIZABLE должен использоваться только в том случае, если он абсолютно необходим.

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

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

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

   INSERT INTO Sales.SalesOrderDetail
     (SalesOrderID,CarrierTrackingNumber,
      OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount) 
      VALUES(43659,'4911-403C-98',1,758,1,874,0)
4. Закройте окно запроса Query Window 2, потому что видно, что этот запрос заблокирован. Мы убедились в том, что уровень изоляции SERIALIZABLE предотвращает также вставку ( INSERT ) новых строк в диапазон данных, который считывается транзакцией.
5. Выполните инструкцию COMMIT TRAN, а затем закройте все окна запросов.

Борьба с блокированиями

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

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

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

Выполняем мониторинг блокирований при помощи динамических административных представлений

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

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

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

   USE AdventureWorks;
   UPDATE Sales.SalesOrderDetail 
   SET OrderQty = 5 
     WHERE SalesOrderID = 43659 AND ProductID = 777
4. Не завершайте выполнение инструкции UPDATE и откройте еще одно окно запроса.
5. Введите и выполните в третьем окне запроса следующую инструкцию, чтобы извлечь данные обо всех процессах пользователей, которые ожидают более 5 секунд.

   SELECT * FROM sys.dm_os_waiting_tasks 
     WHERE session_id > 49 AND wait_duration_ms > 5000

   При помощи этой инструкции вы получаете информацию обо всех процессах пользователей с идентификаторами сеанса больше 49, которые были блокированы больше чем на 5 секунд по любой причине. Столбец blocking_session_id содержит идентификаторы сеанса для заблокированных сеансов. Запишите идентификатор сеанса в результате, чтобы воспользоваться им в пункте 7.
6. Чтобы получить информацию о том, какую инструкцию пытается выполнить заблокированный процесс, можно использовать следующую инструкцию. Эта инструкция соединяет представление sys.dm_os_waiting_tasks с представлением sys.dm_exec_requests, которое возвращает информацию о выполняющихся запросах. Кроме того, эта инструкция использует функцию sys.dm_exec_sql_text(), которая может извлечь инструкцию SQL через дескриптор, предоставляемый в представлении sys.dm_exec_requests. Выполните следующий код:

   SELECT (select SUBSTRING(text,statement_start_offset/2,
          (case when statement_end_offset = -1 then
           len(convert(nvarchar(max), text)) * 2 else
           statement_end_offset end -statement_start_offset)/2)
           from sys.dm_exec_sql_text(sql_handle)) as query_text 
     FROM sys.dm_os_waiting_tasks wt 
     JOIN sys.dm_exec_requests r ON r.session_id = wt.session_id 
     WHERE r.session_id > 50 AND wait_duration_ms > 5000
7. Дополнительную информацию о соединении, участвующем в сценарии блокирования, можно извлечь при помощи представления sys.dm_exec_connections. Замените идентификатор сеанса в запросе (51 в коде, представленном ниже) реальным идентификатором сеанса заблокированного или блокируемого сеанса.

   SELECT *
     FROM sys.dm_exec_connections
     WHERE session_id = 51 —Replace with your id
8. Выполните инструкцию COMMIT TRAN в окне запроса Query Window 1, после чего закройте все окна запросов.
   Дополнительная информация Дополнительную информацию о динамических административных представлениях можно найти в Электронной документации SQL Server 2005, тема "Динамические административные представления и функции".

Борьба с взаимоблокировками

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

Разыгрываем простой сценарий взаимоблокировки

Давайте воспроизведем простой сценарий взаимоблокировки, чтобы посмотреть, как SQL Server управляет взаимоблокировками.

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

   USE tempdb;
   CREATE TABLE t1 (i int)
   BEGIN TRAN
   INSERT INTO t1 Values(1)
3. Откройте еще одно окно запроса и выполните следующие инструкции, чтобы создать маленькую таблицу, вставить в нее данные и попытаться обновить таблицу t1 из окна запроса Query Window 1. Транзакция будет блокирована, поскольку транзакция в окне запроса Query Window 1 пока не зафиксирована.

   USE tempdb;
   CREATE TABLE t2 (i int)
   BEGIN TRAN
   INSERT INTO t2 Values(1)
   UPDATE t1 SET i = 2
4. Не завершайте этот запрос и перейдите в окно запроса Query Window 1. Выполните следующую инструкцию UPDATE, чтобы обновить таблицу t2 и посмотрите, что произойдет.

   UPDATE t2 SET i = 2

   Через несколько секунд одна из транзакций отменяется, и возвращается сообщение об ошибке, показанное на следующем рисунке.

   

   
   

   Это произошло потому, что транзакции блокировали друг друга. Транзакция в окне запроса Query Window 1 владеет блокировкой на таблицу t1 и пытается обновить таблицу t2, а транзакция в окне запросов Query Window 2 владеет блокировкой на таблицу t2 и пытается обновить таблицу t1. Следовательно, обе транзакции будут бесконечно ожидать, пока оппонирующая транзакция снимет блокировку. Такие ситуации выявляются SQL Server и разрешаются путем отката одной из вовлеченных транзакций и выдачей ошибки 1205 соответствующему соединению.
5. Закройте все окна запросов.

Чтобы предотвратить взаимоблокировки или, хотя бы, бороться с ними, следует соблюдать несколько правил:

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

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