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

Чтобы продемонстрировать, почему блокирование на уровне записей не в состоянии решить все проблемы блокировок, создадим таблицу. Чтобы запустить код в течение одного сеанса соединения, используйте анализатор запросов (Query Analyzer) SQL Server 7.0 или другое средство работы с запросами.

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

/* Выход показывает одну исключающую блокировку (Х) по идентификатору строки (RID); это как раз та строка, которую только что изменили. */

В третьем окне запроса запустите следующую команду до того, как во втором окне будет выдана команда Откат (ROLLBACK).

После этого во втором окне запустите команду отката транзакции:

Вторая команда обновления остается заблокированной и не может внести изменения до тех пор, пока предшествующая обновляющая транзакция не выдаст команду Откат (ROLLBACK) и не снимет блокировку. Почему же это происходит, если эти две команды адресованы различным строкам таблицы? Ведь если первая команда UPDATE пробует изменить строку, у которой значение столбца col1 равно 1, то почему не может выполниться вторая команда UPDATE, которая намерена изменить строку, содержащую в столбце col1 значение, равное 3?

Ответ заключается в том, что SQL Server должен просканировать всю таблицу, чтобы выполнить каждое из описанных изменений - ведь таблица не имеет индексов. Первая команда UPDATE сканирует таблицу в поисках значения 1 и находит его в первой строке. SQL Server объявляет исключающую блокировку этой строки и выполняет обновление. Затем команда продолжает сканирование оставшейся части таблицы, не находит более подходящих строк и потому не объявляет более ни одной исключающей блокировки. Если посмотреть выходные данные процедуры sp_lock, то увидим, что имеется одна блокировка типа Х по идентификатору строки, RID, и две блокировки типа IX - на страницу и на таблицу. Две последние блокировки являются преднамеренными, они подробно рассмотрены в статье «Блокировки в SQL Server 7.0». Такие блокировки назначаются на уровнях крупной гранулярности (страницы и таблицы), чтобы отражать блокировки, возникающие на уровнях более мелкой гранулярности (строки или ключи).

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

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

После того как будет создана таблица в соответствии с этим сценарием, попробуйте еще раз запустить первый оператор UPDATE. Теперь картина блокировок несколько изменится. По-прежнему присутствуют блокировка Х (на RID) и две блокировки IX (на страницу и на таблицу), но в дополнение к ним появились две блокировки Х на ключ (KEY). Блокировки KEY накладываются только на индексы, поэтому две вновь появившиеся блокировки относятся к не кластеризованному индексу. При внесении изменений в проиндексированный столбец SQL Server меняет и все не кластеризованные индексы, относящиеся к этому столбцу. На уровне листьев дерева не кластеризованного индекса содержатся в отсортированном порядке все значения ключевого столбца. Первоначально не кластеризованный индекс содержал значения 1, 3 и 5. Потом произошло обновление, 1 заменили на 7. Строка индекса, которая ранее содержала 1, теперь заблокирована до завершения транзакции точно так же, как и строка таблицы. Новая версия строки получила значение 7, и помещена в конец индекса на уровне листьев, поскольку теперь это наибольшее значение. Кроме того, эта новая строка индекса также должна быть заблокирована на время транзакции. Таким образом, хотя строка с данными никуда не перемещается во время обновления, строки индекса на уровне листьев перемещаются, так как не кластеризованный индекс всегда должен быть отсортирован.

Не выполняя оператор Откат (ROLLBACK) для первого оператора UPDATE, запустите второй оператор UPDATE в новом окне. На этот раз он будет успешно выполнен. Используя индекс, SQL Server смог определить, что ему не понадобится заблокированная строка. Ему нужна только строка, в столбце col1 которой хранится значение 3. С помощью индекса SQL Server может получить доступ к нужным строкам напрямую, и проблем с блокировками не возникает. Наконец, убедитесь в том, что все блокировки сняты. Для этого либо произведите откат первоначального обновления, либо завершите транзакцию каким-нибудь другим способом.

Если SQL Server должен прочитать данные в строке прежде чем узнать, нужно ли блокировать эту строку, то почему это условие не применимо к уровню листьев в не кластеризованном индексе? Другими словами, когда второй оператор UPDATE пытается найти значение проиндексированного ключа, равное 3, нужно ли ему прочитать предшествующее значение проиндексированного ключа, которое закрыто исключающей блокировкой?

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

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

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

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

Кэйлен Делэйни (kalen_delaney@compuserve.com, http://www.insidesqlserver.com/) обладает сертификатами MCT и MCSE. Она работает независимым консультантом и преподавателем на северо-западе тихоокеанского побережья США.