SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Поиск и предотвращение тупиковых блокировок. Часть 1

ПУБЛИКАЦИИ  

По материалам статьи Bart Duncan: Deadlock Troubleshooting, Part 1
Перевод Ирины Наумовой

Тупиковая блокировка - это замкнутая цепочка блокирования, в которой два или более потока блокируют друг друга так, что ни один из них не может продолжать выполнение. Когда поток отслеживания тупиковой блокировки в SQL Server обнаруживает замкнутую цепочку блокирования, одного из участников тупиковой блокировки он выбирает в качестве жертвы, отменяет текущий пакет этого spid, и откатывает его транзакции, чтобы позволить другому spid продолжить выполнение. Жертва тупиковой блокировки получит сообщение об ошибке 1205:

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

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

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

  1. Включите флаг трассировки 1222 с помощью команды "DBCC TRACEON (1222,-1) " или добавив "-T1222" в качестве параметра запуска SQL Server. Этот флаг трассировки появился в SQL 2005, он является улучшенной версией своего аналога -T1204. Если Вы работаете с SQL 2005, Вы должны использовать 1222 вместо 1204, если конечно у вас нет укоренившихся мазохистских тенденций. Альтернативы для 1222:

    • Если вы используете SQL 2000 или SQL 7.0, у Вас нет другого выбора, кроме использования флага -Т1204

    • В профайлере имеется событие "Deadlock graph", которое обеспечивает получение той же самой информации что и -T1222. Используйте это событие вместо флага -T1222, если Вы работаете с SQL 2005. Но не тратьте впустую ваше время отлавливая события "Lock:Deadlock" и "Lock:Deadlock Chain", которые включены в SQL 2000, поскольку они не обеспечивают приемлемой полноты картины тупиковой блокировки.

  2. Прочитать информацию, полученную при включенном флаге -T1222. Эта информация будет присутствовать в журнале ошибок SQL Server после того, как произойдёт тупиковая блокировка.
    Она выглядит примерно так:

    deadlock-list
     deadlock victim=processdceda8
      process-list
       process id=processdceda8 taskpriority=0 logused=0 waitresource=KEY: 2:72057594051493888 (0400a4427a09) waittime=5000 ownerId=24008914 transactionname=SELECT lasttranstarted=2006-09-08T15:54:22.327 XDES=0x8fd9a848 lockMode=S schedulerid=1 kpid=4404 status=suspended spid=54 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2006-09-08T15:54:22.293 lastbatchcompleted=2006-09-08T15:54:22.293 clientapp=OSQL-32 hostname=BARTD2 hostpid=3408 loginname=bartd isolationlevel=read committed (2) xactid=24008914 currentdb=2 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
        executionStack
         frame procname=tempdb.dbo.p1 line=2 stmtstart=60 sqlhandle=0x03000200268be70bd
           SELECT c2, c3 FROM t1 WHERE c2 = @p1    
         frame procname=adhoc line=2 stmtstart=32 stmtend=52 sqlhandle=0x020000008a4df52d3
           EXEC p1 3    
        inputbuf
           EXEC p1 3
       process id=process3c54c58 taskpriority=0 logused=16952 waitresource=KEY: 2:72057594051559424 (0900fefcd2fe) waittime=5000 ownerId=24008903 transactionname=UPDATE lasttranstarted=2006-09-08T15:54:22.327 XDES=0x802ecdd0 lockMode=X schedulerid=2 kpid=4420 status=suspended spid=55 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2006-09-08T15:54:22.327 lastbatchcompleted=2006-09-08T15:54:22.310 clientapp=OSQL-32 hostname=BARTD2 hostpid=2728 loginname=bartd isolationlevel=read committed (2) xactid=24008903 currentdb=2 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128056
        executionStack
         frame procname=tempdb.dbo.p2 line=2 stmtstart=58 sqlhandle=0x030002005fafdb0c
           UPDATE t1 SET c1 = FLOOR (c1), c2 = FLOOR (c2) WHERE c1 = @p1    
         frame procname=adhoc line=2 stmtstart=32 stmtend=52 sqlhandle=0x020000006f878816
           EXEC p2 3    
        inputbuf
           EXEC p2 3
      resource-list
       keylock hobtid=72057594051559424 dbid=2 objectname=tempdb.dbo.t1 indexname=idx1 id=lock83642a00 mode=S associatedObjectId=72057594051559424
        owner-list
         owner id=processdceda8 mode=S
        waiter-list
         waiter id=process3c54c58 mode=X requestType=wait
       keylock hobtid=72057594051493888 dbid=2 objectname=tempdb.dbo.t1 indexname=cidx id=lock83643780 mode=X associatedObjectId=72057594051493888
        owner-list
         owner id=process3c54c58 mode=X
        waiter-list
         waiter id=processdceda8 mode=S requestType=wait

  3. "Расшифровка" полученной информации для того, чтобы лучше понять сценарий блокирования.
    Информация о тупиковой блокировке представлена разделами "process-list"(список процессов) и "resource-list"(список ресурсов). "Процесс" - это spid или рабочий поток, который участвует в тупиковой блокировке. Каждому процессу назначается свой идентификатор, вроде этого "processdceda8". Ресурс - это ресурс, который занят (обычно заблокирован) и освобождения которого ждет один из участников. Мне нравится использовать представленный ниже формат для того, чтобы обобщить информацию о тупиковой блокировке. Если хотите, можете пропустить этот шаг, но я никогда этого не делаю; мне это помогает более ясно понять ситуацию тупиковой блокировки. Я выделил желтым цветом данные в результирующем наборе при использовании 1222, чтобы Вы смогли самостоятельно восстановить следующую картину.

    Spid 54 is running this query (line 2 of proc [p1]): SELECT c2, c3 FROM t1 WHERE c2 = @p1 Spid 55 is running this query (line 2 of proc [p2]): UPDATE t1 SET c1 = FLOOR (c1), c2 = FLOOR (c2) WHERE c1 = @p1 Spid 54 is waiting for a Shared KEY lock on index t1.cidx. (Spid 55 holds a conflicting X lock.) Spid 55 is waiting for an eXclusive KEY lock on index t1.idx1. (Spid 54 holds a conflicting S lock.)

    Те из вас, кто работает с SQL Server 2005, и решит, что результат работы -T1222 слишком краток, могут сравнить его с результатом работы -T1204, который намного труднее интерпретировать чем -T1222, и он не дает такого количества полезной информации о тупиковой блокировке. Посмотрите статью "Interpreting Trace Flag 1204 Output", в которой описано, как интерпретировать результаты -T1204.

  4. Прогоните запросы, вовлеченные в тупиковую блокировку, через Database Tuning Advisor. Поместите текст запроса в окно запроса Management Studio, выберите базу данных, в контексте которой должен быть выполнен запрос, щелкните правой кнопкой мыши по тексту запроса, и выберите "Analyze Query in DTA". Не пропустите этот шаг; больше половины проблем, приводящих к тупиковой блокировке, решаются просто добавлением соответствующего индекса, чтобы один из запросов выполнялся быстрее и блокировал меньше ресурсов. Если DTA рекомендует построить индексы, создайте их, и посмотрите, сохраняется ли тупиковая блокировка.

  5. Удостоверьтесь, что запрос использует минимальный необходимый уровень изоляции транзакций (в результирующем наборе, полученном с помощью -T1222, эту информацию можно посмотреть в разделе "isolationlevel"). Запросы, выполненные транзакционными компонентами COM+, по умолчанию выполняются с уровнем изоляции serializable, что обычно является причиной множества тупиковых блокировок. Это можно изменить путем использования подсказки оптимизатору ("...FROM tbl1 С (READCOMMITTED) ... "), или командой SET TRANSACTION ISOLATION LEVEL, или (в Windows 2003 и выше) конфигурируя объект с помощью дополнения к оснастке MMC - Component Services.

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

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

  8. Если один или оба spid выполняют многооператорную транзакцию, возможно, Вам будет необходимо настроить трассу профайлера, чтобы получить информацию о тупиковой блокировке, и идентифицировать полный набор запросов, вызывающих возникновение тупиковой блокировки. К сожалению, -T1204 и -T1222 показывают лишь два запроса, которые "закупорили цикл", но возможно, что одна из блокировок была наложена более ранним запросом, выполненным в рамках той же транзакции.

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

  • Доступ к объектам выполняйте в одном порядке. Рассмотрим следующие два пакета.

    1. Begin Transaction

    1. Begin Transaction

    2. Update Part table

    2. Update Supplier table

    3. Update Supplier table

    3. Update Part table

    4. Commit Transaction

    4. Commit Transaction

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

  • Если оба участника тупиковой блокировки используют один и тот же индекс, необходимо создать индекс, который может обеспечить дополнительный путь доступа для одного из spid. Например, добавляя покрывающий кластерный индекс для SELECT, вовлеченного в тупиковую блокировку, можно предотвратить проблему (при условии что ни один из ключей покрывающего индекса не изменяется другим участником тупиковой блокировки).

  • С другой стороны, если spid стали участниками тупиковой блокировки из-за того, что были использованы альтернативные пути (индексы) для доступа к требуемой строке данных или странице, рассмотрите возможность удаления одного из индексов, или возможность использования подсказки индекса, чтобы оба запроса совместно использовали один путь доступа. Будьте осторожны, результатом такого подхода может стать снижение производительности.

  • Тупиковые блокировки, это специальный тип блокирования, где два spid блокируют друг друга. Иногда лучшим способом предотвратить появление тупиковой блокировки является принудительное блокирование в более ранней точке одной из транзакций. Например, если Вы заранее заблокируете spid A в транзакции со spid B, тогда с самого начала транзакция со spid А не сможет получить ресурсы до окончания блокировки, наложенной spid B. Означает ли это что Вы преднамеренно инициируете блокирование? Да, поскольку ресурсы уже заблокированы, не возникнет ситуации тупиковой блокировки, и в этом смысле, простое блокирование - более оптимальное решение тупикового блокирования. Также, можно продолжать блокирование с помощью подсказок HOLDLOCK и UPDLOCK до завершения транзакции spid B.

  • Если нужно, что бы в качестве жертвы при разрешении тупиковой блокировки выбирается процесс с более высоким приоритетом, для процесса c более низким приоритетом можно использовать установку SET DEADLOCK_PRIORITY LOW. Spid, для которого используется данная установка будет всегда выбран в качестве жертвы при возникновении тупиковой блокировки, участником которой он станет.

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

  • В некоторых случаях может быть полезным использовать подсказку NOLOCK, если один из запросов является инструкцией SELECT. Хотя этот путь является наиболее привлекательным, т.к. представляет собой быстрое и простое решение во многих ситуациях тупикового блокирования, используйте этот подход осторожно, поскольку это приносит проблемы, возникающие при уровне изоляции транзакций read uncommitted (запрос может возвратить противоречивое представление данных). Если Вы незнакомы с проблемами использования этого уровня изоляции, обратитесь к разделу "SET TRANSACTION ISOLATION LEVEL" в SQL Books Online или проконсультируйтесь у сведущих специалистов, перед использованием этой опции.

  • В SQL 2005 Вы могли бы использовать новый уровень изоляции - SNAPSHOT. Это избавит вас от большинства случаев блокирования по сравнению с рисками применения NOLOCK. По моему мнению, еще более полезным нововведением является опция базы данных READ COMMITTED SNAPSHOT (смотри описание ALTER DATABASE), которая позволяет использовать вариант изоляции снимка, не изменяя ваше приложение.

  • Если одна или обе блокировки, фигурирующие в тупиковой блокировке, являются S/X TAB (табличными) блокировками, возможна эскалация блокировок. Вы можете снизить вероятность эскалации блокировки, включив флаг трассировки 1224 (SQL Server 2005 и позже) или 1211 (см. КB: 323630).

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

[В начало]

Перевод: Ирины Наумовой  2007г.

Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013