45888 (Deadlocks), страница 3

Переключимся обратно в первое окно и попытаемся завершить T1:

UPDATE Tbl SET Y=3 WHERE X=2 COMMIT TRAN

Теперь и T1 будет ждать, пока T2 освободит свою коллективную блокировку. Таким образом, транзакции будут ожидать друг друга, цикл в графе ожидания замкнется и, некоторое время спустя, когда менеджер блокировок это обнаружит, одна из транзакций будет отменена. Приложение, запустившее ее, получит сообщение 1205 о взаимоблокировке (Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction), а другая транзакция завершится успешно.

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

Способы устранения

Поскольку взаимоблокировка произошла из-за того, что транзакции удерживали коллективные блокировки и потом попытались их повысить до эксклюзивных, то, в принципе, помочь избежать неприятностей в данном случае сможет понижение уровня изоляции до 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=6 COMMIT TRAN

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

Вторая транзакция: T2

--- установим необходимый уровень изоляции SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE Tbl SET X = 2 WHERE X = 2 –- обновим строку X=2 COMMIT 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 information 23:51:28.00 spid4 23:51:28.00 spid4 Wait-for graph 23:51:28.00 spid4 23:51:28.00 spid4 Node:1 23:51:28.00 spid4 RID: 7:1:50:1 CleanCnt:1 Mode: X Flags: 0x2 23: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:0 23:51:28.00 spid4 SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 1 23:51:28.01 spid4 Input Buf: Language Event: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE Tbl SET X=2 WHERE X=2 COMMIT TRAN 23: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:2 23:51:28.01 spid4 RID: 7:1:50:3 CleanCnt:1 Mode: X Flags: 0x2 23: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:0 23:51:28.01 spid4 SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 1 23: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 TRAN 23: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) GO BEGIN TRAN UPDATE Tbl SET X = 4 WHERE X = 4

Получим примерно следующую картину, с точностью до констант:

Process 53 acquiring IX lock on TAB: 6:2034106287 [] (class bit2000000 ref1) result: OK Process 53 acquiring IU lock on PAG: 6:1:17495 (class bit0 ref1) result: OK Process 53 acquiring U lock on RID: 6:1:17495:0 (class bit0 ref1) result: OK Process 53 releasing lock on RID: 6:1:17495:0 Process 53 acquiring U lock on RID: 6:1:17495:1 (class bit0 ref1) result: OK Process 53 releasing lock on RID: 6:1:17495:1 Process 53 acquiring U lock on RID: 6:1:17495:2 (class bit0 ref1) result: OK Process 53 releasing lock on RID: 6:1:17495:2 Process 53 acquiring U lock on RID: 6:1:17495:3 (class bit0 ref1) result: OK Process 53 acquiring IX lock on PAG: 6:1:17495 (class bit2000000 ref1) result: OK Process 53 acquiring X lock on RID: 6:1:17495:3 (class bit2000000 ref1) result: OK Process 53 releasing lock reference on RID: 6:1:17495:3 Process 53 acquiring S lock on RID: 6:1:17495:3 (class bit0 ref1) result: OK Process 53 releasing lock reference on RID: 6:1:17495:3 Process 53 releasing lock reference on RID: 6:1:17495:3 Process 53 acquiring U lock on RID: 6:1:17495:4 (class bit0 ref1) result: OK Process 53 releasing lock on RID: 6:1:17495:4 ....... Process 53 acquiring U lock on RID: 6:1:17495:9 (class bit0 ref1) result: OK Process 53 releasing lock reference on PAG: 6:1:17495 Process 53 releasing lock on RID: 6:1:17495:9

Сначала сервер накладывает эксклюзивную блокировку намерения IX на таблицу Tbl (TAB 6:2034106287), поскольку в эту таблицу собирается записывать. Далее накладывается блокировка намерения обновления IU на первую страницу в таблице (PAG 6:1:17495). Поскольку в этой таблице нет индекса, то, чтобы найти нужную запись, необходимо прочитать по очереди все записи, входящие в таблицу, а чтобы прочитать запись, ее надо предварительно заблокировать, что мы и наблюдаем.

Process 53 acquiring U lock on RID: 6:1:17495:0

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

Process 53 releasing lock on RID: 6:1:17495:0

Как только находится нужная запись, блокировка на страницу конвертируется в IX:

Process 53 acquiring IX lock on PAG: 6:1:17495

затем блокировка на запись конвертируется в X:

Process 53 acquiring X lock on RID: 6:1:17495:3

и производится изменение записи. Блокировка этой записи, естественно, до конца транзакции не снимается.

Поскольку сервер не знает, что он уже выбрал все записи, удовлетворяющие условию, то он продолжает перебирать по очереди все, что осталось, накладывая и снимая соответствующую блокировку на каждую запись. Если после выполнения этой части транзакции посмотреть на наложенные блокировки, то мы увидим эксклюзивную блокировку записи x = 4 (RID 1:17495:3) и эксклюзивные блокировки намерения выше по иерархии, на страницу и таблицу:

dbid ObjId ObjName Type Resource Mode Status ------ ----------- ------- ---- ---------- ----- ------- 6 2034106287 Tbl RID 1:17495:3 X GRANT 6 2034106287 Tbl PAG 1:17495 IX GRANT 6 2034106287 Tbl TAB IX GRANT

Как и следовало ожидать, больше подходящих записей не нашлось, ничего не обновилось, а значит, других блокировок удерживать не надо.