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

Принятым по умолчанию уровнем изоляции в SQL Server является Зафиксированное чтение (Committed Read), но в приложении можно назначить вместо этого уровня другой. Самый прямолинейный способ сделать это - воспользоваться командой SET:

В предыдущих версиях SQL Server уровни изоляции Повторяемое чтение (Repeatable Read) и Упорядочиваемый (Serializable) воспринимались как синонимы, так как у системы не было механизмов, способных их различить. Предположим, что при работе в режиме Повторяемого чтения (Repeatable Read) был запущен на выполнение следующий запрос:

Режим Повторяемое чтение (Repeatable Read) обеспечивал отсутствие изменений в читаемых строках в процессе выполнения данной транзакции. Другими словами, режим Повторяемое чтение (Repeatable Read) гарантировал, что при повторном чтении будут получены те же самые результаты. SQL Server не мог заблокировать отдельные строки, которые отвечали введенному критерию, поэтому он блокировал страницу или даже всю таблицу. Подобная блокировка по существу устанавливала для рассматриваемой транзакции режим Упорядочиваемый (Serializable), поскольку блокировка страницы или таблицы не давала возможности другим процессам вставить строку, в которой стоимость равнялась бы, к примеру, 12 долларам.

В версии SQL Server 7.0 поддерживается настоящий уровень изоляции Повторяемое чтение (Repeatable Read). В действительности уровень Повторяемое чтение (Repeatable Read) не должен исключать подобные вставки; он предотвращает лишь изменение читаемых данных. Только уровень изоляции Упорядочиваемый (Serializable) не разрешает вводить новые строки в диапазон. Таким образом, реализация действительного уровня изоляции Повторяемое чтение (Repeatable Read) была невозможна до тех пор, пока не появились блокировки строк в версии SQL Server 7.0.

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

SQL Server автоматически запрашивает разделяемую блокировку при любом чтении данных. Таблица, страница, ключ индекса, отдельная строка - все эти объекты могут быть закрыты разделяемой блокировкой. В дополнение к этому SQL Server может накладывать блокировку на диапазоны ключей индекса. Это делается с целью реализации режима Упорядочиваемый (Serializable) при изоляции транзакций. Одновременно несколько процессов могут наложить разделяемую блокировку на одни и те же данные. Но ни один процесс не может потребовать исключающую блокировку для данных, которые закрыты разделяемой блокировкой. Исключение может быть сделано только для процесса, который наложил на эти данные текущую разделяемую блокировку. Разделяемая блокировка снимается сразу же, как только SQL Server прочтет информацию. Для того чтобы изменить такое поведение, следует использовать другие уровни изоляции транзакций - либо Упорядочиваемый (Serializable), либо Повторяемое чтение (Repeatable Read).

SQL Server автоматически назначает исключающую блокировку, когда собирается изменить данные через команды Вставить (Insert), Обновить(Update) или Удалить(Delete). Наложить исключающую блокировку на определенный ресурс данных в каждый момент может только один процесс. Исключающая блокировка остается в силе до завершения транзакции. Поэтому изменяемые данные недоступны любым другим процессам до тех пор, пока текущая транзакция или успешно завершится, или выполнит откат. При использовании уровня изоляции транзакций Читать незафиксированное (Read Uncommitted) другие процессы могут прочитать данные, закрытые исключающей блокировкой.

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

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

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

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

В таблицу Syslockinfo записывается информация о типе заблокированного ресурса (это может быть, например, таблица, ключ или страница), о режиме блокирования и о конкретном идентификаторе ресурса. Тем самым каждая блокировка подробно отслеживается. Когда процесс запрашивает блокировку, SQL Server сравнивает предоставляемые при этом сведения с информацией о зафиксированных ранее в таблице Syslockinfo блокировках и ищет случаи точного совпадения типа и идентификатора ресурса. (Режимы блокировок могут различаться.) Предположим для примера, что один процесс наложил исключающую блокировку на строку в таблице Authors, а другой процесс пытается наложить блокировку на всю эту таблицу. Поскольку строки и таблицы относятся к разным типам ресурсов, SQL Server не обнаружит точного совпадения и не определит конфликт. Другое дело, если в таблице Syslockinfo содержится информация о намеренной блокировке, означающей, что строка таблицы уже находится в состоянии исключающей блокировки. Предположим далее, что процесс с исключающей блокировкой строки в таблице Authors, наложил намеренно исключающие блокировки на страницу и на таблицу, содержащие эту строку. В этой ситуации когда второй процесс сделает попытку объявить исключающую блокировку на таблицу Authors, система найдет в таблице Syslockinfo строку с блокировкой того же самого ресурса (таблицы Authors) и обнаружит конфликт.

SQL Server 7.0 поддерживает два вида блокировок ключей в зависимости от уровня изоляции текущей транзакции. Если установлены уровни изоляции Зафиксированное чтение (Committed Read) или Повторяемое чтение (Repeatable Read), то SQL Server делает попытку заблокировать ключи индекса, к которым он обращается при обработке запроса. Если таблица снабжена кластеризованным индексом, SQL Server запрашивает блокировку ключа, так как строки данных находятся на уровне листьев дерева индекса. Если же таблица представляет собой неупорядоченный массив (то есть не имеет кластеризованного индекса), SQL Server может запросить блокировку ключа для некластеризованного индекса и блокировку строк для данных.

Если установлен уровень изоляции Упорядочиваемый (Serializable), то наступает особая ситуация. Когда в ходе транзакции сканируется диапазон данных, необходимо закрыть блокировкой достаточную часть таблицы, чтобы гарантировать отсутствие каких-либо вставок новых значений в сканируемый диапазон. В противном случае при повторении запроса вставленное значение появится подобно призраку. Продемонстрируем это на следующем примере. Пусть имеется индекс по фамилиям служащих для таблицы Employee, работа происходит в режиме Упорядочиваемый (Serializable), и в текущей транзакции запускается такой оператор SELECT:

Если MacAndrews, MacWorter и McKenna встречаются в дереве индекса таблицы на уровне листьев, то каждый из ключей MacWorter и McKenna требует объявления блокировки на диапазон значений. Блокировка диапазона ключа подразумевает блокирование всех записей, начиная с предшествующего значения ключа и заканчивая заблокированным ключом. Тогда никто не сможет вставить записи в этот диапазон. Так, в рассматриваемом нами примере один диапазон значений начинается с MacAndrews и заканчивается на MacWorter, а другой интервал начинается с ключа MacWorter и завершается значением McKenna. Эти два интервала значений ключа предотвращают вставку значений, превышающих MacAndrews и меньших или равных MacWorter, а также больших, чем MacWorter, но меньших или равных McKenna. Другими словами, эти два диапазона не дадут вставить записи для MacOwen или McBride, которые попадают в интервал значений, указанных в обороте WHERE. Однако при этом не удастся также вставить и значение MacBryde, хотя MacBryde и не удовлетворяет введенному условию. Конечно, блокировки диапазона ключа не совершенны. Но все же они позволяют в значительно большей степени распараллелить работу, чем при блокировке целиком страницы и тем более таблицы. А ведь в предшествующих версиях SQL Server существовали только такие блокировки.

Для того чтобы видеть одновременно и уже введенные системой на данный момент времени блокировки, и те, на которые только поступила заявка, можно либо просматривать таблицу Syslockinfo, либо запустить процедуру sp_lock. (Таблица Syslockinfo не является в полном смысле слова системной таблицей. SQL Server не поддерживает эту таблицу на диске, так как он не поддерживает блокировки на диске. Точнее будет сказать, что Syslockinfo появляется в формате таблицы каждый раз, когда процесс запрашивает Syslockinfo. При этом в ней находится информация о блокировках, полученная от диспетчера блокировок Lock Manager). Другой способ наблюдения за блокировками - воспользоваться прекрасным графическим представлением состояния блокировок, которое обеспечивает SQL Enterprise Manager.

Приводимые далее примеры демонстрируют, как применять процедуру sp_lock для всех типов блокировок при каждом уровне изоляции транзакций. Обратите внимание на то, что в коде sp_lock ключевое слово EXECUTE предшествует вызову процедуры sp_lock. Это ключевое слово требуется в тех случаях, когда вызов хранимой процедуры не является первым оператором в пакете. Заметьте также, что в качестве аргумента процедуры sp_lock использован параметр @@spid, который идентифицирует текущий процесс на сервере (server process ID). Это специально сделано для того, чтобы просматривать не все системные блокировки вообще, а только те, что относятся к вашему процессу.

Терминология, применяемая в выходных данных процедуры sp_lock способна привести к недоразумениям. Часто термины «тип блокировки» и «режим блокирования» используются как синонимы, они взаимно заменимы при определении того, является ли блокировка разделяемой или исключающей. Но в выходных данных процедуры sp_lock столбец Тип () содержит сведения о типе блокируемого ресурса, то есть о степени детализации блокировки. Значение для заблокированного ресурса может появляться в различных местах выходных данных процедуры sp_lock. Если заблокирована база данных целиком, то идентификатор базы данных появится в столбце Идентификатор базы данных (Dbid). Для заблокированной таблицы значение будет содержаться в столбце Идентификатор объекта (Objid). Идентификатор заблокированной страницы состоит из двух частей - идентификатора файла и идентификатора страницы. Он размещается в столбце Ресурс (Resource). Например, обозначение 1:123 относится к странице 123 из первого файла. Идентификатор заблокированной строки содержит три составляющие - идентификатор файла, идентификатор страницы и идентификатор сегмента на странице. Все эти компоненты размещаются в столбце Ресурс (Resource).

Блокировки баз данных при уровне изоляции Зафиксированное чтение (Read Committed). Каждый раз, когда запускается процедура sp_lock, она показывает выходные данные, относящиеся к базе данных master. Если взглянуть на столбец в результатах, приведенных на листинге 1, то увидим блокировки в базе данных 1 (Master) и в базе данных 2 (Tempdb). Кроме того, SQL Server запрашивает дополнительные блокировки базы данных уровня сессии в той базе данных, которая в данный момент является текущей. Этот уровень блокирования можно увидеть в третьей строке результатов, где идентификатор базы данных равен 5, а тип блокировки соответствует базе данных. В последующих примерах блокировки баз данных больше не будут приводиться. Отсутствие блокировок таблицы Authors в данный момент объясняется тем, что выполняемый пакет содержал лишь операторы SELECT, которые сопровождаются только разделяемыми блокировками. По умолчанию разделяемые блокировки снимаются, как только данные будут прочитаны, поэтому ко времени исполнения процедуры sp_lock все блокировки уже были отработаны.

Блокировки ключей и разделяемые блокировки при уровне изоляции Повторяемое чтение (Repeatable Read). В силу того что база данных Authors имеет кластеризованный индекс, все строки данных являются строками индекса на уровне листьев. Как показывает листинг 2, выходные данные представляют блокировки отдельных строк как блокировки ключа, а не блокировки записей. Но блокировки ключей также находятся на уровне листьев для не кластеризованных индексов таблиц. Различить эти два вида блокировок можно по значению, хранящемуся в столбце Indid: у строк данных оно равно 1, а у строк индекса это значение равно 2. (Ваши собственные не кластеризованные индексы могут получить любое значение от 2 до 250.) Поскольку уровень изоляции соответствует Повторяемому чтению (Repeatable Read), разделяемые блокировки сохраняются до окончания транзакции. Заметьте, что две строки данных и две строки индекса закрыты разделяемыми блокировками (S -shared), а блокировки страницы данных, страницы индекса и таблицы относятся к намеренно разделяемому типу (IS – intent-shared).

Блокировки диапазона ключа при уровне изоляции Упорядочиваемый (Serializable). Приведенные на листинге 3 блокировки, относящиеся к уровню изоляции Упорядочиваемый (), подобны блокировкам уровня Повторяемого чтения (), представленным на листинге 2. Основное различие заключается в режиме блокирования. Состоящее из двух частей обозначение IS-S определяет не только блокировку ключа, но и намеренную блокировку диапазона значений ключа. Первая часть (IS) относится к блокировке диапазона значений, начиная с предшествующего значения ключа в индексе и заканчивая заблокированным (S) значением (включительно). Построенному по столбцу c фамилией автора au_lname не кластеризованному индексу (Indid = 2) в таблице соответствуют три записи, так как SQL Server заблокировал три различных диапазона значений ключей.

Исключающие блокировки при уровне изоляции Зафиксированное чтение (Read Committed). Как показано на листинге 4, две строки уровня листьев кластеризованного индекса имеют исключающие блокировки (Х). Страница и таблица закрыты намеренно -исключающими блокировками (IX). Хотя SQL Server запрашивает блокировки обновления тогда, когда он ищет подлежащие обновлению строки, эти блокировки перерастают в исключающие блокировки еще до того, как операция обновления начнет выполняться. Ко времени исполнения процедуры sp_lock блокировки обновления уже завершились.

Блокировки строк при уровне изоляции Зафиксированное чтение (Read Committed). Упомянутая в листинге 5 таблица newTitles не содержит индексов, поэтому блокировка отвечающей критерию записи является исключающей блокировкой строки (RID). Как ожидалось, блокировки типа IX наложены на страницу и таблицу.

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

Кэйлен Дилани (kalen_delaney@compuserve.com, http://www.insidesqlserver.com/) обладает сертификатами MCT и MCSE. Она работает независимым консультантом и преподавателем на северо-западе тихоокеанского побережья США. Она работает с SQL Server с 1987 года. Кэйлен написала книгу «Внутри SQL Server 7.0» (Inside SQL Server 7.0), выпущенную издательством Microsoft Press; она также соавтор книг «SQL Server 6.5 без границ» (SQL Server 6.5 Unleashed) и «Изучи SQL Server за 21 день» (Teach yourself SQL Server in 21 days), изданных в Sams Publishing.

Выходные данные процедуры sp_lock:
/Заголовки столбцов таблицы/
Spid - идентификатор процесса
Dbid - идентификатор базы данных
Objid - идентификатор объекта
Indid - индивидуальный идентификатор
Тип - тип ресурса (степень детализации)
Ресурс - идентификатор ресурса
Режим - режим блокирования
Статус - статус блокировки

Выходные данные процедуры sp_lock:
/Заголовки столбцов таблицы/
Spid - идентификатор процесса
Dbid - идентификатор базы данных
Objid - идентификатор объекта
Indid - индивидуальный идентификатор
Тип - тип ресурса (степень детализации)
Ресурс - идентификатор ресурса
Режим - режим блокирования
Статус - статус блокировки

Выходные данные процедуры sp_lock:
/Заголовки столбцов таблицы/
Spid - идентификатор процесса
Dbid - идентификатор базы данных
Objid - идентификатор объекта
Indid - индивидуальный идентификатор
Тип - тип ресурса (степень детализации)
Ресурс - идентификатор ресурса
Режим - режим блокирования
Статус - статус блокировки
USE PUBS SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM authors WHERE au_lname = `Ringer` EXEC sp_lock @@spid COMMIT TRAN Листинг 4. Исключающие блокировки при уровне изоляции Зафиксированное чтение (Read Committed)

Выходные данные процедуры sp_lock:
/Заголовки столбцов таблицы/
Spid - идентификатор процесса
Dbid - идентификатор базы данных
Objid - идентификатор объекта
Indid - индивидуальный идентификатор
Тип - тип ресурса (степень детализации)
Ресурс - идентификатор ресурса
Режим - режим блокирования
Статус - статус блокировки

Выходные данные процедуры sp_lock:
/Заголовки столбцов таблицы/
Spid - идентификатор процесса
Dbid - идентификатор базы данных
Objid - идентификатор объекта
Indid - индивидуальный идентификатор
Тип - тип ресурса (степень детализации)
Ресурс - идентификатор ресурса
Режим - режим блокирования
Статус - статус блокировки