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

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

dbcc traceon(3604,1200) select * from test dbcc traceoff(3604,1200)

Флаг трассировки 3604 заставляет сервер передавать отладочную информацию в текущее соединение непосредственно клиенту, а 1200 – выводить информацию о блокировках. В результате мы получим следующее:

Process 54 acquiring S lock on DB: 8 (class bit0 ref1) result: OK Process 54 acquiring IS lock on TAB: 8:1993058136 [] (class bit0 ref1) result: OK Process 54 acquiring IS lock on PAG: 8:1:31 (class bit0 ref1) result: OK i n ----------- -------------------- 1 alex 2 rosa 3 dima (3 row(s) affected) Process 54 releasing lock on PAG: 8:1:31 Process 54 releasing lock on TAB: 8:1993058136 []

Хорошо. В первой сессии зафиксируйте транзакцию:

--print @@spid --begin tran select * from test commit

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

begin tran select * from test with (updlock)

Теперь вызов sp_lock 54 дает более интересный результат (таблица 4):

spdi	dbid	ObjId	IndId	Type	Resource	Mode	Status
54	8	0	0	DB		S	GRANT
54	8	1993058136	0	RID	1:29:2	U	GRANT
54	8	1993058136	0	RID	1:29:0	U	GRANT
54	8	1993058136	0	PAG	1:29	IU	GRANT
54	8	1993058136	0	TAB		IX	GRANT
54	8	1993058136	0	RID	1:29:1	U	GRANT

Как видно, на три строки была наложена блокировка обновления, что означает невозможность обновления этих строк другими транзакциями. Кроме этого, были наложены еще две блокировки, которые относятся к типу блокировок намерения (intent locks) – блокировка на страницу и на таблицу. Пусть вас не смущает колонка Resource – ее содержание я опишу позже. Поле ObjId представляет собой идентификатор объекта, имя которого можно получить с помощью функции object_name. IndId – идентификатор индекса, в данном примере всегда равен 0, так как в таблице test индексов не используется.

Блокировки намерений

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

Предположим, некая транзакция пытается изменить какую-либо строку в таблице test. Чтобы определить, что эту транзакцию необходимо заблокировать, менеджеру транзакций (в отсутствие блокировок намерения) пришлось бы сканировать всю таблицу syslockinfo для проверки всех строк таблицы test. Чтобы избежать этой неблагодарной работы, менеджер блокировок сразу устанавливает на страницу и таблицу блокировку намерения обновления (Intent Update) и монопольную блокировку намерения (Intent Exclusive) соответственно, и проверяет уже только их. Таким образом, блокировки намерений – это хинт менеджеру блокировок, который дает значительный выигрыш в производительности при проверке совместимости блокировок.

Рассмотрим блокировки намерений более подробно:

Разделяемая блокировка намерений (обозначается IS). Этот тип блокировки предназначен для того, чтобы вместо проверки множества разделяемых блокировок для каждой строки в момент обновления данных, или установки множества разделяемых блокировок для каждой строки в момент чтения данных, можно было установить одну блокировку намерений для страницы или всей таблицы. Этот тип блокировки совместим со всеми остальными режимами, кроме монопольной блокировки. Другими словами, если транзакция пытается обновить данные, на которые наложена разделяемая блокировка намерений, ее выполнение заблокируется до момента завершения первой транзакции.

Монопольная блокировка намерений (обозначается IX). Монопольная блокировка намерений запрещает доступ к данным какого-либо вида (даже на чтение) и позволяет повысить эффективность работы при множественном обновлении данных. В отличие от простой монопольной блокировки она совместима сама с собой. Т.е. другая транзакция также может наложить монопольную блокировку намерений на те же ресурсы, что и первая транзакция.

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

Чтобы уяснить работу этого типа блокировок, продолжим наш пример. Создайте новую сессию (у меня ее идентификатор равен 55) и выполните следующий скрипт:

begin tran insert into test values(4,'other')

На удивление многих она прекрасно отработает и не выдаст никаких предупреждений. Как же так? Посмотрим, что скажет нам sp_lock

sp_lock 54,55

Результат приведен в таблице 5:

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
54	8	0	0	DB		S	GRANT
54	8	1993058136	0	RID	1:29:02	U	GRANT
54	8	1993058136	0	RID	1:29:00	U	GRANT
54	8	1993058136	0	PAG	1:29	IU	GRANT
54	8	1993058136	0	TAB		IX	GRANT
54	8	1993058136	0	RID	1:29:01	U	GRANT
55	8	0	0	DB		S	GRANT
55	8	1993058136	0	PAG	1:29	IX	GRANT
55	8	1993058136	0	TAB		IX	GRANT
55	8	1993058136	0	RID	1:29:03	X	GRANT

Как видно, предыдущие блокировки остались (еще бы! мы же не зафиксировали транзакцию), и добавились четыре новых: одна блокировка базы, не имеющая никакого значения, две блокировки намерений (на таблицу и страницу) и монопольная блокировка на новую строку (идентификатор 1:29:03).

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

--begin tran select * from test with (updlock) update test set n = 'other1' where i = 1

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

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
54	8	1993058136	0	RID	1:29:03	U	WAIT

Так как на строку 1:29:03 уже наложена монопольная блокировка, никакие другие блокировки на этот же ресурс наложены быть не могут. Но каким образом наш безобидный update на первую строчку привел к наложению блокировки обновления на четвертую строчку? Дело в том, что при отсутствии индекса менеджер блокировок не может определить, какой именно строке соответствует заданное логическое условие, поэтому производится сканирование и установка монопольной блокировки на каждую строчку таблицы. При наличии индекса SQL Server использует другой метод, основанный на блокировках диапазона индекса. Чтобы перейти к их рассмотрению, вернитесь в сессию с идентификатором 55 и выполните команду отката транзакции:

--begin tran insert into test values(4,'other') rollback

После этого нужно откатиться и в первой сессии:

--begin tran select * from test with (updlock) --update test set n = 'other1' where i = 1 rollback

Блокировка диапазона ключа или индекса позволяет заблокировать данные на основе логического значения, такого, как выражение в разделе where. Кроме всего прочего, только этот тип блокировок позволяет эффективно решить проблему чтения фантомов. Эта проблема связана с тем, что даже при наличии уровня изоляции REPEATABLE READ вы можете получить разное количество строк, соответствующих определенному логическому условию в течение одной транзакции (кстати, логическое условие может и отсутствовать). Это может произойти из-за того, что не существует никаких ограничений на вставку новых строк, несмотря на то, что обновление выбранных строк заблокировано разделяемой блокировкой. Новые строки могут содержать значения, удовлетворяющие логическому запросу в разделе where, так что при следующем запросе количество строк в результирующей выборке изменится.

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

ПРИМЕЧАНИЕ До выхода MS SQL Server 7.0 менеджер блокировок поступал таким образом (блокировал всю таблицу) с любыми таблицами, работа с которыми велась с уровнем изоляции SERIALIZABLE.

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