Руководство по взаимоблокировкам

Относится к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsСистема платформы аналитики (PDW)База данных SQL в Microsoft Fabric

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

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

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

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

• Транзакция А создает общую блокировку строки 1.

• Транзакция Б создает общую блокировку строки 2.

• Транзакция А теперь запрашивает монопольную блокировку строки 2 и блокируется до того, как транзакция Б закончится и освободит общую блокировку строки 2.

• Транзакция Б теперь запрашивает монопольную блокировку строки 1 и блокируется до того, как транзакция А закончится и освободит общую блокировку строки 1.

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

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

Взаимоблокировки часто путают с обычными блокировками. Если транзакция запрашивает блокировку ресурса, заблокированного другой транзакцией, то запрашивающая транзакция ожидает, пока блокировка не будет снята. По умолчанию транзакции в ядре СУБД не истекают, если не задан LOCK_TIMEOUT. Запрашивающая транзакция заблокирована, но не находится в состоянии взаимной блокировки, поскольку она не сделала ничего, что могло бы заблокировать транзакцию, владеющую блокировкой. В конце концов транзакция, удерживающая блокировку, завершается и освобождает её, после чего запрашивающая транзакция получает блокировку и продолжает выполнение. Взаимоблокировки устраняются почти сразу, тогда как блокировка теоретически может продолжаться бесконечно долго. Взаимоблокировки иногда называют «смертельными объятиями».

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

На рисунке транзакция T1 имеет зависимость от транзакции T2 для Part ресурса блокировки таблицы. Аналогичным образом транзакция T2 имеет зависимость от транзакции T1 для Supplier ресурса блокировки таблицы. Так как эти зависимости образуют цикл, между транзакциями T1 и T2 возникает взаимоблокировка.

Ниже приведена более общая иллюстрация взаимоблокировки:

• Задача T1 имеет блокировку ресурса R1 (указанная стрелкой от R1 к T1) и запросила блокировку ресурса R2 (указанная стрелкой от T1 до R2).

• Задача T2 имеет блокировку ресурса R2 (указанная стрелкой из R2 в T2) и запросила блокировку ресурса R1 (указана стрелка из T2 в R1).

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

Ресурсы, которые могут вызвать взаимную блокировку

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

• Locks. Ожидание установки блокировок на ресурсы, такие как объекты, страницы, строки, метаданные и приложения, может вызвать взаимоблокировку. Например, транзакция T1 имеет общую (S) блокировку на строке r1 и ожидает получения монопольной (X) блокировки на r2. Транзакция T2 имеет общуюS () блокировку r2 и ожидает получения монопольнойX () блокировки на строке r1. В результате получается цикл блокировки, в котором T1 и T2 ожидают, пока одна транзакция освободит заблокированный другой транзакцией ресурс.

• Рабочие потоки. Задача в очереди, которая ожидает освобождения рабочего потока, может привести к взаимоблокировке. Если задача в очереди удерживает ресурсы, блокирующие все рабочие потоки, возникает взаимоблокировка. Например, сеанс S1 запускает транзакцию и получает совместную блокировку S строки r1, после чего переходит в спящий режим. Активные сеансы, выполняющиеся на всех доступных рабочих потоках, пытаются получить монопольные (X) блокировки строки r1. Так как сеанс S1 не может получить рабочий поток, он не может зафиксировать транзакцию и освободить блокировку строки r1. Возникает взаимоблокировка.

• Memory. Если параллельные запросы ожидают выделения памяти, которое не может быть выполнено из-за недостатка доступной памяти, может возникнуть взаимоблокировка. Например, два параллельных запроса Q1 и Q2 выполняются как определяемые пользователем функции, использующие 10 МБ и 20 МБ памяти соответственно. Если каждому запросу требуется 30 МБ, а общая доступная память составляет 20 МБ, то Q1 и Q2 должны ждать, чтобы освободить память, что приводит к взаимоблокировке.

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

• Ресурсы Multiple Active Result Sets (MARS). Эти ресурсы используются для управления чередованием нескольких активных запросов в режиме MARS. Дополнительные сведения см. в разделе Использование нескольких активных наборов результатов (MARS) в собственном клиенте SQL Server.

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

  • Мьютекс сеанса. Задачи, выполняемые в одном сеансе, чередуются. Это означает, что только одна задача сеанса может выполняться в данный момент времени. Прежде чем задача сможет выполниться, она должна получить исключительный доступ к мьютексу сеанса.

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

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

    U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
    U2:    Rs2=Command2.Execute("select colA from sometable");
    

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

    

Взаимоблокировки также могут возникать, если таблица секционирована и для ALTER TABLE параметр LOCK_ESCALATION имеет значение AUTO. Если LOCK_ESCALATION задано значение AUTO, параллелизм увеличивается, позволяя движку СУБД блокировать разделы таблиц на уровне HoBT, а не на уровне таблицы. Однако если отдельные транзакции удерживают блокировки секций в таблице и пытаются заблокировать еще какой-либо объект в разделе, принадлежащем другой транзакции, это вызовет взаимоблокировку. Этот тип взаимоблокировки можно предотвратить, установив для LOCK_ESCALATION значение TABLE. Однако этот параметр снижает параллелизм, вынуждая крупные обновления раздела ожидать блокировки таблицы.

Обнаружение взаимоблокировок

Все ресурсы, перечисленные в разделе "Ресурсы", которые могут выполнять взаимоблокировку , участвуют в схеме обнаружения взаимоблокировок ядра СУБД. Обнаружение взаимоблокировки выполняется потоком монитора блокировки, который периодически инициирует поиск по всем задачам в экземпляре ядра СУБД. Следующие пункты описывают процесс поиска:

• Значение интервала по умолчанию составляет 5 секунд.

• Если поток мониторинга блокировок обнаруживает взаимоблокировки, интервал обнаружения взаимоблокировок снижается с 5 секунд вплоть до 100 миллисекунд в зависимости от частоты возникновения взаимоблокировок.

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

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

Ядро СУБД обычно выполняет только периодическое обнаружение взаимоблокировок. Так как количество взаимоблокировок, встречающихся в системе, обычно невелико, периодическое обнаружение взаимоблокировок помогает сократить затраты на обнаружение взаимоблокировок в системе.

Если монитор блокировок запускает поиск взаимоблокировок для определенного потока, он определяет ресурс, которого ожидает поток. Затем монитор блокировок определяет владельцев данного ресурса и рекурсивно продолжает поиск взаимоблокировки для этих потоков, пока не найдет цикл. Цикл, определенный таким способом, формирует взаимоблокировку.

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

По умолчанию ядро СУБД выбирает транзакцию, которая наименее затратна для отката, в качестве жертвы взаимоблокировки. В качестве альтернативы пользователь может указать приоритет сеансов в ситуации взаимоблокировки, используя инструкцию SET DEADLOCK_PRIORITY. DEADLOCK_PRIORITY может быть задано значение LOW, NORMAL, или HIGH, либо любое целое значение может быть задано в диапазоне от -10 до 10. В некоторых случаях ядро СУБД может изменить приоритет взаимоблокировки в течение короткого времени, чтобы повысить параллелизм.

Приоритет взаимоблокировки по умолчанию — NORMAL, или 0. Если два сеанса имеют разные приоритеты взаимоблокировки, транзакция на сеансе с более низким приоритетом выбирается в качестве жертвы взаимоблокировки. Если оба сеанса имеют одинаковый приоритет взаимоблокировки, то выбрана транзакция, которая является наименее дорогой для отката. Если сеансы, вовлеченные в цикл взаимоблокировки, имеют один и тот же приоритет в случае взаимоблокировки и одинаковую стоимость, то жертва взаимоблокировки выбирается случайным образом. Задача, которая откатывается, не может быть выбрана в качестве жертвы взаимоблокировки.

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

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

Для просмотра сведений о взаимоблокировках механизм базы данных предоставляет средства мониторинга в виде расширенного события, двух флагов трассировки и события графа взаимоблокировок в SQL Profiler.

Расширенное xml_deadlock_report событие — это рекомендуемый метод для записи сведений о взаимоблокировках.

Расширенное событие взаимоблокировки

В SQL Server 2012 (11.x) и более поздних версиях расширенное событие xml_deadlock_report следует использовать вместо класса событий графа взаимоблокировки в SQL Trace или SQL Profiler.

Сеанс событий system_health записывает xml_deadlock_report события по умолчанию. Эти события содержат граф взаимоблокировки. Поскольку сеанс system_health включен по умолчанию, вам не нужно настраивать отдельный сеанс событий для записи сведений о взаимоблокировке.

Зафиксированный граф взаимоблокировки обычно имеет три узла:

• victim-list. Идентификатор процесса жертвы взаимоблокировки.
• process-list. Сведения о всех процессах, задействованных во взаимоблокировке.
• resource-list. Сведения о ресурсах, участвующих во взаимоблокировке.

Вы можете просмотреть целевые event_file данные сеанса system_health в Management Studio. Если произошли какие-либо xml_deadlock_report события, Management Studio отображает графическое представление задач и ресурсов, участвующих в взаимоблокировке, как показано в следующем примере:

Следующий запрос может просматривать все события взаимоблокировки, захваченные целевым ring_buffer объектом сеанса system_health :

SELECT xdr.value('@timestamp', 'datetime') AS deadlock_time,
       xdr.query('.') AS event_data
FROM (SELECT CAST ([target_data] AS XML) AS target_data
      FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
               ON xs.address = xt.event_session_address
      WHERE xs.name = N'system_health'
            AND xt.target_name = N'ring_buffer') AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY deadlock_time DESC;

Вот набор результатов.

В следующем примере показан пример выходных данных из столбца event_data :

<event name="xml_deadlock_report" package="sqlserver" timestamp="2022-02-18T08:26:24.698Z">
  <data name="xml_report">
    <type name="xml" package="package0" />
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process27b9b0b9848" />
        </victim-list>
        <process-list>
          <process id="process27b9b0b9848" taskpriority="0" logused="0" waitresource="KEY: 5:72057594214350848 (1a39e6095155)" waittime="1631" ownerId="11088595" transactionname="SELECT" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27b9f79fac0" lockMode="S" schedulerid="9" kpid="15336" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2022-02-18T00:26:22.893" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="7908" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088595" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p1" line="3" stmtstart="78" stmtend="180" sqlhandle="0x0300050020766505ca3e07008ba8000001000000000000000000000000000000000000000000000000000000">
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x020000006263ec01ebb919c335024a072a2699958d3fcce60000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p1 4
END
   </inputbuf>
          </process>
          <process id="process27b9ee33c28" taskpriority="0" logused="252" waitresource="KEY: 5:72057594214416384 (e5b3d7e750dd)" waittime="1631" ownerId="11088593" transactionname="UPDATE" lasttranstarted="2022-02-18T00:26:23.073" XDES="0x27ba15a4490" lockMode="X" schedulerid="6" kpid="5584" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-02-18T00:26:22.890" lastbatchcompleted="2022-02-18T00:26:22.890" lastattention="1900-01-01T00:00:00.890" clientapp="SQLCMD" hostname="ContosoServer" hostpid="15316" loginname="CONTOSO\user" isolationlevel="read committed (2)" xactid="11088593" currentdb="5" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
            <executionStack>
              <frame procname="AdventureWorks2022.dbo.p2" line="3" stmtstart="76" stmtend="150" sqlhandle="0x03000500599a5906ce3e07008ba8000001000000000000000000000000000000000000000000000000000000">
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p    </frame>
              <frame procname="adhoc" line="4" stmtstart="82" stmtend="98" sqlhandle="0x02000000008fe521e5fb1099410048c5743ff7da04b2047b0000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
SET NOCOUNT ON
WHILE (1=1)
BEGIN
    EXEC p2 4
END
   </inputbuf>
          </process>
        </process-list>
        <resource-list>
          <keylock hobtid="72057594214350848" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="cidx" id="lock27b9dd26a00" mode="X" associatedObjectId="72057594214350848">
            <owner-list>
              <owner id="process27b9ee33c28" mode="X" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9b0b9848" mode="S" requestType="wait" />
            </waiter-list>
          </keylock>
          <keylock hobtid="72057594214416384" dbid="5" objectname="AdventureWorks2022.dbo.t1" indexname="idx1" id="lock27afa392600" mode="S" associatedObjectId="72057594214416384">
            <owner-list>
              <owner id="process27b9b0b9848" mode="S" />
            </owner-list>
            <waiter-list>
              <waiter id="process27b9ee33c28" mode="X" requestType="wait" />
            </waiter-list>
          </keylock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

Трассировочный флаг 1204 и трассировочный флаг 1222

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

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

Пример флага трассировки 1204

В следующем примере показаны выходные данные при включении флага трассировки 1204. В этом случае таблица в узле 1 — это куча без индексов, а таблица в узле 2 — это куча с некластеризованным индексом. В момент возникновения взаимоблокировки обновляется ключ индекса в узле 2.

Deadlock encountered .... Printing deadlock information
Wait-for graph

Node:1

RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2
 Grant List 0:
   Owner:0x0315D6A0 Mode: X
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event:
BEGIN TRANSACTION
   EXEC usp_p2
 Requested By:
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)

Node:2

KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0
 Grant List 0:
   Owner:0x0315D140 Mode: X
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6
   Input Buf: Language Event:
     BEGIN TRANSACTION
       EXEC usp_p1
 Requested By:
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)

Пример использования флага трассировки 1222

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

deadlock-list
 deadlock victim=process689978
  process-list
   process id=process6891f8 taskpriority=0 logused=868
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444
   transactionname=user_transaction
   lasttranstarted=2022-02-05T11:22:42.733 XDES=0x3a3dad0
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54
   sbid=0 ecid=0 priority=0 transcount=2
   lastbatchstarted=2022-02-05T11:22:42.733
   lastbatchcompleted=2022-02-05T11:22:42.733
   clientapp=Microsoft SQL Server Management Studio - Query
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
   isolationlevel=read committed (2) xactid=310444 currentdb=6
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2022.dbo.usp_p1 line=6 stmtstart=202
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;
     frame procname=adhoc line=3 stmtstart=44
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000
     EXEC usp_p1
    inputbuf
      BEGIN TRANSACTION
       EXEC usp_p1
   process id=process689978 taskpriority=0 logused=380
   waitresource=KEY: 6:72057594057457664 (350007a4d329)
   waittime=5015 ownerId=310462 transactionname=user_transaction
   lasttranstarted=2022-02-05T11:22:44.077 XDES=0x4d9e258 lockMode=U
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0
   priority=0 transcount=2 lastbatchstarted=2022-02-05T11:22:44.077
   lastbatchcompleted=2022-02-05T11:22:44.077
   clientapp=Microsoft SQL Server Management Studio - Query
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user
   isolationlevel=read committed (2) xactid=310462 currentdb=6
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=AdventureWorks2022.dbo.usp_p2 line=6 stmtstart=200
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;
     frame procname=adhoc line=3 stmtstart=44
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000
     EXEC usp_p2
    inputbuf
      BEGIN TRANSACTION
        EXEC usp_p2
  resource-list
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2022.dbo.T2
   id=lock3136940 mode=X associatedObjectId=72057594057392128
    owner-list
     owner id=process689978 mode=X
    waiter-list
     waiter id=process6891f8 mode=U requestType=wait
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2022.dbo.T1
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X
   associatedObjectId=72057594057457664
    owner-list
     owner id=process6891f8 mode=X
    waiter-list
     waiter id=process689978 mode=U requestType=wait

Событие графа взаимоблокировки Profiler

В профилировщике SQL есть событие, представляющее графическое изображение задач и ресурсов, участвующих в взаимоблокировке. Следующий пример иллюстрирует результаты, выводимые компонентом SQL Profiler, когда включено событие Deadlock Graph.

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

Дополнительные сведения о событии взаимоблокировки см. в разделе Класс событий Lock:Deadlock. Дополнительные сведения о графах взаимоблокировки SQL Profiler см. в разделе "Сохранение графов взаимоблокировки" (SQL Server Profiler).

Расширенные события предоставляют эквиваленты классов событий трассировки SQL. Дополнительные сведения см. в разделе "Просмотр эквивалентов расширенных событий для классов событий трассировки SQL". Рекомендуется использовать расширенные события вместо SQL Trace.

Управление взаимоблокировками

Когда экземпляр ядра СУБД выбирает транзакцию в качестве жертвы взаимоблокировки, он завершает текущий пакет, откатывает транзакцию и возвращает ошибку 1205 в приложение. Возвращаемое сообщение структурировано следующим образом:

Your transaction (process ID #...) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

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

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

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

Обработка с помощью TRY...CATCH

Вы можете использовать TRY...CATCH для обработки взаимоблокировок. Ошибку 1205 можно поймать с помощью блока CATCH.

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

Минимизация взаимоблокировок

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

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

Чтобы свести вероятность взаимоблокировок к минимуму:

• Осуществляйте доступ к объектам в одинаковом порядке.
• Избегайте взаимодействия с пользователем в транзакциях.
• Уменьшайте размер транзакций, желательно помещая их в один пакет.
• Избегайте более высоких уровней изоляции, таких как REPEATABLE READ и SERIALIZABLE когда это не требуется.
• Используйте уровень изоляции, основанный на управлении версиями строк.
  • Включите параметр базы данных READ_COMMITTED_SNAPSHOT, чтобы использовать управление версиями строк для транзакций с уровнем изоляции READ COMMITTED.
  • Используйте транзакции изоляции моментальных снимков.
• Используйте связанные соединения.

Доступ к объектам в том же порядке

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

Отказ от взаимодействия с пользователем в транзакциях

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

Уменьшение размера транзакций и хранение их в одном пакете

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

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

Избегайте более высоких уровней изоляции

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

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

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

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

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

Использование связанных соединений

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

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

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

Следующий пример работает в AdventureWorksLT2019 примере базы данных с схемой и данными по умолчанию при включении READ_COMMITTED_SNAPSHOT. Чтобы скачать этот образец, перейдите на страницу образцов баз данных AdventureWorks.

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

Чтобы создать взаимоблокировку, необходимо подключить два сеанса к базе данных AdventureWorksLT2019. Мы называем эти сеансы сеансами A и сеансом B. Эти два сеанса можно создать, создав два окна запросов в SQL Server Management Studio (SSMS).

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

BEGIN TRANSACTION;

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

Теперь в сеансе B выполните следующий пакет. Этот код не запускает транзакцию явным образом. Вместо этого он работает в режиме автофиксации транзакции. Эта инструкция обновляет таблицу SalesLT.ProductDescription. Обновление устанавливает блокировку обновления (U) для соответствующих строк в таблице 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.

Вернитесь к сеансу A. Выполните следующую 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';

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

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

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

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

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

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

WITH cteDeadLocks ([Deadlock_XML])
AS (SELECT CAST (target_data AS XML) AS [Deadlock_XML]
    FROM sys.dm_xe_sessions AS xs
         INNER JOIN sys.dm_xe_session_targets AS xst
             ON xs.[address] = xst.event_session_address
    WHERE xs.[name] = 'system_health'
          AND xst.target_name = 'ring_buffer')
SELECT x.Graph.query('(event/data/value/deadlock)[1]') AS Deadlock_XML,
       x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') AS when_occurred,
       DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) AS DB --Current database of the first listed process
FROM (SELECT Graph.query('.') AS Graph
      FROM cteDeadLocks AS c
      CROSS APPLY c.[Deadlock_XML].nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock_Report(Graph)) AS x
ORDER BY when_occurred DESC;

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

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

При оптимизированной блокировке блокировки страницы и строки не будут храниться до конца транзакции. Они освобождаются сразу после обновления строки. Кроме того, если READ_COMMITTED_SNAPSHOT включена, блокировка обновления (U) не используется. В результате вероятность взаимоблокировок снижается.

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

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

Сначала создайте пример таблицы и добавьте данные.

CREATE TABLE t2
(
    a INT PRIMARY KEY NOT NULL,
    b INT NULL
);

INSERT INTO t2
VALUES (1, 10),
(2, 20),
(3, 30);

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

В сессии 1:

BEGIN TRANSACTION xactA;

UPDATE t2
    SET b = b + 10
WHERE a = 1;

В сеансе 2:

BEGIN TRANSACTION xactB;

UPDATE t2
    SET b = b + 10
WHERE a = 2;

В ходе сеанса 1:

UPDATE t2
    SET b = b + 100
WHERE a = 2;

В сеансе 2:

UPDATE t2
    SET b = b + 20
WHERE a = 1;

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

Представленный сокращенный отчет по взаимоблокировке содержит элементы и атрибуты, относящиеся к оптимизированной блокировке. Под каждым ресурсом в отчете <resource-list> о взаимоблокировке, каждый <xactlock> элемент предоставляет отчеты о базовых ресурсах и информацию о блокировке по TID каждого элемента взаимоблокировки.

<deadlock>
 <victim-list>
  <victimProcess id="process12994344c58" />
 </victim-list>
 <process-list>
  <process id="process12994344c58" taskpriority="0" logused="272" waitresource="XACT: 23:2476:0 KEY: 23:72057594049593344 (8194443284a0)" waittime="447" ownerId="3234906" transactionname="xactA" lasttranstarted="2025-10-08T21:36:34.063" XDES="0x12984ba0480" lockMode="S" schedulerid="2" kpid="204928" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2025-10-08T21:36:40.857" lastbatchcompleted="2025-10-08T21:36:34.063" lastattention="2025-10-08T21:36:11.340" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WS1" hostpid="23380" loginname="user1" isolationlevel="read committed (2)" xactid="3234906" currentdb="23" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <inputbuf>
UPDATE t2
    SET b = b + 20
WHERE a = 1;
   </inputbuf>
  </process>
  <process id="process1299c969828" taskpriority="0" logused="272" waitresource="XACT: 23:2477:0 KEY: 23:72057594049593344 (61a06abd401c)" waittime="3083" ownerId="3234886" transactionname="xactB" lasttranstarted="2025-10-08T21:36:30.303" XDES="0x12995c84480" lockMode="S" schedulerid="2" kpid="63348" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2025-10-08T21:36:38.223" lastbatchcompleted="2025-10-08T21:36:30.303" lastattention="1900-01-01T00:00:00.303" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WS1" hostpid="23380" loginname="user1" isolationlevel="read committed (2)" xactid="3234886" currentdb="23" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <inputbuf>
UPDATE t2
    SET b = b + 100
WHERE a = 2;
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <xactlock xdesIdLow="2476" xdesIdHigh="0" dbid="23" id="lock1299fa06c00" mode="X">
   <UnderlyingResource>
    <keylock hobtid="72057594049593344" dbid="23" objectname="e6fc405e-1ee8-49df-a2b3-54ee0151d851.dbo.t2" indexname="PK__t2__3BD0198ED3CBA65E" />
   </UnderlyingResource>
   <owner-list>
    <owner id="process1299c969828" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process12994344c58" mode="S" requestType="wait" />
   </waiter-list>
  </xactlock>
  <xactlock xdesIdLow="2477" xdesIdHigh="0" dbid="23" id="lock129940b2380" mode="X">
   <UnderlyingResource>
    <keylock hobtid="72057594049593344" dbid="23" objectname="e6fc405e-1ee8-49df-a2b3-54ee0151d851.dbo.t2" indexname="PK__t2__3BD0198ED3CBA65E" />
   </UnderlyingResource>
   <owner-list>
    <owner id="process12994344c58" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process1299c969828" mode="S" requestType="wait" />
   </waiter-list>
  </xactlock>
 </resource-list>
</deadlock>

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

• Обзор расширенных событий
• sys.dm_tran_locks (Transact-SQL)
• Класс событий Deadlock Graph
• Взаимоблокировки при уровне изоляции «Повторяемое чтение»
• Класс событий Lock:Deadlock Chain
• Класс событий Lock:Deadlock
• SET DEADLOCK_PRIORITY (Transact-SQL)
• Анализ и предотвращение взаимоблокировок в Базе данных SQL Azure и базе данных SQL в Fabric
• Открыть, просмотреть и распечатать файл взаимоблокировки в SQL Server Management Studio (SSMS)