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

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

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

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

Цель

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

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

Примечание.

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

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

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

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

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

Уровень изоляции моментальных снимков также включен по умолчанию для новых баз данных в Azure SQL Database. Изоляция моментальных снимков — это дополнительный уровень изоляции на основе строк, обеспечивающий согласованность данных на уровне транзакций и использующий версии строк для выбора обновляемых строк. Чтобы использовать изоляцию моментальных снимков, запросы или подключения должны явно задать уровень изоляции транзакций, соответствующий 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 блокировки удерживаются на протяжении выполнения запроса, как для обеспечения согласованности данных, так и для разрешения отката запроса при необходимости.

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

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

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

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

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

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

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

В настоящее время эта статья относится к поведению движка базы данных без оптимизированной блокировки.

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

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

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

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

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

Примечание.

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

Устранение проблем с блокировкой

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

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

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

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

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

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

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

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

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

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

Сбор информации из отделов транспортных средств

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

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

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

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

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

  • См. столбец 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_info в sys.dm_exec_input_buffer содержит последнюю командную строку, переданную обработчику SQL. Этот запрос также можно использовать для определения сеансов, которые блокируют другие сеансы, включая список session_ids, заблокированных по каждому session_id.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;
  • Выполните этот более сложный пример запроса, предоставленный службой поддержки Майкрософт, чтобы определить верхнюю часть цепочки блокировки нескольких сеансов, включая текст запроса сеансов, участвующих в цепочке блокировки.
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

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

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

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

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

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

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

    • Внимание
    • Сообщенная_ошибка
    • Предупреждение о выполнении

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

    • Отсутствует_условие_объединения

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

    • Rpc_завершено
    • Rpc_starting
    • Sql_пакет_завершён
    • Sql_batch_starting

  • Категория deadlock_monitor

    • отчет_о_застое_в_базе_данных_xml

  • Сессия по категориям

    • Существующее подключение
    • Вход
    • Выход

Примечание.

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

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

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

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

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

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

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

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

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

    • sys.dm_exec_requests.wait_type, wait_time и last_wait_type
      Если значение sys.dm_exec_requests.wait_type равно NULL, запрос в настоящее время ничего не ожидает, а значение last_wait_type указывает на последний wait_type, с которым столкнулся запрос. Дополнительные сведения о sys.dm_os_wait_stats и описание наиболее распространенных типов ожидания см. в разделе sys.dm_os_wait_stats. Значение wait_time можно использовать для определения хода выполнения запроса. Когда запрос к sys.dm_exec_requests таблице возвращает значение в wait_time столбце, которое меньше значения wait_time из предыдущего запроса sys.dm_exec_requests, это указывает на то, что предыдущая блокировка была получена и освобождена, и теперь ожидается новая блокировка (при условии, что значение wait_time ненулевое). Это можно проверить, сравнив wait_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 is 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:Слот(ряд) RID: 5:1:104:3 В этом случае идентификатор базы данных 5 pubs, идентификатор файла 1 является основным файлом данных, страница 104 — это страница, принадлежащая таблице заголовков, и слот 3 указывает положение строки на странице.
    Компиляция DatabaseID:FileID:PageID:Слот(ряд) RID: 5:1:104:3 В этом случае идентификатор базы данных 5 pubs, идентификатор файла 1 является основным файлом данных, страница 104 — это страница, принадлежащая таблице заголовков, и слот 3 указывает положение строки на странице.
    • Представление динамического управления sys.dm_tran_active_transactionssys.dm_tran_active_transactions содержит данные об открытых транзакциях, которые можно объединить с другими представлениями динамического управления, чтобы получить полную картину транзакций, ожидающих фиксации или отката. Используйте следующий запрос для возврата сведений об открытых транзакциях, присоединенных к другим представлениям динамического управления, включая sys.dm_tran_session_transactions. Изучите текущее состояние транзакции, transaction_begin_time и другие ситуационные данные, чтобы оценить, может ли она быть источником блокировки.
    SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, 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), когда последний пакет, отправленный идентификатором сеанса, был last_request_start_time в sys.dm_exec_sessions, как долго выполнялся запрос с помощью start_time в sys.dm_exec_requests, и т. д.

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

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

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

Сценарий Тип ожидания Open_Tran Состояние Разрешается? Другие симптомы
1 НЕ ПУСТОЕ ЗНАЧЕНИЕ >= 0 готово к запуску Да, после завершения запроса. Со временем в столбцах sys.dm_exec_sessions, reads, cpu_time и/или memory_usage наблюдается увеличение. Продолжительность выполнения запроса увеличивается.
2 NULL >0 спящий режим Нет, но идентификатор сеанса можно убить. Сигнал внимания может быть зафиксирован в сеансе расширенного события для данного идентификатора сеанса, указывая на превышение времени выполнения запроса или его отмену.
3 NULL >= 0 готово к запуску № Не выполняется до тех пор, пока клиент не извлечёт все строки или не закроет подключение. Идентификатор сеанса можно убить, но может занять до 30 секунд. Если open_transaction_count = 0, и идентификатор сеанса содержит блокировки, и уровень изоляции транзакций установлен по умолчанию (READ COMMITTED), это, вероятно, причина.
4 Разные >= 0 готово к запуску № Не решается до тех пор, пока клиент не отменит запросы или не закроет соединения. Идентификаторы сеансов могут быть убиты, но могут занять до 30 секунд. Столбец hostnamesys.dm_exec_sessions для идентификатора сеанса в голове цепочки блокировок совпадает с одним из идентификаторов сеанса, который он блокирует.
5 NULL >0 откат Да. В сеансе расширенных событий для этого идентификатора сеанса может быть виден сигнал внимания, указывающий на истечение времени ожидания запроса, отмену или просто выдачу команды отката.
6 NULL >0 спящий режим В конечном итоге. Когда Windows определяет, что сеанс больше не активен, подключение к базе данных SQL Azure нарушается. Значение last_request_start_time в sys.dm_exec_sessions намного предшествует текущему времени.

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

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

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

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

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

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

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

    Этот тип блокировки часто можно определить по идентификатору сеанса, который находится в состоянии ожидания или сна, но при этом уровень вложенности транзакций (@@TRANCOUNT, open_transaction_count из sys.dm_exec_requests) больше нуля. Это может произойти, если в приложении истекло время ожидания запроса или если при его отмене не было выдано необходимое число инструкций ROLLBACK и/или COMMIT. Когда идентификатор сеанса получает время ожидания запроса или отмену, он завершает текущий запрос и пакет, но не автоматически откатывает или фиксирует транзакцию. За это отвечает приложение, так как База данных SQL Azure не может предположить, что из-за отмены одного запроса должен быть произведен откат всей транзакции. Время ожидания запроса (тайм-аут) или его отмена отображается как событие "ВНИМАНИЕ" для ID сеанса в Extended Event.

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

    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 и прерывания транзакции, этот код отката не выполняется в таких случаях.
      • Если пул соединений используется в приложении, открывающем подключение и выполняющем несколько запросов, прежде чем освободить подключение обратно к пулу, например веб-приложение, временно отключив пул подключений, может помочь устранить проблему, пока клиентское приложение не будет изменено для обработки ошибок соответствующим образом. Отключив пул подключений, освобождение подключения вызывает физическое отключение соединения с базой данных Azure SQL, в результате чего сервер отменяет все открытые транзакции.
      • Используйте SET XACT_ABORT ON для подключения или в любых хранимых процедурах, которые начинают транзакции и не корректируют состояние после ошибки. В случае ошибки во время выполнения этот параметр прерывает все открытые транзакции и возвращает управление клиенту. Дополнительные сведения см. в SET XACT_ABORT.

    Примечание.

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

    Внимание

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

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

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

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

    Примечание.

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

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

  4. Блокировка, вызванная сессией, находящейся в состоянии отката

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

    Благодаря ускоренному восстановлению базы данных, введенному в 2019 году, длительные откаты должны быть редкими.

    Решение: Дождитесь завершения отката изменений, внесенных сеансовым идентификатором.

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

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

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

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

    KILL 99

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

  • Last updated on