Может ли добавление столбца идентификатора первичного ключа решить проблемы взаимоблокировки?
У меня есть таблица в SQL-сервере, которая одновременно обрабатывается CRUD с помощью хранимой процедуры, выполняющейся одновременно в разных сеансах:
|----------------|---------|| <some columns> | JobGUID ||----------------|---------|
Процедура работает следующим образом:
- Создайте GUID.
- Вставьте некоторые записи в общую таблицу, описанную выше, пометив их GUID из шага 1.
- Выполните несколько обновлений для всех записей, начиная с шага 2.
- Выберите записи из шага 3 в качестве вывода SP.
Каждый оператор select / insert / update / delete в хранимой процедуре имеет WHERE JobGUID = @jobGUID Таким образом, процедура работает только с записями, вставленными на шаге 2. Однако иногда, когда одна и та же хранимая процедура выполняется параллельно в разных соединениях, в общей таблице возникают взаимоблокировки. Вот график взаимоблокировок от SQL Server Profiler:
Эскалации блокировки не происходят. Я пытался добавить (UPDLOCK, ROWLOCK) блокировка подсказок для всех операторов DML и / или перенос тела процедуры в транзакции с использованием разных уровней изоляции, но это не помогло. Все та же блокировка RID для общей таблицы.
После этого я обнаружил, что в общей таблице нет столбца первичного ключа / идентификатора. И как только я добавил это, тупики, кажется, исчезли:
alter table <SharedTable> add ID int not null identity(1, 1) primary key clustered
Когда я удаляю столбец первичного ключа, взаимоблокировки возвращаются. Когда я добавляю его обратно, я больше не могу воспроизвести тупик.
Итак, вопрос в том, действительно ли столбец идентификаторов первичного ключа действительно способен разрешать тупики или это просто совпадение?
Обновление: как подсказывает Mike Sherrill 'Cat Recall', я попытался создать естественный кластерный первичный ключ для существующих столбцов (без добавления столбца идентификаторов), но все же поймал ту же тупиковую ситуацию (конечно, на этот раз это была блокировка ключа вместо блокировки RID).).
2 ответа
Лучший (до сих пор) ресурс для разрешения тупиковых ситуаций находится здесь: http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx.
Часть 4 говорит:
Запустите запросы, связанные с тупиком, через помощник по настройке базы данных. Поместите запрос в окно запроса Management Studio, измените контекст базы данных на правильную базу данных, щелкните правой кнопкой мыши текст запроса и выберите "Анализ запроса в DTA". Не пропустите этот шаг; более половины проблем с блокировками, которые мы видим, решаются простым добавлением соответствующего индекса, чтобы один из запросов выполнялся быстрее и с меньшим объемом блокировки. Если DTA рекомендует индексы (там будет написано "Ожидаемое улучшение: %"), создайте их и следите, чтобы убедиться, что тупик сохраняется. Вы можете выбрать "Применить рекомендации" в раскрывающемся меню "Действие", чтобы немедленно создать индекс, или сохранить команды "CREATE INDEX" в виде сценария, чтобы создать их во время окна обслуживания. Обязательно настройте каждый из запросов отдельно.
Я знаю, что это не "отвечает" на вопрос "почему", но показывает, что добавление индексов может изменить выполнение таким образом, чтобы уменьшить занимаемую площадь блокировки или ускорить время выполнения, что может значительно снизить вероятность возникновения тупика.
Недавно я видел этот пост, в соответствии с вышеуказанной информацией, я надеюсь, что этот пост поможет вам,
http://databaseusergroup.blogspot.com/2013/10/deadlocked-on-sql-server.html