Как эффективно использовать 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