SQL 2008 SP - причина тупика или красная сельдь?

Question

У одного из моих коллег есть хранимая процедура, которая делает следующее
Начни транс
1) Динамически генерировать оператор выбора.
2) Вставить в таблицу х
3) Выполнить оператор выбора
Конец тран

Если эта хранимая процедура запускается двумя отдельными потоками одновременно, он получает следующую ошибку: System.Data.SqlClient.SqlException: транзакция (ID процесса 57) заблокирована при блокировке | ресурсы буфера связи с другим процессом и были выбраны в качестве жертвы тупика. Перезапустите транзакцию

Является ли эта хранимая процедура действительно проблемой? По моему наивному разуму, это выглядит в худшем состоянии гонки, а не в тупике.

Answer 1

Две последовательности "запись, затем чтение" могут определенно зайти в тупик. Вы пропустили некоторые "детали" в своем сообщении, например, о том, что является фактическим ресурсом, на котором возникает тупик, и что запросы связаны. Мы пролетим мимо штанов и будем читать между строк, составляя большую часть материала из такого плохо документированного поста:

1. Перекрестная запись-чтение. Поток 1 вставляет строку с ключом A, а затем выбирает строку с ключом B. Поток 2 вставляет строку с ключом B, затем выбирает строку с ключом A. Порядок выполнения: T1(A), T2(B), T1(B)- ожидание, T2 (А)-deadlock.
2. Независимая запись-чтение: T1 вставляет A, затем читает A, T2 вставляет B, затем читает B. Критическая информация: на ключе нет индекса, поэтому для считывания A и / или B необходимо сканирование таблицы. Порядок выполнения: T1 пишет A, T2 пишет B, T1 читает A, начинает сканирование, блокирует X-блокировку T2 на B, T2 читает B, начинает сканирование, блокирует X-блокировку T1 на A, тупик.
3. Независимая оптимизированная запись-чтение. Этот случай является самым неприятным для большинства новичков, когда имеются надлежащие индексы доступа, но все еще возникают тупики. Я описал этот случай в тупике чтения / записи, обновление может заблокировать чтение при чтении из-за разного порядка доступа к индексу. Вряд ли это ваш случай, но с такой плохой документацией все возможно.

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

Если бы я рискнул предположить, наиболее вероятный случай - 2). Случай 1), вероятно, будет легко идентифицировать. Случай 2) немного сложнее обнаружить при простом анализе кода, поскольку он зависит от физической схемы (структуры индекса).

Answer 2

Запустите трассировку в профилировщике (выберите пустой шаблон), выберите событие графика взаимоблокировки и на новой вкладке (Настройки извлечения событий) сохраните каждое (отметьте отдельно сохранение событий тупикового XML) в своем собственном файле. Откройте этот файл в программе просмотра XML, и вам будет легко узнать, что происходит. Каждый процесс содержится со стеком вызовов процедур и т. Д., И все блокировки также присутствуют там, так что вы можете быть уверены, что вызывает тупик.

Пусть эта трассировка запускается до тех пор, пока снова не произойдет взаимоблокировка, информация записывается только тогда, когда происходит взаимоблокировка, поэтому не нужно много накладных расходов.