Общие сведения о проблемах блокировки в SQL Server и их устранении

Применимо к: SQL Server (все поддерживаемые версии), Управляемый экземпляр SQL Azure

Оригинальный номер статьи базы знаний: 224453

Цель

В статье описывается блокировка в SQL Server и демонстрируется, как устранить неполадки и решить проблему блокировки.

В этой статье термин «подключение» относится к одному сеансу входа в базу данных. Каждое подключение отображается как идентификатор сеанса (SPID) или session_id во многих представлениях динамического управления. Каждый из этих идентификаторов SPID часто называется процессом, хотя он не является отдельным контекстом процесса в обычном смысле. Вместо этого каждый идентификатор SPID состоит из серверных ресурсов и структур данных, необходимых для обслуживания запросов одного подключения от заданного клиента. Одно клиентское приложение может иметь одно или несколько подключений. С точки зрения SQL Server нет разницы между несколькими подключениями из одного клиентского приложения на одном клиентском компьютере и несколькими подключениями из нескольких клиентских приложений или нескольких клиентских компьютеров; они являются атомарными. Одно подключение может блокировать другое подключение, независимо от исходного клиента.

Что такое блокировка?

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

Длительность и контекст транзакции запроса определяют, как долго удерживаются его блокировки и, таким образом, их влияние на другие запросы. Если запрос не выполняется в транзакции (и указания блокировки не используются), блокировки для операторов SELECT будут храниться только в ресурсе во время его фактического чтения, а не во время запроса. Для операторов INSERT, UPDATE и DELETE блокировки удерживаются во время запроса как для обеспечения согласованности данных, так и для разрешения отката запроса при необходимости.

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

• Блокировка в ядре СУБД
• Настройка блокировки и управление версиями строк
• Режимы блокировки
• Совместимость блокировки
• Уровни изоляции на основе управления версиями строк в ядре СУБД
• Транзакции

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

• Идентификатор SPID удерживает блокировки в наборе ресурсов в течение продолжительного периода времени, прежде чем освободить их. Этот тип блокировки со временем разрешается, но может привести к снижению производительности.

• Идентификатор SPID удерживает блокировки в наборе ресурсов и никогда не освобождает их. Этот тип блокировки не устраняется самостоятельно и запрещает доступ к затронутым ресурсам на неопределенное время.

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

Приложения и блокировка

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

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

При правильном проектировании приложений и запросов SQL Server может поддерживать несколько тысяч одновременных пользователей на одном сервере без небольшой блокировки.

Устранение неполадок, связанных с блокировкой

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

Действия по устранению неполадок:

1. Определение основного сеанса блокировки (головной блокировщик)

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

3. Анализ и понимание причин длительной блокировки

4. Устранение проблемы блокировки путем изменения запроса и транзакции

Теперь давайте рассмотрим, как определить основной сеанс блокировки с помощью соответствующего сбора данных.

Сбор сведений о блокировке

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

Первый метод заключается в запросе объектов динамического управления (DMO) и хранении результатов для сравнения с течением времени. Некоторые объекты, на которые ссылаются в этой статье, являются представлениями динамического управления (DMV), а некоторые — функциями динамического управления (DMF).

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

Сбор информации из представлений динамического управления

Ссылка на представления динамического управления для устранения неполадок, связанных с блокировкой, имеет целью определить SPID (идентификатор сеанса) в верхней части цепочки блокировок и оператор SQL. Найдите идентификаторы SPID жертв, которые были заблокированы. Если какой-либо SPID блокируется другим SPID, изучите SPID, владеющий ресурсом (блокирующий SPID). Блокируется ли также SPID владельца? Вы можете пройтись по цепочке, чтобы найти головной блокировщик, а затем выяснить, почему он сохраняет свою блокировку.

Воспользуйтесь для этого одним из описанных ниже способов.

• В обозревателе объектов SQL Server Management Studio (SSMS) щелкните правой кнопкой мыши объект сервера верхнего уровня, последовательно разверните разделы Отчеты и Стандартные отчеты, затем выберите Действие — все блокирующие транзакции. В этом отчете отображаются текущие транзакции в верхней части цепочки блокировок. Если развернуть транзакцию, в отчете будут показаны транзакции, заблокированные головной транзакцией. В этом отчете также будут показаны блокировка оператора SQL и заблокированный оператор SQL.

• Откройте монитор активности в SSMS и перейдите к столбцу «Заблокировано». Дополнительные сведения о мониторе активности см. здесь.

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

• Команды sp_who и sp_who2 — это устаревшие команды для отображения всех текущих сеансов. Представление динамического управления sys.dm_exec_sessions возвращает больше данных в результирующем наборе, который проще запрашивать и фильтровать. sys.dm_exec_sessions можно найти в основе других запросов.

• Если конкретный сеанс уже определен, можно использовать DBCC INPUTBUFFER(<session_id>) для поиска последнего оператора, отправленного сеансом. Аналогичные результаты можно получить с помощью sys.dm_exec_input_buffer функции динамического управления (DMF) в результирующем наборе, который проще запрашивать и фильтровать, предоставляя session_id и request_id. Например, чтобы вернуть последний запрос, отправленный session_id 66 и request_id 0:

SELECT * FROM sys.dm_exec_input_buffer (66,0);

• Ссылайтесь на sys.dm_exec_requests и столбец blocking_session_id. Если blocking_session_id равно 0, сеанс не блокируется. В то время как sys.dm_exec_requests перечисляет только выполняющиеся в данный момент запросы, любое соединение (активное или нет) будет перечислено в sys.dm_exec_sessions. Создайте это общее соединение между sys.dm_exec_requests и sys.dm_exec_sessions в следующем запросе. Помните, что для возврата sys.dm_exec_requests запрос должен активно выполняться на сервере SQL Server.

• Выполните этот пример запроса, чтобы найти активно выполняемые запросы и их текущий текст пакета SQL или текст входного буфера, используя sys.dm_exec_sql_text или sys.dm_exec_input_buffer представлений динамического управления. Если данные, возвращаемые text столбцом sys.dm_exec_sql_text, имеют значение NULL, запрос в данный момент не выполняется. В этом случае столбец event_info будет sys.dm_exec_input_buffer содержать последнюю строку команды, переданную в модуль SQL. Этот запрос также можно использовать для определения сеансов, блокирующих другие сеансы, включая список session_ids заблокированных на session_id.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

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

WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

• Чтобы перехватывать долго выполняемые или незафиксированные транзакции, используйте другой набор представлений динамического управления для просмотра текущих открытых транзакций, включая sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections и sys.dm_exec_sql_text. Существует несколько представлений динамического управления, связанных с отслеживанием транзакций. Дополнительные сведения о представлениях динамического управления в транзакциях см. здесь.

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];

• Ссылайтесь на sys.dm_os_waiting_tasks, который находится на уровне потоков/задач SQL Server. Возвращает информацию о том, какой тип ожидания SQL в данный момент испытывает запрос. Как и sys.dm_exec_requests, sys.dm_os_waiting_tasks возвращает только активные запросы.

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

Из-за параметра INNER JOIN в sys.dm_os_waiting_tasks в следующем запросе выходные данные из sys.dm_tran_locks ограничиваются только заблокированными запросами, состоянием ожидания и блокировками:

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

При использовании представлений динамического управления хранение результатов запроса с течением времени предоставит точки данных, которые позволяют просмотреть блокировку в течение указанного интервала времени для выявления сохраняемых блокировок или тенденций. Главным инструментом CSS для устранения таких неполадок является использование сборщика данных PSSDiag. Этот инструмент использует статистику производительности SQL Server для сбора результирующих наборов из представлений динамического управления, упомянутых выше, с течением времени. Поскольку этот инструмент постоянно развивается, ознакомьтесь с последней общедоступной версией DiagManager на сайте GitHub.

Сбор сведений о расширенных событиях

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

Существует два способа записи трассировок в SQL Server — расширенные события (XEvents) и трассировки профилировщика. Однако трассировки SQL, использующие SQL Server Profiler, являются устаревшими. XEvents — это новая, более совершенная платформа трассировки, которая обеспечивает большую универсальность и меньшее воздействие на наблюдаемую систему, а ее интерфейс интегрирован в SSMS.

Существуют готовые сеансы расширенных событий, готовые к запуску в SSMS, перечисленные в обозревателе объектов меню для профилировщика XEvent. Дополнительные сведения см. в разделе Профилировщик XEvent. Вы также можете создать собственные пользовательские сеансы расширенных событий в SSMS. Дополнительные сведения см. в разделе Мастер создания сеансов расширенных событий. Для устранения неполадок, связанных с блокировкой, обычно регистрируются:

• Ошибки категорий:
  • Внимание
  • Blocked_process_report**
  • Error_reported (администратор канала)
  • Exchange_spill
  • Execution_warning

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

• Предупреждения категорий:

  • Hash_warning
  • Missing_column_statistics
  • Missing_join_predicate
  • Sort_warning

• Выполнение категорий:

  • Rpc_completed
  • Rpc_starting
  • Sql_batch_completed
  • Sql_batch_starting

• Блокировка категорий

  • Lock_deadlock

• Сеанс категорий

  • Existing_connection
  • Вход
  • Выход

Определение и устранение распространенных сценариев блокировки

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

Анализ блокирующих данных

• Изучите выходные данные sys.dm_exec_requests и sys.dm_exec_sessions представлений динамического управления, чтобы определить верхние части цепочек блокировок, используя blocking_these и session_id. Это позволит наиболее четко определить, какие запросы заблокированы, а какие блокируются. Изучите сеансы, которые заблокированы и блокируются. Существует ли общая или корневая цепочка блокировок? Скорее всего, они используют общую таблицу, а также один или несколько сеансов, участвующих в цепочке блокировки, и выполняют операцию записи.

• Изучите выходные данные представлений динамического управления sys.dm_exec_requests и sys.dm_exec_sessions для получения информации об идентификаторах SPID в верхней части цепочки блокировок. Выполните поиск следующих столбцов:

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

    Состояние	Смысл
    Общие сведения	SPID выполняет фоновую задачу, например обнаружение взаимоблокировки, запись журнала или контрольную точку.
    Спящий режим	В настоящее время SPID не выполняется. Это обычно указывает на то, что SPID ожидает команды от приложения.
    Работает	В настоящее время SPID выполняется в планировщике.
    Готово к запуску	SPID находится в очереди готового к запуску планировщика и ожидает получения времени планировщика.
    Suspended	SPID ожидает ресурс, например блокировку или кратковременную блокировку.

  • sys.dm_exec_sessions.open_transaction_count
    В этом столбце указывается количество открытых транзакций в этом сеансе. Если это значение больше 0, SPID находится в открытой транзакции и может удерживать блокировки, полученные любым оператором в рамках транзакции.

  • sys.dm_exec_requests.open_transaction_count
    Аналогичным образом в этом столбце указывается количество открытых транзакций в этом запросе. Если это значение больше 0, SPID находится в открытой транзакции и может удерживать блокировки, полученные любым оператором в рамках транзакции.

  • sys.dm_exec_requests.wait_type, wait_time и last_wait_type
    Если значение sys.dm_exec_requests.wait_type равно NULL, запрос в настоящее время ничего не ожидает, а значение last_wait_type указывает на последний wait_type, с которым столкнулся запрос. Дополнительные сведения о sys.dm_os_wait_stats и описание наиболее распространенных типов ожидания см. в разделе sys.dm_os_wait_stats. Значение wait_time можно использовать для определения хода выполнения запроса. Когда запрос к таблице sys.dm_exec_requests возвращает значение в столбце wait_time, которое меньше, чем значение wait_time из предыдущего запроса sys.dm_exec_requests, это указывает на то, что предыдущая блокировка была получена и освобождена и теперь ожидает новой блокировки (при условии ненулевого wait_time). Это можно проверить, сравнив wait_resource с выходными данными sys.dm_exec_requests, где отображается ресурс, для которого ожидается запрос.

  • sys.dm_exec_requests.wait_resource Этот столбец указывает ресурс, в котором ожидается заблокированный запрос. В следующей таблице перечислены распространенные wait_resource форматы и их значение:

    Ресурс	Формат	Пример	Объяснение
    Table	DatabaseID:ObjectID:IndexID	ВКЛАДКА: 5:261575970:1	В этом случае база данных с идентификатором 5 является образцом базы данных pubs, object_id 261575970 — таблицей заголовков, а 1 — кластеризованным индексом.
    Page	DatabaseID:FileID:PageID	СТРАНИЦА: 5:1:104	В этом случае базой данных с идентификатором 5 является pubs, файл с идентификатором 1 — первичным файлом данных, а страница 104 — страницей, относящейся к таблице заголовков. Чтобы определить object_id, к которой принадлежит страница, используйте функцию динамического управления sys.dm_db_page_info, передав DatabaseID, FileId, PageId из wait_resource.
    Key	DatabaseID:Hobt_id (хэш-значение для ключа индекса)	КЛЮЧ: 5:72057594044284928 (3300a4f361aa)	В этом случае база данных с идентификатором 5 является Pubs, Hobt_ID 72057594044284928 соответствует index_id 2 для object_id 261575970 (таблица заголовков). Используйте представление каталога sys.partitions, чтобы связать hobt_id с определенным index_id и object_id. Не существует способа удалить хэш ключа индекса по определенному значению ключа.
    Строка	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	В этом случае база данных с идентификатором 5 является pubs, файл с идентификатором 1 — первичным файлом данных, страница 104 — страницей, относящейся к таблице заголовков, а слот 3 указывает положение строки на странице.
    Компиляция	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	В этом случае база данных с идентификатором 5 является pubs, файл с идентификатором 1 — первичным файлом данных, страница 104 — страницей, относящейся к таблице заголовков, а слот 3 указывает положение строки на странице.

  • Представление динамического управления sys.dm_tran_active_transactionssys.dm_tran_active_transactions содержит данные об открытых транзакциях, которые можно объединить с другими представлениями динамического управления, чтобы получить полную картину транзакций, ожидающих фиксации или отката. Используйте следующий запрос для возврата сведений об открытых транзакциях, присоединенных к другим представлениям динамического управления, включая sys.dm_tran_session_transactions. Изучите текущее состояние транзакции, transaction_begin_time и другие ситуационные данные, чтобы оценить, может ли она быть источником блокировки.

    SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                    WHEN 2 THEN 'Read-only transaction'
                                                    WHEN 3 THEN 'System transaction'
                                                    WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
                WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                WHEN 1 THEN 'The transaction has been initialized but has not started.'
                WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
                WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                WHEN 6 THEN 'The transaction has been committed.'
                WHEN 7 THEN 'The transaction is being rolled back.'
                WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
    FROM sys.dm_tran_active_transactions tat 
    INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
    INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
    LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;
    

  • Другие столбцы

    Остальные столбцы в sys.dm_exec_sessions и sys.dm_exec_request также могут предоставить сведения о причине возникновения проблемы. Их полезность зависит от обстоятельств проблемы. Например, можно определить, происходит ли проблема только в определенных клиентах (hostname), в определенных сетевых библиотеках (client_interface_name), когда последний пакет, отправленный SPID, был last_request_start_time в sys.dm_exec_sessions, как долго выполнялся запрос, используя start_time в sys.dm_exec_requests, и так далее.

Распространенные сценарии блокировки

В таблице ниже приведены общие симптомы и их вероятные причины.

Столбцы wait_type, open_transaction_count и status относятся к информации, возвращаемой sys.dm_exec_request, другие столбцы могут быть возвращены sys.dm_exec_sessions. Столбец "Разрешается?" указывает, будет ли блокировка разрешаться самостоятельно, или сеанс должен быть завершен с помощью KILL команды . Дополнительные сведения см. в разделе KILL (Transact-SQL).

Подробные сценарии блокировки

Сценарий 1. Блокировка, вызванная обычным выполнением запроса с длительным временем выполнения

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

Решение:

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

Встроенные в SSMS отчеты из хранилища запросов (доступно в SQL Server 2016) также являются рекомендуемым и ценным инструментом для выявления наиболее затратных запросов, неоптимальных планов выполнения.

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

Сценарий 2. Блокировка, вызванная SPID в спящем режиме с незафиксированной транзакцией

Этот тип блокировки часто можно определить по SPID, который находится в спящем режиме или ожидает команды, но уровень вложенности транзакций (@@TRANCOUNT, open_transaction_count от sys.dm_exec_requests) больше нуля. Это может произойти, если приложение получает время ожидания запроса или выполняет отмену, не выполнив при этом требуемое количество операторов ROLLBACK и/или COMMIT. Когда SPID получает время ожидания запроса или отмену, он завершает текущий запрос и пакет, но не выполняет автоматический откат или фиксацию транзакции. За это отвечает приложение, так как SQL Server не может предполагать, что откат всей транзакции должен быть выполнен из-за отмены одного запроса. Время ожидания или отмена запроса будет отображаться как событие сигнала ATTENTION для SPID в сеансе расширенных событий.

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

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

Затем выполните этот запрос в том же окне:

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

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

Используйте сценарий, описанный ранее в этой статье, на основе sys.dm_tran_active_transactions, чтобы определить незафиксированные транзакции в экземпляре.

Решения:

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

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

  • В обработчике ошибок клиентского приложения выполните IF @@TRANCOUNT > 0 ROLLBACK TRAN после любой ошибки, даже если клиентское приложение не считает, что транзакция открыта. Проверка на наличие открытых транзакций необходима, поскольку хранимая процедура, вызванная во время пакетной обработки, могла начать транзакцию без ведома клиентского приложения. Некоторые условия, такие как отмена запроса, препятствуют выполнению процедуры после выполнения текущего оператора, поэтому даже если процедура имеет логику для проверки IF @@ERROR <> 0 и прерывания транзакции, этот код отката не будет выполняться в таких случаях.

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

  • Используйте SET XACT_ABORT ON для подключения или в любых хранимых процедурах, которые начинают транзакции и не выполняют очистку после ошибки. В случае ошибки времени выполнения этот параметр прерывает все открытые транзакции и возвращает управление клиенту. Дополнительные сведения см. в разделе SET XACT_ABORT (Transact-SQL).

Сценарий 3. Блокировка, вызванная SPID, соответствующее клиентское приложение которого не получает все строки результатов до завершения

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

Решение:

Приложение должно быть перезаписано для получения всех строк результата до завершения. Это не исключает использования операторов OFFSET и FETCH в выражении ORDER BY запроса для выполнения постраничного просмотра на стороне сервера.

Сценарий 4. Блокировка, вызванная распределенной взаимоблокировкой клиента/сервера

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

Пример А. Распределенная взаимоблокировка клиента/сервера с одним клиентским потоком

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

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

В приведенном выше примере один поток клиентского приложения имеет два открытых подключения. Он асинхронно отправляет операцию SQL в dbproc1. Это означает, что он не ожидает возврата вызова перед продолжением. Затем приложение отправляет другую операцию SQL в dbproc2 и ожидает результатов, чтобы начать обработку возвращаемых данных. Когда данные начинают возвращаться (какой бы dbproc ни ответил первым — предположим, что это dbproc1), оно обрабатывает до конца все данные, возвращенные этим dbproc. Приложение извлекает результаты из dbproc1, пока SPID1 не будет заблокирован в блокировке, удерживаемой SPID2 (так как два запроса выполняются асинхронно на сервере). На этом этапе dbproc1 будет ожидать дополнительных данных в течение неограниченного времени. SPID2 не блокируется при блокировке, но пытается отправить данные клиенту dbproc2. Однако dbproc2 фактически блокируется в dbproc1 на уровне приложения, так как dbproc1 использует один поток выполнения приложения. Это приводит к взаимоблокировке, которую SQL Server не удается обнаружить или разрешить, так как только один из задействованных ресурсов является ресурсом SQL Server.

Пример Б. Распределенная взаимоблокировка клиента/сервера с потоком, ориентированным на подключение

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

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

Этот случай схож с примером A, за исключением того, что dbproc2 и SPID2 выполняют оператор SELECT с намерением выполнить обработку строк за один раз и передать каждую строку через буфер в dbproc1 для оператора INSERT, UPDATE или DELETE в той же таблице. В конечном итоге SPID1 (выполняя INSERT, UPDATE или DELETE) блокируется на блокировку, удерживаемую SPID2 (выполняя SELECT). SPID2 записывает строку результата в клиент dbproc2. Затем Dbproc2 пытается передать строку в буфере в dbproc1, но обнаруживает, что dbproc1 занят (он заблокирован в ожидании SPID1 для завершения текущего INSERT, который заблокирован на SPID2). На этом этапе dbproc2 блокируется на уровне приложения dbproc1, SPID (SPID1) которого блокируется на уровне базы данных SPID2. Это также приводит к взаимоблокировке, которую SQL Server не может обнаружить или устранить, поскольку только один из задействованных ресурсов является ресурсом SQL Server.

Оба примера A и B — это фундаментальные проблемы, о которых должны знать разработчики приложений. Они должны выполнять кодирование приложений для соответствующей обработки этих случаев.

Решение:

Если задается время ожидания запроса, то в случае возникновения распределенной взаимоблокировки она не будет работать при наступлении времени ожидания. Дополнительную информацию об использовании времени ожидания запроса можно найти в документации к поставщику подключений.

Сценарий 5. Блокировка, вызванная сеансом в состоянии отката

Для запроса на модификацию данных, который был завершен или отменен вне пользовательской транзакции, будет выполнен откат. Это также может произойти как побочный эффект отключения сеанса сетевого подключения клиента или при выборе запроса в качестве жертвы взаимоблокировки. Часто это можно определить, наблюдая за выходными данными sys.dm_exec_requests, который может указывать на откат (ROLLBACK) command, а столбец percent_complete может отображать прогресс.

Для запроса на модификацию данных, который был завершен или отменен вне пользовательской транзакции, будет выполнен откат. Это также может произойти как побочный эффект перезапуска клиентского компьютера и отключения сетевого сеанса. Аналогичным образом будет выполнен откат запроса, выбранного в качестве жертвы взаимоблокировки. Для запроса на изменение данных откат часто может быть выполнен быстрее, чем первоначально применены изменения. Например, если оператор DELETE, INSERT или UPDATE был запущен в течение часа, откат может занять не менее часа. Это ожидаемое поведение, так как внесенные изменения необходимо откатить, или транзакции и физическая целостность в базе данных будут скомпрометированы. Поскольку это должно произойти, SQL Server помечает SPID как окончательное состояние или состояние отката (что означает, что он не может быть завершен или выбран в качестве жертвы взаимоблокировки). Часто это можно определить, наблюдая за выходными данными sp_who, который может указывать на команду ROLLBACK. Столбец status будет sys.dm_exec_sessions указывать на состояние ROLLBACK.

Решение:

Необходимо дождаться завершения сеанса отката внесенных изменений.

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

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

Сценарий 6. Блокировка, вызванная потерянной транзакцией

Это распространенный сценарий проблем, который частично перекрывается со сценарием 2. Если клиентское приложение останавливается, рабочая станция клиента перезапускается или возникает ошибка прерывания пакетной обработки, все они могут оставить транзакцию открытой. Такая ситуация может возникнуть, если приложение не выполняет откат транзакции в блоках CATCH или FINALLY или не обрабатывает эту ситуацию иным образом.

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

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

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

Затем выполните этот запрос в том же окне:

SELECT @@TRANCOUNT;

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

Решения:

• Лучший способ предотвратить это состояние — улучшить обработку ошибок/исключений приложения, особенно при неожиданном завершении работы. Убедитесь, что вы используете блок Try-Catch-Finally в коде приложения и откатите транзакцию в случае исключения.
• Используйте SET XACT_ABORT ON для подключения или в любых хранимых процедурах, которые начинают транзакции и не выполняют очистку после ошибки. В случае ошибки времени выполнения этот параметр автоматически прерывает все открытые транзакции и возвращает управление клиенту. Дополнительные сведения см. в разделе SET XACT_ABORT (Transact-SQL).
• Чтобы устранить проблему с потерянным подключением клиентского приложения, которое было разорвано без соответствующей очистки ресурсов, можно завершить SPID с помощью команды KILL. Дополнительные сведения см. в разделе KILL (Transact-SQL).

Команда KILL использует значение SPID в качестве входных данных. Например, чтобы завершить SPID 9, выполните следующую команду:

KILL 99

См. также

• Мониторинг производительности с помощью хранилища запросов
• Устранение проблем блокировки, вызванных эскалацией блокировки в SQL Server
• Руководство по блокировке транзакций и управлению версиями строк
• ЗАДАНИЕ УРОВНЯ ИЗОЛЯЦИИ ТРАНЗАКЦИЙ
• Краткое руководство по расширенным событиям в SQL Server