Анализ и предотвращение взаимоблокировок в Базе данных SQL Azure

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

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

Основное внимание в этой статье уделяется выявлению и анализу взаимоблокировок из-за состязания за блокировку. Сведения о других типах взаимоблокировок см. в разделе Ресурсы, у которых может возникать взаимоблокировка.

Как в Базе данных SQL Azure возникает взаимоблокировка

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

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

Пример взаимоблокировки

Взаимоблокировка возникает, когда две или более задачи постоянно блокируют друг друга из-за того, что каждая задача блокирует ресурс, который пытается заблокировать другая задача. Взаимоблокировка также называется циклической зависимостью: в случае взаимоблокировки с двумя задачами транзакция A имеет зависимость от транзакции B, а транзакция B замыкает цикл, поскольку у нее есть зависимость от транзакции A.

Например:

1. Сеанс А начинает явную транзакцию и запускает инструкцию обновления, которая получает блокировку обновления (U) для одной строки таблицыSalesLT.Product, которая преобразуется в монопольную блокировку (X).
2. Сеанс B запускает инструкцию обновления, которая изменяет таблицу SalesLT.ProductDescription. Инструкция update присоединяется к таблице SalesLT.Product, чтобы найти нужные строки для обновления.
   • Сеанс B получает блокировку обновления (U) для 72 строк в таблице SalesLT.ProductDescription.
   • Сеансу B требуется общая блокировка для строк в таблице SalesLT.Product, включая строку, заблокированную Сеансом A. Сеанс B блокируется в SalesLT.Product.
3. Сеанс A продолжает свою транзакцию и теперь выполняет обновление для таблицы SalesLT.ProductDescription. Сеанс A блокируется Сеансом B в SalesLT.ProductDescription.

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

Монитор взаимоблокировок ядра СУБД периодически проверяет задачи на состояние взаимоблокировки. Если монитор взаимоблокировок обнаруживает циклическую зависимость, он выбирает одну из задач в качестве жертвы и завершает ее транзакцию с ошибкой 1205, "Транзакция (идентификатор процесса N) находилась в состоянии взаимоблокировки для ресурсов блокировки с другим процессом и была выбрана в качестве жертвы взаимоблокировки. Запустите транзакцию повторно". Нарушение взаимоблокировки таким образом позволяет другим задачам или задачам, находящимся в состоянии взаимоблокировки, завершить свои транзакции.

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

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

Уровень изоляции по умолчанию в Базе данных SQL Azure

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

Если параметр RCSI включен:

• Инструкции, считывающие данные, не блокируют инструкции, изменяющие данные.
• Инструкции, изменяющие данные, не блокируют инструкции, считывающие данные.

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

Если параметр RCSI был отключен для базы данных в Базе данных SQL Azure, изучите причину отключения этого параметра перед тем, как включать его повторно. Возможно, код приложения был написан с расчетом на то, что запросы на чтение данных будут блокироваться запросами на запись данных. В случае включения параметра RCSI это приведет к неправильным результатам из-за появления состояния гонки.

Интерпретация событий взаимоблокировки

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

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

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

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

• Риск для этого подхода невелик, так как настройка некластеризованных индексов не требует изменений в самом коде запроса. Это снижает риск ошибок пользователя при перезаписи инструкций Transact-SQL, которые приводят к возврату неправильных данных.
• Эффективная настройка некластеризованных индексов помогает запросам находить данные для чтения и изменения более эффективно. При уменьшении объема данных, к которым требуется доступ, снижается вероятность блокировки и зачастую можно предотвратить взаимоблокировки.

В некоторых случаях создание или настройка кластеризованного индекса может снизить вероятность блокировки и взаимоблокировки. Так как кластеризованный индекс включен во все определения некластеризованных индексов, операция создания или изменения кластеризованного индекса может использовать большой объем ресурсов ввода/вывода и занимать длительное время в более крупных таблицах с существующими некластеризованными индексами. Дополнительные сведения см. в разделе Рекомендации по проектированию некластеризованных индексов.

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

• Если взаимоблокировка возникает только при выборе определенного плана для одного из запросов, включенных во взаимоблокировку, принудительное применение плана запросов в хранилище запросов может предотвратить повторное возникновение взаимоблокировок.
• Изменение инструкций Transact-SQL для одной или нескольких транзакций, включенных во взаимоблокировку, также может помочь предотвратить взаимоблокировки. Разделение явных транзакций на более мелкие транзакции требует внимательного написания кода и тестирования, чтобы гарантировать допустимость данных при одновременном изменении.

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

Мониторинг взаимоблокировок и настройка оповещений

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

В этой статье мы будем использовать клиент SQL Server Management Studio (SSMS), так как он обладает функциональными возможностями для отображения графов взаимоблокировки в интерактивном визуальном режиме. Для выполнения этих примеров можно использовать и другие клиенты, такие как Azure Data Studio, однако в них может быть доступен только XML-код для графов взаимоблокировки.

Создание базы данных AdventureWorksLT

Чтобы продолжить работу с примерами, создайте новую базу данных в Базе данных SQL Azure и выберите Пример данных в качестве Источника данных.

Подробные инструкции по созданию AdventureWorksLT с помощью портала Azure, Azure CLI и PowerShell см. в разделе Краткое руководство. Создание отдельной базы данных в Базе данных SQL Azure.

Настройка оповещений взаимоблокировки на портале Azure

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

Выберите Взаимоблокировки в качестве имени сигнала для оповещения. Настройте Группу действий для получения уведомлений с использованием выбранного метода, например, типа действия Электронная почта/Текстовое сообщение/Push-уведомление/Голосовая связь.

Сбор графов взаимоблокировки с расширенными событиями в Базе данных SQL Azure

Графы взаимоблокировки — это полезный источник информации о процессах и блокировках, участвующих во взаимоблокировке. Чтобы собрать графы взаимоблокировки с расширенными событиями (XEvents) в Базе данных SQL Azure, необходимо перехватить событие sqlserver.database_xml_deadlock_report.

Графы взаимоблокировки с расширенными событиями можно собирать с помощью целевого объекта "Кольцевой буфер" или с помощью целевого объекта "Файл событий". Рекомендации по выбору подходящего типа целевого объекта приведены в следующей таблице:

Выберите тип целевого объекта, который вы хотите использовать:

CREATE EVENT SESSION [deadlocks] ON DATABASE 
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer 
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE
    STATE = START;
GO

IF 0 = (SELECT COUNT(*)
    FROM sys.symmetric_keys
    WHERE symmetric_key_id = 101 and name=N'##MS_DatabaseMasterKey##')
BEGIN
    PRINT N'Creating master key';
    CREATE MASTER KEY;
END
ELSE 
BEGIN
    PRINT N'Master key already exists, no action taken';
END
GO

CREATE DATABASE SCOPED CREDENTIAL
    [https://yourstorageaccountname.blob.core.windows.net/yourcontainername]
    WITH
        IDENTITY = 'SHARED ACCESS SIGNATURE', 
        SECRET = 'sp=r&st=2022-04-08T14:34:21Z&se=2032-04-08T22:34:21Z&sv=2020-08-04&sr=c&sig=pUNbbsmDiMzXr1vuNGZh84zyOMBFaBjgWv53IhOzYWQ%3D'
    ;
GO

CREATE EVENT SESSION [deadlocks_eventfile] ON DATABASE 
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.event_file
    (SET filename =
    'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/deadlocks.xel'
    )
WITH (STARTUP_STATE=ON, MAX_MEMORY=4 MB)
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = START;
GO

Причина взаимоблокировки в AdventureWorksLT

Чтобы вызвать взаимоблокировку, необходимо подключить два сеанса к базе данных AdventureWorksLT. Мы будем называть эти сеансы Сеансом A и Сеансом B.

В Сеансе A выполните следующую инструкцию Transact-SQL. Этот код начинает явную транзакцию и запускает одну инструкцию, которая обновляет таблицу SalesLT.Product. Для этого транзакция получает блокировку обновления (U) для одной строки в таблице SalesLT.Product, которая преобразуется в монопольную блокировку (X). Мы оставим транзакцию открытой.

BEGIN TRAN

    UPDATE SalesLT.Product SET SellEndDate = SellEndDate + 1
        WHERE Color = 'Red';

Теперь в Сеансе B выполните следующую инструкцию Transact-SQL. Этот код не запускает транзакцию явным образом. Вместо этого он работает в режиме автофиксации транзакции. Эта инструкция обновляет таблицу SalesLT.ProductDescription. Это обновление приведет к блокировке обновления (U) в 72 строках таблицы SalesLT.ProductDescription. Запрос присоединяется к другим таблицам, включая таблицу SalesLT.Product.

UPDATE SalesLT.ProductDescription SET Description = Description
    FROM SalesLT.ProductDescription as pd
    JOIN SalesLT.ProductModelProductDescription as pmpd on
        pd.ProductDescriptionID = pmpd.ProductDescriptionID
    JOIN SalesLT.ProductModel as pm on
        pmpd.ProductModelID = pm.ProductModelID
    JOIN SalesLT.Product as p on
        pm.ProductModelID=p.ProductModelID
    WHERE p.Color = 'Silver';

Чтобы завершить это обновление, Сеансу B требуется общая блокировка для строк в таблице SalesLT.Product, включая строку, заблокированную сеансом A. Сеанс B блокируется в SalesLT.Product.

Вернитесь к Сеансу А. Выполните следующую инструкцию Transact-SQL. Этот код выполняет вторую инструкцию UPDATE в рамках открытой транзакции.

    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red';

Вторая инструкция обновления в Сеансе A будет заблокирована Сеансом B в SalesLT.ProductDescription.

Теперь Сеанс A и Сеанс B взаимно блокируют друг друга. Ни одна из транзакций не может быть продолжена, так как каждой из них требуется ресурс, заблокированный другой транзакцией.

Через несколько секунд монитор взаимоблокировок определит, что транзакции в Сеансе A и Сеансе B взаимно блокируют друг друга и ни одна из них не может продвинуться дальше. Вы должны увидеть взаимоблокировку с Сеансом A, выбранным в качестве жертвы взаимоблокировки. В Сеансе A появится сообщение об ошибке со следующим текстом:

Сообщение 1205, уровень 13, состояние 51, строка 7 Транзакция (идентификатор процесса 91) находилась в состоянии взаимоблокировки для ресурсов блокировки с другим процессом и была выбрана в качестве жертвы взаимоблокировки. Запустите транзакцию повторно.

Сеанс B завершится успешно.

Если вы настроили оповещения взаимоблокировки на портале Azure, то получите уведомление вскоре после возникновения взаимоблокировки.

Просмотр графов взаимоблокировки из сеанса XEvents

Если вы настроили сеанс XEvents для сбора взаимоблокировок и после запуска сеанса произошла взаимоблокировка, то вы сможете просмотреть интерактивное графическое изображение графа взаимоблокировки, а также XML-код графа взаимоблокировки.

Доступны различные методы для получения сведений о взаимоблокировках для целевых объектов "Кольцевой буфер" и "Файл событий". Выберите целевой объект, используемый для сеанса XEvents:

DECLARE @tracename sysname = N'deadlocks';

WITH ring_buffer AS (
    SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    JOIN sys.dm_xe_database_session_targets AS t 
        ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
    WHERE s.name = @tracename and
    t.target_name = N'ring_buffer'
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM ring_buffer
    CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = STOP;
GO
ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
    STATE = START;
GO

DECLARE @tracename sysname = N'deadlocks_eventfile',
    @filename nvarchar(2000);

WITH eft as (SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    JOIN sys.dm_xe_database_session_targets AS t 
        ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
    WHERE s.name = @tracename and
    t.target_name = N'event_file'
)
SELECT @filename = ft.evtdata.value('(@name)[1]','nvarchar(2000)') 
FROM eft
CROSS APPLY rb.nodes('EventFileTarget/File') as ft(evtdata);

WITH xevents AS (
    SELECT cast(event_data as XML) as ed
    FROM sys.fn_xe_file_target_read_file(@filename, null, null, null )
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM xevents
    CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

declare @filename nvarchar(2000) = N'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/yourfilename.xel';

with xevents AS (
    SELECT cast(event_data as XML) as ed
    FROM sys.fn_xe_file_target_read_file(@filename, null, null, null )
), dx AS (
    SELECT 
        dxdr.evtdata.query('.') as deadlock_xml_deadlock_report
    FROM xevents
    CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata)
) 
SELECT 
    d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
    d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
    d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
    LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'),CHAR(10),' '),CHAR(13),' '))) as query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Просмотр и сохранение графа взаимоблокировки в формате XML

Просмотр графа взаимоблокировки в формате XML позволяет скопировать инструкции inputbuffer Transact-SQL, участвующие во взаимоблокировке. Можно также проанализировать взаимоблокировки в текстовом формате.

Если вы использовали запрос Transact-SQL для возврата сведений о графе взаимоблокировки, то чтобы просмотреть XML-код графа взаимоблокировки, выберите значение в столбце deadlock_xml любой строки, чтобы открыть XML-код графа взаимоблокировки в новом окне в SSMS.

XML-код для этого примера графа взаимоблокировки будет следующим:

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Silver';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

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

1. Выберите Файл и Сохранить как….
2. В поле Тип файла оставьте значение по умолчанию Файлы XML (*.xml)
3. Задайте желаемое Имя файла.
4. Выберите Сохранить.

Сохранение графа взаимоблокировки в виде XDL-файла, который можно отобразить в интерактивном режиме в SSMS

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

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

1. Выберите значение в столбце deadlock_xml из любой строки, чтобы открыть XML-код графа взаимоблокировки в новом окне в SSMS.

2. Выберите Файл и Сохранить как….

3. В поле Тип файла выберите Все файлы.

4. Задайте желаемое Имя файла с расширением .xdl.

5. Выберите Сохранить.

   

6. Закройте файл, нажав на значок X на вкладке в верхней части окна или последовательно выбрав Файл и Закрыть.

7. Снова откройте файл в SSMS, выбрав Файл, а затем Открыть и Файл. Выберите сохраненный файл с расширением .xdl.

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

   

Анализ взаимоблокировки для Базы данных SQL Azure

Граф взаимоблокировки обычно имеет три узла:

• Список жертв. Идентификатор процесса жертвы взаимоблокировки.
• Список процессов. Сведения обо всех процессах, участвующих во взаимоблокировке. Графы взаимоблокировок используют термин "процесс" для сеанса, в котором выполняется транзакция.
• Список ресурсов. Сведения о ресурсах, участвующие во взаимоблокировке.

При анализе взаимоблокировки полезно последовательно проанализировать эти узлы.

Список жертв взаимоблокировки

В списке жертв взаимоблокировки отображается процесс, выбранный в качестве жертвы взаимоблокировки. В визуальном представлении графа взаимоблокировки процессы показаны в виде овалов. Процесс жертвы взаимоблокировки показан в виде овала с "X" внутри.

В XML-представлении графа взаимоблокировки узел victim-list имеет идентификатор процесса, который был жертвой взаимоблокировки.

В нашем примере взаимоблокировки идентификатор процесса жертвы — process24756e75088. Этот идентификатор можно использовать при проверке узлов списка процессов и списка ресурсов, чтобы получить дополнительную информацию о процессе жертвы и ресурсах, которые он заблокировал или блокировку которых запросил.

Список процессов взаимоблокировки

Список процессов взаимоблокировки представляет собой полезный источник сведений о транзакциях, участвующих во взаимоблокировке.

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

• Идентификатор процесса сервера, также известный как идентификатор сеанса или SPID.

• Приоритет взаимоблокировки сеанса. Если у двух сеансов имеются различные приоритеты в случае взаимоблокировки, то в качестве жертвы взаимоблокировки будет выбран сеанс с более низким приоритетом. В этом примере оба сеанса имеют одинаковый приоритет взаимоблокировки.

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

  В нашем примере взаимоблокировка с идентификатором сеанса 89 использовала более низкий объем журнала транзакций и была выбрана в качестве жертвы взаимоблокировки.

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

Для процессов в XML-представлении графа взаимоблокировки доступны дополнительные сведения, включая следующие:

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

В нашем примере взаимоблокировки:

• Мы видим, что оба сеанса выполнялись с помощью клиента SSMS и с именем входа chrisqpublic.
• Хэш плана запросов последней инструкции, которая была выполнена жертвой взаимоблокировки до возникновения взаимоблокировки, равен 0x02b0f58d7730f798. Текст этой инструкции можно просмотреть во входном буфере.
• Хэш плана запросов последней инструкции, которая была выполнена другим сеансом, участвующим во взаимоблокировке, также равен 0x02b0f58d7730f798. Текст этой инструкции можно просмотреть во входном буфере. В данном случае оба запроса имеют одинаковый хэш плана запросов, так как запросы идентичны, за исключением значения литерала, используемого в качестве предиката равенства.

Эти значения будут использоваться далее в этой статье для поиска дополнительных сведений в хранилище запросов.

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

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

Текст запроса во входном буфере может быть усечен. Входной буфер ограничен первыми 4000 символами выполняемой инструкции.

Кроме того, некоторые инструкции, участвующие в взаимоблокировке, могут не включаться в граф взаимоблокировки. В нашем примере Сеанс A выполнил две инструкции обновления в рамках одной транзакции. В граф взаимоблокировки включается только вторая инструкция обновления, которая вызвала взаимоблокировку. Первая инструкция обновления, выполняемая Сеансом A, сыграла роль во взаимоблокировке путем блокировки Сеанса B. Входной буфер query_hash и сопутствующая информация для первой инструкции, выполняемой Сеансом A, не включаются в граф взаимоблокировки.

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

Список ресурсов взаимоблокировки

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

В визуальном представлении взаимоблокировки ресурсы отображаются в виде прямоугольников:

В данном примере взаимоблокировки:

• Жертва взаимоблокировки, которую мы назвали Сеансом А:

  • Владеет монопольной блокировкой (X) на ключ в индексе PK_Product_ProductID таблицы SalesLT.Product.
  • Запрашивает блокировку обновления (U) на ключ в индексе PK_ProductDescription_ProductDescriptionID таблицы SalesLT.ProductDescription.

• Другой процесс, который мы назвали Сеансом B:

  • Владеет блокировкой обновления (U) на ключ в индексе PK_ProductDescription_ProductDescriptionID таблицы SalesLT.ProductDescription.
  • Запрашивает общую блокировку (S) на ключ в индексе PK_ProductDescription_ProductDescriptionID таблицы SalesLT.ProductDescription.

Те же сведения можно просмотреть в XML-коде графа взаимоблокировки в узле Список ресурсов.

Поиск планов выполнения запросов в хранилище запросов

Часто полезно изучить планы выполнения запросов для инструкций, участвующих во взаимоблокировке. Эти планы выполнения часто можно найти в хранилище запросов, используя хэш плана запросов из XML-представления списка процессов в графе взаимоблокировки.

Этот запрос Transact-SQL ищет планы запросов, соответствующие хэшу плана запросов, который мы нашли в нашем примере взаимоблокировки. Подключитесь к пользовательской базе данных в Базе данных SQL Azure, чтобы выполнить запрос.

DECLARE @query_plan_hash binary(8) = 0x02b0f58d7730f798

SELECT 
    qrsi.end_time as interval_end_time,
    qs.query_id,
    qp.plan_id,
    qt.query_sql_text, 
    TRY_CAST(qp.query_plan as XML) as query_plan,
    qrs.count_executions
FROM sys.query_store_query as qs
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qrsi on qrs.runtime_stats_interval_id=qrsi.runtime_stats_interval_id
WHERE query_plan_hash =  @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

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

Поиск шаблонов, повышающих вероятность блокировки

При изучении планов выполнения запросов, участвующих во взаимоблокировках, изучите шаблоны, которые могут способствовать блокировке и взаимоблокировкам.

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

  Настройка индексов для более эффективного поиска строк в запросах на изменение позволяет уменьшить количество используемых блокировок обновления. Это снижает вероятность блокировки и взаимоблокировок.

• Индексированные представления, ссылающиеся на несколько таблиц. При изменении таблицы, на которую ссылается индексированное представление, ядро СУБД также должно сохранить индексированное представление. Для этого требуется применить дополнительные блокировки, что может привести к увеличению вероятности блокировок и взаимоблокировок. Индексированные представления также могут привести к внутреннему выполнению операций обновления на уровне изоляции для фиксации при чтении.

• Изменение столбцов, на которые ссылаются ограничения внешнего ключа. При изменении столбцов в таблице, на которую ссылается ограничение FOREIGN KEY, ядро СУБД должно искать связанные строки в ссылающейся таблице. Для этих операций чтения не могут использоваться версии строк. В случаях, когда включены каскадные обновления или удаления, уровень изоляции может быть эскалирован на сериализуемый на время выполнения инструкции для защиты от фантомных вставок.

• Указания, для которых требуются блокировки. Найдите табличные указания, указывающие на уровни изоляции, для которых требуются дополнительные блокировки. К этим указаниям относятся HOLDLOCK (которое эквивалентно понятию "сериализуемый"), SERIALIZABLE, READCOMMITTEDLOCK (которое отключает параметр RCSI) и REPEATABLEREAD. Кроме того, указания, такие как PAGLOCK, TABLOCK, UPDLOCK и XLOCK, могут увеличить риски блокировки и взаимоблокировок.

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

  Примечание.

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

При проверке полного кода транзакции в плане выполнения или в коде запроса приложения проверьте наличие дополнительных шаблонов, которые могут привести к проблемам:

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

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

• Транзакции, которые обращаются к объектам в разных заказах. Взаимоблокировки менее вероятны, когда параллельные явные транзакции, состоящие из нескольких инструкций, построены на основе одних и тех же шаблонов и обращаются к объектам в одинаковом порядке.

Предотвращение повторного возникновения взаимоблокировки

Существует несколько методов, которые позволяют предотвратить повторное возникновение взаимоблокировок, включая настройку индекса, принудительное применение планов в хранилище запросов и изменение запросов Transact-SQL.

• Просмотр кластеризованного индекса таблицы. Для большинства таблиц кластеризованные индексы оказываются полезными, но часто таблицы реализуются в виде кучи по ошибке.

  Это можно сделать с помощью системной хранимой процедуры sp_helpindex. Например, можно просмотреть сводку индексов таблицы SalesLT.Product, выполнив следующую инструкцию:

  exec sp_helpindex 'SalesLT.Product';
  GO
  

  Просмотрите столбец index_description. Таблица может содержать только один кластеризованный индекс. Если в таблице реализован кластеризованный индекс, столбец index_description будет содержать слово "clustered".

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

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

• Создайте или измените некластеризованные индексы. Настройка некластеризованных индексов для запросов на изменение может помочь ускорить поиск данных для более быстрого обновления, что сокращает количество необходимых блокировок обновления.

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

  

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

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

  CREATE INDEX ix_Product_ProductID_Name_ProductModelID on SalesLT.Product (ProductID, Name, ProductModelID);
  GO
  

  После создания этого индекса взаимоблокировка больше не будет повторяться.

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

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

• Оценка значения индексированных представлений. Еще одним вариантом предотвращения повторных взаимоблокировок в нашем примере является удаление индексированного представления SalesLT.vProductAndDescription. Если это индексированное представление не используется, это позволит сократить затраты на обслуживание индексированного представления с течением времени.

• Использование изоляции моментальных снимков. В некоторых случаях установка уровня изоляции транзакции "Моментальный снимок" для одной или нескольких транзакций, участвующих во взаимоблокировке, может предотвратить повторную блокировку и взаимоблокировку.

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

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

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

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

  • Измените порядок инструкций в транзакциях таким образом, чтобы они обращались к объектам в одинаковом порядке.
  • Разделите транзакции на более мелкие, если это возможно.
  • При необходимости используйте указания запросов для оптимизации производительности. Подсказки можно применять без изменения кода приложения с помощью хранилища запросов.

Узнайте больше способов свести к минимуму взаимоблокировки в руководстве по взаимоблокировкам.

Удаление сеанса XEvents

Вы можете оставить сеанс XEvents, который собирает сведения о взаимоблокировке в критически важных базах данных, работать в течение длительного времени. Имейте в виду, что если вы используете целевой объект "Файл событий", это может привести к появлению больших файлов при возникновении нескольких взаимоблокировок. Вы можете удалить файлы BLOB-объектов из службы хранилища Azure для активной трассировки, за исключением файла, в который выполняется запись.

Если вы хотите удалить сеанс XEvents, инструкции Transact-SQL для удаления сеанса будут одинаковыми независимо от типа целевого объекта.

Чтобы удалить сеанс XEvents, выполните следующую инструкцию Transact-SQL. Перед выполнением кода замените имя сеанса соответствующим значением.

ALTER EVENT SESSION [deadlocks] ON DATABASE
    STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Использование обозревателя службы хранилища Azure

Обозреватель службы хранилища Azure — это автономное приложение, которое упрощает работу с целевыми объектами "Файл событий", хранящимися в BLOB-объектах в службе хранилища Azure. Обозреватель службы хранилища можно использовать для выполнения следующих действий:

• Создание контейнера BLOB-объектов для размещения данных сеанса XEvent.
• Получение подписанного URL-адреса (SAS) для контейнера BLOB-объектов.
  • Как упоминалось в разделе Сбор графов взаимоблокировки с расширенными событиями в Базе данных SQL Azure, требуются разрешения на чтение, запись и получение списка.
  • Удалите все начальные символы ? из Query string, чтобы использовать его в качестве секрета при создании учетных данных для определенной базы данных.
• Просмотр и скачивание файлов расширенных событий из контейнера BLOB-объектов.

Скачивание Обозревателя службы хранилища Azure..

Следующие шаги

Дополнительные сведения о производительности Базы данных SQL Azure:

• Изучение и устранение проблем с блокировкой Базы данных SQL Azure
• Руководство по блокировке и управлению версиями строк транзакций
• Руководство по взаимоблокировкам
• ЗАДАНИЕ УРОВНЯ ИЗОЛЯЦИИ ТРАНЗАКЦИЙ
• База данных SQL Azure: улучшение настройки производительности с помощью автоматической настройки
• Обеспечьте стабильную производительность с помощью Azure SQL
• Логика повторных попыток для временных ошибок.