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

Откуда: Москва
Сообщений: 139
Добрый день!

Уже порядком замучился с параллельным чтением записей из таблицы конвейера
Время от времени падает падает один из экземпляров приложения при обработке очередной записи по проверке на оптимистическую блокировку. Это означает, что что несколько модулей каким-то волшебным образом вычитывают одинаковые данные. Необходимые блокировки при вычитке данных для Update прописаны. Понимаю, что чудес быть не должно, но может есть у кого какие-то идеи? Куда копать?
Повышение уровня изоляции не вариант - существенно ухудшается производительность вставки в конвейер и производительность обработки самой очереди

Сама таблица

/****** Object:  Table [Output].[NotificationPipe]    Script Date: 12/21/2012 09:20:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [Output].[NotificationPipe](
	[ObjectID] [uniqueidentifier] NOT NULL,
	[ts] [bigint] NOT NULL,
	[ProcessingSubsystemID] [varchar](255) NULL,
	[BookedAt] [datetime] NULL,
	[ProcessingStartAt] [datetime] NULL,
	[ProcessingPhase] [int] NOT NULL,
	[Message] [uniqueidentifier] NOT NULL,
	[Notification] [uniqueidentifier] NULL,
	[Contact] [uniqueidentifier] NULL,
	[Subscription] [uniqueidentifier] NOT NULL,
	[Priority] [tinyint] NOT NULL,
 CONSTRAINT [PK_NotificationPipe] PRIMARY KEY CLUSTERED 
(
	[ObjectID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [OutputData]
) ON [OutputData]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [Output].[NotificationPipe]  WITH CHECK ADD  CONSTRAINT [FK_NotificationPipe_Contact] FOREIGN KEY([Contact])
REFERENCES [Customers].[Contact] ([ObjectID])
GO

ALTER TABLE [Output].[NotificationPipe] CHECK CONSTRAINT [FK_NotificationPipe_Contact]
GO

ALTER TABLE [Output].[NotificationPipe]  WITH CHECK ADD  CONSTRAINT [FK_NotificationPipe_Subscription] FOREIGN KEY([Subscription])
REFERENCES [Subscriptions].[Subscription] ([ObjectID])
GO

ALTER TABLE [Output].[NotificationPipe] CHECK CONSTRAINT [FK_NotificationPipe_Subscription]
GO

ALTER TABLE [Output].[NotificationPipe]  WITH CHECK ADD  CONSTRAINT [CCF_NotificationPipe_BookedAt] CHECK  (([NotificationPipe].[BookedAt]>='19000101 00:00:00.000'))
GO

ALTER TABLE [Output].[NotificationPipe] CHECK CONSTRAINT [CCF_NotificationPipe_BookedAt]
GO

ALTER TABLE [Output].[NotificationPipe]  WITH CHECK ADD  CONSTRAINT [CCF_NotificationPipe_ProcessingPhase] CHECK  (([NotificationPipe].[ProcessingPhase]=(0) OR [NotificationPipe].[ProcessingPhase]=(1) OR [NotificationPipe].[ProcessingPhase]=(2)))
GO

ALTER TABLE [Output].[NotificationPipe] CHECK CONSTRAINT [CCF_NotificationPipe_ProcessingPhase]
GO

ALTER TABLE [Output].[NotificationPipe]  WITH CHECK ADD  CONSTRAINT [CCF_NotificationPipe_ProcessingStartAt] CHECK  (([NotificationPipe].[ProcessingStartAt]>='19000101 00:00:00.000'))
GO

ALTER TABLE [Output].[NotificationPipe] CHECK CONSTRAINT [CCF_NotificationPipe_ProcessingStartAt]
GO

ALTER TABLE [Output].[NotificationPipe] ADD  DEFAULT (newsequentialid()) FOR [ObjectID]
GO

ALTER TABLE [Output].[NotificationPipe] ADD  DEFAULT ((1)) FOR [ts]
GO

ALTER TABLE [Output].[NotificationPipe] ADD  DEFAULT (getdate()) FOR [BookedAt]
GO


Процедура пулера

CREATE PROCEDURE [Output].[NotificationPipe$WatchNew](
	@p_UpdateQnt int,
	@p_ModuleId varchar(255),
	@p_PhaseNew int,
	@p_PhaseBooked int ) AS
BEGIN
		DECLARE @PipeItems TABLE( ItemID uniqueidentifier, BookedAt datetime )

		UPDATE TOP( @p_UpdateQnt ) [Output].[NotificationPipe]
		SET ProcessingSubsystemID = @p_ModuleId, ProcessingPhase = @p_PhaseBooked 
		OUTPUT inserted.[ObjectID], inserted.[BookedAt] INTO @PipeItems
		FROM
			 (
				SELECT TOP ( @p_UpdateQnt ) pOrd.[ObjectID] AS OrdObjectID
				FROM [Output].[NotificationPipe] pOrd WITH(UPDLOCK, READPAST)
				WHERE
					ProcessingSubsystemID IS NULL AND
					ProcessingStartAt IS NULL AND
					ProcessingPhase = @p_PhaseNew 
				ORDER BY pOrd.[Priority], pOrd.[BookedAt]
			) pOrd 
		WHERE pOrd.OrdObjectID = ObjectID
			
		SELECT i.[ItemID] FROM @PipeItems i ORDER BY i.[BookedAt]
END


Количество параллельных потоков, использующих процедуру - до 10. Одновременно идет интенсивная вставка данных.
21 дек 12, 09:24    [13663986]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
ars22
Member

Откуда: Москва
Сообщений: 139
Прошу прощения - забыл указать версию сервера

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) 
21 дек 12, 09:54    [13664118]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
WarAnt
Member

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

Невижу я здесь ничего что должно помешать другим коннектам читать одинаковые данные.
Вы про эскалацию блокировок слушали? Если бы слышали то знали бы что ваши WITH(UPDLOCK, READPAST) только рекомендация, а не постулат для сервера, а значит строить всю логику работы вокруг хинтов это поменьшей мере нелогично. Поэтому вполне логично что у вас будут происходить всякие чудеса.
21 дек 12, 10:20    [13664261]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
ars22
Member

Откуда: Москва
Сообщений: 139
Какие можете предложить варианты? Я понимаю, что тут есть какая-то проблема, но я ее не вижу - поэтому собственно и обратился на форум.

Спасибо
21 дек 12, 10:24    [13664290]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Попробуйте так:
WITH x AS
(
 SELECT TOP ( @p_UpdateQnt )
  [ObjectID],
  ProcessingSubsystemID,
  ProcessingPhase
 FROM [Output].[NotificationPipe] WITH(XLOCK, ROWLOCK, READPAST)
 WHERE
  ProcessingSubsystemID IS NULL AND
  ProcessingStartAt IS NULL AND
  ProcessingPhase = @p_PhaseNew 
 ORDER BY [Priority], [BookedAt]
)
UPDATE x
 SET ProcessingSubsystemID = @p_ModuleId, ProcessingPhase = @p_PhaseBooked 
OUTPUT inserted.[ObjectID], inserted.[BookedAt] INTO @PipeItems;
21 дек 12, 11:05    [13664520]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
ars22
Какие можете предложить варианты?


Не пытаться изобретать велосипед и использовать для ассинхронной многопотоковой обработки Service Broker.
21 дек 12, 11:28    [13664671]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
Гость333
Member

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

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


WarAnt
ваши WITH(UPDLOCK, READPAST) только рекомендация, а не постулат для сервера

Хм. Можно пример, когда хинты WITH(UPDLOCK, READPAST) не будут действовать?

WarAnt
Вы про эскалацию блокировок слушали?

А как тут может повлиять эскалация? Вот одна коннекция прочитала over5000 строк из таблицы. Произошла эскалация, на таблицу наложилась X-блокировка. Теперь запрос запускается из другой коннекции. Он попытается наложить IX-блокировку на таблицу и, таким образом, будет ждать, пока первая коннекция не снимет упомянутую X-блокировку. До снятия блокировки вторая коннекция не прочтёт ни одной записи.
21 дек 12, 12:44    [13665194]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
ars22
Member

Откуда: Москва
Сообщений: 139
Вы хотите сказать, что обернув в явную транзакцию Update можно получить требуемый результат?
Я пробовал переписать с помощью явной транзакции. Первым селектом вычитывал данные по тем же условиям, накладывал при этом различные хинты в различных комбинациях и записывал идентификаторы в табличную переменную. Затем в той же транзакции апдейтил данные таблицы конвейера по первичному ключу.

Эффект тот же - в разных потоках вычитываются одинаковые записи
21 дек 12, 12:54    [13665246]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
Гость333
Member

Откуда:
Сообщений: 3683
ars22
Вы хотите сказать, что обернув в явную транзакцию Update можно получить требуемый результат?

Упс, невнимательно посмотрел, что происходит в вашем апдейте. Вопрос про транзакцию пока снимается.
21 дек 12, 13:11    [13665352]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а кто вам сказал что ваши блокировки без изменения данных будут держаться все время выборки на всей выборке?
вы ни транзакцию не открываете ни HOLD не указываете - вот сервер и снимает блокировки по мере необходимости, а конкуренты эти данные с полным правом подхватывают - имеют право
21 дек 12, 13:31    [13665517]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
MasterZiv
Member

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

Невижу я здесь ничего что должно помешать другим коннектам читать одинаковые данные.
Вы про эскалацию блокировок слушали? Если бы слышали то знали бы что ваши WITH(UPDLOCK, READPAST) только рекомендация, а не постулат для сервера, а значит строить всю логику работы вокруг хинтов это поменьшей мере нелогично. Поэтому вполне логично что у вас будут происходить всякие чудеса.


WITH(UPDLOCK) -- не может быть рекомендацией. На это завязывается логика приложения, реализовывать так этот хинт просто глупо.
Он бессмысленный без обязательного следования ему.

READPAST -- не знаю. Но тоже думаю что не будет он рекомендацией. А будет сервер жёстко следовать инструкции (пропускать записи на локах).
21 дек 12, 13:36    [13665555]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
Crimean
а кто вам сказал что ваши блокировки без изменения данных будут держаться все время выборки на всей выборке?
вы ни транзакцию не открываете ни HOLD не указываете - вот сервер и снимает блокировки по мере необходимости, а конкуренты эти данные с полным правом подхватывают - имеют право


Там как бы вся выборка в одной транзакции с UPDATE за счёт того, что реализована в одном операторе, который есть одна транзакция по-любому.
21 дек 12, 13:40    [13665588]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
ars22
Добрый день!

Уже порядком замучился с параллельным чтением записей из таблицы конвейера
Время от времени падает падает один из экземпляров приложения при обработке очередной записи по проверке на оптимистическую блокировку. Это означает, что что несколько модулей каким-то волшебным образом вычитывают одинаковые данные. Необходимые блокировки при вычитке данных для Update прописаны. Понимаю, что чудес быть не должно, но может есть у кого какие-то идеи? Куда копать?


А где и как реализована эта оптимистичная блокировка ?
Я думаю, проблема в ней. Показывай код.

ars22
Повышение уровня изоляции не вариант - существенно ухудшается производительность вставки в конвейер и производительность обработки самой очереди


Тут уровень изоляции уже некуда поднимать, он и так самый большой. У тебя всё на WRITE, уровни изоляции тебе помогут только если будут проблемы с параллельной записью и чтением. У тебя же идёт только запись, это самый низкий и одновременно самый высокий уровень изоляции и так.
21 дек 12, 13:44    [13665625]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
Гость333
Member

Откуда:
Сообщений: 3683
ars22
Время от времени падает падает один из экземпляров приложения при обработке очередной записи по проверке на оптимистическую блокировку. Это означает, что что несколько модулей каким-то волшебным образом вычитывают одинаковые данные.

Опишите, как происходит проверка на эту блокировку.

И ещё мне непонятно следующее. Вот у вас в таблице есть столбец ProcessingPhase, который может принимать значения 0, 1, 2. Отсюда предположу, что фаза последовательно проходит значения 0 -> 1-> 2. Пусть некий процесс сменил фазу с 0 на 1 и одновременно присвоил какое-то значение столбцу ProcessingSubsystemID. Каким образом процесс, ответственный за смену фазы 1 -> 2, прочитает затем эту же строку, с учётом условия "ProcessingSubsystemID IS NULL"?
21 дек 12, 13:46    [13665637]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
ars22
Member

Откуда: Москва
Сообщений: 139
Гость333,

Логика оптимистической блокировки реализована в ядре приложения в закрытом от просмотра коде фреймворка.
В общем случае она базируется на сравнении значения служебного атрибута ts каждого конкретного экземпляра конвейера с тем значением, которое было получено модулем во время последнего считывания объекта.
Через профайлер видно как через этот фреймворк генерится выражение для обновление рассматриваемой таблицы

UPDATE [Output].[NotificationPipe] SET [NotificationPipe].[ts] = CASE WHEN ([a].[ts] < 9223372036854775807) THEN ([a].[ts] + 1) ELSE 1 END, [NotificationPipe].[ProcessingPhase] = @p3900473, [NotificationPipe].[ProcessingStartAt] = @p3900474 FROM [Output].[NotificationPipe] [a] WHERE ([a].[ObjectID] = @p3900475 AND [a].[ts] = @p3900476)
SET @N=@N+@@ROWCOUNT
',N'@p3900473 int,@p3900474 datetime,@p3900475 uniqueidentifier,@p3900476 bigint,@N int output',@p3900473=2,@p3900474='2012-12-21 08:53:00.630',@p3900475='1E70514A-2A4B-E211-A8BE-0026552398B8',@p3900476=1,@N=@p7 output
select @p7


Процесс выглядит так: Каждый из модулей обработки вызывает процедуру пулинга и передает в нее свой идентификатор(задается в конфигурации). Процедура пулинга возвращает список идентификаторов, помеченных в конвейере идентификатором модуля. далее в рамках логики обработки модуль осуществляет некие действия с каждым экземпляром. В ходе выполнения этой логики происходят в том числе и изменение статуса (0->1->2). После завершения обработки осуществляется удаление записи из конвейера.

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

Ошибка возникает примерно раз в 20 минут ингода реже, иногда чаще. В пике в таблицу конвейер может сваливаться до 10 тысяч записей\мин.
Особой проблемы в этом пока нет. На конечный результат это не влияет - логически каждая запись обрабатывается в конечном итоге правильно. Кроме этого модули умеют самостоятельно подниматься после таких ошибок и там можно играться конфигурацией - но сам факт такого поведения меня категорически смущает.
После продолжительных попыток победить проблему уже действительно смотрю, чтобы натравить пулинг на ServiceBroker)
21 дек 12, 14:18    [13665865]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
Crimean
Member

Откуда:
Сообщений: 13147
MasterZiv
Там как бы вся выборка в одной транзакции с UPDATE за счёт того, что реализована в одном операторе, который есть одна транзакция по-любому.


ой ли? у вас сначала SELECT, а на его основании - UPDATE
двум ТАКИМ селектам никто не помешает дать пересекающиеся данные
а двух конкурирующим UPDATE обновить их в порядке естественной очередности
почему бы не переписать все БЕЗ вложенного SELECT?
21 дек 12, 14:36    [13665999]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
ars22
Member

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

Такая конструкция не спасает. Кроме того еще прибавляются дедлоки. Пробовал вместо Updlock Rowlock или Xlock - с дедлоками получше, но эффект одновременного чтения остается

CREATE PROCEDURE [Output].[NotificationPipe$WatchNew](
	@p_UpdateQnt int,
	@p_ModuleId varchar(255),
	@p_PhaseNew int,
	@p_PhaseBooked int ) AS
BEGIN
		DECLARE @PipeItems TABLE( ItemID uniqueidentifier, BookedAt datetime )

		BEGIN TRANSACTION 
		
			INSERT @PipeItems
			SELECT TOP ( @p_UpdateQnt ) pOrd.[ObjectID],pOrd.BookedAt
					FROM [Output].[NotificationPipe] pOrd WITH(UPDLOCK, READPAST)
					WHERE
						ProcessingSubsystemID IS NULL AND
						ProcessingStartAt IS NULL AND
						ProcessingPhase = @p_PhaseNew 
					ORDER BY pOrd.[Priority], pOrd.[BookedAt]
					
			UPDATE [Output].[NotificationPipe]
			SET ProcessingSubsystemID = @p_ModuleId, ProcessingPhase = @p_PhaseBooked 
			FROM @PipeItems pit
			WHERE pit.ItemID = ObjectID
					
		COMMIT
		 
		SELECT i.[ItemID] FROM @PipeItems i ORDER BY i.[BookedAt]
END


GO
21 дек 12, 15:02    [13666232]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
ars22
Member

Откуда: Москва
Сообщений: 139
Еще одно наблюдение - чем меньше вычитка данных (меньшее значение @p_UpdateQnt) тем эффект одновременного чтения меньше.
21 дек 12, 15:10    [13666312]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а на чем дедлоки лезут?
21 дек 12, 15:17    [13666389]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
Crimean
Member

Откуда:
Сообщений: 13147
ars22
Еще одно наблюдение - чем меньше вычитка данных (меньшее значение @p_UpdateQnt) тем эффект одновременного чтения меньше.


конечно!
21 дек 12, 15:24    [13666497]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
ars22
Member

Откуда: Москва
Сообщений: 139
запрос блокировки обновления на индексе
21 дек 12, 15:24    [13666500]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
Crimean
Member

Откуда:
Сообщений: 13147
аа. то есть таки не покажете. ну и ладно.
21 дек 12, 15:27    [13666543]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
ars22
Member

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

Как только поймаю новый граф - выложу. Но дедлоки то ладно - их можно обработать. Но вот задвоение остается все равно.


Спасибо
21 дек 12, 15:32    [13666605]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
Crimean
Member

Откуда:
Сообщений: 13147
вы бы модельку собрали, что ли
21 дек 12, 15:42    [13666738]     Ответить | Цитировать Сообщить модератору
 Re: Чтение одинаковых данных в параллельных транзакциях  [new]
HOLD не нужен
Guest
Crimean
а кто вам сказал что ваши блокировки без изменения данных будут держаться все время выборки на всей выборке?
вы ни транзакцию не открываете ни HOLD не указываете - вот сервер и снимает блокировки по мере необходимости, а конкуренты эти данные с полным правом подхватывают - имеют право


а зачем аж HOLD? достаточно repeatableread
21 дек 12, 15:44    [13666775]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить