В сущности, мы здесь имеем дело опять-таки с нарушением порядка доступа, но несколько в иной форме. Сначала транзакция наложила слабую блокировку на ресурс, а потом вернулась к этому же ресурсу, чтобы наложить более сильную. Особенность этой достаточно часто встречающейся ситуации в том, что это не две разных транзакции, а одна и та же, просто запущенная из разных сессий.
Поскольку взаимоблокировка произошла из-за того, что транзакции удерживали коллективные блокировки и потом попытались их повысить до эксклюзивных, то, в принципе, помочь избежать неприятностей в данном случае сможет понижение уровня изоляции до READ COMMITED. При этом коллективная блокировка не будет держаться до конца транзакции, а снимется сразу после завершения чтения, а значит, обновить записи ничто не помешает. Но тогда вместо взаимоблокировки мы вполне можем получить неверные данные, так как между SELECT и UPDATE сможет втиснуться другая транзакция, которая изменит Y и данные, полученные SELECT’ на момент UPDATE, окажутся неактуальными, чего в некоторых случаях допускать нельзя.
Можно также сразу при чтении наложить эксклюзивную блокировку, но это тоже не самый лучший выход с точки зрения производительности, так как могут существовать транзакции, которым эти данные надо просто прочитать, а наложение эксклюзивной блокировки увеличивает время их пассивного ожидания.
В общем случае наилучшим выходом здесь будет наложение при чтении промежуточной блокировки обновления. Такая блокировка совместима с коллективной, что позволит читающим транзакциям обращаться кэтим данным беспрепятственно. А когда понадобится их обновить, то проблем быть не должно, так как блокировки обновления между собой несовместимы, и значит, другие транзакции, читающие эти данные для последующего изменения (и естественно тоже запросившие их с блокировкой обновления), будут ждать, пока эти данные поменяются, никому не мешая. Для этого необходимо изменить первый оператор транзакции примерно таким образом:
| SELECT @Var = Y FROM Tbl WITH (UPDLOCK) WHERE X = 2 |
К сожалению, взаимоблокировки в реальных приложениях не так тривиальны, и выяснить реальную причину их возникновения бывает достаточно сложно, так как одну из виновных транзакций пристреливают, и приложение работает дальше, оставляя лишь небольшое и малоинформативное сообщение об ошибке. Попробуем разобрать немного более сложный случай в условиях, максимально приближенных к боевым, и попытаемся понять поведение сервера, приведшее к тупиковой ситуации.
Подготовим две транзакции в разных окнах QA и, соответственно, в разных подключениях.
Первая транзакция: T1
| -- установимнеобходимыйуровеньизоляцииSET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE Tbl SET X = 4 WHERE X = 4 –- обновимстроку X=4 WAITFOR DELAY '00:00:10' UPDATE Tbl SET X = 6 WHERE X = 6 –- обновимстроку X=6COMMIT TRAN |
Стоит заметить, что уровень изоляции в данном случае соответствует уровню изоляции, выставляемому по умолчанию, и делать его ниже крайне не рекомендуется. Оператор WAITFOR нужен для того, чтобы сервер исполнял команды не сразу друг за другом, а с разрывом в 10 секунд, чтобы мы успели переключиться во второе окно и стартовать вторую транзакцию, имитируя тем самым одновременность их выполнения.
Вторая транзакция: T2
| --- установимнеобходимыйуровеньизоляцииSET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE Tbl SET X = 2 WHERE X = 2 –- обновимстроку X=2COMMIT TRAN |
Естественно, совершенно не важно, какие значения мы запишем в X в этих транзакциях, в данном случае важно лишь условие выборки.
Запустив T1, а затем, переключившись и запустив T2, мы получим взаимоблокировку. Обратите внимание, что на первый взгляд транзакции вполне безобидны. Более того, условия никак не пересекаются по диапазонам, в первом случае затрагиваются строки X = 4 и X = 6, а во втором X = 2. Можно пойти еще дальше, и изменить в T2 условие таким образом:
| UPDATE Tbl SET Y = 10 WHERE Y = 10 |
Тогда условия выборки не будет пересекаться даже по полям! Но взаимоблокировка все равно произойдет.
Как уже упоминалось выше, в реальной ситуации найти виновные транзакции бывает достаточно проблематично, так как по умолчанию известен только процесс, запустивший транзакцию-«жертву» и, как правило, этого недостаточно.
Определение «виновных» транзакций
Существует возможность заставить сервер выдать более полную информацию об ошибке. Однако не следует этой возможностью злоупотреблять, так как производительность сервера при этом серьезно понижается. Для более тонкой настройки сервер поддерживает флаги трассировки (trace flags). Некоторые из этих флагов предназначены для получения более полной информации об ошибках. Флаги устанавливаются с помощью команды DBCC TRACEON (flag,…), а снимаются, соответственно с помощью DBCC TRACEOFF (flag,…).
Вот краткий перечень флагов, которые могут пригодиться при отлове взаимоблокировок:
1204 – сбор расширенной информации о взаимоблокировке.
3605 – выдача информации в EventLog.
3406 – выдача информации в файл errorlog.
-1 – сбор информации изо всех сессий.
1206 – сбор информации не только о блокировках, участвующих во тупиковой ситуации (что делает флаг 1204), но и об остальных блокировках, наложенных заблокированными транзакциями.
1200 – сбор информации о порядке наложения блокировок (недокументированный).
Сейчас нас интересует флаг под номером 1204 – выдача расширенной информации о взаимоблокировке, получить же информацию при выставленном флаге можно двумя способами.
Запустить SQL Profiler, специальную программу для отслеживания работы сервера, и настроить в ней перехват ошибок (event class Errors and Warnings: Exception and Error Log), а затем выставить флаг трассировки 3605. В этом случае вся дополнительная информация о работе SQL-сервера будет сбрасываться в Event Log и перехватываться профайлером, где ее в последствии можно будет посмотреть.
Выставить флаг отладки 3406. В этом случае вся дополнительная информация будет сбрасываться в файл errorlog, который по умолчанию находится в каталоге LOG директории SQL сервера.
| СОВЕТМожно также добавить флаг 1206 – в этом случае будет сохраняться информация не только о блокировках, непосредственно приведших к тупиковой ситуации, но и об остальных блокировках, чьими владельцами являются транзакции, вовлеченные в замкнутый цикл. |
Итак, сначала установим флаги в одном из окон QA, выполнив следующую команду:
| DBCC TRACEON(1204, 3406, -1) |
Затем выполним T1 в одном окне, потом переключимся в другое, и выполним T2. После того как случится взаимоблокировка, дополнительную информацию о ней можно будет найти в файле errorlog, и среди прочих данных можно будет наблюдать примерно следующее:
| Deadlock encountered .... Printing deadlock information23:51:28.00 spid4 23:51:28.00 spid4 Wait-for graph23:51:28.00 spid4 23:51:28.00 spid4 Node:123:51:28.00 spid4 RID: 7:1:50:1 CleanCnt:1 Mode: X Flags: 0x223:51:28.00 spid4 Grant List 0::23:51:28.00 spid4 Owner:0x19333de0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:53 ECID:023:51:28.00 spid4 SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 123:51:28.01 spid4 Input Buf: Language Event: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE Tbl SET X=2 WHERE X=2COMMIT TRAN23:51:28.01 spid4 Requested By: 23:51:28.01 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:51 ECID:0 Ec:(0x19401548) Value:0x19333da0 Cost:(0/54)23:51:28.01 spid4 23:51:28.01 spid4 Node:223:51:28.01 spid4 RID: 7:1:50:3 CleanCnt:1 Mode: X Flags: 0x223:51:28.01 spid4 Grant List 0::23:51:28.01 spid4 Owner:0x19333d20 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:51 ECID:023:51:28.01 spid4 SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 123:51:28.01 spid4 Input Buf: Language Event:SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE Tbl SET X=4 WHERE X=4 WAITFOR DELAY '00:00:10' UPDATE Tbl SET X=6 WHERE X=6 COMMIT TRAN23:51:28.01 spid4 Requested By: 23:51:28.01 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:53 ECID:0 Ec:(0x19577548) Value:0x19333d60 Cost:(0/54)23:51:28.01 spid4 Victim Resource Owner:23:51:28.01 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:53 ECID:0 Ec:(0x19577548) Value:0x19333d60 Cost:(0/54) |
Здесь представлена информация о замкнутом цикле в графе ожидания, в цикл входят 2 узла (Node:1 и Node:2). Информация об узлах следующая: транзакции T2 (Node:1) нужна строка RID 7:1:50:1 для изменения (Requested By:\Mode U), владеет этой транзакцией процесс с идентификатором 51 (Requested By:\SPID:51). Однако на эту строку уже наложена эксклюзивная блокировка (Grant List:\Mode: X) процессом с идентификатором 53 (Grant List:\SPID: 53). Сама блокировка нужна, чтобы выполнить оператор UPDATE (Statement Type: UPDATE). Далее идет текст пакета, в котором блокировка была запрошена. Точно так же описан и второй узел графа, только там нужна строка RID 7:1:50:3, которой владеет транзакция, уже описанная в первом узле.
Отсюда становится понятно, за какие ресурсы передрались эти транзакции. Но зачем при выполнении оператора UPDATE транзакции T1 потребовалась строка, которой уже владеет T2 (как было уже замечено, по условиям они не пересекаются), пока не ясно. И при этом непонятно, почему T2 удерживала блокировку – ведь по идее она должна была отработать и зафиксироваться, или откатиться, но блокировку снять. Поскольку других транзакций в это время не работало, значит, виновата та же T1.
Разобраться в том, что происходит дальше, поможет недокументированный флаг трассировки 1200, который выдает всю последовательность наложения блокировок.
| ПРИМЕЧАНИЕВообще-то ту же самую информацию можно получить вполне законным способом, используя все тот же SQL Profiler, но дабы не загромождать описание особенностями работы с этой программой, здесь мы будем использовать недокументированный флаг трассировки. |
Запустим первую часть T1 (до WAITFOR), предварительно выставив флаг трассировки, и посмотрим, что за блокировки и в каком порядке накладываются.
| DBCC TRACEON(1200, 3604, -1)GOBEGIN TRAN UPDATE Tbl SET X = 4 WHERE X = 4 |
Получим примерно следующую картину, с точностью до констант:
| Process 53 acquiring IX lock on TAB: 6:2034106287 [] (class bit2000000 ref1) result: OKProcess 53 acquiring IU lock on PAG: 6:1:17495 (class bit0 ref1) result: OKProcess 53 acquiring U lock on RID: 6:1:17495:0 (class bit0 ref1) result: OKProcess 53 releasing lock on RID: 6:1:17495:0Process 53 acquiring U lock on RID: 6:1:17495:1 (class bit0 ref1) result: OKProcess 53 releasing lock on RID: 6:1:17495:1Process 53 acquiring U lock on RID: 6:1:17495:2 (class bit0 ref1) result: OKProcess 53 releasing lock on RID: 6:1:17495:2Process 53 acquiring U lock on RID: 6:1:17495:3 (class bit0 ref1) result: OKProcess 53 acquiring IX lock on PAG: 6:1:17495 (class bit2000000 ref1) result: OKProcess 53 acquiring X lock on RID: 6:1:17495:3 (class bit2000000 ref1) result: OKProcess 53 releasing lock reference on RID: 6:1:17495:3Process 53 acquiring S lock on RID: 6:1:17495:3 (class bit0 ref1) result: OKProcess 53 releasing lock reference on RID: 6:1:17495:3Process 53 releasing lock reference on RID: 6:1:17495:3Process 53 acquiring U lock on RID: 6:1:17495:4 (class bit0 ref1) result: OKProcess 53 releasing lock on RID: 6:1:17495:4.......Process 53 acquiring U lock on RID: 6:1:17495:9 (class bit0 ref1) result: OKProcess 53 releasing lock reference on PAG: 6:1:17495Process 53 releasing lock on RID: 6:1:17495:9 |
Сначала сервер накладывает эксклюзивную блокировку намерения IX на таблицу Tbl (TAB 6:2034106287), поскольку в эту таблицу собирается записывать. Далее накладывается блокировка намерения обновления IU на первую страницу в таблице (PAG 6:1:17495). Поскольку в этой таблице нет индекса, то, чтобы найти нужную запись, необходимо прочитать по очереди все записи, входящие в таблицу, а чтобы прочитать запись, ее надо предварительно заблокировать, что мы и наблюдаем.