Как эффективно использовать LOCK_ESCALATION в SQL Server 2008

В настоящее время у меня возникают проблемы с частыми взаимоблокировками с конкретной пользовательской таблицей в SQL Server 2008. Вот некоторые факты об этой конкретной таблице:

Имеет большое количество строк (от 1 до 2 миллионов)
Все индексы, используемые в этой таблице, имеют блокировку строк ", отмеченные в их параметрах Изменить: в таблице есть только один индекс, который является его основным ключом Строки
часто обновляются несколькими транзакциями, но являются уникальными (например, возможно, что тысячи или несколько операторов обновления выполняются для разных уникальных строк каждый час)
таблица не использует разделы.

После проверки таблицы на sys.tables я обнаружил, что для параметра lock_escalation установлено значение TABLE

У меня очень соблазн включить lock_escalation для этой таблицы в DISABLE, но я не совсем уверен, какой побочный эффект это принесет. Из того, что я понимаю, использование DISABLE позволит свести к минимуму эскалацию блокировок с уровня TABLE, который в сочетании с настройками блокировки строк индексов должен теоретически минимизировать взаимоблокировки, с которыми я сталкиваюсь.

Из того, что я прочитал в Определение порога для эскалации блокировки, кажется, что блокировка автоматически возрастает, когда одна транзакция извлекает 5000 строк.

Что означает одна транзакция в этом смысле? Один сеанс/соединение, получающий 5000 строк через отдельные операторы update/select?

Или это один оператор sql update/select, который извлекает 5000 или более строк?

Понятно понимать любую проницательность, btw, n00b DBA здесь

Спасибо

Ответ 1

LOCK Эскалация срабатывает, когда оператор содержит более 5000 блокировок для объекта SINGLE. Заявление, содержащее 3000 блокировок на двух разных индексах одной и той же таблицы, не приведет к эскалации.

При попытке блокировки блокировки и существует конфликтная блокировка объекта, попытка прерывается и повторится после следующих 1250 блокировок (удерживаемых, не полученных)

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

Вы сможете проверить это, используя событие блокировки блокировки блокировки от профайлера.

Я предлагаю вам зафиксировать след тупика, чтобы определить фактическую причину тупика.

Ответ 2

Я нашел эту статью после того, как быстро Google отключил эскалацию блокировки таблиц. Хотя это не настоящий ответ для OP, я думаю, что он по-прежнему имеет отношение к одному сценарию и примечателен здесь. Там хороший небольшой трюк, который вы можете сделать, чтобы временно отключить эскалацию блокировки таблиц.
Откройте другое соединение и введите что-то вроде.

BEGIN TRAN
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
COMMIT TRAN

как

Эскалация блокировки не может произойти, если другой SPID в настоящее время удерживается несовместимая блокировка таблицы.

из microsoft kb