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» и нажмите «Просмотреть целевые данные…»