Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Направление сортировки кластерного индекса и Range Locks  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Всем привет! В общем лазил по своим закладкам и нашел статью,
"Например, если в базе есть Id 15 и Id 1025, и нет ни одного значения между ними, то при выполнении SELECT * FROM Users WHERE FacebookId = 500 будет наложена Shared блокировка на ключи с 15 до 1025", имеется ввиду при Serializable. Плюс мы тут с коллегой спорили про этот уровень, и главный вопрос был, а так ли это и влияет ли сортировка на блокировки?
Решил проверить
+

create table Test_Serial
( 
 id int not null, 
 txt varchar(10) null 
) 
go 

CREATE UNIQUE CLUSTERED INDEX [ClustIndex_Test_Serial] ON [dbo].[Test_Serial] ([id] asc) 
go 

insert into Test_Serial (id)  
values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),  
(20), (21), (22), (23), (24), (25) 
go 

Begin tran Tran_one 
select id from dbo.Test_Serial with (holdlock) 
where id = 15 
--rollback tran Tran_one 

-- Выполняем в другом окне, обновление пройдет 
begin tran Tran_Two 
Update dbo.Test_Serial set txt = 'txt'  
where id = 10 
--rollback tran Tran_Two 

--Меняем where id = 20 и Select заблокирует этот update. 

go 
--Теперь делаем так. 
CREATE UNIQUE CLUSTERED INDEX [ClustIndex_Test_Serial] ON [dbo].[Test_Serial] 
([id] DESC) WITH (drop_existing = on) 
go 

--Теперь если повторить предыдущий select и update то ситуация будет обратная. 
--Update c where id = 20 проходит, а c where id = 10 нет.  

go 
--Теперь делаем так. Т.е. индекс не уникален. 
CREATE CLUSTERED INDEX [ClustIndex_Test_Serial] ON [dbo].[Test_Serial] 
([id] asc) WITH (DROP_EXISTING = ON) ON [PRIMARY] 
GO 

--При update ситуация такая же как и при уникальном индексе. 
-- А вот при insert нет. 

Begin tran Tran_one 
select id from dbo.Test_Serial with (holdlock) 
where id = 15 
--rollback tran Tran_one 

-- Это вставка не пройдет 
Begin tran Tran_two 
insert into dbo.Test_Serial 
values (20,'txt') 
--rollback tran Tran_two 
--И наоборот, если меняем 20 на 10, то вставка не проходит. 
--Т.е. здесь блокироуется диапазон с 10 до 19. 

--Если поменять направление сортировки у индекса то будет блокироваться диапазон с 11 до 20. 


По итогу.
Если индекс уникальный и когда мы выбираем id = 15 (когда в базе есть только 10 и 20), то при индексе ASC
Update where id = 10 - проходит
Update where id = 20 - нет
при индексе DESC
Update where id = 10 - нет
Update where id = 20 - проходит
Если индекс не уникален, то при update также, а вот insert нет, при индексе ASC
insert id = 10 - нет
insert id = 20 - да, т.е. идет блокировка с 10-19
при индексе Desc
insert id = 10 - да
insert id = 20 - нет, т.е. идет блокировка с 11-20
Обрыл весь гугл и не смог найти где бы это упоминалось. Если есть статьи, дайте ссылку.
Или может я не правильно делал опыт и поведение совсем не такое.
28 мар 18, 08:10    [21291895]     Ответить | Цитировать Сообщить модератору
 Re: Направление сортировки кластерного индекса и Range Locks  [new]
invm
Member

Откуда: Москва
Сообщений: 9279
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/05/26/range-locks/
28 мар 18, 08:53    [21292011]     Ответить | Цитировать Сообщить модератору
 Re: Направление сортировки кластерного индекса и Range Locks  [new]
invm
Member

Откуда: Москва
Сообщений: 9279
aleksrov,

Причем "next key" трактуется как физически, так и логически. Физичиский "next key" (который в порядке просмотра индекса) блокируется всегда.
Хорошо иллюстрируется примером:
use tempdb;
go

create table Test (k int);
create index IX_Test on Test (k);

insert into Test (k)
values
 (1), (2), (3), (4);
go
 
select %%lockres%%, * from Test with (index = IX_Test);

-- case 1
begin tran;
set statistics xml on;
select * from Test with (serializable, index = IX_Test) where k between 2 and 3 order by k;
set statistics xml off;
exec sp_lock @@spid;
commit;

-- case 2
begin tran;
set statistics xml on;
select * from Test with (serializable, index = IX_Test) where k between 2 and 3 order by k desc;
set statistics xml off;
exec sp_lock @@spid;
commit;
go

drop table Test;
go

В case 1 физический "next key" совпадает с логическим.
В case 2 не совпадает. Как итог имеем ненужную блокировку на строку с k = 1.
28 мар 18, 11:50    [21292667]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить