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 избавляется от тупика автоматически, выбирая один из сеансов в качестве жертвы и уничтожая его, позволяя продолжить другому сеансу.  Как правило, жертвой является сеанс, чей  откат наименее затратный. 

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

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

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

HOW_TO

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

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

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

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

Результат:

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

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

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

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

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

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

Суть подхода и история создания Falcon Space

Платформа Falcon Space

Это снижение стоимости владения

за счет меньшего количества людей для поддержки

Это быстрое внесение изменений

по ходу эксплуатации программы

Это современный интерфейс

полная адаптация под мобильные устройства

Веб-приложения на чистом MS SQL. Обучение разработке на Falcon Space

Вы можете разрабатывать самостоятельно или сотрудничать с нами в плане веб-разработки на платформе Falcon Space, используя только SQL и HTML.
Демо-стенд компонентов
На стенде можно посмотреть различные компоненты в действии: таблицы, формы, модальные окна, диаграммы, карта и т.д.
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.