Блокировки в MS SQL Server 2000

давайте рассмотрим примеры их использования. Итак, у нас имеется таблица 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 – название базы данных;

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 =

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

Поможем написать работу на аналогичную тему

Получить выполненную работу или консультацию специалиста по вашему учебному проекту
Нужна помощь в написании работы?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Пишем статьи РИНЦ, ВАК, Scopus. Помогаем в публикации. Правки вносим бесплатно.

Похожие рефераты: