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

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

  • Статья

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

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

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

Как возникают взаимоблокировки

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

  • Запросы, изменяющие данные, могут блокировать друг друга.

  • Запросы могут выполняться на уровнях изоляции, повышающих блокировку. Уровни изоляции можно указать с помощью методов клиентской библиотеки, указания запросовили SET TRANSACTION ISOLATION LEVEL в Transact-SQL.

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

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

Взаимоблокировка возникает, когда две или более задачи постоянно блокируют друг друга из-за того, что каждая задача блокирует ресурс, который пытается заблокировать другая задача. Взаимоблокировка также называется циклической зависимостью: в случае взаимоблокировки с двумя задачами транзакция 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: Transaction (Process ID <N>) was deadlocked on lock resources with another process and is chosen as the deadlock victim. Rerun the transaction. Нарушение взаимоблокировки таким образом позволяет другим задачам или задачам в взаимоблокировке завершить свои транзакции.

Примечание.

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

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

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

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

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

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

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

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

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

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

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

Событие взаимоблокировки создается после того, как диспетчер взаимоблокировок в Базе данных SQL Azure обнаруживает взаимоблокировку и выбирает транзакцию в качестве жертвы. Другими словами, при настройке оповещений о взаимоблокировках уведомление запускается после разрешения отдельной взаимоблокировки. Пользователю не нужно предпринимать никаких действий для этой взаимной блокировки. Приложения должны быть записаны для включения логики повторных попыток, чтобы они автоматически продолжались после получения ошибки 1205: Transaction (Process ID <N>) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

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

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

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

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

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

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

Подход Льготы Рекомендации Сценарии использования
Целевой объект "Кольцевой буфер" — Простая настройка только с использованием Transact-SQL. — Данные событий очищаются при остановке сеанса XEvents по любой причине, например, при переводе базы данных в автономный режим или переключении на резервную базу данных.

— Ресурсы базы данных используются для поддержания данных в кольцевом буфере и запроса данных сеанса.		 — Сбор примеров данных трассировки для тестирования и обучения.

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

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

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

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

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

Целевой объект "Кольцевой буфер" удобен и прост в настройке, но имеет ограниченную емкость, что может привести к потере старых событий. Буфер кольца не сохраняет события в хранилище, а целевой объект буфера кольца очищается при остановке сеанса XEvents. Это означает, что любые собранные XEvents недоступны при перезапуске ядра СУБД по любой причине, например, при переключении на резервный сервер. Целевой объект кольцевого буфера лучше всего подходит для обучения и краткосрочных потребностей, если у вас нет возможности немедленно настроить сеанс XEvents в целевом объекте файла событий.

В этом примере кода создается сеанс XEvents, который фиксирует графы взаимоблокировки в памяти с помощью целевого объекта "Кольцевой буфер". Максимальный объем памяти, разрешенный для целевого объекта кольцевого буфера, составляет 4 МБ, и сеанс автоматически запускается, когда база данных становится доступной, например после отказа.

Чтобы создать и запустить сеанс XEvents для события sqlserver.database_xml_deadlock_report, которое записывает данные в целевой объект "Кольцевой буфер", подключитесь к базе данных и выполните следующую инструкцию Transact-SQL:

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

Вызвать взаимоблокировку в AdventureWorksLT

Примечание.

Этот пример работает в базе данных AdventureWorksLT со схемой и данными по умолчанию при включении RCSI. Инструкции по созданию базы данных см. в разделе Создание базы данных AdventureWorksLT.

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

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

BEGIN TRANSACTION;

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
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

Для выполнения этого обновления сеансу B требуется общая блокировка (S) для строк таблицы SalesLT.Product, включая строку, заблокированную сеансом A. Сеанс B заблокирован из-за SalesLT.Product.

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

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

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

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

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

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 91) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

сеанс B успешно завершён.

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

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

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

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

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

DECLARE @tracename AS sysname = N'deadlocks';

WITH ring_buffer
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER 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

Просмотр и сохранение графа взаимоблокировки в формате 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. Выберите Сохранить.

    снимок экрана в SSMS, показывающий сохранение XML-файла графа взаимоблокировки в файл с расширением xsd.

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

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

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

    снимок экрана: XDL-файл, открытый в SSMS. Граф взаимоблокировки отображается графически, с процессами, обозначаемыми овалами и ресурсами блокировки в виде прямоугольников.

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

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

  • Список жертв. Идентификатор процесса жертвы взаимоблокировки.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Определение сведений для сеанса, таких как имя клиента, имя узла и имя входа.

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

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

  • Мы видим, что оба сеанса выполнялись с помощью клиента SSMS под именем входа chrisqpublic.

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

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

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

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

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

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

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

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

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

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

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

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

Примечание.

Имена баз данных представлены в виде GUID (uniqueidentifier) в графах взаимоблокировки для баз данных в Azure SQL Database. Это physical_database_name для базы данных, указанной в динамических административных представлениях sys.databases и sys.dm_user_db_resource_governance.

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

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

    • Владеет монопольной блокировкой (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 AS 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
     INNER JOIN sys.query_store_query_text AS qt
         ON qs.query_text_id = qt.query_text_id
     INNER JOIN sys.query_store_plan AS qp
         ON qs.query_id = qp.query_id
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_runtime_stats_interval AS 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, выполнив следующую инструкцию:

    EXECUTE sp_helpindex 'SalesLT.Product';
GO

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

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

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

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

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

    снимок экрана плана выполнения запроса. Сканирование кластеризованного индекса выполняется по индексу PK_Product_ProductID в таблице 'Product'.

    Это сканирование индекса выполняется, так как запрос на обновление должен изменить индексированное представление с именем 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 — это автономное приложение, которое упрощает работу с целевыми объектами event file, хранящимися в объектах BLOB в службе хранилища Azure. Обозреватель службы хранилища можно использовать для выполнения следующих действий:

скачать Azure Storage Explorer.

Связанное содержимое