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

Откуда: Berlin, Germany
Сообщений: 12
Добрый день. Прошу помощи, ибо сам разобраться уже не в силах.
Коротко: время от времени возникают дэдлоки.

Подробнее:
Происходит импорт данных из базы Activplant(по сути тоже БД) в базу MS SQL с "сжатием". Т.е., насколько я понимаю, datawarehouse.
Весь импорт организован с помощью SSIS (SQL Integration Services) и запускается как Job по расписанию.
Внутри идет запрос данных к Activplant, после получения которых эти данные запсываются в SQL с небольшим преобразованием построчно.
Все данные распределены на 5 таблиц, для каждой таблицы идет несколько параллельных запросов к Activplant и, соответственно, потом несколько параллельных потоков записи данных (чтобы ускорить процесс и упростить запросы к AP).
Кроме того есть вэб-приложение для ручного импорта данных. Все то же самое но организованно с .net, а не SSIS.
Еще отличие - в вэб-аппликации максимум два потока, в SSIS до 7-и.

Проблема:
Иногда при автоматическом импорте возникают дедлоки. Но! Что смущает и вводит в ступор:
-в веб аппликации, использующей те же StoredProcedures для записи данных DL не было ни разу.
-в SSIS там где 7 потоков - DL нет, они только в том блоке, где 2 потока. Хотя судя по данным в самой БД - во время тех 7и потоков случаются одновременная запись (в таблице сохраняется время записи от GETDATE() - но, не уверен что это реальное время).

Детали:
-В параллельных потоках одной таблицы используются одни и те же SP
-В вэб-программе и в SSIS используются одни и те же SP
-Вот часть кода SP:
SELECT @found = COUNT(ID) FROM [dbo].[Rejects_Shift]
WHERE	InternObjID = @internalOID AND
		ShiftDate = @ShiftDate AND
		ProductionShift = @Shift AND
		Product = @Product AND
		RejDescription = @RejDesc
	
IF (@found <> 0)
BEGIN --same data exists already => update
	BEGIN TRAN
		UPDATE [dbo].[Rejects_Shift]
		SET [timestamp]=GETDATE(),RejCount = @RejCnt
		WHERE	InternObjID = @internalOID AND
				ShiftDate = @ShiftDate AND
				ProductionShift = @Shift AND
				Product = @Product AND
				RejDescription = @RejDesc
	COMMIT TRAN	
END
ELSE BEGIN --no data available for such date/shift/product => insert
	BEGIN TRAN
		INSERT INTO [dbo].[Rejects_Shift]
			([timestamp],[InternObjID],[ShiftDate],[ProductiveTime], [ProductionShift],[Product],[RejDescription] , [RejCount])
		VALUES
			(GETDATE(),@internalOID, @ShiftDate,'', @Shift, @Product , @RejDesc ,@RejCnt)
	COMMIT TRAN
END


Было замечено (пробовал анализировать trace), что DL случается во время апдейта, а не инсерта.
Было замечено (не уверен), что DL случается во время апдейта, когда значение RejCount не изменилось, поэтому изменил кода исключив такой апдейт (в коде примера нет).
Пробовал стартовать проект из VisualStudio - ни разу не смог добиться DL.

Помогите разобраться.
21 сен 12, 12:14    [13201003]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Попробуйте
UPDATE [dbo].[Rejects_Shift] WITH (TABLOCK)
		SET [timestamp]=GETDATE(),RejCount = @RejCnt
		WHERE	InternObjID = @internalOID AND
				ShiftDate = @ShiftDate AND
				ProductionShift = @Shift AND
				Product = @Product AND
				RejDescription = @RejDesc
21 сен 12, 12:33    [13201198]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
Aleksej Edin
Member

Откуда: Berlin, Germany
Сообщений: 12
Jovanny, А можно пояснить почему? Система уже в работе и запускается лишь несколько раз в день - мне не так уж просто наблюдать за процессом "live", поэтому "попробовать" довольно сложно.
Я уже изменил SP т.о. что весь этот блок является одной транзакцией и уже селект блокирует запись/страницу (не уверен что именно) до конца транзакции - т.е. до инсерта или апдейта:
BEGIN TRAN Rej
	SELECT @found = ID, @rejcounterold = RejCount 
	FROM 	[dbo].[Rejects_Shift] WITH (UPDLOCK)
	WHERE	InternObjID = @internalOID AND
			ShiftDate = @ShiftDate AND
			ProductionShift = @Shift AND
			Product = @Product AND
			RejDescription = @RejDesc
	IF (@found = 0) --no data available for such date/shift/product
	BEGIN
		INSERT INTO [dbo].[Rejects_Shift]
			([timestamp],[InternObjID],[ShiftDate],[ProductiveTime], [ProductionShift],[Product],[RejDescription] , [RejCount] )
		VALUES
			(GETDATE(),@internalOID, @ShiftDate,'', @Shift, @Product , @RejDesc ,@RejCnt)
	END
	ELSE IF (@rejcounterold <> @RejCnt) --same data exists, but with other value
	BEGIN
		UPDATE [dbo].[Rejects_Shift]
			SET [timestamp]=GETDATE(),RejCount = @RejCnt
			WHERE	ID = @found
	END		
COMMIT TRAN Rej


Насколько я понимаю при таком случае блокироваться должна именно строчка? И в двух параллельных потоках эта строчка будет гарантированно разной (разный ID, т.к. два потока бегут для разных InternObjID).
Или я ошибаюсь и блокируется страница? Как тогда блокировать только строчку?
21 сен 12, 12:45    [13201320]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]

Guest
если 2008, то может мерже использовать?
21 сен 12, 12:49    [13201338]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
Glory
Member

Откуда:
Сообщений: 104751
Aleksej Edin
Насколько я понимаю при таком случае блокироваться должна именно строчка? И в двух параллельных потоках эта строчка будет гарантированно разной (разный ID, т.к. два потока бегут для разных InternObjID).
Или я ошибаюсь и блокируется страница? Как тогда блокировать только строчку?

Ваш UPDATE изменяет поля входящие в какой то индекс ?
При этом условие в WHERE позволяет использовать другой индекс ?
21 сен 12, 12:52    [13201367]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
Aleksej Edin
Member

Откуда: Berlin, Germany
Сообщений: 12
Glory, я не совсем профи в SQL - можете пояснить про индекс?
Поле ID является PK - это же индекс?
Других ключей или индексов в этой таблице нет (только PK - ID).
21 сен 12, 12:55    [13201391]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Aleksej Edin
селект блокирует запись/страницу

Что блокируется, заранее сказать нельзя. Есть такое понятие, как эскалация блокировок.
Хинтами Вы можете указать уровень эскалации.
Когда стоит TABLOCK, Вы гарантируете, что другие сессии не будут вносить изменения параллельно с текущей, и следовательно, вероятность deadlock-а значительно уменьшается. Правда, эффект от многопоточной загрузки тоже уменьшается.
21 сен 12, 13:26    [13201836]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
Aleksej Edin
Member

Откуда: Berlin, Germany
Сообщений: 12
Jovanny, a TABLOCK именно на апдейте ставить или уже на этапе селекта можно? Я таки попробую...

Параллельность важна в первую очередь на этапе запроса к Activplant - запись в SQL устраивает.
Т.е. теоретически я бы мог все результаты от запросов Activplant объединять с помощью Merge в SSIS проекте, но изменение проекта более трудоемкое чем изменение процедур. Кроме того в вэб-аппликации я merge сделать не могу, и хоть пока DL там не было - не понимая их природу исключить их я не могу.
21 сен 12, 13:34    [13201958]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
При INSERT, как правило, deadlock-ов не возникает.
21 сен 12, 13:37    [13201989]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
Aleksej Edin
Member

Откуда: Berlin, Germany
Сообщений: 12
Jovanny, я понимаю, но это все в одной транзакции. Вопрос в том КОГДА уже лучше блокировать? Во время селекта я определяю куда вствлять/апдейтить. Но, прочитав информацию по ссылке, кажется понтмаю, что с таблоком можно блокировать непосредственно при апдейте.
Ведь если другой процесс уже заблокировал таблицу - первый будет ждать ее освобождения?
21 сен 12, 13:52    [13202145]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
Aleksej Edin
Ведь если другой процесс уже заблокировал таблицу - первый будет ждать ее освобождения?

Ага.
И я бы блокировал таблицу только в UPDATE. Если в других потоках много инсертов, то незачем блокировать всю таблицу в начале транзакции.
21 сен 12, 14:06    [13202278]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
Aleksej Edin
Member

Откуда: Berlin, Germany
Сообщений: 12
Jovanny, пока дважды все прошло без DL, но такое было и раньше. Продолжу наблюдать.
В любом случае спасибо за участие!
21 сен 12, 16:35    [13203712]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
step_ks
Member

Откуда:
Сообщений: 936
Aleksej Edin, вы бы граф дедлока приложили.
22 сен 12, 17:06    [13206706]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock при параллельной записи данных  [new]
Aleksej Edin
Member

Откуда: Berlin, Germany
Сообщений: 12
Jovanny,
итак, похоже что все замечательно работает. Спасибо вам огромное!
Ни разу до этого не встречал упоминания об "эскалации блокировок", поэтому не мог понять как может быть возможна взаимоблокировка при апдейте РАЗНЫХ строк.
Заметно правда небольшое падение скорости записи, но, как я говорил, главной задачей распараллеливания было разделить запросы к Activplant'у, а ускорение записи это лишь "в придачу".

Надеюсь кому-нибудь пригодится этот пост.
25 сен 12, 18:35    [13221022]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить