| 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 onselect 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 | В процедуру были переданы неверные параметры. |
После успешного захвата ресурса вы можете быть уверены, что обладаете тем типом доступа, который запросили. После работы с ресурсом, необходимо вызвать процедуру sp_releaseapplock. Вот ее синтаксис:
| sp_releaseapplock [ @Resource = ] 'resource_name' [ , [ @LockOwner = ] 'lock_owner' ] |
Resource – уникальное имя ресурса, аналогично уникальному имени объекта мьютекса или события.
LockOwner – владелец блокировки. По умолчанию – transaction. Может принимать значения transaction или session.
Эта процедура должна быть вызвана столько раз, сколько вызывалась процедура sp_getapplock.
Примеры использования данной связки имеются в MSDN.
На этом я заканчиваю статью, надеюсь, она оказалась для вас интересной и полезной в практическом плане.
Список литературы
Microsoft SQL Server 2000, Е.В Мамаев, 2001 г.
Inside Microsoft SQL Server 2000, Kalen Delaney, 2001 г.