Что такое взаимоблокировка SQL Server?
Взаимоблокировка в SQL Server возникает, когда 2 процесса / сеанса конкурируют за эксклюзивный доступ к данным в таблицах, но не могут получить эксклюзивный доступ к ним, поскольку другой процесс/сеанс препятствует этому. Это приводит к ситуации, когда ни один процесс не может продолжаться и получает удар. Единственный выход из этого тупика, когда это происходит, заключается в том, что один из процессов должен быть завершен. SQL Server автоматически определяет, когда возникли тупики,и принимает меры, убивая один из процессов, известный как жертва.
Теперь блокировки не ограничиваются блокировками таблиц, но начиная с SQL Server 2012 это может происходить с памятью, несколькими активными результирующими наборами, рабочими потоками и любыми ресурсами, связанными с параллельным выполнением запросов.
Обычно, когда происходит взаимоблокировка, первым признаком является сообщение об ошибке, которое будет отображаться процессу/сеансу, выбранному в качестве жертвы взаимоблокировки, как показано ниже.
Msg 1205, Уровень 13, состояние 51, транзакция строки 6 (идентификатор процесса 62) была заблокирована на ресурсах блокировки с другим процессом и была выбрана в качестве жертвы взаимоблокировки. Повторите транзакцию.
Другой процесс / сессия, который не был выбран в качестве жертвы, в основном не будет знать, что этот процесс участвовал в тупике.
Типы тупиков: существует 2 типа тупиков.
Цикл замки
цикл взаимоблокировки происходит обычно, когда процесс X, который удерживает блокировку на ресурс з ждет, чтобы получить монопольную блокировку на ресурс, но в то же время процесс c удерживает блокировку на ресурс и ждет, чтобы получить монопольную блокировку на ресурс, З.
блокировки преобразования
преобразования тупик обычно происходит, когда в сеансе пытается преобразовать блокировку с одного типа(малый замок) чтобы еще один эксклюзивный тип, но не может этого сделать, потому что другой сеанс уже держит разделяемую блокировку на один и тот же ресурс.
SQL Server существует 3 типа блокировок преобразований.
Описание Имени Типа
SIU Share with Intent Update Shared locks but also has update locks on some resources like page or row.
SIX Share with Intent Exclusive Shared locks и эксклюзивная блокировка на некоторых ресурсах, таких как страница или строка.
UIX Update with Intent Exclusive блокировка U(Update) и блокировка IX(Intent exclusive) принимаются отдельно, но удерживаются одновременно.
Как SQL Server обрабатывает тупиковые ситуации приведено ниже:
Диспетчер блокировок в SQL Server автоматически выполняет поиск тупиков с помощью LOCK_MONITOR каждые 5 секунд. Он смотрит на все ожидающие блокировки, чтобы определить, есть ли какие-либо циклы. Когда он обнаруживает взаимоблокировку, то выбирает одну из транзакций в качестве жертвы и отправляет ошибку 1205 клиенту/сеансу, которому принадлежит соединение. Затем транзакция жертвы завершается и откатывается назад, что освобождает все ресурсы, на которых она удерживала блокировку, позволяя тем самым продолжить другую транзакцию, вовлеченную в тупик.
Выбор жертвы SQL Server в взаимоблокировке обычно выполняется с помощью первого приоритета взаимоблокировки для выбора жертвы.
Если обе транзакции, вовлеченные в взаимоблокировку, имеют одинаковый приоритет взаимоблокировки, то обычно откатывается та, которая имеет наименьшую стоимость. Другими словами, тот, где был использован наименьший объем журнала транзакций, то есть меньше данных для отката.
Слежу за тупиками. Существуют различные инструменты, которые могут быть использованы для получения деталей тупиков. К ним относятся флаги трассировки 1204 и 1222. Вы также можете захватить событие графика взаимоблокировки с помощью SQL Profiler.
Лучший метод записи тупиков, когда мы подозреваем, - это настройка расширенного сеанса событий для регистрации графика взаимоблокировок для записи каждого события с подробными сведениями.
Начиная с SQL Server 2012 и далее, это можно сделать в среде SQL Server Management Studio в разделе Управление - > расширенные события для захвата тупиков. Используя расширенные события, вы сможете довольно легко увидеть, как часто в вашей базе данных возникают тупики.Кроме того, мы можем иметь график взаимоблокировок, доступный для каждого возникшего взаимоблокировки, чтобы помочь вам решить его.
Лучший способ избежать тупиковых ситуаций-попытаться удерживать замки как можно дольше. Убедитесь, что сеансу не придется ждать ввода данных Пользователем в середине транзакции, а сначала получить всю необходимую информацию.
Попробуйте ограничить эскалацию блокировки, используя подсказки, такие как ROWLOCK и т. д., или использовать Read COMMITTED SNAPSHOT ISOLATION или snapshot ISOLATION
Разрешение тупиков может нуждаться в анализе причин и мышлении для лучшего решения. Нет никакого способа решить проблему одним строгим способом, кроме как анализировать, изучать события, пробовать исправления, развертывать, чтобы жить.