SQL. Ошибка. Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Введение

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

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

Если SQL не знает как разобраться с подобной ситуацией взаимных блокировок и уходит в ожидание, то возникает дедлок (deadlock). 

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

1. Убедитесь, что процессы обращаются ко всем общим объектам в одном и том же порядке.

Рассмотрим процесс: 

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

Изменить  порядок операторов,  в котором объекты и ресурсы базы данных должны быть доступны процессам – это хороший пример

2. Сделайте транзакции короткими и простыми

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

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

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

Самую высокую скорость выполнения и самую низкую согласованность имеет уровень read uncommitted (взаимоблокировки отсутствуют). На этом уровне каждая транзакция видит незафиксированные изменения другой транзакции (феномен грязного чтения). В SQL Server вы можете минимизировать конкуренцию за блокировку, одновременно защищая транзакции от грязного чтения или незафиксированных изменений данных, используя уровень read commited. Самую низкую скорость выполнения и самую высокую согласованность — serializable.

Установить уровень транзакции:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Проверить уровень транзакции :

select transaction_isolation_level from sys.dm_exec_sessions where session_id = Process_ID*

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

SQL Server выбирает жертву взаимоблокировки на основе двух факторов: DEADLOCK_PRIORITY, установленного для каждого сеанса, и объема работы, которую SQL Server должен выполнить для отката транзакции.

Параметр DEADLOCK_PRIORITY может быть установлен пользователем на HIGH, NORMAL, LOW или на целочисленное значение от -10 до 10. По умолчанию DEADLOCK_PRIORITY установлено на NORMAL (0)

SET DEADLOCK_PRIORITY HIGH; GO

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

SELECT session_id, DEADLOCK_PRIORITY FROM sys.dm_exec_sessions WHERE SESSION_ID = @@SPID

Подробнее об ошибке

HOWTO по дедлокам

Как понять есть ли дедлок сейчас в базе?

  • Через запрос:
SELECT * FROM sys.sysprocesses

WHERE blocked > 0
  • Через интерфейс:

Для этого в SQL SERVER  перейдите  в «Управление»> «Расширенные события»> «Сеансы»> «system_health»> «package0.event_file» и нажмите «Просмотреть целевые данные…»

Результат:

Как найти на какие именно таблицы висят дедлоки?

На рисунке выше график тупиков, состоящий из разделов ресурсов и процессов.

В разделе Ресурсы  отображаются списки всех ресурсов, попавших в тупик, параметр objectname -  таблица, в которой произошла взаимоблокировка.

 * Примечание: Process ID указан в сообщении об ошибке

Насколько полезной была статья?

Что еще посмотреть по SQL Server

Выгода от использования Falcon Space

В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
Нужна бесплатная консультация?
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.
Сайт использует Cookie. Правила конфиденциальности OK