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

table_id – идентификатор таблицы; table_name – название таблицы;

index_id – идентификатор индекса; index_name – название индекса.

ПРИМЕЧАНИЕ Для получения полного списка команд DBCC (Database Console Commands) можно воспользоваться такой командой: dbcc traceon (2520) dbcc help('?') dbcc traceoff (2520) Чтобы получить синтаксис конкретной команды, например, page, выполните такую последовательность: dbcc traceon (2520) dbcc help('page') dbcc traceoff (2520)

В нашем случае следующий набор команд покажет все страницы таблицы test:

dbcc traceon (3604) dbcc extentinfo(8,'test') dbcc traceoff (3604)

В колонке page_id указаны идентификаторы страниц, а в колонке index_id – идентификаторы индекса. Просмотрим содержимое страницы индекса:

dbcc traceon (3604) dbcc page(8,1,29,1,1) dbcc traceoff (3604)

Вот результат работы данной команды:

PAGE: (1:29) ------------ BUFFER: ------- BUF @0x00DA2740 --------------- bpage = 0x1A25A000 bhash = 0x00000000 bpageno = (1:29) bdbid = 8 breferences = 1 bstat = 0x9 bspin = 0 bnext = 0x00000000 PAGE HEADER: ------------ Page @0x1A25A000 ---------------- m_pageId = (1:29) m_headerVersion = 1 m_type = 2 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4 m_objId = 1993058136 m_indexId = 3 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 13 m_slotCnt = 3 m_freeCnt = 8042 m_freeData = 144 m_reservedCnt = 0 m_lsn = (6:181:20) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 839385609 Allocation Status ----------------- GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED DATA: ----- Slot 0, Offset 0x60 ------------------- Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP 1A25A060: 00000116 00001f00 00000100 00000300 ................ Slot 1, Offset 0x70 ------------------- Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP 1A25A070: 00000216 00001f00 01000100 00000300 ................ Slot 2, Offset 0x80 ------------------- Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP 1A25A080: 00000316 00001f00 02000100 00000300 ................

Так как тип страницы, указанный в заголовке, равен 2, данная страница является индексной. Кроме этого, значение m_indexId равно идентификатору индекса.

ПРИМЕЧАНИЕ Информацию обо всех индексах данной таблицы можно получить с помощью такого запроса: select * from sysindexes where id = object_id('test')

На этом я закончу рассматривать хинты SQL Server’а и блокировки и перейду к проблеме взаимоблокировок (deadlocks). Не забудьте завершать транзакции в 54 и 55 сессиях.

СОВЕТ Для определения текущего количества незавершенных транзакций можно воспользоваться функцией @@trancount.

Взаимоблокировки

В процессе работы параллельных транзакций, обращающихся к одним и тем же ресурсам, возможно возникновение проблемы взаимоблокировок (deadlock), которые также называют тупиковыми блокировками. Если транзакции обращаются к ресурсам последовательно, вероятность возникновения взаимоблокировки очень мала, однако если порядок обращения транзакций к общим ресурсам разный, возможность возникновения мертвой блокировки повышается многократно.

Рассмотрим суть взаимоблокировки на двух транзакциях (t1 и t2), обращающихся к двум ресурсам (A и B). Предположим, что транзакция t1 блокирует ресурс A, после чего транзакция t2 блокирует ресурс B. Если теперь транзакция t1 попытается получить доступ к ресурсу B, а транзакция t2 – к ресурсу А, возникнет взаимоблокировка. В таком состоянии транзакции не могут разрешить конфликт и переходят в состояние ожидания на неопределенно долгий срок. Чтобы изменить время ожидания ресурса для текущего соединения, используется следующая команда:

SET LOCK_TIMEOUT timeout_period

timeout_period задается в миллисекундах. Если указывается значение –1, менеджер транзакций будет ждать освобождения ресурса неопределенно долгое время.

В SQL Server’е предусмотрен механизм автоматического определения взаимоблокировок и разрешения конфликтов. Для этого предназначен специальный серверный процесс LOCK MONITOR, который просыпается каждые 5 секунд [2]. Он проверяет наличие двух и более ожидающих транзакции и вычисляет зависимости между ними. Если оказывается, что транзакции зависят друг от друга, т.е. для получения блокировки одной из них необходимо снять блокировку другой транзакции, фиксируется факт возникновения взаимоблокировки. Единственный способ устранения проблемы заключается в отмене одной из транзакций. Но какой? Для каждой транзакции вводится понятие цены или приоритета. Это значение задается на уровне соединения следующей командой:

SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }

Задание меньшего приоритета для соединения приводит к откату транзакций при возникновении взаимоблокировок с кодом ошибки 1205.

Давайте рассмотрим простой пример взаимоблокировки. Для большей наглядности я продемонстрирую его на одной таблице из трех (!) строк.

Итак, мы продолжаем работать с нашей таблицей test, на поле i которой создан некластерный индекс.

В первой сессии выполним такой запрос:

SET IMPLICIT_TRANSACTIONS on select * from test with(updlock) where i = 2

Здесь мы наложили блокировку обновления на вторую строку таблицы. Теперь во второй сессии введем следующий код:

SET IMPLICIT_TRANSACTIONS on select * from test with (updlock) where i = 1

Та же блокировка обновления накладывается на первую строчку. Перейдем снова к первой сессии:

--SET IMPLICIT_TRANSACTIONS on --select * from test with(updlock) where i = 2 update test set n = (select n from test where i = 2) where i = 1

Так как мы пытаемся получить на первую строку монопольную блокировку, которая несовместима с текущей блокировкой обновления, сессия блокируется.

Теперь во второй сессии введем похожую команду:

--SET IMPLICIT_TRANSACTIONS on --select * from test with (updlock) where i = 1 update test set n = (select n from test where i = 1) where i = 2

Вот она, взаимоблокировка! Обе сессии заблокированы. Однако наше ликование будет недолгим: примерно через 5 секунд менеджер блокировок сообразит, что возникла взаимоблокировка и откатит первую транзакцию.

Server: Msg 1205, Level 13, State 50, Line 3 Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Конечно, пример несколько надуманный, зато он хорошо иллюстрирует проблему: на пустом месте можно получить взаимоблокировку даже на табличке из двух строк.

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

Это правило помогает, но не во всех случаях. Рассмотрим такой пример. В первой сессии выполняем команду:

set implicit_transactions on select * from test with (repeatableread)

Это приводит к наложению разделяемых блокировок на все строки таблицы test. Во второй сессии выполняем тот же запрос:

set implicit_transactions on select * from test with (repeatableread)

Так как разделяемая блокировка совместима сама с собой, запрос выполняется нормально.

Блокировки для первой строки приведены в таблице 17.

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
55	8	1993058136	0	RID	1:31:00	S	GRANT
57	8	1993058136	0	RID	1:31:00	S	GRANT

Нас интересует именно первая строка, так как сейчас мы попытаемся в первой сессии изменить данные:

--set implicit_transactions on --select * from test with (repeatableread) update test set n = 'other' where i = 1

Сессия блокируется, так как монопольная блокировка, необходимая для выполнения изменения, не совместима с разделяемой. Теперь блокировки выглядят так, как показано в таблице 18.

spid	dbid	ObjId	IndId	Type	Resource	Mode	Status
55	8	1993058136	0	RID	1:31:00	S	GRANT
57	8	1993058136	0	RID	1:31:00	U	GRANT
57	8	1993058136	0	RID	1:31:00	X	CNVT

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

Если теперь выполнить во второй сессии ту же команду:

--set implicit_transactions on --select * from test with (repeatableread) update test set n = 'other' where i = 1

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

Чтобы избавиться от подобных ситуаций, нужно устанавливать при запросе данных вместо разделяемой блокировки сразу блокировку обновления. В этом случае вторая сессия будет заблокирована на команде выборки данных до завершения транзакции в первой сессии. Однако и при установке блокировки обновления нельзя быть уверенным, что не произойдет взаимного блокирования транзакций. Если во второй сессии просто выполнить запрос с уровнем изоляции REPEATABLE READ, на все строки опять будет установлена разделяемая блокировка, и при попытке обновления записи в первой сессии, транзакция будет заблокирована. Единственным выходом из ситуации, когда происходит выполнение транзакций с разными уровнями изоляции, будет наложение монопольных блокировок на все строки при запросе данных в первой сессии. Хотя такой метод строго не рекомендуется, он возможен, благодаря наличию хинта xlock. Если вносимые транзакцией изменения будут затрагивать более 70% таблицы, есть смысл вместо xlock установить хинт tablockx, хотя менеджер блокировок достаточно умен, чтобы самостоятельно выполнить эскалацию блокировок.