45808 (Блокировки в MS SQL Server 2000), страница 5

Существует два вида блокировки индекса: блокировка самой записи индекса (index entry) и блокировка диапазона. Блокировка записи индекса эквивалентна блокировке строки, т.е. менеджер блокировок будет блокировать строку, если в таблице отсутствует индекс, или же будет блокировать запись индекса, которая соответствует данной строке. Поскольку индекс может быть построен по неуникальному полю, блокируемая запись не может быть идентифицирована только записью индекса, поэтому для вычисления локатора ресурса применяется алгоритм хеширования идентификатора строки (RID) и записи индекса.

Если бы в нашей таблице существовал индекс по полю i, то в предыдущей таблице блокировок в колонке Resource фигурировали бы хеш-значения записей индекса, а тип ресурса был – KEY.

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

Допустим, у нас есть диапазон значений от A до B. Если мы хотим прочитать какое-либо значение С, которое находится между A и B, но не равно им, возможны следующие варианты:

Если С не существует, накладывается разделяемая блокировка на весь диапазон (A,B), при этом вы не можете вставить никакое значение в этот диапазон. Круглая скобка означает, что значение не входит в диапазон. Кроме этого, вы не можете изменить значения A и B на С. Объяснение: Если позволить вставлять записи в диапазон (A,B) или изменять значения A и B на С, результаты повторного запроса могут измениться, что приведет к появлению фантома. Менеджер блокировок SQL Server’а, по техническим причинам, которые будут рассмотрены далее, накладывает несколько отличные блокировки: запрещается вставка в диапазон [A,B), т.е. запрещается вставлять значения, равные А, и запрещается изменять значение B не только на С, но и на любое другое.

Если С существует, накладывается разделяемая блокировка на диапазоны (A,C] и [C,B). При этом запрещается вставлять значения из диапазона (A,B) и изменять A и B на С. Менеджер блокировок SQL Server’а по техническим причинам накладывает отличные блокировки: запрещается вставка в диапазон [A,B), т.е. запрещается вставлять значения, равные А, и запрещается изменять значение B не только на С, но и на любое другое.

Если мы хотим вставить значение С, которое находится между A и B, то:

Накладывается разделяемая блокировка на диапазон [A, B]. Менеджер блокировок вместо разделяемой блокировки накладывает специальный тип блокировки, предназначенный только для тестирования диапазона индекса.

Вставляется значение С, разделяемая блокировка [A, B] снимается.

Накладывается монопольная блокировка на ключ C.

При удалении значения С, которое находится между A и B, возможны следующие ситуации:

Если значение С отсутствует, накладывается блокировка обновления диапазона на (A,B). Менеджер блокировок SQL Server’а по техническим причинам накладывает блокировку обновления на диапазон (A,B], т.е. вы можете изменить значение A, но не значение B.

Если значение С существует, накладывается разделяемая блокировка на диапазон [A,B]. Затем накладывается монопольная блокировка на ключ C. SQL Server производит несколько отличные действия, а именно: он сначала накладывает монопольную блокировку на диапазон [A,C], а затем блокировку обновления на диапазон [C,B].

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

Диапазон	Запись индекса	Тип	Описание
RangeS	S	RangeS_S	Разделяемая блокировка диапазона, разделяемая блокировка записи индекса.
RangeS	U	RangeS_U	Разделяемая блокировка диапазона, блокировка обновления записи индекса.
RangeX	X	RangeX_X	Монопольная блокировка на диапазон и запись индекса.
RangeI	нет	RangeI_N	Блокировка тестирования. Применяется перед вставкой нового значения в диапазон.

Блокировка диапазона накладывается на диапазон значений от текущего ключа до ближайшего следующего. Если следующего ключа не существует, блокировка накладывается на все значения, начиная с текущего. Так как один диапазон соответствует двум записям индекса, количество диапазонов должно быть меньше количества выбранных (или изменяемых) строк на 1. Но для того, чтобы защитить граничные значения записей индекса (например, от вставки такого же значения) накладывается еще две дополнительные блокировки индекса, расширяющие общий диапазон справа и слева. Таким образом, при выборе N’ого количества строк, количество блокировок диапазона будет всегда равно N+1.

В таблице 8 приведена матрица совместимости блокировок диапазона индекса.

Тип	RangeS_S	RangeS_U	RangeX_X	RangeI_N
RangeS_S	+	+	-	-
RangeS_U	+	-	-	-
RangeX_X	-	-	-	+
RangeI_N	-	-	+	-

ПРИМЕЧАНИЕ Блокировка RangeI_N совместима с любым простым типом блокировки (даже с монопольной блокировкой) исключая Sch-M.

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

create table test_key(i int) insert into test_key values(3) insert into test_key values(5) insert into test_key values(7) insert into test_key values(9) create nonclustered index _i_test_key on test_key(i)

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

SET IMPLICIT_TRANSACTIONS { ON | OFF }

Текущее состояние этой переменной можно определить следующим способом:

select case when (@@options & 2) = 2 then 'IMPLICIT' else 'NOT IMPLICIT' end

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

Для начала посмотрим, что такое разделяемая блокировка диапазона:

set implicit_transactions on select * from test_key with (serializable)

Если сейчас заглянуть в табличку syslockinfo или вызвать хранимую процедуру sp_lock, можно увидеть 5 блокировок типа RangeS_S. Значение в строке Resource однозначно идентифицирует запись индекса, однако для одной строки значение равно (ffffffffffff). Это говорит о том, что весь диапазон чисел от -INF до +INF (где INF - бесконечности) заблокирован. Если вы попытаетесь вставить в таблицу test_key значение, например, 68, у вас ничего не выйдет. Рассмотрим результаты вызова процедуры sp_lock (приведены сокращенно в таблице 9):

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
55	8	2009058193	2	KEY	(ffffffffffff)	RangeS-S	GRANT
56	8	2009058193	0	RID	1:29:04	X	GRANT
56	8	0	0	PAG	1:29	IX	GRANT
56	8	2009058193	2	PAG	1:34	IX	GRANT
56	8	2009058193	2	KEY	(ffffffffffff)	RangeIn	WAIT
56	8	2009058193	0	TAB		IX	GRANT

Так как тип блокировки RangeIn (aka RangeI) несовместим ни с каким другим типом блокировки диапазона, сессия за номером 56 блокируется. Примечательный факт – само значение все-таки было вставлено в таблицу, о чем свидетельствует выделенная строка, и только в момент обновления индекса возникла проблема совместимости блокировок. Чтобы убедится, что значение действительно было вставлено, можно воспользоваться командой dbcc page, которая подробно описана в следующем разделе. Кроме этого видно, что установлены блокировки намерений на таблицу и две страницы: страницу с данными и страницу, на которой располагается индекс.

ПРИМЕЧАНИЕ Индекс располагается на другой странице вследствие того, что он является некластерным.

Теперь отменим транзакцию и выполним такой код:

--set implicit_transactions on --select * from test_key with (serializable) delete from test_key with (serializable) where i = 4 --rollback

Здесь мы пытаемся удалить несуществующую строчку. В соответствии с тем, что наш запрос должен выдавать всегда один и тот же результат, менеджер блокировок должен обеспечить, что никакие другие транзакции не смогут вставить какое-либо значение в диапазон от 3 до 5, а также изменить граничные значения ключей на 4. Это достигается за счет установки разделяемой блокировки обновления диапазона.

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
55	8	2009058193	2	KEY	(23005e3c905a)	RangeS-U	GRANT
55	8	2009058193	2	PAG	1:34	IU	GRANT

Если бы удаляемое значение было равно, например, 40, догадайтесь, какой диапазон был бы заблокирован менеджером блокировок. :) Он бы заблокировал весь диапазон от 9 до положительной бесконечности. А что если бы граничные значения ключа составляли бы не 3 и 5, а 9 и 500? Был бы заблокирован диапазон от 9 до 500. Таковы требования высшего уровня изоляции транзакций!

Чтобы еще лучше усвоить правила блокировки диапазонов, попробуйте выполнить такой запрос:

--set implicit_transactions on select * from test_key with (serializable) where i = 10 --delete from test_key with (serializable) where i = 4 --rollback

Он приведет к установлению разделяемой блокировки на диапазон от 9 до +INF, так что никакие операции вставки для этого диапазона не смогут быть выполнены.