Однако все это не про нас, вернемся к первой сессии (с номером 54) и отменим транзакцию:
| --SET IMPLICIT_TRANSACTIONS on --update test set n = 'other' where i = 2rollback |
Вторая сессия моментально разблокируется, после чего в ней также нужно откатить транзакцию:
| --SET IMPLICIT_TRANSACTIONS on --select * from test with (readpast)--select * from test where i = 1rollback |
Давайте создадим некластерный индекс и выполним те же самые действия. Откройте новую сессию и выполните следующую команду:
| 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 = 0x9bspin = 0 bnext = 0x00000000 PAGE HEADER:------------Page @0x1A25A000----------------m_pageId = (1:29) m_headerVersion = 1 m_type = 2m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4m_objId = 1993058136 m_indexId = 3 m_prevPage = (0:0)m_nextPage = (0:0) pminlen = 13 m_slotCnt = 3m_freeCnt = 8042 m_freeData = 144 m_reservedCnt = 0m_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) = CHANGEDML (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 onselect * from test with(updlock) where i = 2 |
Здесь мы наложили блокировку обновления на вторую строку таблицы. Теперь во второй сессии введем следующий код:
| SET IMPLICIT_TRANSACTIONS onselect * from test with (updlock) where i = 1 |
Та же блокировка обновления накладывается на первую строчку. Перейдем снова к первой сессии:
| --SET IMPLICIT_TRANSACTIONS on--select * from test with(updlock) where i = 2update test set n = (select n from test where i = 2) where i = 1 |
Так как мы пытаемся получить на первую строку монопольную блокировку, которая несовместима с текущей блокировкой обновления, сессия блокируется.
Теперь во второй сессии введем похожую команду:
| --SET IMPLICIT_TRANSACTIONS on--select * from test with (updlock) where i = 1update test set n = (select n from test where i = 1) where i = 2 |
Вот она, взаимоблокировка! Обе сессии заблокированы. Однако наше ликование будет недолгим: примерно через 5 секунд менеджер блокировок сообразит, что возникла взаимоблокировка и откатит первую транзакцию.
| Server: Msg 1205, Level 13, State 50, Line 3Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosenas 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.