MS SQL Server 2008: locking hint для предовращения вставки

@KellyLynch · Регистрация: 15.01.2010

Студворк — интернет-сервис помощи студентам

MS SQL Server 2008

Не могу понять, какой locking hint нужно использовать, чтобы не дать какой-то другой транзакции вставить строку в интересующий меня диапазон строк.

Код примерно такой:

T-SQL
1
2
3
4
5
6
7
8
9
10
begin tran
 
INSERT INTO TargetTable
(WorkCode,WorkNumber)
SELECT Сode, Number
FROM SourceTable
WHERE NOT EXISTS (SELECT 1 FROM TargetTable tt 
                    WHERE tt.WorkCode = Сode 
                        AND tt.WorkNumber = Number)     
commit tran

То есть логика такая: добавлять строку в (WorkCode,WorkNumber) в таблицу TargetTable только если там ещё нет ни одной строки с такой же парой значений (WorkCode,WorkNumber).
Выполняется этот скрипт на уровне изоляции READ COMMITTED SNAPSHOT.
В таблице SourceTable (считаем для простоты) всего одна строка.

Я обнаружил, что если две таких транзакции запустить параллельно, то после COMMIT-а в таблице TargetTable могут оказаться две строки с одинаковой парой значений (WorkCode,WorkNumber)..
Можно предотвратить это, поставив уровень изоляции SERIALIZABLE; но по некоторым причинам мне это неприемлемо. Я хочу добиться результата через locking hint. Но какой?

Причём я не хочу блокировать всю таблицу TargetTable на время вставки. Допустим, я запущу параллельно ещё и такую транзакцию:

T-SQL
1
2
3
4
5
begin tran
INSERT INTO TargetTable
(WorkCode,WorkNumber)
VALUES (123456789, 'A value not equal to the one from SourceTable')
commit tran

то эта последняя транзакция НЕ должна ждать пока не завершатся те первые две. Ведь строка (123456789, 'A value not equal to the one from SourceTable') которую она хочет вставить — НЕ пересекается никак с той строкой из SourceTable.

Я перечитал http://msdn.microsoft.com/en-u... .105).aspx про Table Hints, но так и не понял — что использовать. Потому что не могу применить MSDN к именно моему случаю — "не дать какой-то другой транзакции вставить строку в интересующий нас диапазон строк". Вот например там написано про UPDLOCK:

UPDLOCK
Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.

Не могу понять из этого — он предотвратит вставку НОВЫХ строк в интересующий меня диапазон или нет? То есть если я напишу так:

T-SQL
1
2
3
4
5
6
7
8
INSERT INTO TargetTable
(WorkCode,WorkNumber)
SELECT Сode, Number
FROM SourceTable
WHERE NOT EXISTS (SELECT 1 FROM TargetTable tt 
WITH(UPDLOCK)
                    WHERE tt.WorkCode = Сode 
                        AND tt.WorkNumber = Number)

Это будет то что мне надо?

@invm · 19.09.2013, 12:45

Для начала попробуйте мержем без всяких хинтов:

T-SQL
1
2
3
4
merge into TargetTable t
using SourceTable s on s.Code = t.WorkCode and s.Number = t.WorkNumber
when not matched then
 insert (WorkCode, WorkNumber) values (s.Code, s.Number);

Добавлено через 1 минуту
Ну и ограничение уникальности по WorkCode, WorkNumber было бы не лишним.

@KellyLynch · 19.09.2013, 13:48 **[ТС]**

Сообщение от invm

Для начала попробуйте мержем без всяких хинтов:

T-SQL
1
2
3
4
merge into TargetTable t
using SourceTable s on s.Code = t.WorkCode and s.Number = t.WorkNumber
when not matched then
 insert (WorkCode, WorkNumber) values (s.Code, s.Number);

Добавлено через 1 минуту
Ну и ограничение уникальности по WorkCode, WorkNumber было бы не лишним.

А чем MERGE отличается в этом смысле от WHERE NOT EXISTS (SELECT ...)? Почему он "должен" (или "может") дать ту бллкировку которую я хочу? Где об этом можно почитать?

@invm · 19.09.2013, 14:59

Сообщение от KellyLynch

А чем MERGE отличается в этом смысле от WHERE NOT EXISTS (SELECT ...)?

Потому что merge сразу накладывает U.

Сообщение от KellyLynch

Где об этом можно почитать?

Не знаю, ибо не интересовался. Но можно помониторить профайлером наложение/снятие блокировок.

@KellyLynch · 24.09.2013, 16:48 **[ТС]**

Вот я провёл такой тест:

СОЗДАНИЕ ТАБЛИЦЫ TargetTable и заполнение её некими 100000 начальными значениями (чтоб не пустая была):

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[TargetTable](
    [WorkNumberID] [INT] IDENTITY(1,1) NOT NULL,
    [Workcode] [INT] NOT NULL,
    [WorkNumber] [VARCHAR](40) NOT NULL
) ON [PRIMARY]
 
GO
 
CREATE UNIQUE CLUSTERED INDEX [PrimaryKeyIndex] ON [dbo].[TargetTable] 
(
    [WorkNumberID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX [secondary] ON [dbo].[TargetTable] 
(
    [Workcode] ASC,
    [WorkNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
 
BEGIN TRAN
 
DECLARE @Counter INT = 1
WHILE @Counter <= 100000
BEGIN
        
    INSERT INTO TargetTable
    (WorkCode,WorkNumber)
    VALUES (1000000000 + @Counter, 'A dummy row #' + RTRIM(LTRIM(STR(@Counter))))
 
    SET @Counter = @Counter  + 1
 
END
 
COMMIT TRAN

---Скрипт#1

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DECLARE @SourceTable  TABLE (
        Сode INT,
        NUMBER VARCHAR(40)
    )
    
INSERT INTO @SourceTable
(Сode,NUMBER)
VALUES (1, 'Num')
 
BEGIN tran
 
INSERT INTO TargetTable
(WorkCode,WorkNumber)
SELECT Сode, NUMBER
FROM @SourceTable
WHERE NOT EXISTS (SELECT 1 FROM TargetTable tt 
                    WITH (UPDLOCK, ROWLOCK)
                    --WITH (HOLDLOCK)
                    WHERE tt.WorkCode = Сode 
                        AND tt.WorkNumber = NUMBER)     
 
WAITFOR DELAY '00:00:10'
                        
commit tran

---Скрипт#2. Точная копия Скрипт#1, кроме того что отсутствует DELAY

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DECLARE @SourceTable  TABLE (
        Сode INT,
        NUMBER VARCHAR(40)
    )
    
INSERT INTO @SourceTable
(Сode,NUMBER)
VALUES (1, 'Num')
 
BEGIN tran
 
INSERT INTO TargetTable
(WorkCode,WorkNumber)
SELECT Сode, NUMBER
FROM @SourceTable
WHERE NOT EXISTS (SELECT 1 FROM TargetTable tt 
                    WITH (UPDLOCK, ROWLOCK)
                    --WITH (HOLDLOCK)
WHERE tt.WorkCode = Сode 
                        AND tt.WorkNumber = NUMBER)     
                      
commit tran

---Скрипт#3

SQL
1
2
3
4
5
6
7
BEGIN tran
 
INSERT INTO TargetTable
(WorkCode,WorkNumber)
VALUES (12345, 'A value not from SourceTable')
 
commit tran

Есть небольшая разница с исходным постом - SourceTable является не таблицой а табличной переменной. Но это не имеет значения - блокировки-то не по нему.

Эксперимент #1.
Запускаем один за другим из SQL Studio:

Скрипт #1
Скрипт #2
Скрипт #3

Скрипт #1 работал свои 10 секунд (в соответствии с DELAY)
Скрипт #3 завершился мгновенно, вставив свою строку
Скрипт #2 ждал, пока Скрипт #1 завершится (точнее - снимет блокировку); после чего так ничего и не вставил

РЕЗУЛЬТАТ - то что нужно. Скрипт #2 не нарушил целостности таблицы. Скрипт #3 ничего не ждал - сразу вставил свою строку (никак не пересекающуюся с содержимым @SourceTable).

Теперь Эксперимент #2.
Меняем в Скрипт #1 и Скрипт #2 "(UPDLOCK, ROWLOCK)" на "(HOLDLOCK)".

Запускаем один за другим из SQL Studio:

Скрипт #1
Скрипт #2
Скрипт #3

Скрипт #1 работал свои 10 секунд (в соответствии с DELAY)
Скрипт #3 ждал пока Скрипт #1 завершится (точнее - снимет блокировку); после чего вставил свою строку
Скрипт #2 ждал пока Скрипт #1 завершится (точнее - снимет блокировку); после чего так ничего и не вставил

РЕЗУЛЬТАТ - то что нужно, но не вполне. С целостностью таблицы всё в порядке. НО Скрипту #3 пришлось ждать пока Скрипт #1 закончит свои дела.

Итак - моя цель это сделать так как происходило в Эксперименте #1.
НО - как объяснить, почему в "Эксперименте #1" всё произошло "как надо"? Ведь UPDLOCK "не должен был" сработать так как он сработал? Требовался целый HOLDLOCK. Тем не менее UPDLOCK сработал...

@invm · 25.09.2013, 22:09

Ваш эксперимент абсолютно ни о чем. И ответы ни тут, ни на sql.ru вы похоже не читаете или читаете не вдумываясь.

Начнем с того, что updlock, в вашем применении, ничем не поможет. А поставленную задачу можно решить несколькими способами:
1. Уникальный индекс по Workcode, WorkNumber с опцией IGNORE_DUP_KEY = ON + обычный insert ... select без проверки на сущуствование записи;
2. TIL serializable;
3. Хинты updlock, holdlock в предикате exists инструкции insert. Либо merge с хинтом holdlock;
4. Триггер instead of insert, сериализующий вставку в таблицу.

Варианты 1 и 4 позволят Скрипту #3 выполниться без ожидания.
Триггер должен выглядеть примерно так:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
if object_id('dbo.trTargetTable_InsteadOfInsert', 'TR') is not null
 drop trigger dbo.trTargetTable_InsteadOfInsert;
go
 
create trigger dbo.trTargetTable_InsteadOfInsert
on dbo.TargetTable
instead of insert
as
begin
 set nocount on;
 
 if not exists(select 1 from inserted)
  return;
 
 declare @result int;
 
 exec @result = sp_getapplock 'TargetTable_insert', 'Exclusive', 'Transaction';
 if @result < 0
  begin
   raiserror('Ошибка получения пользовательской эксклюзивной блокировки', 16, 2);
   rollback;
   return;
  end;
 
 merge into dbo.TargetTable t
 using inserted s on s.Workcode = t.Workcode and s.WorkNumber = t.WorkNumber
 when not matched then
  insert (Workcode, WorkNumber) values (s.Workcode, s.WorkNumber);
 
 exec @result = sp_releaseapplock 'TargetTable_insert', 'Transaction';
 if @result < 0
  begin
   raiserror('Ошибка снятия пользовательской эксклюзивной блокировки', 16, 2);
   rollback;
   return;
  end;
end;
go

Если нагрузка на вставку большая, то вариант с триггером не подойдет.

@KellyLynch · 30.09.2013, 14:13 **[ТС]**

Сообщение от invm

Ваш эксперимент абсолютно ни о чем. И ответы ни тут, ни на sql.ru вы похоже не читаете или читаете не вдумываясь.

Начнем с того, что updlock, в вашем применении, ничем не поможет. А поставленную задачу можно решить несколькими способами:
1. Уникальный индекс по Workcode, WorkNumber с опцией IGNORE_DUP_KEY = ON + обычный insert ... select без проверки на сущуствование записи;
2. TIL serializable;
3. Хинты updlock, holdlock в предикате exists инструкции insert. Либо merge с хинтом holdlock;
4. Триггер instead of insert, сериализующий вставку в таблицу.

Варианты 1 и 4 позволят Скрипту #3 выполниться без ожидания.
Триггер должен выглядеть примерно так:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
if object_id('dbo.trTargetTable_InsteadOfInsert', 'TR') is not null
 drop trigger dbo.trTargetTable_InsteadOfInsert;
go
 
create trigger dbo.trTargetTable_InsteadOfInsert
on dbo.TargetTable
instead of insert
as
begin
 set nocount on;
 
 if not exists(select 1 from inserted)
  return;
 
 declare @result int;
 
 exec @result = sp_getapplock 'TargetTable_insert', 'Exclusive', 'Transaction';
 if @result < 0
  begin
   raiserror('Ошибка получения пользовательской эксклюзивной блокировки', 16, 2);
   rollback;
   return;
  end;
 
 merge into dbo.TargetTable t
 using inserted s on s.Workcode = t.Workcode and s.WorkNumber = t.WorkNumber
 when not matched then
  insert (Workcode, WorkNumber) values (s.Workcode, s.WorkNumber);
 
 exec @result = sp_releaseapplock 'TargetTable_insert', 'Transaction';
 if @result < 0
  begin
   raiserror('Ошибка снятия пользовательской эксклюзивной блокировки', 16, 2);
   rollback;
   return;
  end;
end;
go

Если нагрузка на вставку большая, то вариант с триггером не подойдет.

>Начнем с того, что updlock, в вашем применении, ничем не поможет.

А можете обосновать Ваше утверждение? То есть:
1. Почему он "не подойдёт" - со ссылкой на какую-то MS-документацию?
2. Как объяснить результаты того моего эксперимента?

@invm · 30.09.2013, 15:23

Сообщение от KellyLynch

1. Почему он "не подойдёт" - со ссылкой на какую-то MS-документацию?
2. Как объяснить результаты того моего эксперимента?

Потому что нельзя наложить блокировку на ресурс, которого нет.

В случае updlock, ваш первый скрипт никаких U не накладывает, а просто вставляет строку и удерживает на нее X в течение 10 сек. Второй скрипт пытается наложить U на свежевставленную строку и ждет пока первый снимет X. Третий же спокойно вставляет еще одну запись, т.к. никакие блокировки ему не мешают.

Теперь рассмотрим что происходит при holdlock. Holdlock - это синоним serializable. Следовательно, даже если при чтении не обнаружилось подходящих строк, все равно будет наложена блокировка на диапазон ключа или целиком на таблицу, если она куча. В вашем случае, первый скрипт накладывает и удерживает RS-S на диапазон значений ключа, вставляет строку и удерживает на нее X. Второй скрипт также пытается наложить RS-S и ждет пока первый снимет X. Третий пытается наложить X, но ждет пока первый снимет RS-S, т.к. вставляемый ключ попадает в заблокированный диапазон.

Ваша ошибка - последовательное выполнение скриптов. Иначе, при параллельном выполнении скриптов один и два, в варианте с holdlock, - получили бы дедлок. В вашей конструкции, именно для предотвращения дедлока, вместе с holdlock нужно использовать updlock.

@KellyLynch · 30.09.2013, 16:22 **[ТС]**

Сообщение от invm

Ваш эксперимент абсолютно ни о чем. И ответы ни тут, ни на sql.ru вы похоже не читаете или читаете не вдумываясь.

Начнем с того, что updlock, в вашем применении, ничем не поможет. А поставленную задачу можно решить несколькими способами:
1. Уникальный индекс по Workcode, WorkNumber с опцией IGNORE_DUP_KEY = ON + обычный insert ... select без проверки на сущуствование записи;
2. TIL serializable;
3. Хинты updlock, holdlock в предикате exists инструкции insert. Либо merge с хинтом holdlock;
4. Триггер instead of insert, сериализующий вставку в таблицу.

Варианты 1 и 4 позволят Скрипту #3 выполниться без ожидания.
Триггер должен выглядеть примерно так:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
if object_id('dbo.trTargetTable_InsteadOfInsert', 'TR') is not null
 drop trigger dbo.trTargetTable_InsteadOfInsert;
go
 
create trigger dbo.trTargetTable_InsteadOfInsert
on dbo.TargetTable
instead of insert
as
begin
 set nocount on;
 
 if not exists(select 1 from inserted)
  return;
 
 declare @result int;
 
 exec @result = sp_getapplock 'TargetTable_insert', 'Exclusive', 'Transaction';
 if @result < 0
  begin
   raiserror('Ошибка получения пользовательской эксклюзивной блокировки', 16, 2);
   rollback;
   return;
  end;
 
 merge into dbo.TargetTable t
 using inserted s on s.Workcode = t.Workcode and s.WorkNumber = t.WorkNumber
 when not matched then
  insert (Workcode, WorkNumber) values (s.Workcode, s.WorkNumber);
 
 exec @result = sp_releaseapplock 'TargetTable_insert', 'Transaction';
 if @result < 0
  begin
   raiserror('Ошибка снятия пользовательской эксклюзивной блокировки', 16, 2);
   rollback;
   return;
  end;
end;
go

Если нагрузка на вставку большая, то вариант с триггером не подойдет.

Спасибо за методы 1 и 4 - мне они как-то в голову не приходили.

Добавлено через 50 минут

Сообщение от invm

Потому что нельзя наложить блокировку на ресурс, которого нет.

В случае updlock, ваш первый скрипт никаких U не накладывает, а просто вставляет строку и удерживает на нее X в течение 10 сек. Второй скрипт пытается наложить U на свежевставленную строку и ждет пока первый снимет X. Третий же спокойно вставляет еще одну запись, т.к. никакие блокировки ему не мешают.

Теперь рассмотрим что происходит при holdlock. Holdlock - это синоним serializable. Следовательно, даже если при чтении не обнаружилось подходящих строк, все равно будет наложена блокировка на диапазон ключа или целиком на таблицу, если она куча. В вашем случае, первый скрипт накладывает и удерживает RS-S на диапазон значений ключа, вставляет строку и удерживает на нее X. Второй скрипт также пытается наложить RS-S и ждет пока первый снимет X. Третий пытается наложить X, но ждет пока первый снимет RS-S, т.к. вставляемый ключ попадает в заблокированный диапазон.

Ваша ошибка - последовательное выполнение скриптов. Иначе, при параллельном выполнении скриптов один и два, в варианте с holdlock, - получили бы дедлок. В вашей конструкции, именно для предотвращения дедлока, вместе с holdlock нужно использовать updlock.

Вот ради таких объяснений я и завёл эту дискуссию :-) - сейчас хоть что-то начинаю понимать в этих блокировках...

Однако остался такой вопрос. Вы написали:

Теперь рассмотрим что происходит при holdlock. Holdlock - это синоним serializable. Следовательно, даже если при чтении не обнаружилось подходящих строк, все равно будет наложена блокировка на диапазон ключа или целиком на таблицу, если она куча. В вашем случае, первый скрипт накладывает и удерживает RS-S на диапазон значений ключа, вставляет строку и удерживает на нее X. Второй скрипт также пытается наложить RS-S и ждет пока первый снимет X. Третий пытается наложить X, но ждет пока первый снимет RS-S, т.к. вставляемый ключ попадает в заблокированный диапазон."

Те мои скрипты использовали такие данные:

в таблице TargetTable изначально лежало 100,000 строк такого вида:
(1000000001, 'A dummy row #1000000001')
...
(1000100000, 'A dummy row #1000100000')

В таблице @SourceTable лежала одна строка (и её вставляли Скрипты #1 и #2):
(1, 'Num')

Скрипт #3 вставлял такую строку:
(12345, 'A value not from SourceTable')

Теперь хочу выяснить - почему Вы утверждаете что "вставляемый [Скриптом #3] ключ попадает в заблокированный диапазон"? "Вставляемый ключ" в нашем случае - это (12345, 'A value not from SourceTable'). Скрипт #1 сначала искал, а потом вставлял строку (1, 'Num'). Выходит, "заблокированный диапазон" оказался таким что затронул и (12345, 'A value not from SourceTable')? А почему? Я-то думал что SQL Server включит в "заблокированный диапазон ключа" только ту строку (1, 'Num').

Или Вы имели в виду что та моя таблица SourceTable относится к разряду "куча" и потому "заблокированный диапазон" в реальности расширился до всей таблицы? Но в моей SourceTable есть clustered index (хотя и не по столбцам [Workcode], [WorkNumber]); то есть "кучей" она быть не может

Пожалуйста, поясните

@invm · 30.09.2013, 17:45

Сообщение от KellyLynch

Теперь хочу выяснить - почему Вы утверждаете что "вставляемый [Скриптом #3] ключ попадает в заблокированный диапазон"? "Вставляемый ключ" в нашем случае - это (12345, 'A value not from SourceTable'). Скрипт #1 сначала искал, а потом вставлял строку (1, 'Num'). Выходит, "заблокированный диапазон" оказался таким что затронул и (12345, 'A value not from SourceTable')? А почему? Я-то думал что SQL Server включит в "заблокированный диапазон ключа" только ту строку (1, 'Num').

Потому что в диапазоне (0, '') - (1000000001, 'A dummy row #1000000001') нет строки (1, 'Num').
Вот пример:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
use tempdb;
go
 
create table dbo.t (t_id int identity primary key, f1 int not null, f2 int not null);
create unique index IX_t on dbo.t(f1, f2);
go
 
insert into dbo.t (f1, f2) values (100, 100), (200, 200);
go
 
declare @s table (f1 int, f2 int);
--insert into @s values (100, 100);
insert into @s values (1, 1);
 
begin tran;
 
insert into dbo.t
 (f1, f2)
 select
  f1, f2
 from
  @s s
 where
  not exists(select 1 from dbo.t with (holdlock) where f1 = s.f1 and f2 = s.f2);
 
exec sp_lock @@spid;
 
select %%lockres%%, * from dbo.t;
select %%lockres%%, * from dbo.t with(index = IX_t);
 
rollback;
go
 
drop table dbo.t;
go

Посмотрите блокировки для (1, 1) и (100, 100)

@KellyLynch · 30.09.2013, 18:33 **[ТС]**

Сообщение от invm

Потому что в диапазоне (0, '') - (1000000001, 'A dummy row #1000000001') нет строки (1, 'Num').
Вот пример:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
use tempdb;
go
 
create table dbo.t (t_id int identity primary key, f1 int not null, f2 int not null);
create unique index IX_t on dbo.t(f1, f2);
go
 
insert into dbo.t (f1, f2) values (100, 100), (200, 200);
go
 
declare @s table (f1 int, f2 int);
--insert into @s values (100, 100);
insert into @s values (1, 1);
 
begin tran;
 
insert into dbo.t
 (f1, f2)
 select
  f1, f2
 from
  @s s
 where
  not exists(select 1 from dbo.t with (holdlock) where f1 = s.f1 and f2 = s.f2);
 
exec sp_lock @@spid;
 
select %%lockres%%, * from dbo.t;
select %%lockres%%, * from dbo.t with(index = IX_t);
 
rollback;
go
 
drop table dbo.t;
go

Посмотрите блокировки для (1, 1) и (100, 100)

Ага, понял

И напоследок хотел бы опять вернуться к моемй Эксперименту #1 (где UPDLOCK).
Вот Вы писали:

"В случае updlock, ваш первый скрипт никаких U не накладывает, а просто вставляет строку и удерживает на нее X в течение 10 сек. Второй скрипт пытается наложить U на свежевставленную строку и ждет пока первый снимет X."

Из этого я понял что если бы мои Скрипт #1 и Скрипт #2 выполнялись на самом деле параллельно - моя защита не сработала бы!

- Скрипт #1 и Скрипт #2 одновременно узнали бы что такой строки в таблице ещё нет. Это - "...WHERE NOT EXISTS (SELECT 1 FROM TargetTable tt WITH (UPDLOCK, ROWLOCK) WHERE tt.WorkCode = Сode AND tt.WorkNumber = NUMBER)"

- затем каждый из них одновременно вставил бы по одному экземпляру этой строки. При этом каждый из скриптов положил бы lock X на свою вставленную строку

Так?

@invm · 30.09.2013, 19:35

Сообщение от KellyLynch

- Скрипт #1 и Скрипт #2 одновременно узнали бы что такой строки в таблице ещё нет. Это - "...WHERE NOT EXISTS (SELECT 1 FROM TargetTable tt WITH (UPDLOCK, ROWLOCK) WHERE tt.WorkCode = Сode AND tt.WorkNumber = NUMBER)"
- затем каждый из них одновременно вставил бы по одному экземпляру этой строки. При этом каждый из скриптов положил бы lock X на свою вставленную строку
Так?

Почти. Первый наложил X на ключ и вставил строку. Второй тоже хочет X и ждет когда завершится транзакция первого. Потом тоже накладывает X на тот же ключ и обламывается на вставке дубликата.

@KellyLynch · 30.09.2013, 21:02 **[ТС]**

Сообщение от invm

Почти. Первый наложил X на ключ и вставил строку. Второй тоже хочет X и ждет когда завершится транзакция первого. Потом тоже накладывает X на тот же ключ и обламывается на вставке дубликата.

"Первый наложил X на ключ " - а почему здесь Вы говорите "наложил X на ключ", а не "на строку"? Ведь этот мой вопрос был про UPDLOCK, не HOLDLOCK. И Вы же писали выше: "...В случае updlock, ваш первый скрипт никаких U не накладывает, а просто вставляет строку и удерживает на нее X в течение 10 сек..."

Так всё-таки - "на строку" или "на ключ"?

@invm · 30.09.2013, 21:27

Грубо говоря, т.к. индекс PrimaryKeyIndex кластерный, то блокировка ключа блокирует и строку.
А вообще, блокировка накладывается на ресурс. Ресурсом может быть ключ, RID, страница, таблица и т.п.
В вашем случае, при вставке, блокировка X будет наложена на ключи индексов. Если PrimaryKeyIndex сделаете некластерным, то добавится еще X на RID строки.

Новые блоги и статьи Все статьи Все блоги /
сукцессия микоризы: основная теория в виде двух уравнений. anaschu 11.01.2026 https:/ / rutube. ru/ video/ 7a537f578d808e67a3c6fd818a44a5c4/	WordPad для Windows 11 Jel 10.01.2026 WordPad для Windows 11 — это приложение, которое восстанавливает классический текстовый редактор WordPad в операционной системе Windows 11. После того как Microsoft исключила WordPad из. . .	Classic Notepad for Windows 11 Jel 10.01.2026 Old Classic Notepad for Windows 11 Приложение для Windows 11, позволяющее пользователям вернуть классическую версию текстового редактора «Блокнот» из Windows 10. Программа предоставляет более. . .	Почему дизайн решает? Neotwalker 09.01.2026 В современном мире, где конкуренция за внимание потребителя достигла пика, дизайн становится мощным инструментом для успеха бренда. Это не просто красивый внешний вид продукта или сайта — это. . .
Модель микоризы: классовый агентный подход 3 anaschu 06.01.2026 aa0a7f55b50dd51c5ec569d2d10c54f6/ O1rJuneU_ls https:/ / vkvideo. ru/ video-115721503_456239114	Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ФедосеевПавел 06.01.2026 Owen Logic: О недопустимости использования связки «аналоговый ПИД» + RegKZR ВВЕДЕНИЕ Введу сокращения: аналоговый ПИД — ПИД регулятор с управляющим выходом в виде числа в диапазоне от 0% до. . .	Модель микоризы: классовый агентный подход 2 anaschu 06.01.2026 репозиторий https:/ / github. com/ shumilovas/ fungi ветка по-частям. коммит Create переделка под биомассу. txt вход sc, но sm считается внутри мицелия. кстати, обьем тоже должен там считаться. . . .	Расчёт токов в цепи постоянного тока igorrr37 05.01.2026 / * Дана цепь постоянного тока с сопротивлениями и источниками (напряжения, ЭДС и тока). Найти токи и напряжения во всех элементах. Программа составляет систему уравнений по 1 и 2 законам Кирхгофа и. . .

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	30.09.2013, 21:27
	Грубо говоря, т.к. индекс PrimaryKeyIndex кластерный, то блокировка ключа блокирует и строку. А вообще, блокировка накладывается на ресурс. Ресурсом может быть ключ, RID, страница, таблица и т.п. В вашем случае, при вставке, блокировка X будет наложена на ключи индексов. Если PrimaryKeyIndex сделаете некластерным, то добавится еще X на RID строки. 0