Прежде чем идти дальше, давайте рассмотрим небольшой пример. Для просмотра текущих блокировок существует системная хранимая функция sp_lock. Она возвращает информацию о блокировках в формате, описанном в таблице 2.
| Имя колонки | Описание |
| spid | Идентификатор процесса SQL Server. |
| dbid | Идентификатор базы данных. |
| ObjId | Идентификатор объекта, на который установлена блокировка. |
| IndId | Идентификатор индекса. |
| Type | Тип объекта. Может принимать значения: DB, EXT, TAB, PAG, RID, KEY. |
| Resource | Содержимое колонки syslocksinfo.restext. Обычно это идентификатор строки (для типа RID) или идентификатор страницы (для типа PAG). |
| Mode | Типблокировки. Можетприниматьзначения: Sch-S, Sch-M, S, U, X, IS, IU, IX, SIU, SIX, UIX, BU, RangeS-S, RangeS-U, RangeIn-Null, RangeIn-S, RangeIn-U, RangeIn-X, RangeX-S, RangeX-U, RangeX-X. Об этих значениях будет сказано ниже. |
| Status | Статус процесса SQL Server. Может принимать значения: GRANT, WAIT, CNVRT. |
Эта процедура возвращает данные о блокировках из системной таблицы syslockinfo, которая находится в базе данных master.
| ПРИМЕЧАНИЕИнформация именно из этой таблицы используется менеджером блокировок для определения совместимости блокировок при запросе ресурсов транзакциями. |
Во всех примерах используется таблица test, которая создается следующим скриптом:
| create table test(i int, n varchar(20))insert into test values(1,’alex’)insert into test values(2,’rosa’)insert into test values(3,’dima’) |
Во-первых, давайте действительно убедимся, что при чтении данных с уровнем изоляции ниже REPEATABLE READ разделяемые блокировки снимаются сразу же после извлечения данных:
| print @@spidbegin tran select * from test |
Мы начали транзакцию, но оставили ее открытой. Для того чтобы посмотреть, какие блокировки наложены предыдущим скриптом, вызовем процедуру sp_lock (в другой сессии) с параметром, выведенным print @@spid (у меня это 54).
| СОВЕТТекущее значение идентификатора процесса сервера можно увидеть в строке состояния программы Query Analizer. |
| sp_lock 54 |
Результат приведен в таблице 3.
| spdi | dbid | ObjId | IndId | Type | Resource | Mode | Status |
| 54 | 8 | 0 | 0 | DB | S | GRANT |
Мы видим стандартную блокировку, которая создается для каждого соединения с базой данных. Никакой дополнительной блокировки установлено не было.
Другим способом проверки того, что блокировка снимается сразу после извлечения данных, является использование трассировки. Попробуйте выполнить следующую команду:
| dbcc traceon(3604,1200)select * from testdbcc traceoff(3604,1200) |
Флаг трассировки 3604 заставляет сервер передавать отладочную информацию в текущее соединение непосредственно клиенту, а 1200 – выводить информацию о блокировках. В результате мы получим следующее:
| Process 54 acquiring S lock on DB: 8 (class bit0 ref1) result: OKProcess 54 acquiring IS lock on TAB: 8:1993058136 [] (class bit0 ref1) result: OKProcess 54 acquiring IS lock on PAG: 8:1:31 (class bit0 ref1) result: OKi n ----------- -------------------- 1 alex2 rosa3 dima(3 row(s) affected)Process 54 releasing lock on PAG: 8:1:31Process 54 releasing lock on TAB: 8:1993058136 [] |
Хорошо. В первой сессии зафиксируйте транзакцию:
| --print @@spid--begin tran select * from testcommit |
Повторный вызов sp_lock приводит к тем же результатам. Это подтверждает, что предыдущим запросом никаких блокировок не устанавливалось. Теперь попробуем наложить блокировку обновления. Делается это с помощью хинта updlock (хинты подробно будут рассмотрены далее):
| begin tran select * from test with (updlock) |
Теперь вызов sp_lock 54 дает более интересный результат (таблица 4):
| spdi | dbid | ObjId | IndId | Type | Resource | Mode | Status |
| 54 | 8 | 0 | 0 | DB | S | GRANT | |
| 54 | 8 | 1993058136 | 0 | RID | 1:29:2 | U | GRANT |
| 54 | 8 | 1993058136 | 0 | RID | 1:29:0 | U | GRANT |
| 54 | 8 | 1993058136 | 0 | PAG | 1:29 | IU | GRANT |
| 54 | 8 | 1993058136 | 0 | TAB | IX | GRANT | |
| 54 | 8 | 1993058136 | 0 | RID | 1:29:1 | U | GRANT |
Как видно, на три строки была наложена блокировка обновления, что означает невозможность обновления этих строк другими транзакциями. Кроме этого, были наложены еще две блокировки, которые относятся к типу блокировок намерения (intent locks) – блокировка на страницу и на таблицу. Пусть вас не смущает колонка Resource – ее содержание я опишу позже. Поле ObjId представляет собой идентификатор объекта, имя которого можно получить с помощью функции object_name. IndId – идентификатор индекса, в данном примере всегда равен 0, так как в таблице test индексов не используется.
Блокировки намерений всегда устанавливаются на таблицу или страницу, но никогда – на строку. Для чего они нужны? Блокировки намерений относятся к специальным типам блокировок и предназначены для повышения производительности работы менеджера блокировок.
Предположим, некая транзакция пытается изменить какую-либо строку в таблице test. Чтобы определить, что эту транзакцию необходимо заблокировать, менеджеру транзакций (в отсутствие блокировок намерения) пришлось бы сканировать всю таблицу syslockinfo для проверки всех строк таблицы test. Чтобы избежать этой неблагодарной работы, менеджер блокировок сразу устанавливает на страницу и таблицу блокировку намерения обновления (Intent Update) и монопольную блокировку намерения (Intent Exclusive) соответственно, и проверяет уже только их. Таким образом, блокировки намерений – это хинт менеджеру блокировок, который дает значительный выигрыш в производительности при проверке совместимости блокировок.
Рассмотрим блокировки намерений более подробно:
Разделяемая блокировка намерений (обозначается IS). Этот тип блокировки предназначен для того, чтобы вместо проверки множества разделяемых блокировок для каждой строки в момент обновления данных, или установки множества разделяемых блокировок для каждой строки в момент чтения данных, можно было установить одну блокировку намерений для страницы или всей таблицы. Этот тип блокировки совместим со всеми остальными режимами, кроме монопольной блокировки. Другими словами, если транзакция пытается обновить данные, на которые наложена разделяемая блокировка намерений, ее выполнение заблокируется до момента завершения первой транзакции.
Монопольная блокировка намерений (обозначается IX). Монопольная блокировка намерений запрещает доступ к данным какого-либо вида (даже на чтение) и позволяет повысить эффективность работы при множественном обновлении данных. В отличие от простой монопольной блокировки она совместима сама с собой. Т.е. другая транзакция также может наложить монопольную блокировку намерений на те же ресурсы, что и первая транзакция.
Разделяемо-монопольная блокировка намерений (обозначается SIX). Подсказывает менеджеру блокировок, что транзакция в основном выполняет чтение данных и лишь частично, в очень небольшом объеме – их изменение. Основное отличие данного типа блокировки от монопольной блокировки намерений состоит в том, что она совместима с разделяемой блокировкой намерений и не совместима ни с какой другой, включая саму себя.
Чтобы уяснить работу этого типа блокировок, продолжим наш пример. Создайте новую сессию (у меня ее идентификатор равен 55) и выполните следующий скрипт:
| begin tran insert into test values(4,'other') |
На удивление многих она прекрасно отработает и не выдаст никаких предупреждений. Как же так? Посмотрим, что скажет нам sp_lock
| sp_lock 54,55 |
Результат приведен в таблице 5:
| spid | dbid | ObjId | IndId | Type | Resource | Mode | Status |
| 54 | 8 | 0 | 0 | DB | S | GRANT | |
| 54 | 8 | 1993058136 | 0 | RID | 1:29:02 | U | GRANT |
| 54 | 8 | 1993058136 | 0 | RID | 1:29:00 | U | GRANT |
| 54 | 8 | 1993058136 | 0 | PAG | 1:29 | IU | GRANT |
| 54 | 8 | 1993058136 | 0 | TAB | IX | GRANT | |
| 54 | 8 | 1993058136 | 0 | RID | 1:29:01 | U | GRANT |
| 55 | 8 | 0 | 0 | DB | S | GRANT | |
| 55 | 8 | 1993058136 | 0 | PAG | 1:29 | IX | GRANT |
| 55 | 8 | 1993058136 | 0 | TAB | IX | GRANT | |
| 55 | 8 | 1993058136 | 0 | RID | 1:29:03 | X | GRANT |
Как видно, предыдущие блокировки остались (еще бы! мы же не зафиксировали транзакцию), и добавились четыре новых: одна блокировка базы, не имеющая никакого значения, две блокировки намерений (на таблицу и страницу) и монопольная блокировка на новую строку (идентификатор 1:29:03).
Пока все более-менее ясно. Однако если выполнить в первой сессии обновление данных, ради которых, собственно, и была наложена блокировка обновления, ничего не выйдет! При выполнении следующей команды сессия заблокируется:
| --begin tran select * from test with (updlock)update test set n = 'other1' where i = 1 |
Если посмотреть на содержимое таблицы syslockinfo с помощью хранимой процедуры sp_lock, можно увидеть, что добавилась еще одна строка (таблица 6).
| spid | dbid | ObjId | IndId | Type | Resource | Mode | Status |
| 54 | 8 | 1993058136 | 0 | RID | 1:29:03 | U | WAIT |
Так как на строку 1:29:03 уже наложена монопольная блокировка, никакие другие блокировки на этот же ресурс наложены быть не могут. Но каким образом наш безобидный update на первую строчку привел к наложению блокировки обновления на четвертую строчку? Дело в том, что при отсутствии индекса менеджер блокировок не может определить, какой именно строке соответствует заданное логическое условие, поэтому производится сканирование и установка монопольной блокировки на каждую строчку таблицы. При наличии индекса SQL Server использует другой метод, основанный на блокировках диапазона индекса. Чтобы перейти к их рассмотрению, вернитесь в сессию с идентификатором 55 и выполните команду отката транзакции: