Поделиться через

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

  • Статья

Применимо к: База данных SQL Azure базе данных SQL в Fabric

В статье описывается блокировка в базе данных SQL База данных SQL Azure и Fabric, а также демонстрируется устранение неполадок и устранение блокировки.

Цель

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

Сведения об устранении взаимоблокировок см. в статье "Анализ и предотвращение взаимоблокировок" в Базе данных Azure SQL.

Примечание.

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

Общие сведения о блокировках

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

Каждая новая база данных в База данных Azure SQL имеет параметр базы данных с моментальным снимком (RCSI), включенный по умолчанию. Блокировка между сеансами чтения данных и сеансами записи данных сводится к минимуму при включении параметра RCSI, который использует управление версиями строк для повышения параллелизма. Однако блокировка и взаимоблокировка по-прежнему могут возникать в базах данных в База данных SQL Azure, так как:

  • Запросы, изменяющие данные, могут блокировать друг друга.
  • Запросы могут выполняться на уровнях изоляции, повышающих блокировку. Уровни изоляции можно указать в приложениях строка подключения, указаниях запросов или инструкциях SET в Transact-SQL.
  • RCSI может быть отключен, что приводит к тому, что база данных использует общие блокировки (S) для защиты инструкций SELECT, выполняемых на уровне изоляции фиксации чтения. Это может увеличить блокировку и взаимоблокировку.

Уровень изоляции моментальных снимков также включен по умолчанию для новых баз данных в База данных Azure SQL. Изоляция моментальных снимков — это дополнительный уровень изоляции на основе строк, обеспечивающий согласованность данных на уровне транзакций и использующий версии строк для выбора обновляемых строк. Чтобы использовать изоляцию моментальных снимков, запросы или подключения должны явно задать уровень изоляции транзакций, соответствующий SNAPSHOT. Это можно сделать только при включении изоляции моментальных снимков для базы данных.

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

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Если rcSI включен, is_read_committed_snapshot_on столбец возвращает значение 1. Если включена изоляция моментальных снимков, snapshot_isolation_state_desc столбец возвращает значение ON.

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

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

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

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

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

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

Оптимизированная блокировка

Оптимизированная блокировка — это новая функция ядро СУБД значительно сокращает объем памяти блокировки и количество блокировок, которые одновременно требуются для записи. Оптимизированная блокировка использует два основных компонента: блокировка идентификатора транзакции (TID) (также используется в других функциях управления версиями строк) и блокировка после квалификации (LAQ). Для этого не требуется дополнительная конфигурация.

В настоящее время эта статья относится к поведению ядро СУБД без оптимизированной блокировки.

Дополнительные сведения и сведения о том, где доступна оптимизированная блокировка, см. в статье "Оптимизированная блокировка".

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

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

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

Если приложения и запросы спроектированы правильно, База данных SQL Azure способна поддерживать многие тысячи одновременных пользователей на одном сервере с небольшой блокировкой.

Примечание.

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

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

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

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

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

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

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

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

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

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

Чтобы противодействовать трудностям устранения проблем с блокировками, администратор базы данных может использовать скрипты SQL, которые постоянно отслеживают состояние блокировки базы данных в Базе данных Azure SQL. Существует два главных метода сбора этих данных.

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

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

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

Не забудьте выполнить каждый из этих сценариев в целевой базе данных в Базе данных Azure SQL.

  • Команды 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);

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

  • Выполните этот пример запроса, чтобы найти активно выполняемые запросы и их текущий текст пакета SQL или текст входного буфера, используя sys.dm_exec_sql_text или sys.dm_exec_input_buffer представлений динамического управления. Если в поле text sys.dm_exec_sql_text возвращается значение NULL, запрос в данный момент не выполняется. В этом случае поле sys.dm_exec_input_buffer содержит последнюю командную строку, event_info переданную обработчику 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;

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. Оно возвращает сведения о типе ожидания SQL, в котором в данный момент находится запрос. Как и sys.dm_exec_requests, sys.dm_os_waiting_tasks возвращает только активные запросы.

Примечание.

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

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

Из-за параметра 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>';
  • При использовании представлений динамического управления хранение результатов запроса с течением времени предоставит точки данных, которые позволяют просмотреть блокировку в течение указанного интервала времени для выявления сохраняемых блокировок или тенденций.

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

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

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

См. документ, в котором объясняется, как использовать мастер создания сеанса расширенных событий в SSMS. Однако для Баз данных SQL Azure SSMS предоставляет вложенную папку Extended Events для каждой базы данных в обозревателе объектов. Используйте мастер сеансов расширенных событий для сбора следующих полезных событий.

  • Ошибки категорий:

    • Внимание
    • Error_reported
    • Execution_warning

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

    • Missing_join_predicate

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

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Category deadlock_monitor

    • database_xml_deadlock_report

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

    • Existing_connection
    • Имя входа
    • Выход

Примечание.

Детальные сведения о взаимоблокировках см. в статье "Анализ и предотвращение взаимоблокировок в Базе данных Azure SQL".

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

Изучив предыдущие сведения, можно определить причину большинства проблем с блокировкой. В остальной части этой статьи описывается, как использовать эту информацию для выявления и устранения некоторых распространенных сценариев блокировки. В рамках обсуждения предполагается, что вы использовали сценарии блокировки (упоминавшиеся ранее) для сбора информации о блокирующих 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_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 форматы и их значение:

    Ресурс Форматировать Пример Описание
    Таблица DatabaseID:ObjectID:IndexID ВКЛАДКА: 5:261575970:1 В этом случае база данных с ИД 5 — это пример базы данных pubs, объект с ИД 261575970 — таблица titles, а 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 указывает положение строки на странице.
    Компиляция DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 В этом случае база данных с идентификатором 5 является pubs, файл с идентификатором 1 — первичным файлом данных, страница 104 — страницей, относящейся к таблице заголовков, а слот 3 указывает положение строки на странице.
    • Представление динамического управления sys.dm_tran_active_transactions sys.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
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, 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), в определенных сетевых библиотеках (net_library), когда последний пакет, отправленный SPID, был last_request_start_time в start_time, как долго выполнялся запрос, с помощью sys.dm_exec_sessions в sys.dm_exec_requests и т. д.

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

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

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

Сценарий Тип ожидания 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 COMMMITTED), это возможная причина.
4 Разные >= 0 готово к запуску № Не разрешается, пока клиент не отменит запросы или не закроет подключения. SPID можно убить, но может занять до 30 секунд. Столбец hostname в sys.dm_exec_sessions для SPID, находящегося в верхней части цепочки блокировки, будет таким же, как и у SPID, который он блокирует.
5 NULL >0 откат Да. Сигнал внимания может быть замечен в сеансе расширенных событий для этого SPID, указывая время ожидания запроса или отмену, или просто инструкция отката была выдана.
6 NULL >0 спящий режим Рано или поздно эту меру потребуется реализовать. Когда Windows NT определяет, что сеанс больше не активен, подключение База данных SQL Azure нарушается. Значение last_request_start_time в sys.dm_exec_sessions намного предшествует текущему времени.

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

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

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

    Отчеты из хранилища запросов в SSMS — также весьма рекомендуемый и полезный инструмент для определения наиболее ресурсоемких запросов и неоптимальных планов выполнения. Также ознакомьтесь на портале Azure с разделом Интеллектуальная производительность для Базы данных SQL Azure, в том числе с подразделом Анализ производительности запросов.

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

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

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

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

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

    CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
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 Azure, что приводит к откату всех открытых транзакций сервера.
      • Используйте SET XACT_ABORT ON для подключения или в любых хранимых процедурах, которые начинают транзакции и не очищают после ошибки. В случае ошибки во время выполнения этот параметр прерывает все открытые транзакции и возвращает управление клиенту. Дополнительные сведения см. в разделе SET XACT_ABORT (Transact-SQL).

    Примечание.

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

    Внимание

    После SET XACT_ABORT ON операторы T-SQL, следующие за оператором, который вызывает ошибку, не будут выполняться. Это может повлиять на предполагаемый поток существующего кода.

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

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

    Влияние этого сценария уменьшается при включении моментального снимка с уровнем изоляции read committed в базе данных, что является конфигурацией по умолчанию в Базе данных SQL Azure. Дополнительные сведения см. в разделе "Общие сведения о блокировке" этой статьи.

    Примечание.

    См. руководство по алгоритмам повторной отправки для приложений, подключающихся к Базе данных SQL Azure.

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

  4. Блокировка, вызванная сеансом в состоянии отката

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

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

    Разрешение: подождите, пока SPID завершит откат внесенных изменений.

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

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

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

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

    KILL 99

Связанный контент