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

Откуда: Москва
Сообщений: 794
SQL SERVER 2008R2. Ситуация. Имеется таблица этапов проекта, ключевое поле НомерПодпроекта (значение напр.1001-1, 1001 - номер проекта, 1 - номер этапа). В ней описываются этапы проекта. Один этап - одна строка. Проект может состоять из 1-5 этапов. Соответственно 1001-1, 1001-2, 1001-3, 1001-4. Кроме этого имеется таблица документов, у каждого документа ссылка на подэтап, поле НомерПодпроекта, значение, например 1001-3. Ключевой связи по полям НомерПодпроекта нету. Часто возникает потребность вставить дополнительный этап, например, между 2-м и 3-м уже имеющимися. Алгоритм таков: для всех этапов больше или равных 3 в НомереПодпроекта изменяется значение поля (вместо 1001-3 -> 1001-4). В документах тоже самое - все что больше или равно 3 - добавляется 1. На освободившееся место в таблице этапов проектов вставляется строка этапа №3. Написана хранимая процедура, которая все это делает в рамках транзакции. Теперь проблема. Налажена репликация слиянием между двумя офисами. Частота обновления 5 минут. Механизм вставки этапа часто приводит к конфликту. Только что измененный (увеличенный на 1) номер этапа на издателе не передается на подписчика. Обратный эффект наблюдается гораздо реже. Соответственно возникает конфликт вставки строки с повторяющимся первичным ключом. К том же сбиваются ссылки документов на номера этапов. Никак не могу понять в какой момент это происходит и как переписать процедуру, чтобы измененные значения этапов корректно передавались до следующего сеанса репликации. Как мне кажется, конфликт происходит оттого, что в момент изменения значений процедурой уже идет сеанс репликация, которая тоже что-то там себе блокирует и не забирает в этот момент данные из транзакции хранимой процедуры. А к следующему сеансу репликации готово - имеем на издателе и подписчике строку с одним и тем же ключом, и издатель пытается передать эту строку на подписчика. В процедуре установил TRANSACTION ISOLATION LEVEL SERIALIZABLE. Не помогает.
15 сен 16, 11:36    [19667750]     Ответить | Цитировать Сообщить модератору
 Re: транзакции + блокировки + репликация  [new]
garvy
Member

Откуда: Москва
Сообщений: 794
Если моя догадка верна, тогда уточняющий вопрос: Как при запуске транзакции в хранимой процедуре проверить - не идет ли сейчас сеанс репликации?
15 сен 16, 11:54    [19667891]     Ответить | Цитировать Сообщить модератору
 Re: транзакции + блокировки + репликация  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Вполне можно на издателе и подписчике при двухсторонней репликации вставить одно и то же значение в поле с уникальным ограничением и получить конфликт в реплицируемых данных. При односторонней репликации тоже можно получить ошибку, если на подписчике удалить запись , а потом с издателя получить команду на ее изменение или удаление. В вашем случае может решением будет зарезервировать диапазон номеров для издателя и для подписчика и чтоб они гарантированно не пересекались.
15 сен 16, 12:35    [19668169]     Ответить | Цитировать Сообщить модератору
 Re: транзакции + блокировки + репликация  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
garvy
Если моя догадка верна, тогда уточняющий вопрос: Как при запуске транзакции в хранимой процедуре проверить - не идет ли сейчас сеанс репликации?

смотрите выполняется ли сейчас такой процесс. Но только не туда копаете имхо.
15 сен 16, 12:36    [19668182]     Ответить | Цитировать Сообщить модератору
 Re: транзакции + блокировки + репликация  [new]
garvy
Member

Откуда: Москва
Сообщений: 794
Мистер Хенки
Вполне можно на издателе и подписчике при двухсторонней репликации вставить одно и то же значение в поле с уникальным ограничением и получить конфликт в реплицируемых данных. При односторонней репликации тоже можно получить ошибку, если на подписчике удалить запись , а потом с издателя получить команду на ее изменение или удаление.
Этого нету. Процедура вставки строки срабатывает с одной стороны. Зная наш бизнесс-процесс утверждаю, что вероятность, что разные сотрудники в разных офисах параллельно заинтересуются и вставят оду и ту же строку в проект, стремится к нулю. За этой ошибкой наблюдаю достаточно давно. Процесс ручного устранения отработал - подключаюсь к подписчику и вручную в таблице увеличиваю на единицу номера сдвигаемых этапов, т.е. в итоге доделываю некую работку, которую должна была сделать репликация в сеансе обмена. После этого новая запись с издателя приходит на подписчика. Но вот почему репликация этого не доделывает, что ей мешает- не ясно.
15 сен 16, 14:16    [19668778]     Ответить | Цитировать Сообщить модератору
 Re: транзакции + блокировки + репликация  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Посмотрите конфликты , посмотрите ошибки агента репликации. Может команда не проходит
15 сен 16, 14:34    [19668911]     Ответить | Цитировать Сообщить модератору
 Re: транзакции + блокировки + репликация  [new]
buven
Member

Откуда:
Сообщений: 792
Мистер Хенки
Но только не туда копаете имхо.

+1
garvy
Этого нету. Процедура вставки строки срабатывает с одной стороны.

Поясните. Человек в офисе 1 для проекта 1 создал 3 этапа с номерами 1-3. В офисе2 на этот же проект создали еще 2 этапа.
Какие номера будут у этапов проекта в офисе2 до репликации?

garvy
Зная наш бизнесс-процесс утверждаю, что вероятность, что разные сотрудники в разных офисах параллельно заинтересуются и вставят оду и ту же строку в проект, стремится к нулю.

А им и не надо. Офис1 вставляет этап2 между этап1 и этап2, а офис2 делает в это время этап3, т.к. видит, что первые 2 уже созданы, а действия Офис1 еще не долетели в Офис2 .
15 сен 16, 14:59    [19669125]     Ответить | Цитировать Сообщить модератору
 Re: транзакции + блокировки + репликация  [new]
invm
Member

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

Вы, по своему обыкновению, многословны, но полезной технической информации практически нет...

garvy
Алгоритм таков: для всех этапов больше или равных 3 в НомереПодпроекта изменяется значение поля (вместо 1001-3 -> 1001-4).
Каким образом? Построчно? Одной инструкцией update?
garvy
Механизм вставки этапа часто приводит к конфликту. Только что измененный (увеличенный на 1) номер этапа на издателе не передается на подписчика.
Конфликт где? Конфликт в чем? Ошибка какая?
15 сен 16, 15:08    [19669210]     Ответить | Цитировать Сообщить модератору
 Re: транзакции + блокировки + репликация  [new]
garvy
Member

Откуда: Москва
Сообщений: 794
invm
garvy,

Вы, по своему обыкновению, многословны, но полезной технической информации практически нет...

garvy
Алгоритм таков: для всех этапов больше или равных 3 в НомереПодпроекта изменяется значение поля (вместо 1001-3 -> 1001-4).
Каким образом? Построчно? Одной инструкцией update?
garvy

одной инструкцией UPDATE

Механизм вставки этапа часто приводит к конфликту. Только что измененный (увеличенный на 1) номер этапа на издателе не передается на подписчика.
Конфликт где? Конфликт в чем? Ошибка какая?

Конфликт вставки строки с неуникальным ключом на подписчике
15 сен 16, 15:42    [19669429]     Ответить | Цитировать Сообщить модератору
 Re: транзакции + блокировки + репликация  [new]
garvy
Member

Откуда: Москва
Сообщений: 794
Вот полный текст процедуры

CREATE PROC [dbo].[PR_Etap_INSERT] @NProj varchar(15), @InsPosL INT

AS
 /*  Вставляет этап в [Заказы 1цепочка] и соответственно смещает группу данных в [Заказы 2цепочка]  */
BEGIN
DECLARE  @MaxPosL INT, @I INT;
/* @NProj - номер проекта
  @InsPosL - номер "последовательности" (он же №этапа) куда надо  сделать вставку
  @InsPosL=2 - т.е. вставка в позицию №2 : было 1,2,3 -> 1=1, 2=new, 3=2, 4=3
  @InsPosL=1 - т.е. вставка в позицию №1 : было 1,2,3 -> 1=new, 2=1, 3=2, 4=3
  @InsPosL=4 - т.е. вставка в позицию №4 : было 1,2,3 -> 1=1, 2=2, 3=3, 4=new
  @InsPosL=7 - т.е. вставка в позицию №7 : было 1,2,3 -> 1=1, 2=2, 3=3, 4=new, т.е. перед вставкой-анализ
  @InsPosL=0 - т.е. вставка в позицию №0 : как @InsPosL=1 т.е. вставка в позицию №1
  [Заказы: 2цепочка] смещает [Nподпроекта] и [NNподпроекта]: '1002-2' и '1002-2-1' -> '1002-3' и '1002-3-1' если вставка этапа <=2
     если вставка этапа >, то изменений в [Заказы: 2цепочка] нет
  [Заказы: Документы]  ==\\==
*/
SET @MaxPosL=(select max([Последовательность]) from dbo.[Заказы 1цепочка] where [Nпроекта] = @NProj);
IF @InsPosL<1 SET @InsPosL= 1;
IF @InsPosL>@MaxPosL SET @InsPosL= @MaxPosL+1;

BEGIN TRAN;

UPDATE dbo.[Заказы1цепочка] 
  set [Nподпроекта] =[Nпроекта]+'-'+LTRIM(STR( [Последовательность] +1)), [Последовательность]=[Последовательность]+1
  where  [Nпроекта] = @NProj and [Последовательность] >=@InsPosL;

UPDATE dbo.[Заказы2цепочка] 
  set [Nподпроекта] = dbo.NProject ([Nподпроекта])+'-'+LTRIM(STR(dbo.NEtap ([Nподпроекта])+1)),
      [NNподпроекта] = dbo.NProject ([Nподпроекта])+'-'+LTRIM(STR(dbo.NEtap ([Nподпроекта])+1))+'-'+LTRIM(STR([Последовательность]))
  where  dbo.NProject([Nподпроекта])= @NProj and dbo.NEtap([Nподпроекта]) >=@InsPosL;

UPDATE dbo.[Заказы Документы] 
  set [Nподпроекта] =  dbo.NProject ([Nподпроекта])+'-'+LTRIM(STR(dbo.NEtap ([Nподпроекта])+1)),
      [NNподпроекта] = dbo.NProject ([Nподпроекта])+'-'+LTRIM(STR(dbo.NEtap ([Nподпроекта])+1))+'-'+LTRIM(STR(dbo.NPodEtap ([NNподпроекта])))
  where  dbo.NProject([Nподпроекта])= @NProj and dbo.NEtap ([Nподпроекта]) >=@InsPosL and dbo.NPodEtap ([NNподпроекта])>0;

UPDATE dbo.[Заказы Документы] 
  set [Nподпроекта] =  dbo.NProject ([Nподпроекта])+'-'+LTRIM(STR(dbo.NEtap ([Nподпроекта])+1))
  where  dbo.NProject([Nподпроекта])= @NProj and dbo.NEtap ([Nподпроекта]) >=@InsPosL and dbo.NPodEtap ([NNподпроекта])=0;

INSERT INTO  dbo.[Заказы1цепочка] (Nпроекта, Nподпроекта, Продавец, Покупатель, Последовательность, Договор, [Тип сделки], [Nвид оплаты], Примечание,Сотрудник, Добавлено) 
VALUES (@NProj, LTRIM(STR(@NProj))+'-'+LTRIM(STR(@InsPosL)), null, null, @InsPosL, null, '000000', 1,null, null, getdate());

IF @@ERROR>0
 BEGIN
  ROLLBACK TRAN
  RETURN 1
 END
ELSE
 begin
  COMMIT TRAN
  RETURN 0
 END
END


Сообщение было отредактировано: 15 сен 16, 15:51
15 сен 16, 15:48    [19669468]     Ответить | Цитировать Сообщить модератору
 Re: транзакции + блокировки + репликация  [new]
invm
Member

Откуда: Москва
Сообщений: 9407
garvy
Конфликт вставки строки с неуникальным ключом на подписчике
Это вы так интерпретируете сообщение о нарушении уникальности ключа?
garvy
Вот полный текст процедуры
Если ошибка таки в нарушении уникальности ключа, то обратите внимание, что инструкция вида
UPDATE dbo.[Заказы1цепочка] 
  set [Nподпроекта] =[Nпроекта]+'-'+LTRIM(STR( [Последовательность] +1)), [Последовательность]=[Последовательность]+1
  where  [Nпроекта] = @NProj and [Последовательность] >=@InsPosL;
На подписчике будет применяться построчно в виде N команд update. И никто не гарантирует, что выполняться они будут в порядке убывания уникального ключа, значения которого вы модифицируете.
15 сен 16, 16:02    [19669548]     Ответить | Цитировать Сообщить модератору
 Re: транзакции + блокировки + репликация  [new]
garvy
Member

Откуда: Москва
Сообщений: 794
garvy
Вот полный текст процедуры
Если ошибка таки в нарушении уникальности ключа, то обратите внимание, что инструкция вида
UPDATE dbo.[Заказы1цепочка] 
  set [Nподпроекта] =[Nпроекта]+'-'+LTRIM(STR( [Последовательность] +1)), [Последовательность]=[Последовательность]+1
  where  [Nпроекта] = @NProj and [Последовательность] >=@InsPosL;
На подписчике будет применяться построчно в виде N команд update. И никто не гарантирует, что выполняться они будут в порядке убывания уникального ключа, значения которого вы модифицируете.[/quot] Вот это уже горячо! Поразмыслю над этим! Спасибо!
15 сен 16, 16:13    [19669602]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить