Класс событий Lock:Deadlock
События класса Lock:Deadlock создаются при отмене попытки получить блокировку, приводящую к взаимоблокировке и выбранную сервером в качестве жертвы.
Класс событий Lock:Deadlock предназначен для отслеживания возникновения взаимоблокировок и объектов, которые в них участвуют. Эти сведения предназначены для определения степени влияния взаимоблокировок на производительность приложения. Они позволяют просмотреть код приложения и изменить его так, чтобы минимизировать взаимоблокировки.
Столбцы данных класса событий Lock:Deadlock
| Имя столбца данных | Тип данных | Описание: | Идентификатор столбца | Доступно для фильтрации |
|---|---|---|---|---|
| ApplicationName | nvarchar | Имя клиентского приложения, создавшего подключение к экземпляру SQL Server. Этот столбец заполняется значениями, передаваемыми приложением, а не отображаемым именем программы. | 10 | Да |
| BinaryData | Изображение | Идентификатор ресурса блокировки. | 2 | Да |
| ClientProcessID | int | Идентификатор, присвоенный главным компьютером сервера процессу, в котором работает клиентское приложение. Этот столбец данных заполняется в том случае, если клиент вводит идентификатор клиентского процесса. | 9 | Да |
| DatabaseID | int | Идентификатор базы данных, в которой запрашивается блокировка. Sql Server Profiler отображает имя базы данных, если столбец данных ServerName фиксируется в трассировке и сервер доступен. Определите значение для базы данных, используя функцию DB_ID. | 3 | Да |
| имя_базы_данных | nvarchar | Имя базы данных, в которой запрашивается блокировка. | 35 | Да |
| Длительность | bigint | Время (в микросекундах) с момента выдачи запроса на блокировку до момента возникновения взаимоблокировки. | 13 | Да |
| EndTime | datetime | Время окончания взаимоблокировки. | 15 | Да |
| EventClass | int | Тип события = 25. | 27 | No |
| EventSequence | int | Порядковый номер данного события в запросе. | 51 | No |
| GroupID | int | Идентификатор группы рабочей нагрузки, в которой запускается событие трассировки SQL. | 66 | Да |
| HostName | nvarchar | Имя компьютера, на котором выполняется клиентская программа. Этот столбец данных заполняется, если клиент предоставляет имя узла. Чтобы определить имя узла, используйте функцию HOST_NAME. | 8 | Да |
| IntegerData | int | Номер взаимоблокировки. Номера назначаются начиная от 0 с момента запуска сервера и для каждой взаимоблокировки увеличиваются на единицу. | 25 | Да |
| IntegerData2 | int | Указано только в ознакомительных целях. Не поддерживается. Совместимость с будущими версиями не гарантируется. | 55 | Да |
| IsSystem | int | Указывает, произошло событие в системном или в пользовательском процессе. 1 = системный, 0 = пользовательский. | 60 | Да |
| LoginName | nvarchar | Имя имени входа пользователя (имя для входа в систему безопасности SQL Server или учетные данные входа Microsoft Windows в формате DOMAIN\username). | 11 | Да |
| LoginSid | Изображение | Идентификатор безопасности вошедшего в систему пользователя. Эти сведения можно найти в представлении каталога sys.server_principals. Значение идентификатора безопасности уникально для каждого имени входа на сервере. | 41 | Да |
| Режим | int | Результирующий режим после взаимоблокировки. |
0=NULL — совместим с любыми другими режимами блокировки (LCK_M_NL)
1 = блокировка стабильности схемы (LCK_M_SCH_S)
2 = блокировка изменения схемы (LCK_M_SCH_S)
3 = совмещаемая блокировка (LCK_M_S)
4 = блокировка обновления (LCK_M_U)
5 = монопольная блокировка (LCK_M_X)
6 = коллективная блокировка намерения (LCK_M_IS)
7 = блокировка намерения обновления (LCK_M_IU)
8 = монопольная блокировка намерения (LCK_M_IX)
9 = совмещаемая блокировка с намерением обновления (LCK_M_SIU)
10 = совмещаемая блокировка с намерением монопольного доступа (LCK_M_SIX)
11 = блокировка обновления с намерением монопольного доступа (LCK_M_UIX)
12 = блокировка массового обновления (LCK_M_BU)
13 = совмещаемая блокировка диапазона ключей — совмещаемая блокировка (LCK_M_RS_S)
14 = совмещаемая блокировка диапазона ключей — блокировка обновления (LCK_M_RS_U)
15 = блокировка вставки в диапазон ключей — блокировка NULL (LCK_M_RI_NL)
16 = блокировка вставки в диапазон ключей — совмещаемая блокировка (LCK_M_RI_S)
17 = блокировка вставки в диапазон ключей — блокировка обновления (LCK_M_RI_U)
18 = блокировка вставки в диапазон ключей — монопольная блокировка (LCK_M_RI_X)
19 = монопольная блокировка диапазона ключей — совмещаемая блокировка (LCK_M_RX_S)
20 = монопольная блокировка диапазона ключей — блокировка обновления (LCK_M_RX_U)
Что такое deadlock и как с ним бороться?
Начнем с того, что буквальный перевод слова deadlock означает «мертвая блокировка». При работе с BDE (Delphi, C++Builder, . ) с клиентской части и в IB Database с триггерами и хранимыми процедурами мы имеем два случа появления сообщения deadlock – при чтении и при обновлении. До действительно «мертвого» блокирования дело не доходит, поскольку IB SQL Link стартует любую транзакцию с параметром NO WAIT (т. е. не ожидать разрешения конфликта).
Примечание. BDE или нет – суть дела от этого не меняется. Но в компонентах прямого доступа можно управлять параметрами транзакций – см. статью http://www.ibase.ru/ibtrans/.
Deadlock при обновлении
Две транзакции, еще не завершившиеся, но пытающиеся обновить одни и те же записи, считаются конкурирующими. Существует два режима обработки deadlock – wait и no wait (с ожиданием и без ожидания). В BDE для любых транзакций IB используется режим без ожидания, и режим с ожиданием можно установить только при прямой работе с IB API (например, через FreeIBComponents).
«Неудачливой», естественно, считается транзакция, получившая сообщение о deadlock. Это означает, что одно из действий, проводимых в транзакции, не может быть выполнено. Следовательно, такая транзакция должна быть отменена (rollback). Следует избегать длительных транзакций, которые могут попасть в такую ситуацию – единственным выходом из нее будет попытка начать транзакцию снова и повторить все действия.
Уменьшить число возможных конфликтов обновления можно сократив время выполнения транзакции. Например, сначала принимаются данные от пользователя, и если он подтверждает введенную информацию, приложение стартует транзакцию, быстро передает данные на сервер, и завершается. Чем быстрее пройдет транзакция, тем больше у нее шансов завершиться успешно. Именно для этого в BDE 3.x был введен режим Cached Updates (кэшированные изменения). При Cached Updates изменения накапливаются на клиентской части приложения, затем при вызове метода ApplyUpdates изменения «выстреливаются» на сервер. В любом случае, даже если вы не можете избавиться от длительных обновляющих транзакций, нужно продумать логику приложения и обязательно предусматривать в приложении обработку возникающих конфликтов.
Deadlock при чтении
Внимание! Все описываемые ниже проблемы исправлены в BDE 4.01. При этом параметр DRIVER FLAGS указывать не нужно.
Deadlock при чтении возникает в основном в SQL-серверах, которые используют страничные блокировки при чтении или модификации данных (MS SQL и Sybase). Кажется странным, что при работе с IB, в котором блокировки вообще отсутствуют (конфликт обновления блокировкой не считается – это не блокировка, а именно конфликт), иногда все-таки возникает deadlock при чтении данных.
Причина вот в чем: транзакции уровня изоляции Read Committed имеют в IB два режима – NO RECORD VERSION и RECORD VERSION (см. описание параметров транзакций). В первом случае, если при чтении записи ядро IB обнаруживает наличие неподтвержденной (uncommitted) версии этой записи, то возвращает сообщение о deadlock. Это как бы сигнализирует приложению, что скоро эта запись возможно будет обновлена (ведь в ReadCommitted чужие изменения будут видны сразу после их подтверждения (commit)). В режиме RECORD VERSION наличие неподтвержденных версий записей игнорируется, и всегда возвращается старая версия записи.
Казалось бы, так почему бы BDE не работать по умолчанию в режиме RECORD VERSION? К сожалению, так изначально было заложено – транзакция Read Committed в BDE запускается с параметром NO RECORD VERSION – но до версии Delphi 2.0 этого неудобства почти никто не заметил. А вот почему не заметили, читаем дальше.
Уровень изоляции в AUTOCOMMIT в разных версиях BDE
В зависимости от версии BDE менялись уровни изоляции по умолчанию. Когда вы явно не используете методы управления транзакциями (Database.StartTransaction, Commit и Rollback), то за вас это делает BDE. Не верите? Посмотрите в SQL Monitor. Самое главное, что тип транзакции по умолчанию «зашит» в SQL Link. И даже если вы поместили на форму компонент TDatabase, и изменили у него свойство tiTransIsolation, то это не влияет на BDE, пока вы не вызовете метод Database.StartTransaction.
- Delphi 1.0, BDE 2.52 – Repeatable Read
- Delphi 2.0, BDE 3.x – Read Committed
- Delphi 3.0, BDE 4.0 – Read Committed
- Delphi 3.01, BDE 4.01, 4.51 – Read Committed с параметром RECORD VERSION – deadlock-и при чтении отсутствуют.
Внимание! Не устанавливайте DRIVER FLAGS, не прочитав предварительно READLINK.TXT.
- 0 Read Committed, немедленное подтверждение любых изменений (может вызвать перечитывание курсоров TQuery)
- 512 Repeatable Read, немедленное подтверждение любых изменений (может вызвать перечитывание курсоров TQuery)
- 4096 Read committed, подтверждение изменений выполняется как COMMIT RETAIN, сохраняя контекст курсоров TQuery
- 4608 Repeatable read, подтверждение изменений выполняется как COMMIT RETAIN, сохраняя контекст курсоров TQuery
Copyright iBase.ru © 2002-2023
Немного про Deadlock
В оффициальной документации Mysql про типы блокировок написано совсем немного, а именно:
Есть 2 типа блокировок — Shared (S) и Exclusive (X). Первый тип позволяет только читать данные прикрытые этой блокировкой, второй — читать, писать, удалять и (о чем скромно умолчали) — получить блокировку уровня S
Так же сказано что если Транзакция№1 владеет блокировкой типа S на строке r, то другая Транзакция№2 может захватить эту блокировку. Чтобы получить блокировку типа X на этой строке, второй транзакции придется тихо подождать в сторонке.
Если же Транзакция№1 владеет блокировкой типа X на строке r, то Транзакция№2 не может ни захватить эту же блокировку, ни получить новую уровня S. Она опять тихо идет и ждет пока Транзакция№1 освободит требуемую строку.
Здесь есть один важный момент, который необходимо усвоить: блокировки S и X — это 2 разные блокировки. Это не значит что блокировка S, это какое-то подмножество блокировки X. Это две разных сущности.
Вернемся к дедлокам. На некоторых форумах я встречал вопросы «Как получит deadlock в Mysql». На самом деле очень просто.
Все необходимы ингредиенты у нас имеются в наличии: две транзакции, блокировки типа S и X и строка, на которую получают блокировки.
Краткий рецепт приготовления deadlock на одной строке
1) Транзакция№1 получает блокировку S и продолжает работу
2) Транзакция№2 пытается получить блокировку типа X и… начинает ждать когда Транзакция№1 освободит блокировку S
3) Транзакция№1 пытается получить блокировку типа X и… начинает ждать когда Транзакция№2 получит блокировку типа X и освободит её
Тут есть один скользкий момент. Казалось бы что мешает Транзакции№1 получить блокировку X если она уже имеет блокировку S на этой же строке. А мешает то о чем мы говорили
1) Во-первых X и S это две разных блокировки
2) Во-вторых блокировка типа S не дает право на получение блокировки типа X. Никаких привилегий — в очередь!
Код для ситуации выше
BEGIN; SELECT * FROM `testlock` WHERE LOCK IN SHARE MODE; /* GET S LOCK */ SELECT SLEEP(5); SELECT * FROM `testlock` WHERE FOR UPDATE; /* TRY TO GET X LOCK */ COMMIT; Transaction #2
BEGIN; SELECT * FROM `testlock` WHERE FOR UPDATE; /* TRY TO GET X LOCK - DEADLOCK AND ROLLBACK HERE */ COMMIT; Как же с этим бороться? Офф. сайт Mysql советует комититься почаще, а так же перепроверять код ошибки и перепроводить откатившуюся транзакцию. Мне кажется есть вариант получше — сразу получать блокировку типа X. Тогда на третьем шаге нашего рецепта Транзакция№1 смогла бы получить свою законную блокировку и спокойно завершиться
Напоследок скажу что определить причину deadlock поможет команда SHOW ENGINE INNODB STATUS, которая показывает какие блокировки кто держит и какие ожидает
Руководство по взаимоблокировкам
В этой статье подробно рассматриваются взаимоблокировки в ядро СУБД SQL Server. Взаимоблокировки вызываются конкурирующими, параллельными блокировками в базе данных, часто в многофакторных транзакциях. Дополнительные сведения о блокировке транзакций см . в руководстве по блокировке транзакций и настройке версий строк.
Дополнительные сведения об идентификации и предотвращении взаимоблокировок в База данных SQL Azure см. в статье «Анализ и предотвращение взаимоблокировок в База данных SQL Azure».
Общие сведения о взаимоблокировках
Взаимоблокировка возникает, когда две или более задачи постоянно блокируют друг друга из-за того, что задача каждой из сторон блокирует ресурс, который пытаются заблокировать другие задачи. Например:
- Транзакция А создает общую блокировку строки 1.
- Транзакция Б создает общую блокировку строки 2.
- Транзакция А теперь запрашивает монопольную блокировку строки 2 и блокируется до того, как транзакция Б закончится и освободит общую блокировку строки 2.
- Транзакция Б теперь запрашивает монопольную блокировку строки 1 и блокируется до того, как транзакция А закончится и освободит общую блокировку строки 1.
Транзакция А не может завершиться, пока не завершится Транзакция Б, а Транзакция Б заблокирована Транзакцией А. Это условие также называется цикличной зависимостью: Транзакция А зависит от Транзакции Б, а Транзакция Б замыкает цикл, так как зависит от Транзакции А.
Обе транзакции находятся в состоянии взаимоблокировки и будут всегда находиться в состоянии ожидания, если взаимоблокировка не будет разрушена внешним процессом. Монитор взаимоблокировок SQL Server ядро СУБД периодически проверка для задач, которые находятся в взаимоблокировке. Если монитор обнаруживает цикличную зависимость, то выбирается одна задача, для которой транзакция будет завершена с ошибкой. Это позволяет другой задаче завершить свою транзакцию. Позднее приложение может повторно выполнить транзакцию, которая завершилась с ошибкой, обычно после того как другая транзакция (бывшая в состоянии взаимоблокировки) завершится.
Взаимоблокировки часто путают с обычными блокировками. Если транзакция запрашивает блокировку на ресурс, заблокированный дугой транзакцией, то запрашивающая транзакция ожидает до тех пор, пока блокировка не освобождается. По умолчанию транзакции SQL Server не истекает, если LOCK_TIMEOUT не задано. Запрашивающая транзакция блокируется, но не устанавливается в состояние взаимоблокировки, потому что запрашивающая транзакция ничего не сделала, чтобы заблокировать транзакцию, владеющую блокировкой. Наконец, владеющая транзакция завершится и освободит блокировку, и затем запрашивающая транзакция получит блокировку и продолжится. Взаимоблокировки разрешаются почти сразу, в то время как блокировка может, в теории, сохраняться бесконечно. Взаимоблокировки иногда называют тупиковыми ситуациями.
Условие взаимоблокировки может возникнуть в любой системе с несколькими потоками, не только в системе управления реляционными базами данных, и может возникнуть для ресурсов, отличных от блокировок объектов баз данных. Например, в многопоточной операционной системе один поток может занять один или более ресурсов, таких как блокировки памяти. Если приобретенный ресурс в настоящее время принадлежит другому потоку, первый поток может ожидать освобождения целевого ресурса. В таком случае говорят, что ожидающий поток зависит от владеющего потока для данного ресурса. В экземпляре SQL Server ядро СУБД сеансы могут взаимоблокировки при получении ресурсов, отличных от базы данных, таких как память или потоки.
На рисунке транзакция T1 имеет зависимость от транзакции T2 для Part ресурса блокировки таблицы. Аналогичным образом транзакция T2 имеет зависимость от транзакции T1 для Supplier ресурса блокировки таблицы. Так как эти зависимости из одного цикла, возникает взаимоблокировка транзакций T1 и T2.
Взаимоблокировка может произойти также в случае, когда таблица секционирована, а параметр LOCK_ESCALATION инструкции ALTER TABLE имеет значение AUTO. Если LOCK_ESCALATION задано значение AUTO, параллелизм увеличивается, позволяя SQL Server ядро СУБД блокировать секции таблиц на уровне HoBT, а не на уровне таблицы. Однако если отдельные транзакции удерживают блокировки секций в таблице и пытаются заблокировать еще какой-либо объект в разделе, принадлежащем другой транзакции, это вызовет взаимоблокировку. Такого типа взаимоблокировок можно избежать, установив параметр LOCK_ESCALATION в значение TABLE . Однако это заметно снизит степень параллелизма, поскольку операциям массового обновления данных секции нужно будет ожидать блокировки таблицы.
Обнаружение и прекращение взаимоблокировок
Взаимоблокировка возникает, когда две или более задачи постоянно блокируют друг друга из-за того, что задача каждой из сторон блокирует ресурс, который пытаются заблокировать другие задачи. На следующем графике приведена общая схема состояния взаимоблокировки, в которой:
- Задача T1 блокирует ресурс R1 (изображается в виде стрелки от R1 к T1) и запросила блокировку ресурса R2 (изображается в виде стрелки от T1 к R2).
- Задача T2 блокирует ресурс R2 (изображается в виде стрелки от R2 к T2) и запросила блокировку ресурса R1 (изображается в виде стрелки от T2 к R1).
- Так как ни одна из задач не может продолжиться до тех пор, пока не освободится ресурс, а ни один из ресурсов не может быть освобожден до тех пор, пока не продолжится задание, существует состояние взаимоблокировки.
SQL Server ядро СУБД автоматически обнаруживает циклы взаимоблокировки в SQL Server. SQL Server ядро СУБД выбирает один из сеансов в качестве жертвы взаимоблокировки, а текущая транзакция завершается ошибкой, чтобы разорвать взаимоблокировку.
Ресурсы, которые могут взаимоблокировки
Каждый сеанс пользователя может иметь одну или несколько запущенных от его имени задач, которые могут использовать или ожидать использование различных ресурсов. Следующие типы ресурсов могут привести к блокировке, которая может привести к взаимоблокировке.
- Блокировки. Ожидание получения блокировок ресурсов, таких как объекты, страницы, строки, метаданные и приложения, могут вызвать взаимоблокировку. Например, транзакция 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 должны ожидать, пока каждый из них не освободит память, что приведет к взаимоблокировке.
- Ресурсы, связанные с параллельным выполнением запросов. Координатор, производитель или потоки потребителей, связанные с портом обмена, могут блокировать друг друга, вызывая взаимоблокировку, как правило, при включении хотя бы одного другого процесса, который не является частью параллельного запроса. Кроме того, при запуске параллельного запроса SQL Server определяет степень параллелизма или количество рабочих потоков на основе текущей рабочей нагрузки. При неожиданном изменении системной рабочей нагрузки, например когда начинается выполнение на сервере новых запросов или системе не хватает потоков исполнителя, может возникать взаимоблокировка.
- Ресурсы режима MARS. Эти ресурсы используются для управления чередованием активных запросов в режиме MARS. Дополнительные сведения см. в разделе Использование множественных активных результирующих наборов (MARS).
- Пользовательский ресурс. Если поток ожидает ресурс, потенциально контролируемый пользовательским приложением, ресурс считается внешним или пользовательским и рассматривается как заблокированный.
- Объект взаимного исключения сеанса. Задачи, выполняемые в одном сеансе, чередуются. Это означает, что только одна задача сеанса может выполняться в данный момент времени. Перед тем как задача может быть запущена на выполнение, она должна получить монопольный доступ к объекту взаимного исключения сеанса.
- Объект взаимного исключения транзакции. Все задачи, выполняемые в одной транзакции, чередуются. Это означает, что только одна задача транзакции может выполняться в данный момент времени. Перед тем как задача может быть запущена на выполнение, она должна получить монопольный доступ к объекту взаимного исключения транзакции.
Чтобы задача могла быть запущена в режиме MARS, она должна занять объект взаимного исключения сеанса. Если задача выполняется в транзакции, она должна занять объект взаимного исключения транзакции. Этим гарантируется то, что только одна задача будет активна в каждый момент времени данного сеанса и данной транзакции. Как только потребуются необходимые объекты взаимного исключения, задача сможет выполняться. По завершении задачи или завершении посреди запроса сначала освобождается объект взаимного исключения транзакции, затем объект взаимного исключения сеанса в порядке, обратном тому, в котором они занимались. Однако взаимоблокировки могут произойти и с этими ресурсами. В следующем псевдокоде две задачи, запрос пользователя U1 и запрос пользователя U2, выполняются в одном сеансе.
U1: Rs1=Command1.Execute("insert sometable EXEC usp_someproc"); U2: Rs2=Command2.Execute("select colA from sometable");Хранимая процедура, выполняемая запросом пользователя U1, заняла объект взаимного исключения сеанса. Если хранимая процедура занимает много времени для выполнения, предполагается, что sql Server ядро СУБД, что хранимая процедура ожидает входных данных от пользователя. Запрос пользователя U2 ожидает освобождения объекта взаимного исключения сеанса, в то время как пользователь ожидает результирующий набор от U2, а U1 ожидает пользовательский ресурс. Это состояние взаимоблокировки логически представляется так:
Обнаружение взаимоблокировок
Все ресурсы, перечисленные в приведенном выше разделе, участвуют в схеме обнаружения взаимоблокировок SQL Server ядро СУБД. Обнаружение взаимоблокировки выполняется потоком монитора блокировки, который периодически инициирует поиск по всем задачам в экземпляре SQL Server ядро СУБД. Следующие пункты описывают процесс поиска:
- Значение интервала по умолчанию составляет 5 секунд.
- Если поток монитора блокировки находит взаимоблокировки, интервал обнаружения взаимоблокировок снижается с 5 секунд до 100 миллисекунд в зависимости от частоты взаимоблокировок.
- Если поток монитора блокировки перестает находить взаимоблокировки, SQL Server ядро СУБД увеличивает интервалы между поиском до 5 секунд.
- Если взаимоблокировка была только что найдена, предполагается, что следующие потоки, которые должны ожидать блокировки, входят в цикл взаимоблокировки. Первая пара элементов, ожидающих блокировки, после того как взаимоблокировка была обнаружена, запускает поиск взаимоблокировок вместо того, чтобы ожидать следующий интервал обнаружения взаимоблокировки. Например, если текущее значение интервала равно 5 секунд и была обнаружена взаимоблокировка, следующий ожидающий блокировки элемент немедленно приводит в действие детектор взаимоблокировок. Если этот ожидающий блокировки элемент является частью взаимоблокировки, она будет обнаружена немедленно, а не во время следующего поиска взаимоблокировок.
Sql Server ядро СУБД обычно выполняет периодическое обнаружение взаимоблокировок. Так как число взаимоблокировок, произошедших в системе, обычно мало, периодическое обнаружение взаимоблокировок помогает сократить издержки от взаимоблокировок в системе.
Если монитор блокировок запускает поиск взаимоблокировок для определенного потока, он идентифицирует ресурс, ожидаемый потоком. После этого монитор блокировок находит владельцев определенного ресурса и рекурсивно продолжает поиск взаимоблокировок для этих потоков до тех пор, пока не найдет цикл. Цикл, определенный таким способом, формирует взаимоблокировку.
После обнаружения взаимоблокировки SQL Server ядро СУБД завершает взаимоблокировку, выбрав один из потоков в качестве жертвы взаимоблокировки. SQL Server ядро СУБД завершает выполнение текущего пакета для потока, откатывает транзакцию жертвы взаимоблокировки и возвращает ошибку 1205 в приложение. Откат транзакции жертвы взаимоблокировки снимает все блокировки, удерживаемые транзакцией. Это позволяет транзакциям потоков разблокироваться и продолжить выполнение. Ошибка 1205 жертвы взаимоблокировки записывает в журнал ошибок сведения обо всех потоках и ресурсах, затронутых взаимоблокировкой.
По умолчанию SQL Server ядро СУБД выбирает в качестве жертвы взаимоблокировки сеанс, выполняющий транзакцию, которая является наименее дорогой для отката. В качестве альтернативы пользователь может указать приоритет сеансов в ситуации взаимоблокировки, используя инструкцию SET DEADLOCK_PRIORITY . DEADLOCK_PRIORITY может принимать значения LOW, NORMAL или HIGH или в качестве альтернативы может принять любое целочисленное значение в промежутке (-10 до 10). Приоритет в случае взаимоблокировки по умолчанию устанавливается на значение NORMAL. Если у двух сеансов имеются различные приоритеты в случае взаимоблокировки, то в качестве жертвы взаимоблокировки будет выбран сеанс с более низким приоритетом. Если у обоих сеансов установлен одинаковый приоритет в случае взаимоблокировки, то в качестве объекта взаимоблокировки будет выбран сеанс, откат которого потребует наименьших затрат. Если сеансы, вовлеченные в цикл взаимоблокировки, имеют один и тот же приоритет в случае взаимоблокировки и одинаковую стоимость, то жертва взаимоблокировки выбирается случайным образом.
При работе со средой CLR монитор взаимоблокировки автоматически обнаруживает взаимоблокировку для ресурсов синхронизации (мониторы, блокировки чтения и записи и соединение потоков), доступ к которым был получен изнутри управляемых процедур. Однако взаимоблокировка снимается путем создания сообщения об исключительной ситуации в процедуре, которая была выбрана в качестве жертвы взаимоблокировки. Важно понимать, что исключение не освобождает ресурсы, которыми владеет жертва взаимоблокировки, автоматически; ресурсы должны быть освобождены явно. В соответствии с поведением исключения, исключение, используемое для идентификации жертвы взаимоблокировки, может быть поймано и отклонено.
Средства сведений о взаимоблокировок
Для просмотра сведений о взаимоблокировке SQL Server ядро СУБД предоставляет средства мониторинга в виде сеанса system_health xEvent, двух флагов трассировки и события графа взаимоблокировки в SQL Profiler.
В этом разделе содержатся сведения о расширенных событиях, флагах трассировки и трассировках, но расширенное событие Взаимоблокировки — это рекомендуемый метод для записи сведений о взаимоблокировках.
Расширенное событие взаимоблокировки
Начиная с SQL Server 2012 (11.x), xml_deadlock_report расширенные события (xEvent) следует использовать вместо класса событий Взаимоблокировки в sql Trace или SQL Profiler.
Кроме того, начиная с SQL Server 2012 (11.x), когда возникают взаимоблокировки, system_health сеанс уже захватывает все xml_deadlock_report xEvents, содержащие граф взаимоблокировки. Так как сеанс system_health включен по умолчанию, не требуется, чтобы отдельный сеанс xEvent был настроен для записи сведений о взаимоблокировке. Никаких дополнительных действий для записи информации о взаимоблокировках xml_deadlock_report с помощью xEvent не требуется.
Зафиксированный граф взаимоблокировки обычно имеет три узла:
- victim-list. Идентификатор процесса жертвы взаимоблокировки.
- process-list. Сведения обо всех процессах, участвующих во взаимоблокировке.
- resource-list. Сведения о ресурсах, участвующие во взаимоблокировке.
system_health Открытие файла сеанса или кольцевого буфера, если xml_deadlock_report xEvent записано, Management Studio представляет графическое изображение задач и ресурсов, участвующих в взаимоблокировке, как показано в следующем примере:
Следующий запрос может просматривать все события взаимоблокировки, захваченные буфером кольца сеансов system_health :
SELECT xdr.value('@timestamp', 'datetime') AS [Date], 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 [Date] DESC;
В следующем примере показаны выходные данные после выбора первой ссылки приведенного выше результата:
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+ unknown SET NOCOUNT ON WHILE (1=1) BEGIN EXEC p1 4 END UPDATE t1 SET c2 = c2+1 WHERE c1 = @p unknown SET NOCOUNT ON WHILE (1=1) BEGIN EXEC p2 4 END Дополнительные сведения см. в разделе Использование сеанса system_health
Флаги трассировки 1204 и 1222
При возникновении взаимоблокировок флаг трассировки 1204 и флаг трассировки 1222 возвращает сведения, которые записываются в журнале ошибок SQL Server. Флаг трассировки 1204 сообщает сведения о взаимоблокировке, отформатированные каждым узлом, участвующим в взаимоблокировке. Флаг трассировки 1222 форматирует сведения о взаимоблокировки, сначала по процессам, а затем по ресурсам. Есть возможность включения обоих флагов трассировки для получения двух представлений одного события взаимоблокировки.
Избегайте использования флага трассировки 1204 и 1222 в системах с интенсивными рабочими нагрузками, в которых возникают взаимоблокировки. Использование этих флагов трассировки может привести к проблемам с производительностью. Вместо этого используйте расширенное событие Взаимоблокировки для записи необходимых сведений.
Помимо определения свойств флага трассировки 1204 и 1222, в следующей таблице также показаны сходство и различия.
BatchID (sbid для флага трассировки 1222). Определяет пакет, из которого выполнение кода запрашивает или удерживает блокировку. Если режим MARS отключен, значение BatchID равно 0. Если режим MARS включен, для активных пакетов задается значение в диапазоне от 1 до n. При отсутствии активных пакетов в сеансе BatchID присваивается значение 0.
Mode. Задает тип блокировки для конкретного ресурса, который запрошен, предоставлен или ожидается потоком. Значением Mode может быть IS (с намерением совмещаемого доступа), S (совмещаемая), U (на обновление), IX (с намерением монопольного доступа), SIX (совмещаемая с намерением монопольного доступа) и X (монопольная).
Line # (строка для флага трассировки 1222). Содержит номер строки в текущем пакете инструкций, который выполнялся в момент возникновения взаимоблокировки.
Lists. Владелец блокировки может быть частью этих списков:
Grant List. Перечисляет текущих владельцев ресурса.
Convert List. Перечисляет текущих владельцев, которые пытаются перенести блокировки на более высокий уровень.
Wait List. Перечисляет текущие запросы на новые блокировки ресурса.
Statement Type. Описывает тип инструкции DML (SELECT, INSERT, UPDATE или DELETE), для которой потокам выданы разрешения.
Victim Resource Owner. Указывает участвующий поток, который SQL Server выбирает в качестве жертвы, чтобы разорвать цикл взаимоблокировки. Выбранный поток и все его подпроцессы завешены.
executionstack. Представляет код Transact-SQL, который выполняется во время взаимоблокировки.
priority. Представляет собой приоритет в случае взаимоблокировки. В некоторых случаях SQL Server ядро СУБД может выбрать изменение приоритета взаимоблокировки в течение короткого времени, чтобы повысить параллелизм.
logused. Пространство журнала, используемое задачей.
идентификатор владельца. Идентификатор транзакции, которая управляет запросом.
status. Состояние задачи. Принимает одно из следующих значений:
>>pending. Ожидание потока исполнителя.
>>runnable. Готов к запуску, но ожидает такт.
>>running. Выполняется в данный момент в планировщике.
>>suspended. Выполнение приостановлено.
>>done. Задача выполнена.
>>spinloop. Ожидание освобождение элемента Spinlock.
waitresource. Ресурс, необходимый для выполнения задачи.
waittime. Время ожидания ресурса в миллисекундах.
schedulerid. Планировщик, ассоциированный с этой задачей. См. sys.dm_os_schedulers (Transact-SQL).
hostname. Имя рабочей станции.
isolationlevel. Текущий уровень изоляции транзакции.
Xactid. Идентификатор транзакции, которая управляет запросом.
currentdb. Идентификатор базы данных.
lastbatchstarted. Последний раз, когда клиентский процесс запустил выполнение пакета.
lastbatchcompleted. Последний раз, когда клиентский процесс завершил выполнение пакета.
clientoption1 и clientoption2. Устанавливает параметры для данного клиентского соединения. Это битовая маска, которая включает сведения о параметрах, обычно управляемых инструкциями SET, такими как SET NOCOUNT и SET XACTABORT.
OBJECT. Определяет таблицу, по которой удерживается или запрошена блокировка. OBJECT представлен как OBJECT: db_id:object_id Например, TAB: 6:2009058193 .
KEY. Определяет диапазон ключа в индексе, по которому удерживается или запрошена блокировка. КЛЮЧ представлен как KEY: db_id:hobt_id (хэш-значение ключа индекса). Например, KEY: 6:72057594057457664 (350007a4d329) .
PAG. Определяет страничный ресурс, по которому удерживается или запрошена блокировка. PAG представляется как PAG: db_id:file_id:page_no Например, PAG: 6:1:20789 .
EXT. Определяет структуру экстента. EXT представлен как EXT: db_id:file_id:extent_no . Например, EXT: 6:1:9 .
DB. Определяет блокировку базы данных. DB представляется одним из следующих способов:
База данных: db_id[BULK-OP-DB] идентифицирует блокировку базы данных, взятую базой данных резервной копии.
База данных: db_id[BULK-OP-LOG] , которая идентифицирует блокировку, взятую журналом резервного копирования для этой конкретной базы данных.
APP. Определяет блокировку, выполненную ресурсом приложения. APP представлено как APP: lock_resource Например, APP: Formf370f478 .
METADATA. Представляет ресурсы метаданных, участвующие во взаимоблокировке. Поскольку METADATA содержит множество вспомогательных ресурсов, возвращаемое значение зависит от заблокированного вспомогательного ресурса. Например, METADATA.USER_TYPE возвращает user_type_id = *integer_value* . Дополнительные сведения о ресурсах и подресурсах МЕТАДАННЫх см. в sys.dm_tran_locks (Transact-SQL).
Пример флага трассировки 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 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 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 mode=X associatedObjectId=72057594057392128 owner-list owner mode=X waiter-list waiter mode=U requestType=wait keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2022.dbo.T1 indexname=nci_T1_COL1 mode=X associatedObjectId=72057594057457664 owner-list owner mode=X waiter-list waiter mode=U requestType=waitСобытие графа взаимоблокировки Profiler
Событие в SQL Profiler, которое представляет собой графическое описание задач и ресурсов, вовлеченных во взаимоблокировку. Следующий пример иллюстрирует результаты, выводимые компонентом SQL Profiler, когда включено событие Deadlock Graph.
Sql Profiler создает трассировки, которые были устарели в 2016 году и заменены расширенными событиями. Расширенные события имеют гораздо меньше затрат на производительность и гораздо более настраиваются, чем трассировки. Рекомендуется использовать событие взаимоблокировки расширенных событий вместо трассировок.
Дополнительные сведения о событии взаимоблокировки см. в разделе Класс событий Lock:Deadlock. Дополнительные сведения о запуске графа взаимоблокировки sql Profiler см. в разделе «Сохранение графов взаимоблокировки» (SQL Server Profiler).
Существуют эквиваленты для классов событий трассировки SQL в расширенных событиях, см . раздел «Эквиваленты расширенных событий» классам событий трассировки SQL. Расширенные события рекомендуется использовать для трассировки SQL.
Обработка взаимоблокировок
Когда экземпляр SQL Server ядро СУБД выбирает транзакцию в качестве жертвы взаимоблокировки, он завершает текущий пакет, откатывает транзакцию и возвращает сообщение об ошибке 1205 в приложение.
Your transaction (process ID #52) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun your transaction.
Так как любое приложение, отправляющее запросы Transact-SQL, можно выбрать в качестве жертвы взаимоблокировки, приложения должны иметь обработчик ошибок, который может перехватать сообщение об ошибке 1205. Приложение, которое не обрабатывает эту ошибку, не будет знать о том, что произошел откат транзакции, и произойдет ошибка.
Благодаря обработчику ошибки 1205 приложение сможет справиться с взаимоблокировкой и предпринять действия по ее исправлению (например автоматически повторить запрос, который не был выполнен из-за взаимоблокировки). Если повторное выполнение запроса происходит автоматически, пользователю не обязательно знать о возникновении взаимоблокировки.
Прежде чем повторять запрос, следует приостановить приложение на короткое время. Это позволит второй участвующей транзакции закончить работу и освободить блокировки, которые частично создавали взаимоблокировку. Благодаря этому приложение минимизирует вероятность повторного возникновения взаимоблокировки при повторном выполнении запроса.
Минимизация взаимоблокировок
Хотя полностью избежать взаимоблокировок нельзя, следующие соглашения по написанию кода могут уменьшить вероятность возникновения взаимоблокировок. Минимизация взаимоблокировок приводит к увеличению пропускной способности системы (выполнению большего количества транзакций за единицу времени) и уменьшению накладных расходов системы на обслуживание, так как транзакции реже:
- Откатываются с отменой всей выполненной транзакцией работы.
- Повторно выполняются приложениями, так как при возникновении взаимоблокировок они откатывались.
Для минимизации взаимоблокировок:
- Осуществляйте доступ к объектам в одинаковом порядке.
- Избегайте взаимодействия с пользователем в транзакциях. — Уменьшение размера транзакций и хранение их в одном пакете.
- Используйте низкий уровень изоляции.
- Используйте уровень изоляции строк, основанный на управлении версиями строк.
- Установите для параметра базы данных READ_COMMITTED_SNAPSHOT значение ON, чтобы разрешить транзакциям с зафиксированным чтением использовать управление версиями строк.
- Используйте изоляцию моментальных снимков.
Осуществление доступа к объектам в одинаковом порядке
Если все одновременные транзакции будут осуществлять доступ к объектам в одинаковом порядке, то появление взаимоблокировок менее вероятно. Например, если две параллельные транзакции получают блокировку Supplier таблицы, а затем в Part таблице одна транзакция блокируется в Supplier таблице, пока не завершится другая транзакция. После фиксации или отката первой транзакции вторая продолжает работу, и взаимоблокировки не происходит. Использование хранимых процедур для всех изменений данных может стандартизировать порядок доступа к объектам.
Отказ от взаимодействия с пользователем в транзакциях
Скорость выполнения пакетов без вмешательства пользователя гораздо выше, чем скорость выполнения пакетов, в которых пользователь должен вручную реагировать на запросы,поэтому нет необходимости создавать транзакции, в которых происходит взаимодействие с пользователем, например вводить запрашиваемый приложением параметр. Например, если транзакция ожидает ввода информации пользователем, а пользователь ушел на обед или отправился домой в конце рабочей недели, выполнение транзакции может завершиться только после его возвращения. Это уменьшает пропускную способность системы, так как любые блокировки, устанавливаемые транзакцией, будут сняты только после подтверждения или отката транзакции. Даже если взаимоблокировка не возникла, другие транзакции, требующие доступа к тем же ресурсам, окажутся заблокированными до окончания выполнения текущей транзакции.
Уменьшение размера транзакций и хранение их в одном пакете
Обычно взаимоблокировка возникает, когда несколько долго выполняемых транзакций запускаются одновременно в одной базе данных. Чем длиннее транзакция, тем дольше будут удерживаться полученные монопольные блокировки или блокировки обновления, которые блокируют другие действия и могут привести к взаимоблокировке.
Помещение транзакций в один пакет сокращает объемы передачи данных по сети во время транзакции, уменьшая возможные задержки при завершении транзакций и снятии блокировок.
Дополнительные сведения о блокировках обновлений см . в руководстве по блокировке транзакций и управлении версиями строк.
Использование низкого уровня изоляции
Определите, может ли транзакция выполняться при более низком уровне изоляции. Применение фиксации чтением позволяет транзакции считывать данные, считанные до этого (но не измененные) другой транзакцией, не ожидая завершения выполнения этой другой транзакции. Использование более низкого уровня изоляции, например фиксации чтением, устанавливает совмещаемые блокировки на более короткий промежуток времени, чем при использовании более высокого уровня изоляции, например сериализации. Это уменьшает количество состязаний блокировок.
Использование уровня изоляции, основанного на управлении версиями строк
Если параметр базы данных READ_COMMITTED_SNAPSHOT установлен в ON, то транзакция, запущенная с уровнем изоляции подтверждения чтением, использует во время операция считывания управление версиями, а не совмещаемые блокировки.
Некоторые приложения зависят от блокировок и монополизации ресурсов, обеспечиваемых уровнем изоляции read committed. В такие приложения перед включением данного параметра необходимо внести изменения.
В изоляции моментальных снимков также применяется управление версиями строк, не использующее во время операций считывания разделяемых блокировок. Прежде чем транзакция может выполняться при изоляции моментального снимка, ALLOW_SNAPSHOT_ISOLATION необходимо задать ON параметр базы данных.
Применение этих уровней изоляции приводит к минимизации взаимоблокировок, возникающих между операциями считывания и записи.
Использование связанных соединений
При использовании связанных соединений два или более соединения, открытые одним и тем же приложением, могут действовать совместно друг с другом. Любые блокировки, используемые вторичными соединениями, устанавливаются так, как если бы они запрашивались первичным соединением, и наоборот. Поэтому соединения не блокируют друг друга.
Остановка транзакции
В сценарии взаимоблокировки транзакция жертвы автоматически останавливается и откатывается. Нет необходимости останавливать транзакцию в сценарии взаимоблокировки.
Причина взаимоблокировки
Этот пример работает в AdventureWorksLT2019 примере базы данных с схемой и данными по умолчанию при включении READ_COMMITTED_SNAPSHOT. Чтобы скачать этот пример, посетите примеры баз данных AdventureWorks.
Чтобы вызвать взаимоблокировку, необходимо подключить два сеанса к базе данных AdventureWorksLT2019 . Мы будем ссылаться на эти сеансы как сеанс A и сеанс B. Эти два сеанса можно создать, просто создав два окна запросов в SQL Server Management Studio (SSMS).
В Сеансе 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, выбранным в качестве жертвы взаимоблокировки. Сеанс 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.
Затем можно просмотреть сведения о взаимоблокировке в целевом объекте system_health ring_buffer сеанса расширенных событий, который включен и активен по умолчанию в SQL Server. Обратите внимание на следующий запрос:
WITH cteDeadLocks ([Deadlock_XML]) AS ( SELECT [Deadlock_XML] = CAST(target_data AS 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 Deadlock_XML = x.Graph.query('(event/data/value/deadlock)[1]') , when_occurred = x.Graph.value('(event/data/value/deadlock/process-list/process/@lastbatchstarted)[1]', 'datetime2(3)') , DB = DB_Name(x.Graph.value('(event/data/value/deadlock/process-list/process/@currentdb)[1]', 'int')) --Current database of the first listed process FROM ( SELECT Graph.query('.') AS Graph FROM cteDeadLocks 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 для визуального графа взаимоблокировки. Граф взаимоблокировки должен выглядеть примерно так, как показано на следующем рисунке.
Оптимизированная блокировка и взаимоблокировка
Область применения: База данных SQL Azure
Оптимизированная блокировка представила другой метод для механики блокировки, которая изменяет, как взаимоблокировки с участием эксклюзивных блокировок TID могут быть сообщены. В каждом ресурсе в отчете взаимоблокировки каждый элемент сообщает базовые ресурсы и конкретную информацию о блокировках каждого члена взаимоблокировки.
Рассмотрим следующий пример, в котором включена оптимизированная блокировка:
CREATE TABLE t2 (a int PRIMARY KEY not null ,b int null); INSERT INTO t2 VALUES (1,10),(2,20),(3,30) GOСледующие команды TSQL в двух сеансах создают взаимоблокировку в таблице t2 :
--session 1 BEGIN TRAN foo; UPDATE t2 SET b = b+ 10 WHERE a = 1;--session 2: BEGIN TRAN bar UPDATE t2 SET b = b+ 10 WHERE a = 2;--session 1: UPDATE t2 SET b = b + 100 WHERE a = 2;--session 2: UPDATE t2 SET b = b + 20 WHERE a = 1;Этот сценарий конкурирующих UPDATE операторов приводит к взаимоблокировке. В этом случае ресурс блокировки ключей, где каждый сеанс содержит блокировку X на своем собственном TID и ожидает блокировки S на другом TID, что приводит к взаимоблокировке. Следующий XML-код, захваченный как отчет взаимоблокировки, содержит элементы и атрибуты, относящиеся к оптимизированной блокировке:
Связанный контент
- Расширенные события
- sys.dm_tran_locks (Transact-SQL)
- Класс событий Deadlock Graph
- Удаленная служба данных ADO: взаимоблокировки с уровнем повторяемой изоляции
- Класс событий Lock:Deadlock Chain
- Класс событий Lock:Deadlock
- SET DEADLOCK_PRIORITY (Transact-SQL)
- Анализ и предотвращение взаимоблокировок в Базе данных SQL Azure
- Открытие, просмотр и печать файла взаимоблокировки в SQL Server Management Studio (SSMS)