Главная » Microsoft SQL Server, Базы данных » Концепция блокировок в SQL Server

0

SQL Server реализует свойство изоляции с помощью блокировок, которые защищают строки транзакций от влияния других транзакций. Блокировки SQL Server нельзя представлять себе только как состояния “страница заблокирована” и “страница разблокирована”. На самом деле все гораздо сложнее. И перед тем как начать управлять блокировками, нужно понять схему их действия.

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

Каждая блокировка обладает тремя свойствами.

?               Гранулярность, или размер блокировки.

?               Режим, или тип блокировки.

?               Продолжительность, или режим изоляции блокировки.

Нет ничего невозможного в том, чтобы увидеть блокировки, и некоторые приемы могут облегчить просмотр текущих блокировок. К тому же соревнование блокировок, т.е. способность различных типов блокировок допускать другие блокировки, может неблагоприятно сказаться на производительности, если этого не понимать и не управлять этим.

Гранулярность блокировок

Фрагмент данных, контролируемый блокировкой, может варьироваться от одной строки до всей базы данных (табл. 51.2). Некоторые комбинации блокировок, в зависимости от их гранулярности, могут удовлетворять требованиям сосуществования.

Таблица 51.2. Гранулярность блокировок

Размер блокировки

Описание

Блокировка строки

Блокировка одной строки. Наименьшая из возможных блокировок, поскольку SQL Server не блокирует столбцы

Блокировка страницы

Блокировка одной страницы размером в 8 Кбайт. На блокируемой странице может находиться одна или несколько строк

Блокировка экстента

Блокировка восьми страниц общим объемом в 64 Кбайт

Блокировка таблицы

Блокировка всей таблицы

Блокировка базы дан

Блокировка всей базы данных. Эта блокировка, как правило, используется при

ных

изменениях схемы

Блокировка ключа

Блокировка узлов индекса

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

1.              Блокировка 25 строк может быть расширена на блокировку целой страницы.

2.              Если 25 блокируемых строк распределены по четырем страницам одного и того же экстента, отдельные блокировки страниц и этих 25 строк могут быть заменены блокировкой всего экстента, так как в блокировках задействовано больше 50% страниц экстента.

3.              Если в блокировке задействовано множество экстентов, то весь этот набор блокировок может быть заменен блокировкой всей таблицы.

Динамическая блокировка дает разработчикам баз данных SQL Server массу преимуществ.

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

?               Производительность базы данных сохраняется даже при увеличении ее размеров за счет автоматического повышения диспетчером гранулярности блокировок.

?               Динамические блокировки упрощают администрирование.

Режимы блокировок

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

Соревнование блокировок

Взаимодействие и совместимость блокировок играют жизненно важную роль в обеспечении целостности транзакций в SQL Server и их производительности. Некоторые режимы блокировки делают невозможными другие; ниже приведена полная таблица совместимости блокировок (табл. 51.3).

Таблица 51.3. Совместимость блокировок

Блокировка 1

IS

Блокировка 2 запрашивает S U IX SIX

X

Намеренная общая (Intent Shared, или IS)

Да

Да

Да

Да

Да

Да

Общая (Shared, или S)

Да

Да

Да

Нет

Нет

Нет

Обновления (Update, или U)

Да

Да

Нет

Нет

Нет

Нет

Намеренная эксклюзивная (Intent Exclusive, или IX)

Да

Нет

Нет

Да

Нет

Нет

Общая с намеренно эксклюзивной (SIX)

Да

Нет

Нет

Нет

Нет

Нет

Эксклюзивная (X)

Нет

Нет

Нет

Нет

Нет

Нет

Общая блокировка (S)

Самой распространенной и часто неправильно используемой блокировкой является общая, которую часто называют блокировкой чтения. Если некоторая транзакция устанавливает общую блокировку, она тем самым как бы сообщает: “Я просматриваю эти данные”. Множество разных транзакций могут одновременно просматривать одни и те же данные, в зависимости от режима изоляции.

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

Эксклюзивная блокировка (X)

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

Блокировка обновления (U)

Название “блокировка обновления” может ввести в некоторое заблуждение. Она не применяется, когда транзакция выполняет обновление, — в этом процессе используется эксклюзивная блокировка. На самом деле блокировка обновления означает подготовку транзакции к установке эксклюзивной блокировки и выполнение сканирования данных для выявления строк, которые необходимо блокировать. Блокировку обновления можно трактовать как общую. которая готова превратиться в эксклюзивную.

Во избежание возникновения взаимоблокировок (о них мы поговорим позже в этой главе) в конкретный момент времени только одна транзакция может удерживать блокировку обновления.

Преднамеренные блокировки

Преднамеренную блокировку можно представить себе как желтый сигнал светофора, т.е. предупреждение других транзакций о том, что должно что-то произойти. Главной целью преднамеренной блокировки является повышение производительности. Так как преднамеренность используется во всех типах блокировок и во всех гранулярностях, SQL Server имеет несколько типов преднамеренных блокировок:

?               преднамеренная общая блокировка (IS);

?               преднамеренная эксклюзивная блокировка (IX);

?               общая с преднамеренной эксклюзивной блокировкой (SIX).

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

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

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

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

Преднамеренные блокировки также исключают возникновение проблем, связанных с соревнованием общих блокировок, — то. что я называю “постоянной блокировкой”. Пока одна транзакция поддерживает общую блокировку, другая транзакция не может установить эксклюзивную. Что случится, если некто будет устанавливать общую блокировку каждые пять секунд и удерживать ее на десять секунд, пока транзакция ожидает эксклюзивной блокировки? В этом случае транзакция UPDATE теоретически может ожидать вечность. В то же время, если транзакция установила преднамеренную эксклюзивную блокировку (IX), никакая другая транзакция не сможет установить общую блокировку. Преднамеренная эксклюзивная блокировка не является в полной мере эксклюзивной — это подготовка места для установки эксклюзивной блокировки.

Блокировка схемы (Sch-M, Sch-S)

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

Блокировка модификации схемы (Sch-M) применяется только тогда, когда SQL Server изменяет физическую схему базы данных. Если SQL Server выполнил операцию добавления столбца таблицы только наполовину, блокировка схемы предотвратит просмотр и модификацию данных другими транзакциями до завершения операции изменения схемы.

Продолжительность блокировки

Третье свойство блокировки — ее продолжительность — определяется уровнем изоляции. Чем строже изоляция, тем дольше удерживается блокировка. SQL Server реализует все четыре перечисленные выше уровня изоляции транзакций. Абсолютный уровень изоляции (serialization) создает самые строгие блокировки. С другой стороны, самый слабый уровень изоляции (read uncommited) эффективно отключает блокировку (табл. 51.4).

Таблица 51.4. Уровни изоляции и продолжительность блокировки

Уровень изоляции

Продолжительность общей блокировки

Продолжительность эксклюзивной блокировки

Read Uncommited

Отсутствует

Удерживается достаточно долго, чтобы избежать физического искажения; в противном случае эксклюзивная блокировка ни применяется, ни обеспечивается

Read Commited

Удерживается во время чтения данных

Удерживается до подтверждения транзакции

Repeatable Read

Удерживается до подтверждения транзакции

Удерживается до подтверждения транзакции

Serializable

Удерживается до подтверждения транзакции

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

Snapshot

Нет данных

Нет данных

Мониторинг блокировок

За неимением возможности просмотреть блокировки может показаться, что различные типы блокировок — чистая теория. К счастью, SQL Server является относительно открытой средой, и в ней легко инспектировать текущие блокировки.

Утилита Activity Monitor является прекрасным графическим инструментом про- Новинка ^ смотра и отслеживания блокировок.

2005

Использование Management Studio

В Management Studio можно просмотреть транзакции выбранной базы данных, используя страницу Summary, на которую помещаются данные из динамических представлений управления. Среди отчетов, связанных с транзакциями, следующие: All Transactions, All Blocking Transactions (рис. 51.9), Top Transactions by Age, Top Transactions by Blocked Transaction Count. Top Transactions by Lock Count, Resource Locking by Object и User Statistics.

Использование Activity Monitor

Утилита Activity Monitor (рис. 51.10) представляет собой инструмент отслеживания блокировок в разрезе процессов и объектов. Ее окно можно обновлять вручную или автоматически каждые пять секунд. Activity Monitor можно открыть из окна Object Explorer, выбрав в контекстном меню сервера пункт Managements SQL Server Logs.

Рис. 51.9. Страница Summary утилиты Management Studio открывает простой способ просмотра ключевой информации о блокировках транзакций

Puc. 51.10. Утилита Activity Monitor отображает массу информации о текущих блокировках

Использование утилиты Profiler

SQL Server Profiler можно использовать для просмотра блокированных процессов, используя событие Error and Warnings:Blocked Process Report (рис. 51.11).

Puc. 51.11. SQL Server Profiler позволяет осуществлять мониторинг блокировок и вызвавшего их кода в формате XML

Из множества доступных методов мониторинга блокировок Activity Monitor и страница Summary утилиты Management Studio являются лучшим способом определения, когда блокировки являются источником проблем. Для локализации источника проблемы Profiler предлагает просмотр фактического кода транзакций.

Тонкость заключается в том, что по умолчанию это событие в Profiler отключено. Для его включения необходимо сконфигурировать параметр the blocked process threshold. К тому же это дополнительный параметр, который следует предварительно включить. В следующем фрагменте кода устанавливается продолжительность блокировок в одну секунду:

sp_c°nfigure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘blocked process threshold1, 1;

GO

RECONFIGURE;

Результатом является полное описание всех блокированных и блокирующихся процессов в формате XML (рис. 51.11). Сохранение этой трассировки в файле и ее последующий анализ является прекрасной методикой отладки блокировок.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии