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

Откуда:
Сообщений: 94
MS SQL Server 2008: lockinMS SQL Server 2008

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

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


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 на время вставки. Допустим, я запущу параллельно ещё и такую транзакцию:


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-us/library/ms187373(v=sql.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.


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


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)





Это будет то что мне надо?
20 сен 13, 15:47    [14864609]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
serializable можно и хинтом для конкретной таблицы указать.
20 сен 13, 16:03    [14864731]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
SERIALIZABLE
Guest
читайте про TIL
особенно про SERIALIZABLE
20 сен 13, 16:08    [14864766]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
SERIALIZABLE
Guest
SERIALIZABLE
читайте про TIL
особенно про SERIALIZABLE

ах да

HOLDLOCK = SERIALIZABLE
20 сен 13, 16:09    [14864773]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
Гость333
Member

Откуда:
Сообщений: 3683
KellyLynch
UPDLOCK

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

Вставку в диапазон, как уже сказали, предотвратит хинт HOLDLOCK (он же SERIALIZABLE).
А чтобы не возникали дедлоки, укажите два хинта XLOCK, HOLDLOCK.
20 сен 13, 16:43    [14865004]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
KellyLynch
Member

Откуда:
Сообщений: 94
Гость333
KellyLynch
UPDLOCK

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

Вставку в диапазон, как уже сказали, предотвратит хинт HOLDLOCK (он же SERIALIZABLE).
А чтобы не возникали дедлоки, укажите два хинта XLOCK, HOLDLOCK.


Спасибо за указание - да, теперь я вижу что HOLDLOCK (= SERIALIZABLE) приведёт к тому что:

...
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
...


Но у меня появилась ПРОТИВОПОЛОЖНАЯ проблема :-) - как убедить коллег что UPDLOCK в данной ситуации будет недостаточен? Они считают что хватит и UPDLOCK+ROWLOCK. Тем более что я проводил тест и видел: в моём тесте хватило и UPDLOCK+ROWLOCK! Такая комбинация сделала то что я хочу:
- не дала второй такой же транзакции вставить строку;
- позволила вставить строку (123456789, 'A value not equal to the one from SourceTable') ещё одной транзакции, которая работала в другом диапазоне значений (см мой исходный пост)

Итак - КАК ЭТО ОБЪЯСНИТЬ? UPDLOCK "не должен был" блокировать вставку , но он это сделал... Почему? Может это из-за страничной гранулярности? Опять же из MSDN-а не поймёшь.
22 сен 13, 22:08    [14869943]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
KellyLynch
Member

Откуда:
Сообщений: 94
Гость333
KellyLynch
UPDLOCK

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

Вставку в диапазон, как уже сказали, предотвратит хинт HOLDLOCK (он же SERIALIZABLE).
А чтобы не возникали дедлоки, укажите два хинта XLOCK, HOLDLOCK.


Хочу ещё спросить отдельно о "А чтобы не возникали дедлоки, укажите два хинта XLOCK, HOLDLOCK"

А кк именно добавление XLOCK поможет устранить (или снизить вероятность) появления deadloсk-ов?
22 сен 13, 22:53    [14870066]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
Гость333
Member

Откуда:
Сообщений: 3683
KellyLynch
Тем более что я проводил тест и видел: в моём тесте хватило и UPDLOCK+ROWLOCK!

Покажите ваш тест в виде воспроизводимого T-SQL кода.
23 сен 13, 09:31    [14870778]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> - не дала второй такой же транзакции вставить строку;

такой же - может, и нет. а вот такой, скажем, не пробовали:
INSERT INTO TargetTable (WorkCode,WorkNumber) VALUES (123, 'A value _equal_ to the one from SourceTable')

но, присоединюсь: лучше конечно воспроизводимый пример увидеть.
23 сен 13, 09:41    [14870814]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
SERIALIZABLE
Guest
KellyLynch
Но у меня появилась ПРОТИВОПОЛОЖНАЯ проблема :-) - как убедить коллег что UPDLOCK в данной ситуации будет недостаточен? Они считают что хватит и UPDLOCK+ROWLOCK.



первое окно SSMS
/*
создание таблиц + заполнение вспомогательной таблицы.
*/

-- откат транзакции прошлого запуска
if @@trancount > 0 rollback

-- таблица с конкурентной вставкой 
if object_id('dbo.t1') is not null
	drop table dbo.t1
create table t1 (id int not null, spid int, primary key (id)) 

-- вспомогательная таблица для генерации новых id 
if object_id('dbo.t') is not null
	drop table dbo.t
create table dbo.t(id int identity(1,1), f int, primary key (id))
while 1=1
begin
	begin tran
        -- увеличение id на 1
	insert dbo.t(f)
	values (1)

	-- небольшое ожидание, чтобы писатели вместе подошли к выбору нового id
	waitfor delay '00:00:00.1'
	commit tran
end



второе и третье (четвертое, пятое и т.д.) окно SSMS, с конкурентной вставкой
-- читаем максимальное значение из вспомогательной таблицы и пытаемся его записать. 
while 1=1
begin
	insert dbo.t1  (id, spid)
	select id, @@spid
	from (
		select id =MAX(ID)
                /* тут мы ждем окончание очередной транзакции в первом окне, 
               чтобы одновременно взять это значение и наперегонки пытаться его записать*/
		from dbo.t with (xlock)
	) t
	where not exists (
		select *
/* 
               используем три варианта хинтов.
               1.with  (updlock, rowlock)  - падает на уникальности, ЧТД
               2.with  (holdlock, rowlock)  - один из коннектов падает на deadlock-е, о чем говорил Гость333
               3.with  (holdlock, rowlock, xlock) -  все нормально, записи добавляются, spid-ы в таблице с конкурентной вставкой разные
*/

		from dbo.t1 with  (updlock, rowlock) 
		where id = t.id
	)
end
23 сен 13, 10:27    [14871046]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
KellyLynch
Member

Откуда:
Сообщений: 94
Гость333
KellyLynch
Тем более что я проводил тест и видел: в моём тесте хватило и UPDLOCK+ROWLOCK!

Покажите ваш тест в виде воспроизводимого T-SQL кода.


Вот он

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

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

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

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

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 сработал...
24 сен 13, 16:42    [14878591]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
KellyLynch
Member

Откуда:
Сообщений: 94
daw
> - не дала второй такой же транзакции вставить строку;

такой же - может, и нет. а вот такой, скажем, не пробовали:
INSERT INTO TargetTable (WorkCode,WorkNumber) VALUES (123, 'A value _equal_ to the one from SourceTable')

но, присоединюсь: лучше конечно воспроизводимый пример увидеть.



Вот он

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

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

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

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

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 сработал...
24 сен 13, 16:43    [14878600]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
SERIALIZABLE
Guest
KellyLynch,

проблема c updlock "вылезает" в том случает, когда между not exists и insert (внутри одного запроса) одного коннекта, успеет проскочить not exists другого коннекта.

это не самый частый случай, но как его имитировать я показал выше
24 сен 13, 16:49    [14878628]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
KellyLynch
Member

Откуда:
Сообщений: 94
SERIALIZABLE
KellyLynch,

проблема c updlock "вылезает" в том случает, когда между not exists и insert (внутри одного запроса) одного коннекта, успеет проскочить not exists другого коннекта.

это не самый частый случай, но как его имитировать я показал выше


хорошо; а что Вы можете сказать по тому примеру который я дал в посте [url=]https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1048597&msg=14878600[/url] ?
24 сен 13, 16:51    [14878648]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
SERIALIZABLE
Guest
KellyLynch
SERIALIZABLE
KellyLynch,

проблема c updlock "вылезает" в том случает, когда между not exists и insert (внутри одного запроса) одного коннекта, успеет проскочить not exists другого коннекта.

это не самый частый случай, но как его имитировать я показал выше


хорошо; а что Вы можете сказать по тому примеру который я дал в посте [url=]https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1048597&msg=14878600[/url] ?

а именно проблема?
24 сен 13, 16:55    [14878672]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
KellyLynch
Member

Откуда:
Сообщений: 94
SERIALIZABLE
KellyLynch
пропущено...


хорошо; а что Вы можете сказать по тому примеру который я дал в посте [url=]https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1048597&msg=14878600[/url] ?

а именно проблема?


" как объяснить, почему в "Эксперименте #1" всё произошло "как надо"? Ведь UPDLOCK "не должен был" сработать так как он сработал? Требовался целый HOLDLOCK. Тем не менее UPDLOCK сработал..."
24 сен 13, 17:00    [14878712]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
daw
Member

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

это потому, что у вас в обоих запросах и #1 и #2 перед вставкой блокируются одни и те же ключи - вот этим:

SELECT 1 FROM TargetTable tt
WITH (UPDLOCK, ROWLOCK)

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

INSERT INTO TargetTable
(WorkCode,WorkNumber)
VALUES (123, 'A value from SourceTable')

попытаются вставить строку _пересекающуюся_ со строками в @SourceTable, то такой вставке ничто не помешает.
а с holdlock-ом - помешает. потому что так блокироваться будут уже диапазоны.
24 сен 13, 17:28    [14878906]     Ответить | Цитировать Сообщить модератору
 Re: MS SQL Server 2008: locking hint для предовращения вставки  [new]
SERIALIZABLE
Guest
KellyLynch
SERIALIZABLE
пропущено...

а именно проблема?


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

вы ответы читаете?



SERIALIZABLE
проблема c updlock "вылезает" в том случает, когда между not exists и insert (внутри одного запроса) одного коннекта, успеет проскочить not exists другого коннекта.


у вас в тесте нет попытки сымитировать подобную ситуация
24 сен 13, 17:39    [14878965]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить