Миф о том, что эскалация блокировок происходит сначала с уровня строки на уровень страницы, а затем с уровня страницы на уровень таблицы

Автор: Paul Randal, A SQL Server DBA myth a day: (23/30) lock escalation

Миф №23: эскалация блокировок происходит сначала с уровня строки на уровень страницы, а затем с уровня страницы на уровень таблицы.

ЛОЖЬ

Нет, никогда. Эскалация блокировок в SQL Server  всегда переходит непосредственно к блокировке таблицы.

Начиная с SQL Server 2005, вы можете изменить поведение эскалации блокировок (если вы действительно знаете, что делаете), используя следующие флаги трассировки:

• 1211 – полностью отключает эскалацию блокировок и позволяет памяти блокировок вырасти до 60% динамически распределяемой памяти (не-AWE память для 32-разрядных систем и обычная память для 64-разрядных систем), после чего дальнейшие попытки блокировки будут завершаться ошибкой нехватки памяти.
• 1224 – отключает эскалацию блокировок до тех пор, пока не будет использовано 40% памяти, а затем снова включает эскалацию.

Флаг 1211 имеет приоритет над флагом 1224, если установлены оба — так что будьте вдвойне осторожны.

Начиная с SQL Server 2008, вы можете изменять поведение эскалации блокировок для каждой таблицы с помощью команды ALTER TABLE blah SET (LOCK_ESCALATION = XXX), где XXX может быть одним из:

• TABLE: всегда эскалировать непосредственно до блокировки таблицы.
• AUTO: если таблица секционирована, эскалировать до блокировки на уровне секции, но затем не эскалировать дальше.
• DISABLE: отключить эскалацию блокировок. Это не отключает блокировки таблиц — блокировка таблицы может потребоваться при некоторых обстоятельствах, например, при просмотре таблицы (table scan) кучи на уровне изоляции SERIALIZABLE.

Ещё в январе 2008 года я опубликовал в блоге пример настройки секционированной таблицы и демонстрации эскалации блокировок на уровне секции в действии — смотрите SQL Server 2008: Partition-level lock escalation details and examples.

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