Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Потокобезопасный insert if not exist  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
Всеми привет,
Задача тривиальная на мой взгляд. Вставить запись в таблицу, если ее еще там нет.
Написал хранимку, вызываю ее из 10 потоков в бесконечном цикле.
Падает с ошибкой
автор
Violation of UNIQUE KEY constraint 'UK_Platform_Url'. Cannot insert duplicate key in object 'dbo.Platform'.

Сам запрос:
	insert into dbo.Platform(Url)
		select u.Url
			from @urls u
			left join dbo.Platform p
				on u.Url = p.Url
			where p.url is null
Т.е. проблема в том, что несколько потоков пытались вставлять одну и ту же запись, оба получили, что такой записи еще нет, один вставил, другой отвалился.
Как решить проблему? Думал, найду решение быстро, а бьюсь уже третий час :(
14 июл 11, 16:19    [10974922]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
хинтовать
14 июл 11, 16:21    [10974942]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
Гадя Петрович,

Брависимо!
Как?
Перепробовал уже кучу комбинаций разных хинтов
14 июл 11, 16:26    [10975010]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
автор
Как решить проблему?


Ограничение заменить на уникальный индекс с опцией IGNORE_DUP_KEY.
14 июл 11, 16:29    [10975051]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
НедавноОбсуждали
Guest
https://www.sql.ru/forum/actualthread.aspx?tid=865467
14 июл 11, 16:30    [10975070]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Kudep, а если обрабатывать ошибку через try catch
14 июл 11, 16:33    [10975088]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
Kudep
Гадя Петрович,

Брависимо!
Как?
Перепробовал уже кучу комбинаций разных хинтов
rowlock,xlock?
14 июл 11, 16:33    [10975089]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
Гадя Петрович,

	insert into dbo.Platform (Url)
		select u.Url
			from @urls u 
			left join dbo.Platform p with(rowlock,xlock)
				on p.Url = u.Url
			where p.Url is null
выдает ту же ошибку.
14 июл 11, 16:40    [10975146]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
тогда совет pkarklin-а
14 июл 11, 16:42    [10975162]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
кстати, про xlock в боле написано как-то неоднозначно
автор
Указывает, что монопольные блокировки применяются и удерживаются до завершения транзакции. Если при этом указан аргумент ROWLOCK, PAGLOCK или TABLOCK, монопольная блокировка применяется к соответствующему уровню гранулярности.
имеется ввиду, что монопольная блокировка применяется принудительно, или она применяется к соответствующему уровню гранулярности, если возникнет?
14 июл 11, 16:54    [10975249]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Павел-П
Guest
pkarklin
автор
Как решить проблему?


Ограничение заменить на уникальный индекс с опцией IGNORE_DUP_KEY.


Особенно хорош этот подход, когда у Вас во втором INSERT-е с дубликатами хранится очень важная информация, которая просто теряется.
14 июл 11, 17:26    [10975482]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Павел-П,

Давайте не путать теплое с мягким, вставку не повторяющихся и обновление других полей для существующих.
14 июл 11, 17:41    [10975559]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
pkarklin
автор
Как решить проблему?


Ограничение заменить на уникальный индекс с опцией IGNORE_DUP_KEY.

спасибо!
15 июл 11, 09:51    [10977859]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
Правда теперь через некоторое время после начала работы возникает:
автор
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Запрос всё тот же:
insert into dbo.Platform (Url)
		select u.Url
			from @urls u 
			left join dbo.Platform p --with(nolock)
				on p.Url = u.Url
			where p.Url is null
15 июл 11, 09:59    [10977894]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Не лучше ли
INSERT dbo.Platform(Url)
SELECT u.Url
FROM @urls u
WHERE NOT EXISTS(SELECT * FROM dbo.Platform p WHERE p.Url=u.Url);
?

Версия сервера какая?
15 июл 11, 10:10    [10977940]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
iap
Не лучше ли
INSERT dbo.Platform(Url)
SELECT u.Url
FROM @urls u
WHERE NOT EXISTS(SELECT * FROM dbo.Platform p WHERE p.Url=u.Url);
?

Версия сервера какая?

Microsoft SQL Server 2008 (RTM) Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

Вариант с подзапросом проработал заметно дольше, но в итоге все-равно вывалился с дэдлок экзепшеном
15 июл 11, 10:18    [10977978]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Включите версионность на базе (READ_COMMITED_SNAPSHOT) и:
INSERT INSERT dbo.Platform(Url)
SELECT Url FROM @urls
EXCEPT
SELECT Url FROM dbo.Platform
15 июл 11, 10:25    [10978027]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
Я щас подумал, зачем мне вообще теперь эта проверка, если я добавил уникальный индекс на поле Url с опцией IGNORE_DUP_KEY ))
Оставил так:
INSERT dbo.Platform(Url)
SELECT u.Url
FROM @urls u
но экзепшен с дедлоком все-равно возникает.
Тут похоже ситуация такая, несколько потоков приходят к этому инсерту, лочат табличку, и делают вставку. А так как сама вставка длится относительно долго, то субд решает, что этот деддлок
15 июл 11, 10:26    [10978032]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Kudep,

Надо бы граф посмотреть, если будут дедлоки после включения версионности.
15 июл 11, 10:28    [10978046]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Kudep
Я щас подумал, зачем мне вообще теперь эта проверка, если я добавил уникальный индекс на поле Url с опцией IGNORE_DUP_KEY ))
Проверка значительно уменьшает вероятность конфликтов.
Верните её. IMHO
15 июл 11, 10:28    [10978048]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
andsm
Member

Откуда: Москва
Сообщений: 1320
Блог
Куча ответов, но ни один не предложил нормальный рабочий вариант.
У нас что, и на sql.ru никто не слышал про уровни изоляции?
Потокобезопасный insert if not exist:
	insert into dbo.Platform(Url)
		select u.Url
			from @urls u
			left join dbo.Platform p with(holdlock, updlock)
				on u.Url = p.Url
			where p.url is null
Этот подход довольно плох по блокировкам если нет индекса по Platform.Url. Но если Platform.Url проиндексирован, то будет более менее нормально для средней системы.
15 июл 11, 10:35    [10978092]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
andsm,

Индекс есть, с самого первого поста. ;) Уровни изоляции предлагали, правда не в этом трэде но по этому вопросу (в его обсуждении на ПТ ), но так мы получим бутылочное горлышко, и смысла во многопотоковости не будет.
15 июл 11, 10:41    [10978137]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
andsm
Куча ответов, но ни один не предложил нормальный рабочий вариант.
У нас что, и на sql.ru никто не слышал про уровни изоляции?
Потокобезопасный insert if not exist:
	insert into dbo.Platform(Url)
		select u.Url
			from @urls u
			left join dbo.Platform p with(holdlock, updlock)
				on u.Url = p.Url
			where p.url is null
Этот подход довольно плох по блокировкам если нет индекса по Platform.Url. Но если Platform.Url проиндексирован, то будет более менее нормально для средней системы.
HOLDLOCK
Равнозначен аргументу SERIALIZABLE. Дополнительные сведения об аргументе SERIALIZABLE см. далее в этом разделе. Аргумент HOLDLOCK применяется только к таблице или представлению, для которых он задан, и только на время транзакции, определенной использующей его инструкцией. Аргумент HOLDLOCK нельзя использовать в инструкции SELECT, включающей параметр FOR BROWSE.
SERIALIZABLE
Равнозначен аргументу HOLDLOCK. Накладывает дополнительные ограничения на совмещаемую блокировку: удерживает ее до завершения транзакции вместо снятия блокировки сразу после того, как таблица или страница данных больше не требуется, независимо от того, завершена ли транзакция. Просмотр выполняется с той же семантикой, что и транзакция, запущенная на уровне изоляции SERIALIZABLE. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
UPDLOCK
Указывает, что блокировки обновления применяются и удерживаются до завершения транзакции. UPDLOCK применяет блокировку обновления для операций чтения только на уровне строки или таблицы. Если UPDLOCK используется в сочетании с TABLOCK или по какой-либо другой причине уже получена блокировка на уровне таблицы, то вместо этого будет получена монопольная (X) блокировка.

Если указано UPDLOCK, то подсказки уровня изоляции READCOMMITTED и READCOMMITTEDLOCK не используются. Например, если уровень изоляции в данном сеансе установлен в SERIALIZABLE и в запросе указано (UPDLOCK, READCOMMITTED), то подсказка READCOMMITTED не будет учитываться и транзакция будет выполняться на уровне изоляции SERIALIZABLE.
http://msdn.microsoft.com/ru-ru/library/ms187373.aspx

Вопрос: зачем же и HOLDLOCK и UPDLOCK вместе? "Масло масляное"?
15 июл 11, 10:45    [10978158]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
pkarklin
Kudep,

Надо бы граф посмотреть, если будут дедлоки после включения версионности.

ща попробую, спасиб.
запустил
alter database MyTestDb
set read_committed_snapshot on
go
пока выполняется, хотя БД небольшая, 100 метров всего занимает
15 июл 11, 10:45    [10978166]     Ответить | Цитировать Сообщить модератору
 Re: Потокобезопасный insert if not exist  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
iap
Вопрос: зачем же и HOLDLOCK и UPDLOCK вместе? "Масло масляное"?


S локи совместимы, U - нет.
15 июл 11, 10:47    [10978183]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить