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

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

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

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

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

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

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

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

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

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

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

Кратковременные (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 и повторно инициировать транзакцию. Такое приложение также должно следить за распределенными взаимными блокировками, когда возможны одновременные перекрестные запросы ресурсов на клиентском и серверном уровнях.

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

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

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

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