Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
Интересно
Guest
Для базы установлено
ALLOW_SNAPSHOT_ISOLATION ON
READ_COMMITTED_SNAPSHOT ON
Далее лишнее в примере поскипано...

есть таблица
table(Id int primary key )


и есть процедура - смысл ее добавить новый key в таблицу, если есть то удалить и добавить по новой (в жизни естественно это не просто таблица с одним полем)
create procedure insertOrUpdate(@Id int)
as
begin
	begin tran
		delete table1 where Id = @Id

		insert into table1(Id)
			select @Id where 
			not exists ( select * from table1 where Id = @Id  ) 
	commit tran
end

Процедуру вызывают часто и много с разных клиентов, могут и одновременно с одинаковым @Id вызываться,
редко случается что падаем при добавлении неуникального ключа
Вопрос:

как избавиться от падения? есть какая нить павильная практика, кроме как переписать все нафиг
8 сен 11, 15:30    [11248425]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
Aleksey-K
Member

Откуда: Москва
Сообщений: 3116
А чем IDENTITY вас не устраивает?
С уважением, Алексей
8 сен 11, 15:34    [11248477]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
Интересно
как избавиться от падения? есть какая нить павильная практика, кроме как переписать все нафиг
Не добавлять разные ключи, какая же ещё. Например, выделите каждому клиенту свой диапаазон ключей.
8 сен 11, 15:35    [11248487]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
delete table1 where
Guest
Интересно
как избавиться от падения? есть какая нить павильная практика, кроме как переписать все нафиг

нанять хотя бы одного программиста
8 сен 11, 15:36    [11248498]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
Интересно
Guest
alexeyvg,

есть много клиентов которые обрабатывают кучу заказов, могут хватать и одинаковые, как обрабатывают помешают результат в таблицу, хочется чтобы результат в таблице был один, последний первый не важно
8 сен 11, 15:38    [11248527]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
komrad
Member

Откуда:
Сообщений: 5758
Интересно
alexeyvg,

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


триггер на инсерт с проверкой наличия данного ключа
8 сен 11, 15:43    [11248585]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Интересно

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

MERGE ?
8 сен 11, 15:44    [11248595]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
Интересно
Guest
Aleksey-K,

Автоинкремент всем хорош, но здесь ненужен, номера точно уникальны и соответствуют каждый определенной одной сущности.
Да и возниконовению дублей по номеру он не помешает

мне пока виден только один способ вернее грабля:
обернуть в try ... catch
8 сен 11, 15:45    [11248606]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
Интересно
Guest
Glory,

Спасибо, но нужно и на 2005м тоже
8 сен 11, 15:48    [11248631]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
Aleksey-K
Member

Откуда: Москва
Сообщений: 3116
Интересно
Aleksey-K,

Автоинкремент всем хорош, но здесь ненужен, номера точно уникальны и соответствуют каждый определенной одной сущности.
Да и возниконовению дублей по номеру он не помешает

мне пока виден только один способ вернее грабля:
обернуть в try ... catch

Ну тогда перед вставкой в одной транзакции блокируйте таблицу эксклюзивно. Другого надежного способа я не знаю.
С уважением, Алексей
8 сен 11, 15:48    [11248632]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
Интересно
Guest
komrad,

А это поможет при таком уровне изоляции, который выставлен для базы?
8 сен 11, 15:51    [11248678]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
Интересно
alexeyvg,

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

Тогда 2 варианта - не давать "могут хватать и одинаковые", либо не сохранять уже сохранённое. Во втором случае непонятно, зачем делается delete table1 where Id = @Id.
8 сен 11, 16:11    [11248893]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
Интересно
Guest
alexeyvg,

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

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

Спасибо.
8 сен 11, 16:52    [11249307]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
не интересно
Guest
Интересно,

проблема в вашей рукописной генерилке id
8 сен 11, 16:55    [11249356]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
Интересно
Guest
не интересно,

Таковой нет, есть номер и он уникален для сущности (кстати получен всеми любимым идентити)
p.s.
что то все так цепляются к ключу, пускай будет в исходной таблице ключ по идентити и еще поле field2 c констрайнтом уникальности, проблема то, не решится
p.p.s.
если ближе к жизни то есть куча заказов с их Id (ключевые на идентити) обработчики берут эти заказы и что то там вычисляют. результат пишут в таблицу результатов указывая ID заказа, вот и хотелось бы чтобы в результатах оставалась только 1 запись относящаяся к одному заказу.

Почему могут хватать одинаковые: система большая распределенная - хватают по сети, сеть может и отвалиться, после взлета обработчик опять может схватить свой недообработанный заказ, ну а его может кто то и обрабатывать в этот момент
8 сен 11, 17:11    [11249510]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
не интересно
Guest
Интересно,

чуток не в тему, но:
- хорошо бы завести логические блокировки. если один взял заказ в обработку, значит второму незачем его брать
- удалять всё-добавлять заново всё - не лучший вариант, лучше более smart подходить к данным. merge (если есть) или ins-del-upd по необходимости.

оба пункта косвенно чуть разгрузят конфликты.
8 сен 11, 17:20    [11249591]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
Интересно
не интересно,

Таковой нет, есть номер и он уникален для сущности (кстати получен всеми любимым идентити)
p.s.
что то все так цепляются к ключу, пускай будет в исходной таблице ключ по идентити и еще поле field2 c констрайнтом уникальности, проблема то, не решится
p.p.s.
если ближе к жизни то есть куча заказов с их Id (ключевые на идентити) обработчики берут эти заказы и что то там вычисляют. результат пишут в таблицу результатов указывая ID заказа, вот и хотелось бы чтобы в результатах оставалась только 1 запись относящаяся к одному заказу.

Почему могут хватать одинаковые: система большая распределенная - хватают по сети, сеть может и отвалиться, после взлета обработчик опять может схватить свой недообработанный заказ, ну а его может кто то и обрабатывать в этот момент
Чтобы не отваливался ваш insert, достаточно поставить хинт:
insert into table1(Id)
select @Id 
where not exists ( select * from table1 with(updlock) where Id = @Id  ) 

Однако всё равно работать будет неправильно. Потому как кто то вставит запись между delete и insert.
Хотя при стратегии "кто первый, тот и прав", это неважно.

Так что с т.з. бизнес-логики, лучьше переделать код на:
begin tran
insert into table1(Id)
select @Id where
not exists ( select * from table1 with(updlock) where Id = @Id)
if @@rowcount = 0 return
commit tran
8 сен 11, 17:25    [11249633]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
не интересно
Guest
Интересно,

снапшоты осознанно включили?
8 сен 11, 17:40    [11249769]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
Интересно,

автор
как избавиться от падения? есть какая нить правильная практика, кроме как переписать все нафиг

Добавить столбец в таблицу с типом timestamp.

update ...
where id =@id and  scope_timestamp = @scope_timestamp

при неудачной попытке - сообщение пользователю.
8 сен 11, 17:47    [11249808]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
Интересно
Guest
постараюсь ответить всем здесь
1. "снимок" включали осознано
2. локировать таблицу, конечно, поможет от этой проблемы, но скорее всего полезут неприятные дедлоки - там цепочка хорошая и на таблицу есть форины, которые imho увеличат вер-сть дедлока
3. Сделать невозможность выдачи уже взятого заказа не получится т.к. отвалившийся обработчик может уже и не включиться ( чел. выключил комп и ушел в отпуск, комп сломался, инет пропал навсегда...)

Спасибо, всем кто принял участие в обсуждении
8 сен 11, 18:56    [11250188]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
iljy
Member

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

ну в общем-то рещается тривиально:
create procedure insertOrUpdate(@Id int)
as
begin
      set transaction isolation level serializable
	begin tran
		delete table1 where Id = @Id

		insert into table1(Id)
			select @Id where 
			not exists ( select * from table1 where Id = @Id  ) 
	commit tran
end
Удаление наложит блокировку диапазона ключа, и выполнение удаления-вставки становится атомарным. Повторное изменение ключа в другой тразнзакции будет ждать завершения этой, при этом чтения снимков блокироваться не будут. Если очень-очень хочется - можно попробовать организовать выполнение этой процедуры в автономной транзакции.
8 сен 11, 19:23    [11250298]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31969
iljy
Интересно,

ну в общем-то рещается тривиально:
А по моему, разумнее убрать begin tran...commit tran, и поставить, как я писал, хинт with(updlock)
8 сен 11, 21:55    [11250769]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное добавление одинакового ключа несколькими клиентами как избежать?  [new]
iljy
Member

Откуда:
Сообщений: 8711
alexeyvg
iljy
Интересно,

ну в общем-то рещается тривиально:
А по моему, разумнее убрать begin tran...commit tran, и поставить, как я писал, хинт with(updlock)

Не уверен. Я в принципе не люблю делать изменения на уровне SNAPSHOT - существует весьма ненулевая вероятность свалиться по конфликту версий. Но это уже дело вкуса.
9 сен 11, 00:54    [11251191]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить