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
SQL. Получение null при конкатенации (объединении) строк
SQL. Проблема с доступом к таблице БД
SQL Management Studio медленно работает, тормозит. Как решить проблему
Распространенные ошибки SQL в хранимых процедурах и запросах
Не запускается Configuration Manager
Решение проблем MS SQL Server с блокировками
Решение ошибки Cannot resolve the collation conflict between
Сколько памяти использует SQL Server
Не создается диаграмма в SQL Server. Ошибка при создании диаграмм БД
Ищем партнеров-разработчиков на T-SQL
Выгода от использования Falcon Space
В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
- Шаг 1. Создать концепт проекта
- Шаг 2. Получить оценку бюджета (КП)
- Шаг 3. Заключить договор
- Шаг 4. Создать совместно техническое задание
- Шаг 5. Поэтапная реализация проекта