Блокировки в MS SQL Server 2000
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, хотя менеджер блокировок достаточно умен, чтобы самостоятельно выполнить эскалацию блокировок.
Заключение
В заключение я хочу рассмотреть вопросы, которые не были затронуты в данной статье, но могут иметь определенный интерес.
Владельцы блокировки
Возможно, вы не задумывались над этим вопросом, или думали, что владельцем всех блокировок является менеджер блокировок, однако это не так. У каждой блокировки есть владелец, и его можно найти в таблице syslockinfo в поле req_ownertype. Это поле может принимать три значения: 1, 2 и 3. Если значение равно 1, владельцем блокировки является транзакция – это самая распространенная ситуация. Если req_ownertype равен 2 – владельцем является курсор, созданный с опцией SCROLL_LOCKS. И, наконец, значение 3 говорит о том, что владельцем транзакции является сессия. Как правило, объектом подобной блокировки является база данных, а сама блокировка накладывается в момент выполнения команды use [database] и снимается, когда выполняется другая команда use, или завершается сессия.
Опции индекса
По умолчанию SQL Server выбирает наиболее подходящий, по его мнению, уровень детализации блокировок и выполняет эскалацию блокировок при увеличении их количества. Вы можете задать другую стратегию блокировки непосредственно в самом запросе, с помощью хинтов, а можете (только для индексов) указать заранее степень детализации объектов блокировок. Делается это с помощью хранимой процедуры sp_indexoption. Вот ее синтаксис:
|
sp_indexoption [ @IndexNamePattern = ] 'index_name' , [ @OptionName = ] 'option_name' , [ @OptionValue = ] 'value' |
IndexNamePattern – Имя индекса.
OptionName – Имя опции. Может принимать значения, перечисленные в таблице 19.
| Имя опции | Описание |
| AllowRowLocks | Если установлена в TRUE, разрешает использования блокировок на уровне строк. |
| AllowPageLocks | Если установлена в TRUE, разрешает использования блокировок на уровне страниц. |
| DisAllowRowLocks | Если установлена в TRUE, запрещает использование блокировок на уровне строк. |
| DisAllowPageLocks | Если установлена в TRUE, запрещает использование блокировок на уровне страниц. |
Булево значение.
Рассмотрим пример ее использования. Следующий запрос установит около сотни блокировок, в чем можно убедиться, воспользовавшись хранимой процедурой sp_lock.
|
set implicit_transactions on select City from Customers with (repeatableread) |
Однако если перед этим запросов вызвать хранимую процедуру sp_indexoption следующим образом:
| EXEC sp_indexoption 'Customers.City', 'allowrowlocks', false |
Будет установлена всего одна (!) разделяемая блокировка на таблицу Customers.
Блокировки приложений
В SQL Server 2000 появились две новые процедуры, предназначенные для работы с пользовательскими блокировками. Пользовательские блокировки позволяют решить проблему доступа к тем ресурсам, на которые не распространяется действие менеджера блокировок. Например, когда вы хотите защитить отдельный столбец в таблице или вызов хранимой процедуры, пользовательские блокировки являются лучшим выходом.
|
ПРИМЕЧАНИЕ Возможно также написание расширенных хранимых процедур, в которых использовались бы механизмы синхронизации операционной системы. До выхода SQL Server 2000 это был единственный метод. |
Рассмотрим синтаксис процедур.
|
sp_getapplock [ @Resource = ] 'resource_name', [ @LockMode = ] 'lock_mode' [ , [ @LockOwner = ] 'lock_owner' ] [ , [ @LockTimeout = ] 'value' ] |
Resource – уникальное имя ресурса, аналогично уникальному имени объекта мьютекса или события.
LockMode – Тип запрашиваемой блокировки. Может принимать значения Shared, Update, Exclusive, IntentExclusive и IntentShared.
LockOwner – владелец блокировки. По умолчанию – transaction. Может принимать значения transaction или session. При указании владельца Session, блокировка снимается либо при завершении сессии, либо при завершении работы SQL Server’а, либо при вызове процедуры sp_releaseapplock.
LockTimeout – значение в миллисекундах, в течение которого транзакция будет ожидать ресурса, если не сможет получить доступа сразу. По умолчанию берется значение, которое возвращает функция @@LOCK_TIMEOUT.
Чтобы убедиться, что ресурс захвачен, необходимо проверить возвращаемое значение функции. Если оно больше или равно 0, ресурс захвачен, если нет – произошла ошибка. Все возможные значения приведены в таблице 20.
| Значение | Описание |
| 0 | Ресурс успешно захвачен. |
| 1 | Ресурс был захвачен после определенного ожидания. |
| -1 | Ресурс захвачен не был, произошел возврат по таймауту. |
| -2 | Запрос на ресурс был прерван. |
| -3 | Запрос на ресурс был прерван вследствие возникновения мертвой блокировки. |
| -999 |