logo search
SQL2008_Administration

8.4.7. Работа с блокировками

Уровни блокировки в SQL Server:

  1. Блокировка уровня базы данных (DB). Такие блокировки автоматически накладываются на любую базу данных, к которой подключен пользователь;

  2. Блокировки уровня объекта (например, TAB). Такие блокировки могут накладываться на таблицу или индекс, как при выполнении обычных запросов, так и при выполнении служебных операций с этими объектами;

  3. Блокировки уровня экстента (EXT). Они используются только при выполнении служебных операций (создание новых таблиц, увеличение размера файлов баз данных и т.п.);

  4. Блокировки уровня страницы (PAG). При их применении блокируется вся страница размером 8 Кбайт, со всеми записями, которые в ней находятся. Такой тип блокировки может применяться как для страниц данных, так и для страниц индексов;

  5. Блокировки уровня записи/ключа (RID/KEY). Такие блокировки накладываются на отдельные записи. Блокировки типа RID накладываются на записи в таблицы без кластерного индекса (heap), а блокировки типа KEY — на записи в таблицах, для которых предусмотрен кластерный индекс.

Эскалация блокировок

SQL Server управляет уровнем блокировок автоматически, пытаясь вначале использовать только блокировки на уровне записи. В случае необходимости SQL Server может принять решение об использовании блокировок более высокого уровня. Такое повышение уровня называется эскалацией блокировок (lock escalation).

Решение об эскалации блокировок SQL Server принимает в двух ситуациях:

  1. Когда запрос пытается применить к одному объекту (таблице или индексу) более 5000 блокировок на уровне записи или страницы. При этом SQL Server никогда не производит эскалацию с уровня записи до уровня страницы, а сразу пытается наложить блокировку на таблицу;

  2. Когда место в области ОП, отведенной на работы с блокировками, заканчивается. На каждую блокировку SQL Server отводит 96 байт. Размер области памяти для работы с блокировками SQL Server по умолчанию (при установленном параметре сервера Lock, равном 0) устанавливается динамически. Как только размер этой области достигает 40 процентов от общего размера памяти, которую использует процесс SQL Server (она ограничивается операционной системой или параметром MAX SERVER MEMORY), то SQL Server автоматически пытается произвести эскалацию блокировок. Если размер области оперативной памяти достиг 60 процентов от объема памяти, максимально доступной SQL Server, создание новых блокировок не производится, а клиенту возвращается ошибка 1204 (unable to allocate lock resource — невозможно разместить ресурсы блокировки).

Мониторинг событий эскалации блокировок

Мониторинг событий эскалации блокировок можно производить при помощи профилировщика, выбрав в нем событие Lock:Escalation.

Решение проблем эсклации блокировок:

  1. Использовать соответствующий уровень изоляции транзакций – таким образом можно вообще избавиться от блокировок при запросах на чтение данных. Блокировки при операциях записи не налагаются при использовании уровней:

    1. READ COMMITED - для параметра базы данных READ_COMMINTED_SNAPSHOT установлено значение ON;

    2. READ UNCOMMITED;

    3. SNAPSHOT (новый уровень изоляции, который появился в SQL Server 2008).

  2. Перевести базу данных в состояние Read-only (Только чтение). В этом случае при обращении к ней никакие блокировки использоваться не будут, что может дать очень большой выигрыш в производительности и повысить количество пользователей, которые могут работать с таблицей одновременно;

  3. Если изначально нужно повысить уровень блокировки, то можно использовать хинты NOLOCK, ROWLOCK, PAGLOCK и TABLOCK в запросах;

  4. Использовать хранимую процедуру SP_INDEXOPTION (в SQL Server 2008 вместо нее рекомендуется использовать команду ALTER INDEX). При помощи этой процедуры можно вообще отключить блокировку на уровне страниц и записей: SQL Server будет автоматически применять блокировку на уровне объекта. Эту хранимую процедуру можно использовать для настройки уровня блокировок:

    1. для некластерного индекса;

    2. для кластерного индекса (эти же настройки будут одновременно применены для таблицы);

    3. для таблицы, у которой нет кластерных индексов (передать этой хранимой процедуре вместо имени индекса имя таблицы);

Чтобы избавиться от блокировок уровня страницы необходимо установить значение True для параметра DisAllowPageLocks в этой хранимой процедуре.

  1. Использовать графический интерфейс SQL Server 2008 для настройки уровня блокировки:

    1. открыть свойства индекса в Object Explorer,

    2. перейти на вкладку Options (Параметры) и воспользоваться флажками Use row locks then accessing the index (Использовать блокировки записей при обращении к индексу) и Use page locks then accessing the index (Использовать блокировки страниц при обращении к индексу).

  2. Настроить вручную размер области блокировки в памяти (если места выделено недостаточно). Для этой цели можно использовать параметр конфигурации сервера Lock, а также попробовать увеличить количество оперативной памяти, доступной SQL Server, при помощи увеличения физической оперативной памяти на сервере или при помощи параметра MAX SERVER MEMORY;

  3. Использовать флаги трассировки:

    1. Флаг 1211 отключает любую эскалацию блокировок. С этим параметром нужно быть очень осторожным, так как вы можете резко снизить производительность работы сервера и увеличить расход оперативной памяти. Кроме того, вы рискуете при исчерпании оперативной памяти, отводимой на блокировки, получить ошибку 1204.

    2. Флаг 1224 отключает эскалацию блокировок, производимую по счетчику (5000 блокировок на нижнем уровне), однако при исчерпании места в области оперативной памяти, отводимой под блокировки, эскалация все равно будет происходить.

Если одновременно установить флаги 1224 и 1211, то приоритет будет отдан флагу 1224.