Блокировки в MS SQL Server 2000 (стр. 6 из 9)
| spid | dbid | ObjId | IndId | Type | Resource | Mode | Status |
| 55 | 8 | 2009058193 | 2 | KEY | (ffffffffffff) | RangeS-S | GRANT |
| 56 | 8 | 2009058193 | 0 | RID | 1:29:04 | X | GRANT |
| 56 | 8 | 0 | 0 | PAG | 1:29 | IX | GRANT |
| 56 | 8 | 2009058193 | 2 | PAG | 1:34 | IX | GRANT |
| 56 | 8 | 2009058193 | 2 | KEY | (ffffffffffff) | RangeIn | WAIT |
| 56 | 8 | 2009058193 | 0 | TAB | IX | GRANT |
Так как тип блокировки RangeIn (aka RangeI) несовместим ни с каким другим типом блокировки диапазона, сессия за номером 56 блокируется. Примечательный факт – само значение все-таки было вставлено в таблицу, о чем свидетельствует выделенная строка, и только в момент обновления индекса возникла проблема совместимости блокировок. Чтобы убедится, что значение действительно было вставлено, можно воспользоваться командой dbcc page, которая подробно описана в следующем разделе. Кроме этого видно, что установлены блокировки намерений на таблицу и две страницы: страницу с данными и страницу, на которой располагается индекс.
| ПРИМЕЧАНИЕИндекс располагается на другой странице вследствие того, что он является некластерным. |
Теперь отменим транзакцию и выполним такой код:
| --set implicit_transactions on--select * from test_key with (serializable)delete from test_key with (serializable) where i = 4--rollback |
Здесь мы пытаемся удалить несуществующую строчку. В соответствии с тем, что наш запрос должен выдавать всегда один и тот же результат, менеджер блокировок должен обеспечить, что никакие другие транзакции не смогут вставить какое-либо значение в диапазон от 3 до 5, а также изменить граничные значения ключей на 4. Это достигается за счет установки разделяемой блокировки обновления диапазона.
| spid | dbid | ObjId | IndId | Type | Resource | Mode | Status |
| 55 | 8 | 2009058193 | 2 | KEY | (23005e3c905a) | RangeS-U | GRANT |
| 55 | 8 | 2009058193 | 2 | PAG | 1:34 | IU | GRANT |
Если бы удаляемое значение было равно, например, 40, догадайтесь, какой диапазон был бы заблокирован менеджером блокировок. :) Он бы заблокировал весь диапазон от 9 до положительной бесконечности. А что если бы граничные значения ключа составляли бы не 3 и 5, а 9 и 500? Был бы заблокирован диапазон от 9 до 500. Таковы требования высшего уровня изоляции транзакций!
Чтобы еще лучше усвоить правила блокировки диапазонов, попробуйте выполнить такой запрос:
| --set implicit_transactions onselect * from test_key with (serializable) where i = 10--delete from test_key with (serializable) where i = 4--rollback |
Он приведет к установлению разделяемой блокировки на диапазон от 9 до +INF, так что никакие операции вставки для этого диапазона не смогут быть выполнены.
Последний тип блокировок, рассматриваемых в этой статье, связан с командами DDL и схемой данных (Schema). Блокировка схемы (Schema lock) предназначена для блокирования метаданных объектов базы данных. Типичной командой изменения таких данных может служить команда ALTER. Эта команда приводит к изменению системных таблиц метаданных, таких, как syscolumns, sysobjects, sysforeignkeys и других. Чтобы запретить другим транзакциям обращаться к модифицируемым ресурсам и их метаданным, введены блокировки схемы. Блокировки схемы бывают всего двух типов:
Блокировка стабильности схемы (Schema Stability Lock), обозначается Sch-S. Данный тип блокировки предназначен для гарантии неизменности метаданных, но не самих данных. Т.е. блокировка стабильности схемы – единственная из всех типов блокировок, совместимых с монопольной блокировкой. В основном она устанавливается при компиляции тела запроса или хранимой процедуры, на это время запрещается вносить изменения в схему данных, однако разрешается устанавливать любой тип блокировок на сами данные, с которыми будет работать компилируемый запрос.
Блокировка изменения схемы (Schema Modification Lock), обозначается Sch-M. Данный тип блокировки не совместим ни с каким другим типом, ни с самим собой. Устанавливается после внесения изменений в схему данных и снимается после завершения транзакции.
Рассмотрим пример. В первой сессии выполните следующий код:
| begin tran alter table test add another_field int |
В таблице 11 приведено содержимое таблицы syslockinfo для данной сессии.
| spid | dbid | ObjId | IndId | Type | Resource | Mode | Status |
| 55 | 8 | 0 | 0 | DB | S | GRANT | |
| 55 | 8 | 1 | 0 | TAB | IX | GRANT | |
| 55 | 8 | 3 | 0 | TAB | IX | GRANT | |
| 55 | 8 | 3 | 2 | KEY | (31038b3afd68) | X | GRANT |
| 55 | 8 | 1993058136 | 0 | TAB | Sch-M | GRANT | |
| 55 | 8 | 1 | 1 | KEY | (5800c7965d9d) | X | GRANT |
| 55 | 8 | 3 | 1 | KEY | (5b00f3711a81) | X | GRANT |
| 55 | 8 | 0 | 0 | DB | S | GRANT |
Давайте проанализируем четыре выделенные строчки:
Наложена монопольная блокировка ключа на объект с идентификатором 3. Это не что иное, как таблица syscolumns. Более подробную информацию об этом ключе можно получить, выполнив такой запрос:
| select * from sysindexes where id = 3 and indid = 2 |
Наложена блокировка изменения схемы на объект с идентификатором 1993058136. Это не что иное, как таблица test.
Наложена монопольная блокировка ключа на объект с идентификатором 1. Это не что иное, как таблица sysobjects. Более подробную информацию об этом ключе можно получить, выполнив такой запрос:
| select * from sysindexes where id = 1 and indid = 1 |
Наложена монопольная блокировка ключа c идентификатором 1 на объект с идентификатором 3. Подробную информацию об этом ключе можно получить, выполнив такой запрос:
| select * from sysindexes where id = 3 and indid = 1 |
Еще один тип блокировки – Bulk Update – используется совместно с операциями массивного обновления. Он совместим только с блокировкой стабильности схемы и с самим собой.
Блокировки могут преобразовываться друг в друга по следующей схеме (рисунок 1).
Рисунок 1.
Таблица 12 показывает совместимость описанных типов блокировок друг с другом.
| Тип | IS | S | U | IX | SIX | X | Sch-S | Sch-M | BU |
| IS | + | + | + | + | + | - | + | - | - |
| S | + | + | + | - | + | - | + | - | - |
| U | + | + | - | - | - | - | + | - | - |
| IX | + | + | - | + | - | - | + | - | - |
| SIX | + | + | - | - | - | - | + | - | - |
| X | - | - | - | - | - | - | + | - | - |
| Sch-S | + | + | + | + | + | + | + | - | + |
| Sch-M | - | - | - | - | - | - | - | - | - |
| BU | - | - | - | - | - | - | + | - | + |
Как уже говорилось ранее, для каждой сессии может быть выбран используемый по умолчанию уровень изоляции, действующий при выполнении любых команд T-SQL. Ниже я покажу, как это сделать.
Рассмотрим синтаксис команды установки уровня изоляции по умолчанию для текущего соединения.
| SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE } |
READ UNCOMMITTED – устанавливает уровень изоляции транзакций, на котором решается проблема «грязной» записи;
READ COMMITTED – устанавливает уровень изоляции, на котором решается проблема «грязного» чтения;
REPEATABLE READ – устанавливает уровень изоляции, на котором решается проблема неповторяемого чтения;
SERIALIZABLE – устанавливает уровень изоляции, на котором решается проблема чтения фантомов.
Установка какого-либо значения остается действительной до конца сессии, или до тех пор, пока пользователь явно не изменит уровень изоляции транзакций.
Существует документированный способ просмотра текущего уровня изоляции с помощью команды DBCC USEROPTIONS, но лично я всегда пользуюсь более мощным, хотя и не документированным, средством. Чтобы узнать текущее значение изоляции, нужно воспользоваться таким скриптом:
| dbcc traceon (3604)dbcc pss(1,53)dbcc traceoff (3604) |
Команда dbcc pss предназначена для вывода текущих настроек указанного в параметре соединения. Вот ее синтаксис:
| DBCC pss( suid, spid, printopt = { 1 | 0 } ) |
suid – идентификатор пользователя (см. хранимую процедуру sp_helpuser);
spid – идентификатор серверного процесса;
printopt – флаг вывода (0 – стандартная информация, 1 – расширенная). По умолчанию 0.
Эта команда выводит много интересной информации, но для нас главное – выяснить текущий уровень изоляции. Для этого нужно среди выведенных многочисленных переменных найти одну с названием isolation_level. В моем случае она оказалась равна 0. Это говорит о том, что для данного соединения используется уровень изоляции по умолчанию, который, как я уже говорил, равен READ COMMITTED. Вот все значения, которые может принимать эта переменная:
0 – уровень изоляции по умолчанию (READ COMMITTED);
1 – уровень изоляции READ UNCOMMITTED;
2 – уровень изоляции READ COMMITTED;
3 – уровень изоляции REPEATABLE READ;
4 – уровень изоляции SERIALIZABLE.
Теперь рассмотрим, каким образом управлять уровнем изоляции транзакций на уровне отдельных команд. Вот синтаксис раздела FROM, который относится к нашей теме (все сказанное ниже относится не только к запросам, но и к командам изменения данных DELETE и UPDATE):
| FROM { < table_source > }<table_source> ::= table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]< table_hint > ::= { INDEX ( index_val [ ,...n ] ) | FASTFIRSTROW | HOLDLOCK | NOLOCK | PAGLOCK | READCOMMITTED | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK } |
Уровни READUNCOMMITTED и READCOMMITTED соответствуют одноименным уровням изоляции транзакций, только пишутся слитно. Блокировки при использовании этих уровней снимаются сразу после выполнения команды. В случае хинтов REPEATABLEREAD и SERIALIZABLE блокировки остаются до конца транзакции. Далее приводится подробное описание всех остальных хинтов.