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

Область применения: SQL Server, Управляемый экземпляр SQL Azure

Исходный номер базы знаний: 224453

Сводка

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

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

Примечание.

В этой статье рассматриваются экземпляры SQL Server, включая Управляемый экземпляр SQL Azure. Сведения об устранении неполадок блокировки в базе данных SQL Azure см. в статье Общие сведения о блокировке в базе данных SQL Azure и способы ее устранения.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Второй метод — использовать расширенные события (XEvents) или трассировки профилировщика SQL для записи выполняемых операций. Так как трассировка 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 это старые команды, которые отображают все текущие сеансы. DmV 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 wait_type в настоящее время возникает. Как и sys.dm_exec_requests, sys.dm_os_waiting_tasks возвращает только активные запросы.

Примечание.

Дополнительные сведения о типах ожидания, включая статистические статистики ожидания с течением времени, см. в sys.dm_db_wait_stats dmV.

Используйте 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.

В обозреватель объектов в меню XEvent Profiler можно найти предварительно созданные сеансы расширенных событий, готовые к запуску в SSMS. Дополнительные сведения см. в разделе Профилировщик 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 находится в очереди готового к запуску планировщика и ожидает получения времени планировщика.
Приостановлена	SPID ожидает ресурс, например блокировку или кратковременную блокировку.

sys.dm_exec_sessions.open_transaction_count

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

Аналогичным образом в этом столбце показано количество открытых транзакций в этом запросе. Если это значение больше 0, SPID находится в открытой транзакции и может хранить блокировки, приобретенные любой активной инструкцией в рамках транзакции. В отличие от sys.dm_exec_sessions.open_transaction_countтого, если нет активного запроса, в этом столбце отображается значение 0.
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_resourcesys.dm_exec_requests данные, в которых отображается ресурс, для которого ожидается запрос.

sys.dm_exec_requests.wait_resource

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

Ресурс	Форматировать	Пример	Описание
Таблица	DatabaseID:ObjectID:IndexID	ВКЛАДКА: 5:261575970:1	В этом случае база данных с идентификатором 5 является образцом базы данных pubs, `object_id` 261575970 — таблицей заголовков, а 1 — кластеризованным индексом.
Стр.	DatabaseID:FileID:PageID	СТРАНИЦА: 5:1:104	В этом случае базой данных с идентификатором 5 является pubs, файл с идентификатором 1 — первичным файлом данных, а страница 104 — страницей, относящейся к таблице заголовков. Чтобы определить object_id, к которой принадлежит страница, используйте функцию динамического управления sys.dm_db_page_info, передав DatabaseID, FileId, PageId из `wait_resource`.
Ключ	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 указывает положение строки на странице.

Представление динамического управления 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).

Сценарий	Wait_type	Open_Tran	Состояние	Разрешается?	Другие симптомы
1	НЕ NULL	>= 0	готово к запуску	Да, после завершения запроса.	В столбцах `sys.dm_exec_sessions`, `reads`, `cpu_time` и/или `memory_usage` со временем будет увеличиваться. Продолжительность запроса будет высокой после его завершения.
2	NULL	>0	спящий режим	Нет, но SPID можно завершить.	В сеансе расширенных событий для этого SPID может появиться сигнал внимания, указывающий на время ожидания запроса или отмену.
3	NULL	>= 0	готово к запуску	№ Не разрешается, пока клиент не получит все строки или не закроет подключение. SPID можно завершить, но это может занять до 30 секунд.	Если open_transaction_count = 0 и SPID удерживает блокировки, пока уровень изоляции транзакции установлен по умолчанию (READ COMMITTED), это, скорее всего, является причиной.
4	Разные	>= 0	готово к запуску	№ Не разрешается, пока клиент не отменит запросы или не закроет подключения. SPID можно завершить, но это может занять до 30 секунд.	Столбец `hostname` в `sys.dm_exec_sessions` для SPID, находящегося в верхней части цепочки блокировки, будет таким же, как и у SPID, который он блокирует.
5	NULL	>0	откат	Да.	В сеансе расширенных событий для этого SPID может появиться сигнал внимания, указывающий на время ожидания или отмены запроса, или будет просто выдан оператор отката.
6	NULL	>0	спящий режим	Рано или поздно эту меру потребуется реализовать. Если Windows NT определит, что сеанс больше не активен, подключение будет разорвано.	Значение `last_request_start_time` в `sys.dm_exec_sessions` намного предшествует текущему времени.

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

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

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

Решение.

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

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

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

Примечание.

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

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

Этот тип блокировки часто можно определить с помощью SPID, спящего или ожидающего команды с уровнем вложения транзакций (@@TRANCOUNTopen_transaction_countот sys.dm_exec_requests) больше нуля. Это может произойти, если приложение получает время ожидания запроса или выполняет отмену, не выполнив при этом требуемое количество операторов ROLLBACK и/или COMMIT. Когда SPID получает время ожидания запроса или отмену, он завершает текущий запрос и пакет, но не автоматически откатывает или фиксирует транзакцию. Приложение отвечает за это действие, так как SQL Server не может предположить, что всю транзакцию необходимо откатить из-за отмены одного запроса. Время ожидания запроса или отмена отображается как событие СИГНАЛА ВНИМАНИЯ для 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).

Примечание.

Подключение не сбрасывается, пока оно не будет повторно использовано из пула подключений, поэтому возможно, что пользователь может открыть транзакцию, а затем освободить подключение к пулу подключений, но оно может не быть повторно использовано в течение нескольких секунд, в течение которых транзакция будет оставаться открытой. Если подключение не используется повторно, транзакция будет прервана, когда истекло время ожидания подключения, и будет удалена из пула подключений. Таким образом, оптимально для клиентского приложения прервать транзакции в своем обработчике ошибок или использовать SET XACT_ABORT ON, чтобы избежать этой потенциальной задержки.

Внимание

После SET XACT_ABORT ON операторы T-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. Блокировка, вызванная сеансом в состоянии отката

При удалении или отмене запроса на изменение данных за пределами определяемой пользователем транзакции запрос откатывается. Этот откат также может произойти как побочный эффект отключения сеанса клиентской сети, перезагрузки клиентского компьютера или запроса, выбранного в качестве жертвы взаимоблокировки. Часто не удается выполнить откат запроса на изменение данных быстрее, чем изначально были применены изменения. Например, если оператор DELETEили INSERTUPDATE инструкция выполнялась в течение часа, может потребоваться по крайней мере час, чтобы откатить. Это поведение ожидается, так как если изменения не откатываются, транзакционная и физическая целостность в базе данных будут скомпрометированы. Так как этот откат должен завершиться, SQL Server помечает сеанс в состоянии KILLED/ROLLBACK, и вы не можете снова убить сеанс или выбрать его в качестве жертвы взаимоблокировки. Это состояние часто можно определить, наблюдая за выходными данными, в которых sys.dm_exec_requests отчеты command столбца KILLED/ROLLBACKи percent_complete столбец показывают ход выполнения.

Примечание.

При включенной функции ускоренного восстановления баз данных откат происходит редко. Эта функция была добавлена в SQL Server 2019.

Решение.

Дождитесь завершения отката изменений сеанса.

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

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

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

Это распространенный сценарий проблем, который частично перекрывается со сценарием 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

Примечание.

Выполнение команды KILL может занять до 30 секунд из-за интервала между проверками для команды KILL.

Обратная связь

Были ли сведения на этой странице полезными?

Last updated on 2026-08-04

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

Сводка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Связанные материалы

Обратная связь

Дополнительные ресурсы