SQL Server Поиск закладок Решение тупиковой ситуации
Я обнаружил тупик поиска закладок в своем приложении и не могу решить, какое решение использовать. Ни один из них не кажется оптимальным.
Вот запросы:
UPDATE TEST SET DATA = @data WHERE CATEGORY = @cat
SELECT DATA, EXTRA_COLUMN FROM TEST WHERE CATEGORY = @cat
Проблема заключается в том, что в CATEGORY и DATA существует некластеризованный индекс, который используется обоими запросами в обратном порядке с кластеризованным индексом.
То есть: обновление блокирует кластеризованный индекс и обновляет таблицу, в то время как select блокирует некластеризованный индекс для поиска закладок, и они оба хотят блокировки друг друга (взаимоблокировка).
Вот варианты, которые я нашел:
1 - Создайте индекс, который включает все столбцы из запроса выбора. - Это сработало, но я не думаю, что это хорошая идея, я должен был бы включить любой столбец, который используется в любом выбранном запросе, который можно обновить в любом месте приложения.
2 - Измените уровень изоляции транзакции базы данных на COMMITTED_SNAPSHOT.
3 - Добавить подсказку NOLOCK к выбранному
4 - опустить индекс
5 - принудительно заблокировать одну из транзакций на более раннем этапе, прежде чем она получит возможность получить блокировку, которая в итоге блокирует другую транзакцию. (Не работал)
Я думаю, что второй вариант - лучший выбор, но я знаю, что он может создавать другие проблемы, не должен ли COMMITTED_SNAPSHOT быть уровнем изоляции по умолчанию в SQL SERVER?
Мне кажется, что нет никаких ошибок ни в приложении, ни в логике базы данных, это одна простая таблица с некластеризованным индексом и два запроса, которые обращаются к одной и той же таблице, один для обновления, а другой для выбора.
Какой лучший способ решить эту проблему? Есть ли другое решение?
Я действительно ожидал, что SQL Server сможет решить его самостоятельно.
2 ответа
Изоляция моментальных снимков - очень надежное решение для удаления операций чтения из уравнения. Многие РСУБД имеют их всегда. Они не вызывают много проблем на практике. Предпочитайте это решение некоторым хрупким ручным решениям, таким как очень специфические индексы или подсказки.
Попробуйте добавить некластеризованный индекс в категорию (включая Data & Extra_Column) и добавить следующие подсказки к своим запросам:
UPDATE t SET t.DATA = @data FROM TEST WITH (index(ix_Cat)) WHERE CATEGORY = @cat
SELECT DATA, EXTRA_COLUMN FROM TEST WITH (index(ix_Cat)) WHERE CATEGORY = @cat
Это обеспечит, чтобы оба запроса обновляли / выбирали данные в одном и том же порядке, а также предотвращали взаимную блокировку.