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

Откуда:
Сообщений: 296
в текущий момент происходит вставка в таблицу if not exists (select...) insert into.
В IF происходит проверка на дубликат с дополнительным условием(select с другой таблицей).
Сейчас увеличилась нагрузка на процедуру и получается что проскакивают дубликаты,то есть две транзакции прошли if и вставили данные. Уровень изоляции транзакции стандартный,хотелось бы обойтись "малыми жертвами"
Подскажите варианты решения проблемы.
12 апр 16, 17:53    [19048823]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
блокировку накладывать нужно при проверке
12 апр 16, 17:55    [19048835]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
iljy
Member

Откуда:
Сообщений: 8711
sergei64_89,

во-первых - уберите if и используйте merge или insert ... select с условием на exists
во-вторых - поднимайте уровень изоляции для данной конструкции до SERIALIZABLE
12 апр 16, 18:03    [19048870]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
invm
Member

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

if заменить на
insert into MyTable
 (...)
select
 ...
where
 not exists(select * from MyAnotherTable with (serializable, updlock) where ...)
12 апр 16, 18:20    [19048920]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
sergei64_89
Member

Откуда:
Сообщений: 296
Knyazev Alexey,
пробовал в updlock, но в данном случае ставится iu на страницу.
Опишу ситуацию table1:id, column1,column2. table2:id,table1Id,column1
if not exists (select * from table1 inner join
 table2 on table1.id=table2.table1Id
where table2.column1='test' and table1.column1='d')
begin
insert into table2

А теперь представим что две транзакции вставляют в table2 ('test','test'),получается обе транзакции пройдут условие....
12 апр 16, 18:28    [19048953]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
iljy
Member

Откуда:
Сообщений: 8711
sergei64_89,

вам не updlock нужен, а блокировка диапазона ключа (вы же новые записи вставляете, не старые меняете), а ее дает только SERIALIZABLE.
12 апр 16, 18:32    [19048970]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
invm
Member

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

Вам было написано, что нужно сделать - 19048920
serializable + updlock выстроит конкурирующие транзакции в очередь вне зависимости от наличия/отсутствия проверяемых строк в table1.
12 апр 16, 18:54    [19049055]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
sergei64_89
Member

Откуда:
Сообщений: 296
invm,но при этом наложит запрет на обновление всех данных в таблице. Это слишком большая цена.
12 апр 16, 22:05    [19049627]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
sergei64_89
но при этом наложит запрет на обновление всех данных в таблице
С чего вдруг?
У вас запрос из exists приводит к скану table1?
12 апр 16, 22:19    [19049671]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
iljy
Member

Откуда:
Сообщений: 8711
sergei64_89,

я чет никак не пойму - на кой ляд вам вообще вперся updlock? Чего вы хотите этим добиться?
12 апр 16, 22:25    [19049685]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
iljy
я чет никак не пойму - на кой ляд вам вообще вперся updlock?
Каким образом, имея только S-блокировки, даже на serializable, защититься от появления дубликатов?
12 апр 16, 22:38    [19049711]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
iljy
Member

Откуда:
Сообщений: 8711
invm
iljy
я чет никак не пойму - на кой ляд вам вообще вперся updlock?
Каким образом, имея только S-блокировки, даже на serializable, защититься от появления дубликатов?


Вы в принципе знаете, что такое updlock? Он предназначен для минимизации вероятности взаимоблокировок между двумя меняющими существующие записи транзакциями путем наложения блокировки U на запись, которая потенциально может быть изменена этой же самой транзакцией (U-блокировки между собой не совместимы, в отличии от S). Транзакция же ТС на другую таблицу сама по себе никак не влияет, а чтобы не дать другой транзакции поменять прочитанную нами запись более чем достаточно обычной S-блокировки (собственно изменение требует наложение X-блокировки, которая не совместима ни с чем, даже с S).

И вообще, у ТС совершенно банальная проблема фантомных чтений, и еще во времена бронтозавров высоколобые дядьки пришли к выводу, что эту проблему однозначно решает уровень изоляции SERIALIZABLE (за счет удержания S-блокировок до конца транзакции + наложения блокировок на диапазон ключа).
12 апр 16, 22:53    [19049737]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
sergei64_89
Member

Откуда:
Сообщений: 296
iljy,в select у меня join c другой таблицей и еще пару подзапросов(с вычислением).
serializable ставит S блокировку диапазоны ключей,что запретит их изменение в других транзакциях или я неверно понимаю?
есть варианты обойтись без блокировки таблицы на update,select
12 апр 16, 23:24    [19049825]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
iljy
Member

Откуда:
Сообщений: 8711
sergei64_89
iljy,в select у меня join c другой таблицей и еще пару подзапросов(с вычислением).
serializable ставит S блокировку диапазоны ключей,что запретит их изменение в других транзакциях или я неверно понимаю?
есть варианты обойтись без блокировки таблицы на update,select


Все правильно, ставит.
Почему таблицы? Блокируются только конкретные диапазоны (ну если у вас укрупнение блокировок не пойдет).
12 апр 16, 23:28    [19049838]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
iljy
Вы в принципе знаете, что такое updlock?
Нет, конечно. Откуда мне про это знать?

Нет у ТС проблемы фантомных чтений. У ТС проблема обеспечить игнорирование дубликатов при конкурирующих вставках.

Рассмотрим конструкцию
insert into SomeTable
 (key, value)
select
 @SomeKey, @SomeValue
where
 not exists(select * from SomeTable with (serializable) where key = @SomeKey)
Для простоты будем считать, что key - ПК.

Теперь представим одновременное выполнении этой конструкции в двух сеансах для одного и того же @SomeKey, который в таблице отсутствует.
В каждом из сеансов:
- будет установлена S на @SomeKey
- not exists вернет истину
- будет предпринята попытка вставить строку в таблицу. Для этого S ковертируется в X.

В результате - дедлок.

Итак, S + serializable не дает возможности игнорировать дубликат ключа при вставке.

Для обеспечения такой возможности, нужно конкурирующие сеансы выстраивать в очередь для одного и тогоже значения ключа.
Т.е. блокировки при вычитке должны быть несовместимы. Следовательно, нужно накладывать либо U, либо X.
Но, только на serializable можно наложить блокировку на отсутствующее в таблице значение ключа.

Итого, нужно либо serializable + updlock, либо serializable + xlock.
12 апр 16, 23:46    [19049902]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
iljy
Member

Откуда:
Сообщений: 8711
invm,

в вашем последнем примере вы как раз накладываете U-блокировки на таблицу, которую собираетесь менять, и оно, конечно же, помогает избегать взаимоблокировок. А теперь посмотрите на ваш же собственный пример 19048920: вы меняете одну таблицу, а U накладываете на другую. Гарантий при этом никаких (кто сказал, что проверки дубликатов одной таблицы требуют обращения всегда к одинаковым записям другой таблицы? Даже у ТС в 19048953 мы можем обращаться к разным table1ID, но при этом у них может быть table1.column1='d'), а ожиданий может стать заметно больше.

Вообще, все было бы сильно проще, если бы ТС привел проблемный запрос и структуру БД полностью, чтобы не приходилось гадать. Запрос, приведенный в 19048953, вызывает ряд вопросов, но хрустальный шар говорит мне, что table1 - таблица-справочник, на который ссылается FK, следовательно, запись с нужным table1ID там быть обязана, плюс table1.ID - уникальное поле, так что на ней даже SERIALIZABLE не требуется. Что касается table2, на ней точно нужен индекс по column1, тогда и updlock можно будет наложить, не опасаясь полной блокировки таблицы.

.
13 апр 16, 04:57    [19050159]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
sergei64_89
Member

Откуда:
Сообщений: 296
invm ,iljy благодарю за ответы,поставил with(serializable,updlock) и некластерный индекс,согласно sp_lock блокируется теперь диапазон ключей в некластерном индексе,что не блокирует таблицу
13 апр 16, 10:53    [19050947]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
iljy
в вашем последнем примере вы как раз накладываете U-блокировки на таблицу, которую собираетесь менять, и оно, конечно же, помогает избегать взаимоблокировок.
В моем последнем примере суть не дедлок, а возврат предикатом not exists истины для нескольких конкурирующих сеансов.
iljy
Гарантий при этом никаких (кто сказал, что проверки дубликатов одной таблицы требуют обращения всегда к одинаковым записям другой таблицы?
Вот именно, что при serializable + U(X) есть гарантия, что not exists вернет истину только для одного из конкурирующих сеансов.
13 апр 16, 15:33    [19052438]     Ответить | Цитировать Сообщить модератору
 Re: вставка с пользовательской проверкой  [new]
iljy
Member

Откуда:
Сообщений: 8711
invm
iljy
в вашем последнем примере вы как раз накладываете U-блокировки на таблицу, которую собираетесь менять, и оно, конечно же, помогает избегать взаимоблокировок.
В моем последнем примере суть не дедлок, а возврат предикатом not exists истины для нескольких конкурирующих сеансов.
iljy
Гарантий при этом никаких (кто сказал, что проверки дубликатов одной таблицы требуют обращения всегда к одинаковым записям другой таблицы?
Вот именно, что при serializable + U(X) есть гарантия, что not exists вернет истину только для одного из конкурирующих сеансов.


Гарантию отсутствия дубликатов дает SERIALIZABLE, а UPDLOCK нужен для решения проблемы потенциальных дедлоков, что к вопросу собственно целостности базы отношения не имеет.
13 апр 16, 15:38    [19052471]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить