SQL Server 2008 R2: Разблокируем проблемы с блокировками

Устранение проблем базы данных — занятие для сильных духом, в особенности когда идет речь об устранении заморочек с блокировками. Время от времени слон в посудной лавке оказывается обезумевшим носорогом, другими словами как бы маленькая проблема оказывается неувязкой, которую приходится длительно и муторно устранять. В других случаях вы длительно сможете не замечать решение препядствия, находящееся у вас практически под носом. Конкретно так дело обстоит с SQL Server 2008 R2, где известную делему с блокировкой удается решить, просто применив самый свежайший пакет исправлений либо обновление.

Время от времени выполняемые в базе данных операции приводят к дилеммам с краткосрочными и обыкновенными блокировками. Блокировок и разблокировок не избежать. Они происходят в хоть какой системе управления реляционными базами данных, и SQL Server 2008 R2 не исключение.

Блокировка происходит, когда один сеанс базы данных, либо идентификатор процесса сервера (SPID), получает блокировку определенного ресурса, а в это время 2-ой SPID пробует получить конфликтующую блокировку такого же ресурса. Обычно, блокировки ресурса не продолжаются длительно.Как 1-ый сеанс высвобождает блокировку, ее может получить 2-ой SPID, чтоб продолжить обработку запроса.

Грозные факты из жизни блокировок

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

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

Можно настроить характеристики блокировки на уровне строк и страничек. Такие блокировки по дефлоту разрешены для индексов. SQL Server 2008 R2 также поддерживает секционирование таблиц. Потому что при секционировании данные разбиваются на отдельные объекты, секционирование таблиц содействует увеличению общей производительности.

Длительность блокировок также определяется типами запросов. Когда в рамках транзакции запрос не производится и не употребляются подсказки блокировки, блокировки для выполнения инструкций SELECT производятся лишь на время чтения ресурса, но не во время запроса. Блокировки для инструкций INSERT, UPDATE и DELETE сохраняются на всегда выполнения запроса. Это помогает гарантировать согласованность данных и позволяет SQL Server откатывать запросы в случае необходимости.

Когда запрос производится в рамках транзакции, длительность блокировки определяется 3-мя факторами:

типом запроса;
уровнем изоляции транзакции;
наличием либо отсутствием подсказок блокировки.

Краткосрочные (locking) и обыденные (blocking) блокировки — обычное явление в реляционных базах данных, но они могут усугублять производительность, если блокировки ресурсов сохраняются в протяжении долгого времени. Производительность также мучается, когда, заблокировав ресурс, SPID не в состоянии высвободить его.

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

Укрощение блокировок

Монитор активности (Activity Monitor) SQL Server помогает обнаруживать проблемы с краткосрочными и обыкновенными блокировками. Пристально смотрите за показателями Wait Time (Время ожидания), Wait Type (Тип ожидания), Wait Resource (Ожидание ресурса) и Blocked By (Заблокирован) для обозначенных в перечне процессов.

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

sys.dm_os_tasks
sys.dm_os_waiting_tasks
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_resource_governor_workload_group

Получить более четкую картину краткосрочных и обыденных блокировок можно при помощи представления sys.dm_tran_locks, которое предоставляет информацию об активных запросах блокировки — выполненных и ожидающих выполнения. Представления sys.dm_exec_connections, sys.dm_exec_sessions и sys.dm_exec_requests предоставляют сведения соответственно об активных подключениях, сеансах и запросах.

Направьте внимание на представление sys.dm_exec_requests (оно описано тщательно в библиотеке MSDN). Запросы с состоянием «sleeping» (Приостановленные) окончили выполнение и, вероятнее всего, ждут команду от приложения. Запросы с состоянием «Running» (Производится) либо «Runnable» (Готово к запуску) в текущий момент обрабатываются. Запрос с состоянием «suspended» (Приостановлен) ждет получение блокировки либо другое событие.

Столбец wait_type, как предполагает его заглавие, ворачивается тип ожидания. Если значение больше нуля, SPID находится в состоянии ожидания. За дополнительной информацией нужно обращаться к столбцам wait_time и wait_resource. Если запрос заблокирован, в wait_time показана длительность в миллисекундах. В wait_resource указан ресурс, освобождение которого ждет SPID. Направьте внимание также, что blocking_session_id содержит идентификатор сеансов, блокирующих запрос либо отрицательное значение с информацией о обладателе заблокированного ресурса.

Неправильно работающие приложения фронтального плана могут создавать различные препядствия с блокировкой. Если приложение не в состоянии подабающим образом управлять уровнями вложенных транзакций, может получиться ситуация с блокировкой, в какой тип ожидания запроса (wait_type) является нулевым, состояние «sleeping», но при всем этом число транзакций (open_transaction_count) не равно нулю.

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

Если приложение не выбирает все строчки результата, выходит ситуация с блокировкой, в какой тип ожидания запроса (wait_type) является нулевым, состояние «runnable», но при всем этом число транзакций (open_transaction_count) не равно нулю. Более возможно, что приложение не избрало все строчки результатов и оставило блокировку таблицы, закрывая доступ к ней другим SPID. По способности приложение необходимо изменять так, чтоб оно выбирало все приобретенные результаты.

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

sys.dm_db_missing_index_groups
sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats

Принципиально держать в голове, что индексы могут замедлять операции конфигурации данных (о чем говорится в статье “О фоновых операциях с индексом”). Потому при разработке индексов не забудьте воспользоваться представлением sys.dm_db_ index_usage_stats.

Индексация не непременно вызывает долгосрочную блокировку таблиц. При оперативном индексировании употребляется только коллективная блокировка намерения (Intent Share, IS) в начальной таблице на время выполнения основной стадии операции индексирования. Это позволяет продолжать выполнение запросов либо обновлений. В общем случае коллективная блокировка применяется на мотивированном объекте в протяжении недлинного периода времени сначала (и, в неких случаях, в конце) операции индексирования.

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

Борьба с обоюдными блокировками

Особенный тип задачи с блокировками именуется обоюдной блокировкой (deadlock) и случается, когда два либо больше SPID получают блокировки различных объектов, но при всем этом каждому из SPID нужен объект, заблокированный другим SPID. В этой ситуации каждый SPID ждет, пока другой высвободит блокировку, но этого не происходит. Можно понизить число обоюдных блокировок, но вполне избежать их нереально. К счастью, диспетчер блокировок в SQL Server автоматом выявляет появление обоюдных блокировок. Найдя такую делему, диспетчер блокировок делает три операции:

выбирает SPID, подлежащий уничтожению;
инициирует сообщение об ошибке 1205, которое посылает соответственному клиенту;
уничтожает SPID, чтоб тот высвободил заблокированный ресурс и продолжилась работа другого SPID.

Монитор активности и представление sys.dm_tran_locks также помогают выявлять обоюдные блокировки. В мониторе активности нужно смотреть за показателями Wait Time (истекшее время в миллисекундах), Wait Type (указывает, ждет ли операция освобождения) и Wait Resource (в случае состояния ожидания показывает ресурс, освобождение которого ожидается).

Приложения фронтального плана должны разрабатываться так, чтоб уметь управляться с обоюдными блокировками. Корректно написанное приложение фронтального плана, получив ошибку 1205, должно опять подключаться к SQL Server и повторно инициировать транзакцию. Такое приложение также должно смотреть за распределенными обоюдными блокировками, когда вероятны одновременные перекрестные запросы ресурсов на клиентском и серверном уровнях.

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

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

Материалы по теме

Analyzing Deadlocks with SQL Server Profiler
Minimize Blocking in SQL Server
Designing Partitioned Tables and Indexes

Аналогичный товар: Комментирование на данный момент запрещено, но Вы можете оставить ссылку на Ваш сайт.

Комментарии закрыты.