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

Блокировки в SQL Server 2005, типы блокировок, эскалация блокировок (lock escalation), хинты NOLOCK, ROWLOCK, PAGLOCK и TABLOCK, sp_indexoption, ALTER INDEX ALLOW_PAGE_LOCKS, флаги трассировки 1211 и 1224

Очень важные возможности оптимизации SQL Server связаны с подсистемой работы с блокировками. Реализация этой подсистемы в SQL Server вызывает справедливые нарекания у специалистов, и для многих практических задач ручная настройка блокировок является необходимой.

В SQL Server предусмотрено пять главных уровней блокировок:

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

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

q      блокировки уровня экстента (EXT). Такие блокировки нечасто можно увидеть в SQL Server Management Studio или в результатах выполнения хранимой процедуры sp_lock. Они используются только при таких служебных операциях, как создание новых таблиц, увеличении размера файлов баз данных и т. п.;

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

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

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

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

Отметим некоторые технические моменты, связанные с эскалацией блокировок.

В большинстве ситуаций SQL Server изначально пытается использовать блокировки уровня записи или страницы. Какой именно уровень блокировок использовать — записи или страницы, определяется перед началом выполнения запроса. Решение об эскалации блокировок SQL Server принимает в двух ситуациях:

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

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

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

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

Внешне механизм эскалации выглядит очень логичным, однако на практике с ним возникают проблемы:

q      при выполнении операций, которые должны быть выполнены с большим количеством записей в таблице, SQL Server пытается вначале использовать блокировки уровня записи. В результате на установку и последующее снятие таких блокировок расходуется значительное количество системных ресурсов. Этого можно было бы избежать, если сразу применить для выполнения операции нужный уровень блокировок (PAG или TAB);

q      вторая, более важная проблема, заключается в том, что SQL Server применяет эскалацию блокировок, в том числе и на мощных серверах, с которыми одновременно работает большое число пользователей. Типичная ситуация выглядит таким образом: в базе данных есть большая таблица, с которой постоянно работают пользователи (назовем ее главной таблицей). За счет эскалации блокировок количество записей, которые одновременно блокируют пользователи, автоматически увеличивается, в результате чего другие пользователи не могут получить к ним доступ. Таким образом, при достижении определенного количества пользователей работа с этой таблицей резко затрудняется. Особенно неприятно то, что заблокированными оказываются те записи, с которыми пользователи на самом деле не работают: просто они попали на одну страницу с другими записями, открытыми в данный момент.

Для решения этих проблем в зависимости от ситуации можно использовать разные способы:

q      первая возможность, которая позволяет вообще избавиться об блокировок при запросах на чтение данных, — использовать соответствующий уровень изоляции транзакций. При операциях записи блокировки не налагаются при использовании уровней:

·                READ COMMITED, когда для параметра базы данных READ_COMMINTED_SNAPSHOT установлено значение ON;

·                READ UNCOMMITED;

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

Такие уровни изоляции вполне можно использовать для запросов со статистической информацией, когда абсолютной точностью информации можно пожертвовать ради производительности. Для финансовых отчетов такой способ может не подойти;

q      еще одно радикальное решение — перевести базу данных в состояние READ-ONLY. В этом случае при обращении к ней никакие блокировки использоваться не будут, что может дать очень большой выигрыш в производительности и повысить количество пользователей, которые могут работать с таблицей одновременно. Но по очевидным причинам такое решение подходит далеко не всегда;

q      если вам нужно изначально повысить уровень блокировки, то можно использовать хинты NOLOCK, ROWLOCK, PAGLOCK и TABLOCK в запросах. Минус такого подхода — не всегда есть возможность изменять код приложения;

q      другая возможность, менее известная, но вполне доступная администраторам, — применение команды ALTER INDEX для отключения блокировки на уровне страниц и записей (в SQL Server 2000 для этой цели можно было использовать хранимую процедуру sp_indexoption). Эту команду можно использовать для настройки уровня блокировок:

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

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

·                для таблицы, у которой нет кластерных индексов (данные находятся в "куче" — heap). Эта возможность является недокументированной, но ее вполне можно использовать.

Используя значение OFF для параметра ALLOW_PAGE_LOCKS в этой команде, вы можете избавиться от блокировок уровня страницы — главной причины проблем одновременного доступа пользователей.

В SQL Server 2005 появилась возможность настройки уровня блокировки на графическом экране. Для этого достаточно открыть свойства индекса в Object Explorer, перейти на вкладку Options и воспользоваться флажками Use row locks then accessing the index (Использовать блокировки записей при обращении к индексу) и Use page locks then accessing the index (Использовать блокировки страниц при обращении к индексу);

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

q      еще одна радикальная возможность — использовать флаги трассировки. Флаг 1211 просто отключает любую эскалацию блокировок. С этим параметром нужно быть очень осторожным, т. к. вы можете резко снизить производительность работы сервера и увеличить расход оперативной памяти. Кроме того, при исчерпании оперативной памяти, отводимой на блокировки, вы рискуете получить ошибку 1204. Второй флаг — 1224. Он отключает эскалацию блокировок, производимую по счетчику (5000 блокировок на нижнем уровне), однако при исчерпании места в области оперативной памяти, отводимой под блокировки, эскалация все равно будет происходить.

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

Получить учебные материалы по этому курсу