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

Изучение и устранение проблем блокировок в Базе данных SQL Azure

  • Статья

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

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

Цель

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

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

Примечание.

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

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

Блокировка — это неизбежная особенность любой системы управления реляционными базами данных (СУБД) с параллелизмом на основе блокировок. Блокировка в базе данных 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 для записи выполняемых действий.

Сбор сведений из динамических административных представлений

Обращение к DMV для устранения неполадок, связанных с блокировкой, выполняется для определения 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 в результирующем наборе, который проще запрашивать и фильтровать, предоставляя 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. Этот запрос также можно использовать для обнаружения сеансов, блокирующих другие сеансы, и получения списков идентификаторов заблокированных сеансов по идентификаторам блокирующих сеансов.

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

Благодаря ВНУТРЕННЕМУ СОЕДИНЕНИЮ в 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
      В этом столбце отображается состояние конкретного запроса. Как правило, состояние "sleeping" (сон) означает, что SPID завершил выполнение и ожидает, пока приложение отправит другой запрос или пакет. Состояние "runnable" (готов к запуску) или "running" (выполняется) указывает, что 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 TAB: 5:261575970:1 В этом случае база данных с ИД 5 — это пример базы данных pubs, объект с ИД 261575970 — таблица titles, а 1 — кластеризованный индекс.
    Стр. DatabaseID:FileID:PageID PAGE: 5:1:104 В этом случае база данных с ИД 5 — это pubs, файл с ИД 1 — основной файл данных, а страница 104 — это страница, принадлежащая таблице titles. Чтобы определить object_id, которому принадлежит страница, используйте функцию динамического управления sys.dm_db_page_info, передав в нее DatabaseID, FileId и PageId из wait_resource.
    Ключ DatabaseID:Hobt_id (хэш-значение для ключа индекса) KEY: 5:72057594044284928 (3300a4f361aa) В этом случае база данных с ИД 5 — это Pubs, а Hobt_ID 72057594044284928 соответствует index_id 2 для object_id 261575970 (таблица titles). Используйте представление каталога sys.partitions, чтобы связать hobt_id с определенными index_id и object_id. Нет способа отменить хэш ключа индекса к определенному значению ключа.
    Строка DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 В этом случае база данных с ИД 5 — это pubs, файл с ИД 1 — основной файл данных, страница 104 — это страница, принадлежащая таблице titles, и слот 3 указывает позицию строки на странице.
    Компилировать DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 В этом случае база данных с ИД 5 — это pubs, файл с ИД 1 — основной файл данных, страница 104 — это страница, принадлежащая таблице titles, и слот 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).

Сценарий Тип ожидания Открытая транзакция Состояние Разрешается? Другие признаки
1 NOT NULL >= 0 готово к запуску Да, по завершении запроса. В sys.dm_exec_sessions, reads, cpu_time и (или) memory_usage значения столбцов будут увеличиваться со временем. Запрос может выполняться довольно долго.
2 NULL >0 sleeping Нет, но 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 sleeping Со временем. Когда 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

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