Здравствуйте, wildwind, Вы писали:
W>Для меня все же остается непонятным, почему нельзя использовать key-range lock в отсутствие индекса. key-range это ведь виртуальный объект, ни к какому физическому (вроде таблицы или записи) не привязан.
Key-range один из режимов блокирования, но использует он физический индекс. А каким образом можно иначе реализовать механизм исключающий фантомное чтение для уровня serializable, чтобы в определенном интервале заблокировать вставку записей с минимальными затратами ресурсов и доступным уровнем конкуренции? Например, страница, но она по сути своей представляет разнородный неупорядоченный набор данных, где невозможно определить ни начало, ни конец, а чтобы это сделать нужно обойти все страницы, что в конечном итоге сводится к блокировке целой таблицы. Про записи я вообще молчу, это более мелкая единица, а затраты на реализацию блокировок в интервальном значении еще больше. Другое дело индекс, который представляет собой сбалансированное дерево с упорядоченной структурой, где легко можно найти начало-конец значения и использовать их для предотвращения вставки новых значений, тем самым, исключив аномалию фантомное чтение. Но это только мое мнение.
Здравствуйте, Olaf, Вы писали:
O> Key-range один из режимов блокирования, но использует он физический индекс.
Я понимаю, для чего используется Key-range. Мне интересно, для чего он логически привязан именно к индексу, а не просто к полю. Конечно, это уже интминые детали реализации, но все же. Насколько мне известно, блокировки в SQL Server физически не привязаны к физическим структурам данных (таблицам и индексам), менеджер блокировок хранит их в своих структурах в памяти. В отличие, скажем, от Oracle, где блокировки именно физически привязаны к данным, информация о них хранится вместе с данными, на страницах таблиц и индексов. Казалось бы, зачем менеджеру блокировок необходимо существование индекса?
Рассмотрим например ситуацию, когда индекс имеется, и мы на уровне serializable делаем select where column between 'X' and 'Y'. Даже если в данном интервале не окажется строк, key-range блокировка будет все равно наложена, на пустой диапазон, так? Или в этом случае на всю таблицу?
Здравствуйте, wildwind, Вы писали: w> Рассмотрим например ситуацию, когда индекс имеется, и мы на уровне serializable делаем select where column between 'X' and 'Y'. Даже если в данном интервале не окажется строк, key-range блокировка будет все равно наложена, на пустой диапазон, так?
Нашел старый SQL 205 Express? новее у меня сейчас ничего нет. В описанной ситуации наблюдаю key-range блокировки что с индексом, что без оного.
Подготовка
Скрытый текст
drop table T1
create table T1 (
Id Int Identity primary key,
Value1 varchar(50),
Value2 varchar(50)
)
create index IX_T1_Value1 on T1 (value1)
insert into T1 (Value1, Value2) values ('AAA', 'AAA')
insert into T1 (Value1, Value2) values ('BBB', 'BBB')
insert into T1 (Value1, Value2) values ('EEE', 'EEE')
Эксперимент
Скрытый текст
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM T1
WHERE Value2 between 'XXX' and 'YYY'
SELECT resource_type, resource_description, request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
AND resource_type <> 'DATABASE'
Здравствуйте, wildwind, Вы писали:
W>Рассмотрим например ситуацию, когда индекс имеется, и мы на уровне serializable делаем select where column between 'X' and 'Y'. Даже если в данном интервале не окажется строк, key-range блокировка будет все равно наложена, на пустой диапазон, так? Или в этом случае на всю таблицу?
Если есть индекс, а диапазон пустой, то блокировка наложится на ближайшие значения индекса (причем, влючая нижний индекс и не включая верхний, если я правильно помню). Если же индекса нет, то на всю таблицу.
Слово Key в Key-Range обозначает именно ключ индекса )
Если бы можно было накладывать блокировку на пустое место, то небыло бы смысла в такой затее как Key-Range — заблокировал пустое значение и все...
Не смотря на то, что блокировки хранятся отдельно — все равно нужна какая-то привязка к реальным объектам по которым идет выборка, отсюда и возникает необходимость привязываться к ключам индекса.
Здравствуйте, Tigor, Вы писали:
T> Так это, наверно, рендж блокировки на весь кластернный индекс и получились.
Похоже на то.
Значения resource_description, кстати, соответствуют таковым при выборке по PK. И вставка строки в другой транзакции блокируется, причем независимо от того, попадает ли Value2 в данный диапазон.
Здравствуйте, wildwind, Вы писали:
W>Здравствуйте, wildwind, Вы писали:
w>> Рассмотрим например ситуацию, когда индекс имеется, и мы на уровне serializable делаем select where column between 'X' and 'Y'. Даже если в данном интервале не окажется строк, key-range блокировка будет все равно наложена, на пустой диапазон, так?
W>Нашел старый SQL 205 Express? новее у меня сейчас ничего нет. В описанной ситуации наблюдаю key-range блокировки что с индексом, что без оного.
W>...
1. Есть описание алгоритма для уникального/неуникального индекса с предикатом сравнения и диапазона Range locks
Ваш случай следующий:
Range Predicate (key between the two values)
◦‘range lock on all the key values in the range when using ‘between’
◦‘range’ lock on the ‘next’ key that is outside the range. This is true both for unique and non-unique indexes. This is to ensure that no row can be inserted between the requested key and the one after that. If the ‘next’ key does not exist, then a range lock is taken on the ‘infinity’ value.
Т.е. диапазонная блокировка накладывается до так называемого бесконечного значения, фактически до конца диапазона. Судя потому, что вы показали это и есть значение ffffffffffff.
2. Давайте для подтверждения наличия key-range блокировок (с индексом и без) запускать следующий запрос
select object_name(i.[object_id]) as ObjectName,
i.name AS IndexName,
l.request_mode AS LockRequestMode,
l.resource_type AS LockResourceType,
l.resource_description AS LockResourceDescription
from sys.dm_tran_locks as l
left join sys.partitions p on p.hobt_id = l.resource_associated_entity_id
left join sys.indexes as i on i.[object_id] = p.[object_id] and i.index_id = p.index_id
where l.resource_type != 'database'
вместо вашего select * from sys.dm_tran_locks.
3. Индекс на самом деле вы создали, причем кластерный по полю Id, а потом еще один. Уберите его и проведите эксперимент заново.
Здравствуйте, wildwind, Вы писали: W>Это таблица? Почему тогда ее имя не отображается?
Да, это таблица. Мой запрос просто не рассчитан на это, отображать имя таблицы. Главное определить какой индекс используется и какие блокировки на него накладываются. Ниже исправленная версия:
Запрос
select l.request_session_id as Spid,
db_name(l.resource_database_id) as DbName,
case
when l.resource_type = 'OBJECT' then object_name(l.resource_associated_entity_id)
when l.resource_associated_entity_id = 0 then ''
else object_name(p.object_id) end as ObjectName,
i.name as IndexName,
l.request_mode as LockRequestMode,
l.resource_type as LockResourceType,
l.resource_description as LockResourceDescription
from sys.dm_tran_locks as l
left join sys.partitions p on p.hobt_id = l.resource_associated_entity_id
left join sys.indexes as i on i.[object_id] = p.[object_id] and i.index_id = p.index_id
where l.resource_type != 'database'
Здравствуйте, Divineshadow, Вы писали:
D>Объясните пожалуйста, почему возникает исключение и что я не так не так понимаю?
Ну, почему здесь вылезает дедлок — уже объяснили, а сама задача правильно решается так:
1. Использовать дефолтный уровень изоляции (Read Committed)
2. Построить уникальный индекс по value1
3.1 Надо быть готовым, что очень-очень редко, при высоких нагрузках и высокой вероятности нескольким транзакциям одновременно добавить одно и то же значение value1 будет вылезать исключение при попытке вставить двойное значение в уникальный индекс. Скорее всего за время жизни приложения это ни разу и не случится.
Либо
3.2 При выборке id по value1 использовать хинт UPDLOCK, что уберет вероятность вставки одного и того же значения value1 двумя разными транзакциями
Мы уже победили, просто это еще не так заметно...
