1. Краткое введение в тупики Взаимная блокировка возникает, когда два или более процессов SQL Server...
2. Выбор событий
3. Выбор столбцов данных
4. Выбор организации столбцов
5. Запуск трассировки
6. Анализ трассировки
7. Уменьшение проблем блокировки
8. Резюме

Краткое введение в тупики

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

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

Тупики были проклятием многих администраторов баз данных. Хотя это редко встречается для хорошо разработанного и написанного приложения, взаимные блокировки могут быть серьезной проблемой для - как я могу сказать это деликатно? - «менее эффективного» кода приложения. Что еще более расстраивает, так это то, что администратор базы данных не может многое сделать для предотвращения взаимоблокировок, поскольку бремя предотвращения их в первую очередь ложится на разработчиков приложений. После того, как приложение разработано и написано, администратору баз данных трудно что-либо сделать, кроме как идентифицировать код, вызывающий сбой, и сообщить о нем разработчикам, чтобы его можно было исправить.

В SQL Server 2000 и более ранних версиях наиболее распространенным способом отслеживания проблем взаимоблокировки было использование флага трассировки. В SQL Server 2005 все еще можно использовать флаги трассировки (1204 или 1222), но их не всегда легко использовать. Когда был представлен SQL Server 2005, в Profiler SQL Server 2005 были добавлены новые события (они также есть в SQL Server 2008), что позволяет очень легко идентифицировать взаимоблокировки. В этой статье мы узнаем, как использовать SQL Server 2005 Profiler для захвата и анализа взаимоблокировок.

Краткий Профилировщик Учебник для начинающих

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

SQL Server Profiler - это графический интерфейс пользователя для функции в SQL Server, называемой SQL Trace. По сути, SQL Trace имеет возможность внутренней активности SQL Server, что позволяет вам видеть, что происходит внутри вашего SQL Server, включая взаимоблокировки. В этой статье мы будем использовать графический интерфейс SQL Server Profiler, хотя вы можете использовать код Transact-SQL, чтобы выполнить то же самое, используя SQL Trace.

Для захвата трассировки SQL Server с использованием SQL Server Profiler вам необходимо создать трассировку, которая включает в себя несколько основных шагов:

1) Сначала вам нужно выбрать события, которые вы хотите собрать. События - это возникновение некоторой активности внутри SQL Server, которую может отслеживать Profiler, например тупик или выполнение оператора Transact-SQL.

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

3) Поскольку на большинстве серверов SQL Server много разных пользователей, на которых запущено много разных приложений, работающих с разными базами данных на одном и том же экземпляре SQL Server, к трассировке можно добавить фильтры, чтобы уменьшить объем возвращаемых данных трассировки. Например, если вы заинтересованы только в нахождении взаимоблокировок в одной конкретной базе данных, вы можете установить фильтр так, чтобы возвращались только события взаимоблокировок из этой базы данных.

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

5) После того, как вы создали трассировку, используя описанные выше шаги, вы готовы ее запустить. Если вы используете графический интерфейс SQL Server Profiler, результаты трассировки отображаются в графическом интерфейсе по мере их захвата. Кроме того, вы можете сохранить собранные события для последующего анализа.

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

Выбор событий

Хотя для диагностики большинства проблем взаимоблокировок требуется только одно событие, я хотел бы включить в свой след дополнительные контекстные события, чтобы лучше понять, что происходит с кодом. Контекстные события - это события, которые помогают представить другие события в перспективе. События, которые я предлагаю вам собрать, включают в себя:

· График тупиковой ситуации

· Блокировка: тупик

· Блокировка: тупиковая цепь

· RPC: Завершено

· SP: StmtCompleted

· SQL: BatchCompleted

· SQL: BatchStarting

Рисунок 1 : Мне нравится включать дополнительные контекстные события, чтобы помочь мне лучше понять, что происходит с кодом.

Вот краткое объяснение каждого из этих событий.

График тупика

Из семи событий, которые я перечислил выше, единственное событие, которое вы должны иметь, - это событие Deadlock Graph. Он отображает в формате XML и графически чертеж, который точно показывает причину тупика. Мы рассмотрим, как интерпретировать этот рисунок позже в этой статье.

Замок: Тупик

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

Lock: тупиковая цепь

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

RPC: Завершено
RPC: событие Completed возникает после выполнения хранимой процедуры в виде удаленного вызова процедуры. Он включает полезную информацию о выполнении хранимой процедуры, включая время ЦП, использованное для выполнения хранимой процедуры, общую продолжительность времени выполнения хранимой процедуры, логические операции чтения и записи, произошедшие во время ее выполнения, а также имя хранимой процедуры. Сама процедура.

SP: StmtCompleted

Хранимые процедуры состоят из одного или нескольких операторов. В SQL Server 2005 каждый оператор в хранимой процедуре отслеживается. Событие SP: StmtCompleted указывает, когда закончился оператор в хранимой процедуре. Столбцы данных события StmtCompleted предоставляют много полезной информации об операторе, включая фактический код в операторе, продолжительность выполнения оператора, количество использованного времени ЦП, количество логических операций чтения и записи, количество строк, возвращаемых оператором. заявление, среди других.

SQL: BatchStarting

Событие SQL: BatchStarting вызывается всякий раз, когда начинается новый пакет. Как только начинается пакет, происходит один или несколько отдельных операторов Transact-SQL. Событие SQL: BatchStarting - это хорошее событие, позволяющее легко увидеть, где начинается пакет, но, кроме этого, оно не особенно полезно.

SQL: BatchCompleted

Событие SQL: BatchCompleted наступает при завершении пакета. Это означает, что один или несколько операторов Transact-SQL завершены для пакета. Событие SQL: BatchCompleted более полезно, чем событие SQL: BatchStarting, поскольку оно включает полезную информацию, такую ​​как продолжительность всего пакета, логическое число операций чтения и записи, вызванных всеми операторами внутри пакета, общее количество строк, возвращаемых партия и другая полезная информация.

Выбор столбцов данных

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

· События

· TextData

· ApplicationName

· DatabaseName

· ServerName

· SPID

· LoginName

· BinaryData

Выбор организации столбцов

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

Запуск трассировки

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

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

Анализ трассировки

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

Рисунок 2 : Это результаты захвата тупика с использованием событий, которые я рекомендовал.

Чтобы создать тупик для демонстрационных целей, я запустил две отдельные транзакции в двух разных процессах, которые, как я знаю, создали бы тупик. Они представлены восемью событиями SQL: BatchStarting и SQL: BatchCompleted в начале описанной выше трассировки.

Когда SQL Server определяет, что произошла взаимоблокировка, первое событие, обозначающее это, - это событие «Блокировка: цепочка взаимоблокировок». В приведенной выше трассировке есть два из них, для SPID 55 и SPID 54. Затем запускается событие Deadlock Graph, и, наконец, вызывается событие Lock: Deadlock.

Как только SQL Server обнаруживает тупик, он выбирает проигравшего и победителя. Событие SQL: BatchCompleted, которое следует сразу за событием Lock: Deadlock, - это транзакция, которая отменяется и откатывается, а следующее событие SQL: BatchCompleted - это событие, которое было выбрано в качестве победителя и успешно выполнено.

Если у вас возникли проблемы с приведенным выше примером, не беспокойтесь, так как это будет иметь больше смысла, когда мы внимательно рассмотрим событие Deadlock Graph.

Когда я щелкаю событие Deadlock Graph в Profiler, в нижней части экрана Profiler появляется график взаимоблокировки, как показано ниже.

Рисунок 3 : График взаимоблокировки суммирует все действия, которые привели к возникновению тупика.

Да, я знаю, что вы пока не можете прочитать график, но я хотел, чтобы вы увидели взаимосвязь между верхней и нижней частями экрана Profiler. В этом графике есть нечто большее, чем кажется на первый взгляд.

Левый овал на графике с синим крестиком представляет транзакцию, выбранную SQL Server как жертва тупика. Если вы наведете указатель мыши на овал, появится всплывающая подсказка. Этот овал также известен как узел процесса, поскольку он представляет процесс, который выполняет определенную задачу, такую ​​как INSERT, UPDATE или DELETE.

Правый овал на графике представляет успешную транзакцию. Если вы наведете указатель мыши на овал, появится всплывающая подсказка. Этот овал также известен как узел процесса.

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

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

Теперь, когда у вас есть общее представление о «большой» картине, давайте углубимся в детали. Давайте начнем с рассмотрения каждого из узлов ресурсов, начиная с успешного, в правой части нашего графика тупиковых ситуаций.

Рисунок 4 : Эта транзакция была выбрана в качестве победителя

Прежде чем мы обсудим, что говорит нам этот ресурсный узел, сначала нам нужно выучить несколько новых терминов. Как видите, в Resource Node есть ряд новых терминов.

· Идентификатор процесса сервера : это SPID процесса.

· Идентификатор пакета сервера : это внутренний номер ссылки для пакета, в котором выполняется этот код.

· Идентификатор контекста выполнения : это внутренний ссылочный номер потока для вышеуказанного SPID. Значение 0 представляет основной или родительский поток.

· Приоритет тупика : по умолчанию ни одна транзакция не имеет большего или меньшего шанса стать жертвой тупика, чем другая. Однако, если вы используете команду SET DEADLOCK PRIORITY для определенного сеанса, тогда этому сеансу может быть присвоено значение Low, Normal или High; установка приоритета транзакции этого сеанса над транзакцией другого сеанса. Это позволяет администратору базы данных или разработчику контролировать, какой сеанс важнее другого, когда речь идет о взаимоблокировках. Значение 0 указывает, что этому процессу не назначен приоритет.

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

· Владелец ID : это внутренний ссылочный номер для транзакции, которая происходит.

· Дескриптор транзакции : это внутренний ссылочный номер, который указывает состояние транзакции.

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

Теперь, когда у нас есть определения узла процесса, давайте более подробно рассмотрим то, что говорит нам рисунок 4. Во-первых, мы знаем, что это была выигрышная сделка, потому что через нее нет синего креста. Во-вторых, он предоставляет точный код Transact-SQL, который был запущен и вызвал тупик. Это чрезвычайно полезная информация, поскольку она позволяет нам отследить событие до конкретного проблемного кода. В-третьих, он говорит нам, что этот Узел процесса имеет эксклюзивную блокировку на верхнем Ресурсном Узле (X представляет эксклюзивную блокировку). И в-четвертых, он сообщает нам, что запросил еще одну эксклюзивную блокировку на нижнем узле ресурсов. Когда вы смотрите на этот узел процесса изолированно, это не имеет большого значения. Проблема возникает, когда эта транзакция сталкивается с другой транзакцией, как мы узнаем далее.

Рисунок 5 : Эта транзакция является жертвой тупика.

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

Рисунок 6 : Узлы ресурсов говорят нам, за какие ресурсы боролись транзакции.

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

· Идентификатор HoBt: это число относится к подмножеству страниц данных / индекса в пределах одного раздела. Они могут быть в форме кучи или B-дерева. В SQL Server 2005 идентификатор HoBt идентичен идентификатору раздела, найденному в таблице sys.partitions.

· Связанный объект: это идентификатор объекта таблицы, связанной с этим индексом.

· Имя индекса: имя индекса.

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

Верхний узел ресурсов представляет индекс PK_SalesTaxRate_SalesTaxRateID, а нижний узел ресурсов представляет индекс PK_SalesTerritory_TerritoryID.

Теперь, когда мы обсудили все детали этого графика тупиков, давайте соберем все части вместе.

1. SPID 54 запустил транзакцию, затем запросил и получил эксклюзивную блокировку индекса PK_SalesTaxRate_SalesTaxRateID.

2. SPID 55 запустил транзакцию, а затем запросил исключительную блокировку для индекса PK_SalesTerritory_TerritoryID.

3. SPID 55, как часть той же транзакции, затем запросил блокировку обновления для индекса PK_SalesTaxRate_SalesTaxRateID. Однако эта блокировка не была предоставлена, поскольку SPID 54 уже имел исключительную блокировку для индекса. В большинстве случаев это означает, что SPID 55 должен ждать своей очереди, прежде чем сможет получить блокировку обновления для PK_SalesTaxRate_SalesTaxRateID. В этот момент SPID 54 вызывает блокировку SPID 55.

4. Поскольку вышеупомянутая блокирующая блокировка продолжается, SPID 54 хочет завершить свою транзакцию. На шаге 1 выше он только начал транзакцию, но не завершил ее. Теперь SPID 54 хочет завершить транзакцию. Для этого он должен получить эксклюзивную блокировку PK_SalesTerritory_TerritoryID. Проблема в том, что он не может получить блокировку для этого индекса, потому что SPID 55 уже имеет эксклюзивную блокировку. Теперь у нас тупик. Ни один из SPID не может быть продолжен, поскольку каждая транзакция блокирует завершение другой транзакции. Поскольку это нехорошо, SQL Server просматривает две транзакции и решает уничтожить ту, которая до сих пор использовала наименьшее количество ресурсов. В этом случае SPID 55 использовал 220 единиц журнала, а SPID 54 использовал 1612 единиц журнала. Это указывает на то, что SPID 55 должен быть уничтожен, поскольку он использует меньше ресурсов.

5. SQL Server убивает SPID 55, и транзакция откатывается, что освобождает исключительную блокировку PK_SalesTerritory_TerritoryID, теперь позволяя SPID 54 получить исключительную блокировку и завершить транзакцию.

Возможно, вам придется прочитать этот раздел несколько раз, чтобы понять все действия, которые я только что описал. Это не особенно легко следовать. Однако, как только вы поймете, что говорит график Deadlock, вы сможете лучше определить код и / или объекты, которые способствуют проблеме взаимоблокировки, что дает возможность исправить ее. В большинстве случаев это потребует участия разработчиков. К счастью, теперь у вас есть информация, которой вы должны поделиться с разработчиками, чтобы они могли решить проблему.

Уменьшение проблем блокировки

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

• Убедитесь, что дизайн базы данных правильно нормализован.

• Каждый раз обращайтесь к объектам базы данных в одном и том же порядке.

• Сделайте транзакции максимально короткими.

• Во время транзакций не разрешайте ввод данных пользователем.

• Избегайте курсоров.

• Рассмотрите возможность уменьшения эскалации блокировки, используя подсказку ROWLOCK или PAGLOCK.

• Попробуйте использовать подсказку NOLOCK для предотвращения блокировки.

• Используйте как можно более низкий уровень изоляции для пользовательских соединений.

Резюме

Как видите, Profiler может быть очень мощным инструментом, помогающим администраторам баз данных выявлять проблемы взаимоблокировки. Создание тупиковой трассировки Profiler легко создать и запустить. Поэтому, если в настоящее время у вас возникают проблемы с блокировкой в ​​ваших базах данных, найдите время, чтобы применить то, что вы узнали из этой статьи. Чем раньше вы попробуете, тем ближе вы будете к решению проблем с блокировкой.

Отслеживание тупиков. В этой статье Брэд демонстрирует, как вы можете использовать SQL Server Profiler для определения того, как возникают проблемы взаимоблокировки, и как вы можете минимизировать вероятность их повторного появления. Ответ SQL Средство мониторинга и оповещения Red Gate уведомляет вас о возникновении такой проблемы, как взаимоблокировка, и предоставляет вам диагностические данные, необходимые для решения проблемы.

Похожие

Комментарии