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

Откуда:
Сообщений: 303
Всем привет.
Можно это как-то сделать процедурно на блокировках?
Я наивно полагал, что конструкция типа:
begin transaction
if not exists(select null from table1 with(xlock или serializable) where unique_field='value') begin
 insert into table1(unique_field) value('value')
end
commit
поможет.
Пытался защититься от случая, когда между селектом и инсертом кто-то вставит такое же значение. Ожидал наложения блокировки диапазона индекса RangeX_X между соседними ключами для value (что конечно не идеально), но получаю зачастую блокировку намерения IX на страницу, которая безполезна. В лучшем случае deadlock, в худшем вставка дубля.
16 ноя 05, 11:37    [2073437]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
Веселов Константин
Member

Откуда: Новосибирск
Сообщений: 208
AntonGart
Всем привет.
Можно это как-то сделать процедурно на блокировках?
Я наивно полагал, что конструкция типа:
begin transaction
if not exists(select null from table1 with(xlock или serializable) where unique_field='value') begin
 insert into table1(unique_field) value('value')
end
commit
поможет.
Пытался защититься от случая, когда между селектом и инсертом кто-то вставит такое же значение. Ожидал наложения блокировки диапазона индекса RangeX_X между соседними ключами для value (что конечно не идеально), но получаю зачастую блокировку намерения IX на страницу, которая безполезна. В лучшем случае deadlock, в худшем вставка дубля.


Что значит зачастую? Я правильно понял, что все таки RangeS-S иногда получается получить?
16 ноя 05, 11:54    [2073560]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
AntonGart
Защита уникальных значений без констрейнтов и триггеров.

Чем не угодили?
16 ноя 05, 11:55    [2073567]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
Веселов Константин
Member

Откуда: Новосибирск
Сообщений: 208
Гавриленко Сергей Алексеевич
AntonGart
Защита уникальных значений без констрейнтов и триггеров.

Чем не угодили?


Он не хочет выполнять операцию, которая может вызвать исключение.
16 ноя 05, 12:00    [2073593]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Хм... Конструкция
BEGIN TRAN

IF NOT EXISTS(SELECT * FROM Table1 (SERIALIZABLE) WHERE ...

Как раз таки накладывает RangeS_S блокировку.
16 ноя 05, 12:01    [2073601]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
В догонку. Естественно при наличии индекса и его использовании в WHERE.
16 ноя 05, 12:02    [2073612]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
Веселов Константин
Гавриленко Сергей Алексеевич
AntonGart
Защита уникальных значений без констрейнтов и триггеров.

Чем не угодили?

Он не хочет выполнять операцию, которая может вызвать исключение.

Для этого можно написать проверку. Которую он, в принципе, уже и пишет:
exists(select null from table1 with(xlock или serializable) where unique_field='value')
16 ноя 05, 12:03    [2073622]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
Веселов Константин
Member

Откуда: Новосибирск
Сообщений: 208
Гавриленко Сергей Алексеевич
Веселов Константин
Гавриленко Сергей Алексеевич
AntonGart
Защита уникальных значений без констрейнтов и триггеров.

Чем не угодили?

Он не хочет выполнять операцию, которая может вызвать исключение.

Для этого можно написать проверку. Которую он, в принципе, уже и пишет:
exists(select null from table1 with(xlock или serializable) where unique_field='value')


Я об этом и говорю. К тому же он хочет гарантированно не получить дубликат, поэтому и with(xlock или serializable). Вот что у него не получается я не пойму, в такой схеме все должно работать.
16 ноя 05, 12:08    [2073664]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
Веселов Константин
Member

Откуда: Новосибирск
Сообщений: 208
Все, понял. RageS-S сам по себе не будет блокировать операцию not exists(select null from table1 with(xlock или serializable) where unique_field='value'). Поэтому две паралельные транзакции попадут на место insert into table1(unique_field) value('value'), Эта ситуация и расценится как deadlock.
16 ноя 05, 12:15    [2073698]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
Веселов Константин
Member

Откуда: Новосибирск
Сообщений: 208
Блокировка RangeS-S будет при этом принадлежать двум транзакциям.
16 ноя 05, 12:17    [2073716]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
WiRuc
Member

Откуда: Воронеж
Сообщений: 1280
Веселов Константин
Все, понял. RageS-S сам по себе не будет блокировать операцию not exists(select null from table1 with(xlock или serializable) where unique_field='value'). Поэтому две паралельные транзакции попадут на место insert into table1(unique_field) value('value'), Эта ситуация и расценится как deadlock.


Естественно, уровень SERIZLIABLE защищает только от модификации данных, но никак не от чтения. Вам надо в NOT EXISTS использовать либо хинт WITH(UPDLOCK,SERIALIZABLE), либо WITH(XLOCK,SERIALIZABLE).
16 ноя 05, 12:18    [2073721]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
Веселов Константин
Member

Откуда: Новосибирск
Сообщений: 208
Вот выход
not exists(select null from table1 with(SERIALIZABLE , UPDLOC) where unique_field='value')
16 ноя 05, 12:19    [2073727]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
Веселов Константин
Member

Откуда: Новосибирск
Сообщений: 208
UPDLOC - UPDLOCK. При этом наложится RangeS-U.
16 ноя 05, 12:21    [2073739]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Нет, наложится RangeX-U.
16 ноя 05, 13:01    [2074012]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
aleks2
Guest
Деловое предложение
declare @table1 table(unique_field varchar(10))

insert into @table1(unique_field) 
select TOP 1 'value'
WHERE not exists(select * from @table1 where unique_field='value') 

insert into @table1(unique_field) 
select TOP 1 'value'
WHERE not exists(select * from @table1 where unique_field='value') 

select  from @table1
16 ноя 05, 13:31    [2074229]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
AntonGart
Member

Откуда:
Сообщений: 303
To Aleks2: Точно! По-моему оптимальное решение. Проверить потом @ROWCOUNT...
To ALL: А зачем еще и SERIALIZABLE? Просто UPDLOCK недостаточно?
И все равно, не всегда накладывает Range-блокировку. Причем из разных сессий по-разному...
16 ноя 05, 18:23    [2076085]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
AntonGart
Member

Откуда:
Сообщений: 303
Хм... Хотя, если посмотреть план запроса от aleks2, то там сначала index seek, а потом insert, может ли теоретически между ними чужая вставка вклиниться? Сам же и отвечаю, не может, так как разделяемая блокировка висит на прочитанном индексе. А вот чужое чтение перед вставкой может, и значит приходим к той же по сути ситуации? Или бред все это? :)
17 ноя 05, 00:15    [2076739]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
aleks2
Guest
AntonGart
Хм... Хотя, если посмотреть план запроса от aleks2, то там сначала index seek, а потом insert, может ли теоретически между ними чужая вставка вклиниться? Сам же и отвечаю, не может, так как разделяемая блокировка висит на прочитанном индексе. А вот чужое чтение перед вставкой может, и значит приходим к той же по сути ситуации? Или бред все это? :)


Угомонись - выполнение одной инструкции SQL атомарно.
17 ноя 05, 05:35    [2076880]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
WiRuc
Member

Откуда: Воронеж
Сообщений: 1280
aleks2
AntonGart
Хм... Хотя, если посмотреть план запроса от aleks2, то там сначала index seek, а потом insert, может ли теоретически между ними чужая вставка вклиниться? Сам же и отвечаю, не может, так как разделяемая блокировка висит на прочитанном индексе. А вот чужое чтение перед вставкой может, и значит приходим к той же по сути ситуации? Или бред все это? :)


Угомонись - выполнение одной инструкции SQL атомарно.


Атомарно с точки зрения полного выполнения либо полного невыполнения модификации над таблицей. В вашем случае возможна таже самая ситуация, что и в самом исходном вопросе, т.е. возможна одновременная вставка дубля. В подзапросе нужен хинт UPDLOCK,SERIALIZABLE.
17 ноя 05, 09:30    [2077202]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
aleks2
Guest
WiRuc
aleks2
AntonGart
Хм... Хотя, если посмотреть план запроса от aleks2, то там сначала index seek, а потом insert, может ли теоретически между ними чужая вставка вклиниться? Сам же и отвечаю, не может, так как разделяемая блокировка висит на прочитанном индексе. А вот чужое чтение перед вставкой может, и значит приходим к той же по сути ситуации? Или бред все это? :)


Угомонись - выполнение одной инструкции SQL атомарно.


Атомарно с точки зрения полного выполнения либо полного невыполнения модификации над таблицей. В вашем случае возможна таже самая ситуация, что и в самом исходном вопросе, т.е. возможна одновременная вставка дубля. В подзапросе нужен хинт UPDLOCK,SERIALIZABLE.


Пример в студию...
17 ноя 05, 11:02    [2077682]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
AntonGart
Member

Откуда:
Сообщений: 303
Не угомонюсь :) Все же объясните пожалуйста, зачем еще и SERIALIZABLE? UPDLOCK и так держится до конца транзакции. Или кажется доходит: с SERIALIZABLE мы как раз и добиваемся блокировки диапазона индекса. Верно?
17 ноя 05, 11:03    [2077693]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
Glory
Member

Откуда:
Сообщений: 104760
AntonGart
Не угомонюсь :) Все же объясните пожалуйста, зачем еще и SERIALIZABLE? UPDLOCK и так держится до конца транзакции. Или кажется доходит: с SERIALIZABLE мы как раз и добиваемся блокировки диапазона индекса. Верно?

На чем и "так держиться UPDLOCK до конца транзакции" в запросе
select TOP 1 'value' WHERE not exists(select * from @table1 where unique_field='value')
17 ноя 05, 11:47    [2077982]     Ответить | Цитировать Сообщить модератору
 Re: Защита уникальных значений без констрейнтов и триггеров.  [new]
AntonGart
Member

Откуда:
Сообщений: 303
Точнее будет так (с нормальной таблицей):
select TOP 1 'value' WHERE not exists
(select * from table1 with updlock,serializable) where unique_field='value')
Если значение еще не существует, то накладывается RangeS_U, я же ошибочно предполагал, что и без SERIALIZABLE (только с UPDLOCK) наложится RangeS_U, а получал всего лишь IU на страницу (причем иногда все же накладывалась RangeS_U, что и сбивало с толку).
17 ноя 05, 12:39    [2078394]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить