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

Откуда:
Сообщений: 109
Всем доброго вечера!

Сегодня обнаружилась следующая проблемка: в системе есть шлюз (хранимая процедура) для загрузки данных о товарах из внешних систем GOODS_LOAD ( @SHOP_ID, ... данные о новом товаре ... ) в таблицу GOODS.

В таблице PK (SHOP_ID, GOODS_ID) рассчитывается самостоятельно, краткий код хранимой процедуры:
...

declare @GOODS_ID int

select	@GOODS_ID = MAX(GOODS_ID)
from	GOODS
where	SHOP_ID = @SHOP_ID

set @GOODS_ID = ISNULL( @GOODS_ID, 1 )

insert GOODS
values ( @SHOP_ID, @GOODS_ID ... )

...


Процедура работает отлично, пока ее не начинают вызывать из нескольких потоков одновременно... что конечно приводит к дублированию первичного ключа. Собственно вопрос - как решить данную проблему?

Спасибо за внимание)
28 май 14, 17:38    [16087131]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Tketano,

sequence
28 май 14, 17:40    [16087151]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Tketano,

добавить к полю GOODS_ID свойство identity
28 май 14, 17:58    [16087284]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Tketano
Member

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

по многим причинам структура таблицы изменениям не подлежит
28 май 14, 18:00    [16087299]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Tketano
Member

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

забыл сделать очень важное уточнение) SQL Server 2000
28 май 14, 18:05    [16087322]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104751
Tketano
Собственно вопрос - как решить данную проблему?

Блокировать таблицы, чтобы никто не мог параллельно прочитать одинаковые значения
28 май 14, 18:31    [16087471]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Crimean
Member

Откуда:
Сообщений: 13147
+1 табличка с identity и использовать ее как генератор ИД
28 май 14, 18:42    [16087542]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Tketano
Member

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

Получается придется любые вставки в эту таблицу переделать через данную таблицу? Таких изменений будет вагон и маленькая тележка...
28 май 14, 19:03    [16087673]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104751
Tketano
Таких изменений будет вагон и маленькая тележка...

Ну так можно оставить все как есть.
Такой подход называется "оптимистическа блокировка"
Просто те, кто получают ошибку ПервичногоКлюча, выполняют уже произведенные действия еще раз
28 май 14, 19:10    [16087717]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Tketano
WarAnt,

по многим причинам структура таблицы изменениям не подлежит


тогда тока serialize и например updlock и пусть весь мир подождет:)
28 май 14, 19:42    [16087896]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Tketano
Member

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

Если у вас пример данного механизма?) Как сейчас не пробовал эти блокировки указать - эффекта ноль
28 май 14, 20:02    [16087973]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
Tketano
Процедура работает отлично, пока ее не начинают вызывать из нескольких потоков одновременно... что конечно приводит к дублированию первичного ключа. Собственно вопрос - как решить данную проблему?
Tketano
Получается придется любые вставки в эту таблицу переделать через данную таблицу? Таких изменений будет вагон и маленькая тележка...
Так вам что нужно: обеспечить бесконфликтность только в процедуре или везде?
28 май 14, 20:29    [16088046]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Crimean
Member

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

а лучше max() везде? и конфликты? identity решит все с минимумом затрат
28 май 14, 21:25    [16088218]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Tketano
Member

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

как я понимаю табличку identity придется постоянно поддерживать в актуальном состоянии. Проблема в том, что в системе существуют несколько процессов, которые в обход хранилки GOODS_INSERT вставляют данные большим набором в таблицу GOODS ...
28 май 14, 21:42    [16088286]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Tketano
Member

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

Прошу пояснить правильно ли я вас понял.

Есть хранилка GOODS_INSERT, которая непосредственно расчитывает PK и вставляет новую запись (с транзакцией). Тогда в ней добавляем условие:

...

declare @GOODS_ID int

select	@GOODS_ID = MAX(GOODS_ID)
from	GOODS (nolock)
where	SHOP_ID = @SHOP_ID

set @GOODS_ID = ISNULL( @GOODS_ID, 1 )

INSERT GOODS
VALUES ( ... )

-- ---------------------
-- НОВОЕ УСЛОВИЕ:
-- ---------------------
WHERE NOT EXISTS ( select * from GOODS (nolock) where SHOP_ID = @SHOP_ID and GOODS_ID = @GOODS_ID )

if @@ROWCOUNT = 1
  RETURN 0
else
  RETURN 100     -- дублирование PK

...


А непосредственно в GOODS_LOAD изменяем обычный вызов GOODS_INSERT на:

declare @rc int = 100

while @rc = 100
begin
  exec @rc = GOODS_INSERT ( ... )
end


Или я неправильно все понял?))
28 май 14, 21:51    [16088317]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104751
Tketano
Тогда в ней добавляем условие:

PrimaryKey constrаint и так не позволит добавить дубликаты. И сгенерирует ошибку
28 май 14, 21:58    [16088334]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Tketano
Member

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

ну тогда эта ошибка выкенет exception и весь процесс свалится..
28 май 14, 22:02    [16088344]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Glory
Member

Откуда:
Сообщений: 104751
Tketano
Glory,

ну тогда эта ошибка выкенет exception и весь процесс свалится..

И что ? Пишите клиенту - Операция закончилась неудачей. Повторите еще раз.
И все
28 май 14, 22:04    [16088350]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
Tketano
как я понимаю табличку identity придется постоянно поддерживать в актуальном состоянии.
Она и так будет в актуальном состоянии. Ее даже пустой можно держать.
Tketano
Проблема в том, что в системе существуют несколько процессов, которые в обход хранилки GOODS_INSERT вставляют данные большим набором в таблицу GOODS ...
И как эти процессы генерируют GOODS_ID?

А вообще, нет особых проблем сериализовать вставку в таблицу не изменяя существующий код.
28 май 14, 22:44    [16088473]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
автор
Если у вас пример данного механизма?) Как сейчас не пробовал эти блокировки указать - эффекта ноль


Ну как то так...

Set transaction isolation level serializable
Begin tran

declare @GOODS_ID int

select	@GOODS_ID = MAX(GOODS_ID)
from	GOODS with (updlock)
where	SHOP_ID = @SHOP_ID

set @GOODS_ID = ISNULL( @GOODS_ID, 1 )

insert GOODS
values ( @SHOP_ID, @GOODS_ID ... )
Commit tran
...
28 май 14, 23:18    [16088586]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9155
Если в SQL2000 есть sp_getapplock - попробуйте.
29 май 14, 11:22    [16089912]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
iap
Member

Откуда: Москва
Сообщений: 47194
Владислав Колосов
Если в SQL2000 есть sp_getapplock - попробуйте
Tketano
Проблема в том, что в системе существуют несколько процессов, которые в обход хранилки GOODS_INSERT вставляют данные большим набором в таблицу GOODS ...
Как же заставить всех использовать sp_getapplock??
29 май 14, 11:47    [16090116]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
iap
Как же заставить всех использовать sp_getapplock??
Триггером instead of insert.
29 май 14, 11:54    [16090190]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
o-o
Guest
invm,
про DTS (2000) не знаю,
но если массово грузить SSIS-пакетом (либо через Import/Export wizard),
то по умолчанию там Fast Load и в нем не выставлен FIRE_TRIGGERS,
триггеры напрочь игнорируются
29 май 14, 13:29    [16091032]     Ответить | Цитировать Сообщить модератору
 Re: Параллельная вставка строк в таблицу  [new]
Tketano
Member

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

А зачем указывать updlock, если указывается set transaction isolation level serializable?
29 май 14, 13:36    [16091118]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить