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

NOLOCK – разрешается использовать только с командой SELECT. Аналогичен хинту READUNCOMMITTED, т.е. не накладывает никаких блокировок и игнорирует блокировки других транзакций.

PAGLOCK – пожелание менеджеру блокировок устанавливать блокировки на уровне страниц. Это пожелание выполняется очень редко.

READPAST – разрешается использовать только с командой SELECT. Позволяет выбрать только те строки, на которые не установлена монопольная блокировка. Другими словами, позволяет выбрать все не измененные строки.

ROWLOCK – пожелание менеджеру блокировок устанавливать блокировки на уровне строк. Это пожелание выполняется очень редко.

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

select * from test with(tablock,holdlock)

Установит разделяемую блокировку до конца транзакции.

TABLOCKX – устанавливает монопольную блокировку на всю таблицу до конца транзакции даже для команды SELECT.

UPDLOCK – разрешается использовать только с командой SELECT. Позволяет установить блокировку обновления вместо разделяемой блокировки. Действие блокировки продолжается до завершения транзакции.

XLOCK – Действие аналогично хинту UPDLOCK с той лишь разницей, что устанавливается монопольная блокировка на ресурс.

Теперь давайте рассмотрим примеры их использования. Итак, у нас имеется таблица test следующего содержания (таблица 13).

i	n
1	alex
2	rosa
3	dima

Начнем с монопольных блокировок. В первой сессии выполните команду:

update test set n = 'other' where i = 2

К тому же результату (наложение монопольной блокировки на строку) привела бы команда

select * from test with(xlock) where i = 2

Теперь, во второй сессии:

select * from test with (nolock)

Вторая сессия не заблокировалась и выдала три строчки, включая измененную. Если бы мы не указали хинта nolock, сессия заблокировалась бы, так как по умолчанию установлен режим изоляции READ COMMITTED. Этому режиму необходима разделяемая блокировка, но так как монопольная блокировка не совместима ни с какой другой, менеджер блокировок заблокировал бы сессию.

Еще один интересный результат можно получить с помощью следующей команды (во второй сессии):

select * from test with (readpast)

Результаты ее выполнения зависят от того, какую команду мы выполнили в первой сессии. Если выполнялся запрос с хинтом xlock, результирующий набор будет содержать все строки таблицы. Однако если была выполнена команда UPDATE, SQL Server выдаст нам всего две строчки: 1 и 3. Такое поведение, признаться, для меня явилось неожиданностью, так как блокировки, накладываемые этими командами, полностью совпадают. У меня возникло предположение, что сервер каким-то образом помечает действительно измененные строки или вообще не вносит их в файл базы данных. Чтобы получить больше информации по этому поводу, воспользуемся очередной недокументированной возможностью, позволяющей просматривать физическое содержимое данных.

Но сначала я бы хотел рассмотреть содержимое колонки Resources, которую возвращает хранимая процедура sp_lock. Значение это состоит из трех цифр, разделенных двоеточием, первое из которых означает номер файла в базе данных, второе – номер страницы и третье – номер строки. Таким образом, запись 1:31:2 означает вторую строку на странице 31 в первом файле базы данных.

Вот синтаксис команды, с помощью которой можно получить физическое содержимое таблицы:

DBCC PAGE ({dbid|dbname}, filenum, pagenum [,print option] [,cache])

dbid – идентификатор базы данных; dbname – название базы данных;

filenum – идентификатор файла базы данных;

pagenum – идентификатор страницы

print option – флаги вывода информации. Может принимать значения, приведенные в таблице 14.

Значение	Описание
0	Установлен по умолчанию. Выводится только заголовок страницы.
1	Выводится заголовок страницы и содержимое каждой строчки.
2	Выводится заголовок страницы и полный дамп памяти.
3	Относительно новый флаг, который аналогичен флагу 1, с той разницей, что значения форматируются в удобочитаемый вид для каждой колонки.

cache – флаг, указывающий, выбирать данные из кэша или нет. Если 1 (по умолчанию), данные выбираются из кэша, 0 – с диска.

Откроем новую сессию и выполним следующий набор команд (у меня база, в которой содержится таблица test, имеет идентификатор 8):

dbcc traceon (3604) dbcc page(8,1,31,3,1) dbcc traceoff (3604)

Приведу результат, выводимый данной командой:

PAGE: (1:31) ------------ BUFFER: ------- BUF @0x00D88500 --------------- bpage = 0x19548000 bhash = 0x00000000 bpageno = (1:31) bdbid = 8 breferences = 1 bstat = 0xb bspin = 0 bnext = 0x00000000 PAGE HEADER: ------------ Page @0x19548000 ---------------- m_pageId = (1:31) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0 m_objId = 1993058136 m_indexId = 0 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 8 m_slotCnt = 3 m_freeCnt = 8026 m_freeData = 247 m_reservedCnt = 0 m_lsn = (6:181:2) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 0 Allocation Status ----------------- GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x60 ------------------ Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 19548060: 00080030 00000001 02000002 15001100 0............... 19548070: 656c6100 78 .alex i = 1 n = alex Slot 1 Offset 0xe1 ------------------ Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 195480E1: 00080030 00000002 02000002 16001100 0............... 195480F1: 68746f00 7265 .other i = 2 n = other Slot 2 Offset 0x8a ------------------ Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 1954808A: 00080030 00000003 02000002 15001100 0............... 1954809A: 6d696400 61 .dima i = 3 n = dima

Отсюда можно сделать вывод, что данные обновляются непосредственно, а менеджер блокировок следует какому-то специальному алгоритму, проверяющему лог транзакций (transaction log), при использовании хинта readpast.

На будущее хочу заметить, что тип страницы (выделен) может принимать несколько значений, из которых для нас важны два: 1 – для страниц данных, 2 – для страниц индексов.

Давайте продвигаться дальше. Так как в данный момент у нас заблокирована одна строка (вторая), попытаемся во второй сессии выполнить такой запрос:

--select * from test with (readpast) select * from test where i = 1

Ура! Наконец-то она заблокировалась! :) Это произошло потому, что в отсутствии индекса на таблице оптимизатор выполняет полное сканирование таблицы для поиска строк, удовлетворяющих заданному логическому условию. При этом он неизбежно натыкается на заблокированную строчку, что приводит к блокировке транзакции. Вот какие блокировки установлены на текущий момент (сокращенно, таблица 15):

ObjId	IndId	Type	Resource	Mode	Status
1993058136	0	PAG	1:31	IS	GRANT
1993058136	0	PAG	1:31	IX	GRANT
1993058136	0	TAB		IS	GRANT
1993058136	0	TAB		IX	GRANT
1993058136	0	RID	1:31:01	X	GRANT
1993058136	0	RID	1:31:01	S	WAIT
1993058136	0	PAG	1:31	IS	GRANT
1993058136	0	PAG	1:31	IX	GRANT
1993058136	0	TAB		IS	GRANT
1993058136	0	TAB		IX	GRANT

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

Предположим, вы не имеете доступа к сессиям 54 и 55, которые в данный момент конкурируют в получении доступа к ресурсам, и не можете выполнить команды фиксации транзакции или отката. Единственным возможным выходом из сложившейся ситуации является убиение одного из процессов. Для получения подробной информации о процессе можно воспользоваться хранимой процедурой sp_who и ее недокументированным аналогом sp_who2. Последняя возвращает значительно больше информации, однако результаты выполнения обеих содержат колонку cmd, в которой хранится тип выполняющейся команды, если процесс заблокирован, или AWAITING COMMAND – если все нормально.

Для получения более подробной информации о выполняющейся команде можно использовать следующую команду:

DBCC INPUTBUFFER (55)

Эта команда возвращает такой результат:

EventType Parameters EventInfo -------------- ---------- ----------------------------------------------- Language Event 0 --SET IMPLICIT_TRANSACTIONS on --select * from test where i = 1

Эта команда может использоваться для просмотра первых 255 символов команд для заданной сессии.

После того, как выяснится жертва, можно вызывать команду KILL.

Однако все это не про нас, вернемся к первой сессии (с номером 54) и отменим транзакцию:

--SET IMPLICIT_TRANSACTIONS on --update test set n = 'other' where i = 2 rollback

Вторая сессия моментально разблокируется, после чего в ней также нужно откатить транзакцию:

--SET IMPLICIT_TRANSACTIONS on --select * from test with (readpast) --select * from test where i = 1 rollback

Давайте создадим некластерный индекс и выполним те же самые действия. Откройте новую сессию и выполните следующую команду:

create nonclustered index _i_idx on test(i)

Теперь в первой сессии выполним обновление записи:

--SET IMPLICIT_TRANSACTIONS on update test set n = 'other' where i = 2 --rollback

А во второй – запрос:

--SET IMPLICIT_TRANSACTIONS on --select * from test with (readpast) select * from test with (xlock) where i = 1 --rollback

Чтобы увидеть блокировки второй сессии, я поставил хинт xlock. Запрос не заблокируется, давайте посмотрим содержимое syslockinfo (таблица 16).

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
55	8	1993058136	0	RID	1:31:00	X	GRANT
55	8	1993058136	0	PAG	1:31	IX	GRANT
54	8	1993058136	0	PAG	1:31	IX	GRANT
55	8	1993058136	3	PAG	1:29	IX	GRANT
55	8	1993058136	0	TAB		IX	GRANT
54	8	1993058136	0	TAB		IX	GRANT
54	8	1993058136	0	RID	1:31:01	X	GRANT
55	8	1993058136	3	KEY	(21001d31a802)	X	GRANT

Как видим, первая сессия наложила монопольную блокировку на первую строку 1:31:01, а вторая – на нулевую 1:31:00. Кроме этого, вторая сессия наложила монопольную блокировку на ключ (последняя строка) и монопольную блокировку намерений на страницу, где располагается индекс (1:29).

Чтобы действительно убедиться, что индекс создан именно на этой странице, нужно выполнить очередную недокументированную команду:

extentinfo [({'database_name'| dbid} [, {'table_name' | table_id} [, {'index_name' | index_id}]])]

dbid – идентификатор базы данных; dbname – название базы данных;