Приветствую всех посетителей сайта Info-Comp.ru! Сегодня мы с Вами поговорим о взаимоблокировках (Deadlocks) в Microsoft SQL Server, узнаем, как посмотреть информацию по взаимоблокировкам, а также как минимизировать количество взаимоблокировок в системе.
Интересные статьи по блокировкам в Microsoft SQL Server
- Блокировки в Microsoft SQL Server – что это такое и для чего нужны
- Типы блокировок в Microsoft SQL Server
- Эскалация блокировок в Microsoft SQL Server
Что такое взаимоблокировки (Deadlocks)
Взаимоблокировка (Deadlocks) – ситуация, при которой две транзакции блокируют друг друга, и ни одна из них не может продолжать свое выполнение.
Классический пример взаимоблокировки
| Старт транзакции | Транзакция 1 | Транзакция 2 |
| Шаг 1 | Накладывает совмещаемую блокировку на ресурс 1 | Накладывает совмещаемую блокировку на ресурс 2 |
| Шаг 2 | Запрашивает эксклюзивную блокировку на ресурс 2 | Запрашивает эксклюзивную блокировку на ресурс 1 |
| Шаг 3 | Ожидает, когда с ресурса 2 будет снята совмещаемая блокировка, наложенная Транзакцией 2 на первом шаге | Ожидает, когда с ресурса 1 будет снята совмещаемая блокировка, наложенная Транзакцией 1 на первом шаге |
| Шаг 4 | Взаимоблокировка (Deadlocks) | |
Заметка! Транзакции в T-SQL – основы для новичков с примерами.
Как Вы понимаете, если возникла взаимоблокировка, то обе транзакции будут заблокированы вечно, т.е. ни одна из них не будет выполнена, поэтому данная ситуация, т.е. Deadlock, требует вмешательства извне.
В SQL Server реализован так называемый «Монитор взаимоблокировок (Deadlock Monitor)», который в автоматическом режиме периодически (где-то каждые 5 секунд) осуществляет поиск взаимоблокировок и если он находит их, то вмешивается в данный процесс с целью устранения сложившейся ситуации.
А решение здесь только одно, выбрать в качестве жертвы одну из транзакций и принудительно завершить ее, таким образом, одна из транзакций будет выполнена успешно, а вторая завершится с известной ошибкой
1205 – Транзакция (указан идентификатор процесса) вызвала взаимоблокировку ресурсов «указан ресурс» с другим процессом и была выбрана в качестве жертвы для ее разрешения. Запустите транзакцию повторно.
В SQL Server этот механизм настроен таким образом, что в качестве жертвы выбирается та транзакция, которую легче откатить, иными словами, если, например, в Транзакции 1 до Deadlock уже были выполнены какие-то изменения данных, а в Транзакции 2 нет, там было только чтение данных, то, конечно же, в качестве жертвы будет выбрана Транзакции 2, так как там по сути-то и откатывать нечего.
Заметка! План выполнения запроса в Microsoft SQL Server – что это такое и для чего он нужен.
Однако в качестве альтернативы мы можем сами указать приоритет сеанса в ситуации взаимоблокировки, для этого мы можем использовать инструкцию SET DEADLOCK_PRIORITY, где в качестве значения указать LOW, NORMAL или HIGH. Таким образом, если, например, мы для одного сеанса, который очень важен для нас, укажем высокий приоритет, то при возникновении взаимоблокировки у нашего сеанса будет более высокий приоритет и он не будет выбран в качестве жертвы, если, конечно же, другой сеанс, с которым у нас возникла взаимоблокировка, не обладает таким же приоритетом, кстати, в таком случае, мы снова возвращаемся к принципу «кого легче откатить».
Кроме этого, иногда в приложении реализуют обработку взаимоблокировок, например, приложение запускает транзакцию, и если она попала во взаимоблокировку, и ей не повезло, т.е. ее выбрали в качестве жертвы, то приложение перехватывает эту ошибку и, например, запускает эту транзакцию повторно, чтобы она все-таки выполнилась. Таким образом, пользователь даже не знает о том, что во время его работы возникали какие-то ошибки.
Как посмотреть информацию по взаимоблокировкам
Посмотреть информацию по взаимоблокировкам, возникшим в системе, можно с помощью следующих инструментов:
- Профилировщик XEvent (сеанс system_health, событие xml_deadlock_report)
- SQL Profiler (событие Deadlock Graph)
- Флаги трассировки 1204 и 1222. После включения в журнале ошибок SQL Server фиксируются необходимые сведения о взаимоблокировках (Однако не рекомендуется использовать эти флаги трассировки в системах с большим объемом рабочих нагрузок, которые вызывают взаимоблокировки, так как это может привести к проблемам с производительностью)
Как минимизировать количество взаимоблокировок
Взаимоблокировки увеличивают накладные расходы на обслуживание системы, так как
- Приходится откатывать транзакцию
- Приходится повторно выполнять эту транзакцию из приложения.
Поэтому рекомендуется минимизировать количество взаимоблокировок.
К сожалению, полностью избежать взаимоблокировок нельзя, однако можно уменьшить вероятность их возникновения.
Это достигается путем правильного написания кода.
Для этого:
- Осуществляйте доступ к объектам во всех индукциях в одинаковом порядке. Например, если в инструкции в транзакции Вы сначала обновляете данные в Таблице 1, а затем обновляете данные в Таблице 2, то придерживайтесь такого порядка во всех инструкциях, т.е. не нужно в другой инструкции обновлять сначала Таблицу 2, а затем Таблицу 1;
- Избегайте взаимодействия с пользователем в транзакциях. Иными словами, не нужно в транзакции запрашивать какие-то данные от пользователя, а то может возникнуть ситуация, когда Вы начали транзакцию, в которой запросили какие-то данные от пользователя, т.е. пользователь должен вручную, допустим во всплывающем окне, ввести эти данные, а этот пользователь взял и решил уйти на обед или вовсе пошел домой. Таким образом, открытая транзакция будет висеть, т.е. удерживать ресурсы, и тем самым блокировать кучу других транзакций;
- Уменьшайте размер транзакций, т.е. используйте короткие транзакции, так как обычно взаимоблокировка возникает, когда несколько долго выполняемых транзакций запускаются одновременно в одной базе данных. Чем длиннее транзакция, тем дольше будут удерживаться полученные эксклюзивные блокировки или блокировки обновления, которые блокируют другие действия и могут привести к взаимоблокировке;
- Используйте низкий уровень изоляции, где это возможно (а также, не нужно необоснованно повышать уровень изоляции). Использование более низкого уровня изоляции устанавливает совмещаемые блокировки на более короткий промежуток времени, чем при использовании более высокого уровня изоляции, а это уменьшает количество конфликтов блокировок;
- Подумайте об использовании уровня изоляции строк, основанном на управлении версиями строк:
- SNAPSHOT
- READ COMMITTED SNAPSHOT
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
На сегодня это все, надеюсь, материал был Вам полезен!
