Изучение и устранение проблем блокировок в Базе данных 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, который использует управление версиями строк для повышения параллелизма. Однако блокировка и взаимоблокировка могут по-прежнему возникать в базах данных в Базе данных Azure SQL, так как:

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

Уровень изоляции моментальных снимков также включен по умолчанию для новых баз данных в База данных 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. Если в поле textsys.dm_exec_sql_text возвращается значение NULL, запрос в данный момент не выполняется. В этом случае поле event_infosys.dm_exec_input_buffer будет содержать последнюю командную строку, переданную в обработчик 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 может появиться сигнал "внимание", указывающий, что произошел тайм-аут или отмена запроса, или просто выдана инструкция rollback.
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

См. также

Дальнейшие действия