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

Откуда:
Сообщений: 152
Доброе время суток.
Вопрос - как разделить записи из одной таблицы при параллельной обработке. Нужны умные мысли/советы.

Есть некая таблица CREATE TABLE tableA (Id INT PRIMARY KEY, Status INT NOT NULL)
Есть некая процедура sp_DoSomething, которая работает примерно так:
1. Получили набор строк из tableA (на основании определенного набора параметров)
2. Собрали на основании п.1 во времянки кучу данных из разных больших таблиц
3. Проанализировали по хитрому полученные данные
4. В транзакции изменили статусы в tableA у обработанных строк и вставили кучу данных в несколько связных таблиц.

Процедура sp_DoSomething может быть запущена одновременно разными пользователями и в разных сессиях в обработку могут пойти пересекающиеся наборы строк из tableA, чего естественно надо избежать. Избежать надо корректно, т.е. позволить процедуре сохранить не пересекающиеся данные, а пересекающиеся сохранить только в одном экземпляре (в одной из сессий).
Почитав и подумав получил два варианта.
1. Использование timestamp. При сохранении проверять, что запись уже была обработана и исключать такие строки. Недостатки - 1)дополнительные проверки при сохранении данных 2)в каждой из сессий будет тратится время на обработку пересекающихся данных
2. Использование статуса "в обработке". В начале процедуры захваченным строкам присваивается статус в обработке, по окончанию обработки статус снимается. Недостатки - если процедура прекратила работу по какой-то причине, строки останутся заблокированными. И кому-то придется эти статусы снимать руками.

Больше вариантов пока не родилось. Есть идеи?
7 дек 11, 00:03    [11717975]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Glamorama
Есть идеи?
Есть. Для начала опубликовать результат
select @@version
7 дек 11, 01:07    [11718202]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
А в транзакции, видимо низя заблокировать эти строки?
7 дек 11, 02:13    [11718369]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
руби концы
Guest
Glamorama,

TABLOCKX. Не пересекутся.
7 дек 11, 10:12    [11719054]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Jovanny
Member

Откуда:
Сообщений: 1196
руби концы
TABLOCKX. Не пересекутся.

Правда, будут монопольно блокировать таблицу, так что для чтения другие сессии могут использовать уровни изолированности только READ UNCOMMITTED или SNAPSHOT. Не уверен, что это всегда подойдёт.
7 дек 11, 11:42    [11719716]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Glory
Member

Откуда:
Сообщений: 104751
Glamorama
Процедура sp_DoSomething может быть запущена одновременно разными пользователями и в разных сессиях в обработку могут пойти пересекающиеся наборы строк из tableA, чего естественно надо избежать.

Сколько раз может быть обработана одна запись из tableA ? Один или много ?
7 дек 11, 11:47    [11719761]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
руби концы
TABLOCKX
Из пушки по воробьям.

UpdLock, ReadPast, RowLock (контрольный)
+ Пример
USE tempdb
GO
CREATE TABLE dbo.TestLock (ID Int IDENTITY CONSTRAINT [PK_TestLock] PRIMARY KEY)
GO
INSERT dbo.TestLock DEFAULT VALUES
GO 5
BEGIN TRAN TSession1
	SELECT	*
	FROM	dbo.TestLock WITH (UpdLock, ReadPast, RowLock)
	WHERE	ID IN (1,2,3)
-- ROLLBACK
GO
-- В другой сессии
BEGIN TRAN TSession2
	SELECT	*
	FROM	dbo.TestLock WITH (UpdLock, ReadPast, RowLock)
	WHERE	ID IN (3,4,5)
-- ROLLBACK
GO
DROP TABLE dbo.TestLock
ID
1
2
3

ID
4
5
ReadPast-у пофигу в каком процессе была локировка, может там у вас есть другая процедура изменяющая данные.
А ещё надо кортролить пропущенные строки. Например колонкой статусом.
+ К примеру
-- В начале
INSERT	@Table
SELECT	 A.ID
	,B....
	,CASE	WHEN B.ID IS NULL
		THEN Convert(Bit,1)
		ELSE Convert(Bit,0)
		END	AS Locked	-- AS [Skiped]
FROM	     dbo.TestLock A WITH (NoLock)
	JOIN dbo.TestLock B WITH (UpdLock, ReadPast, RowLock) ON B.ID = A.ID
WHERE	A.ID IN (A,B,C)

.....

-- В конце
IF Exists(
	SELECT	*
	FROM	     @Table		L
		JOIN dbo.TestLock	T ON T.ID = L.ID
	WHERE	    L.Locked    = 1	-- Пропущенные
		AND T.[Status] != X	-- Кошерное состояние
)	PRINT	'Ахтунг ...'
Только не прямо так. Нужно подумать о локировках и самом процессе обработки.
7 дек 11, 12:51    [11720448]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Glamorama
Member

Откуда:
Сообщений: 152
1.
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
2. Строки последовательно могут обрабатываться хоть 1000 раз каждая
3. Блокировка строк с Readpast - интересный вариант. Однако это приведет к блокировке других ресурсов, которые блочить надолго не хотелось бы.
7 дек 11, 14:30    [11721698]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Glory
Member

Откуда:
Сообщений: 104751
Glamorama
2. Строки последовательно могут обрабатываться хоть 1000 раз каждая


begin transaction
update tablea set status = 1
OUTPUT inserted.id INTO #worktable
where status = 0 ....

select... from #worktable ....

update a set status = 0
from tablea a INNER JOIN #worktable b on a.id = b.id

commit
7 дек 11, 15:12    [11722152]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Коляныч
Member

Откуда:
Сообщений: 279
Glamorama,
Предположим начали выполнение в такой последовательности:

sp_DoSomething(0,199)
sp_DoSomething(100,299)


Первая куда-то что-то считала строчки с 0 по 199 и начала обрабатывать. Какой должна быть логика у второй? Должна ли она дожидаться окончания первой? Или может приступить к обработке только доступного поддиапазона 200-299? Обращается ли к этой таблице в этот момент какие-то другие транзакции на чтение? Если им захочется прочесть строчку №99, должны ли они дожидаться окончания первого вызова процедуры sp_DoSomething (которая пока что ничего не апдейтит, а просто что-то считает в уме)? Обращаются ли к этой таблице другие транзакции с запросами на изменение данных или модифицировать что-либо может только эта процедура? Изменяют ли другие параллельно исполняющиеся транзакции строчки в других таблицах, косвенно участвующих в рассчётах? Могут ли внесённые или изменения чего-либо аннулировать тот результат, который sp_DoSomething в это время просчитывает во временных таблицах?
7 дек 11, 16:48    [11723316]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Glamorama
Member

Откуда:
Сообщений: 152
1. Первая сожрет строчки с 0 по 199, вторая с 200 по 299. Никакого ожидания.
2. Предполагается, что все процедуры, которые могут обрабатывать эту таблицу(и для которых критично состояние данных в этой таблице), будут использовать единый алгоритм разделения строк.
3. Связные таблицы модифицируются только в процедурах, использующих механизм разделения строк на этой таблице.
4. Запрещено законом менять данные, влияющие на расчет, во время расчета.
7 дек 11, 18:33    [11724299]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
аж 2 варианта предложили
Guest
Glamorama,

11722152 11720448

а он все пункты непринятия расписывает
7 дек 11, 21:05    [11725253]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Glamorama3
Блокировка строк с Readpast
Однако это приведет к блокировке других ресурсов, которые блочить надолго не хотелось бы.
Бредятина, а всё от непонимания.
8 дек 11, 00:26    [11726040]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Glamorama
Member

Откуда:
Сообщений: 152
Скажу по другому - не охота всю процедуру закатывать в длинную транзакцию.
Потому как придется переделывать кусочки, которые рассчитаны на работу вне транзакции.
8 дек 11, 11:58    [11727843]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Glory
Member

Откуда:
Сообщений: 104751
Glamorama
Скажу по другому - не охота всю процедуру закатывать в длинную транзакцию.
Потому как придется переделывать кусочки, которые рассчитаны на работу вне транзакции.

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

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

Mnior предложил вариант, удовлетворяющий вашим хотелкам. На всякий случай разжевываю:
begin tran;

if object_id('tempdb..#w', 'U') is not null
 drop table #w;

create table #w (id int primary key);

insert into #w
 (id)
 select
  id
 from
  dbo.TableA with (updlock, readpast, rowlock)
 where
  <ваши условия отбора для обработки>;

/*
Далее используем #w как рабочую таблицу:
2. Собрали на основании п.1 во времянки кучу данных из разных больших таблиц
3. Проанализировали по хитрому полученные данные
4. Изменили статусы в tableA у обработанных строк и вставили кучу данных в несколько связных таблиц.
*/

commit;

Но вы по человечески делать не хотите, вам надо с извращениями. Вот вам вариант с извращениями:
declare @l varchar(max), @r varchar(max);

if object_id('tempdb..#w', 'U') is not null
 drop table #w;

create table #w (id int primary key);

insert into #w
 (id)
 select
  id
 from
  dbo.TableA with (nolock)
 where
  <ваши условия отбора для обработки>;

select
 @l = 'declare @result int; ' + 
      (
       select 
        'exec @result = sp_getapplock ''TableA_' + cast(id as varchar(10)) +
        ''', ''Exclusive'', ''Session'', 0; if @result < 0 delete from #w where id = ' +
        cast(id as varchar(10)) + '; '
       from
        #w
       for xml path(''), type
      ).value('.', 'varchar(max)');

exec(@l);

/*
Далее используем #w как рабочую таблицу:
2. Собрали на основании п.1 во времянки кучу данных из разных больших таблиц
3. Проанализировали по хитрому полученные данные
4. В транзакции изменили статусы в tableA у обработанных строк и вставили кучу данных в несколько связных таблиц.
*/

select
 @r = 'declare @result int; ' +
      (
       select
        'exec @result = sp_releaseapplock ''TableA_' +
        cast(id as varchar(10)) + ''', ''Session'';'
       from
        #w
       for xml path(''), type
      ).value('.', 'varchar(max)');

exec(@r);

Причем такое извращение придется делать везде, где модифицируется TableA. Думается, это не многим легче, чем
Glamorama
переделывать кусочки, которые рассчитаны на работу вне транзакции.
8 дек 11, 13:48    [11729040]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Glamorama
Member

Откуда:
Сообщений: 152
До такого изврата я и сам додумался, но это уже перебор.
Одна длинная транзакция мне всяко милее.
8 дек 11, 17:39    [11731461]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
додумуму
Guest
Glamorama
До такого изврата я и сам додумался, но это уже перебор.
Одна длинная транзакция мне всяко милее.

читай "херасе вы даете, я это даже понять не в состоянии".
автор
1. Использование timestamp. При сохранении проверять, что запись уже была обработана и исключать такие строки. Недостатки - 1)дополнительные проверки при сохранении данных 2)в каждой из сессий будет тратится время на обработку пересекающихся данных
2. Использование статуса "в обработке". В начале процедуры захваченным строкам присваивается статус в обработке, по окончанию обработки статус снимается. Недостатки - если процедура прекратила работу по какой-то причине, строки останутся заблокированными. И кому-то придется эти статусы снимать руками.

транзакцию придумать не можешь, зато applock'и с кучей изврата - давно придумал, но слишком скучно это, оказывается.
8 дек 11, 22:00    [11732869]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Коляныч
Member

Откуда:
Сообщений: 279
Glamorama
1. Первая сожрет строчки с 0 по 199, вторая с 200 по 299. Никакого ожидания.
2. Предполагается, что все процедуры, которые могут обрабатывать эту таблицу(и для которых критично состояние данных в этой таблице), будут использовать единый алгоритм разделения строк.
3. Связные таблицы модифицируются только в процедурах, использующих механизм разделения строк на этой таблице.
4. Запрещено законом менять данные, влияющие на расчет, во время расчета.



Я бы сделал процедурку приблизительно так:

create proc do_somtehing @begin int, @end int
as
	declare @t table(id int)
	begin tran	
		insert @t select id from Table_1 with(readpast,updlock) where id between @begin and @end

		---- что-то медленное

		---- тут быстрый апдейт таблицы
	commit tran



Хинт updlock вешает U-блокировку, которая разрешает другим процессам читать строчки, но уже запрещает ставить на них U или X. readpast - означает проигнорировать залоченные строчки и захватить только доступные.


exec do_somtehing 0, 199 - установит U на диапазон 0..199
exec do_somtehing 100, 299 - установит U только на диапазон 200..299

пока они что-то считают во временных таблицах другие процессы вполне себе имеют туда доступ на чтение:

select * from Table_1 where id = 55


но попытка что-либо удалить или проапдейтить в этой таблице закончится ожиданием завершения do_somtehing, захватившей диапазон
update Table_1 set v = 'AAAA' where id = 55
9 дек 11, 09:03    [11733997]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Коляныч
Member

Откуда:
Сообщений: 279
Коляныч,

Но в приведенном примере есть хитрости с эскалацией. Например первый вызов решит с целью экономии ресурсов захватить всю страницу PAGE1. Пусть разложение по страницам было такое:

PAGE 1
  1 ...
  2 ...

  200 ...


PAGE 2
  201 ...
  202 ...
  203 ...

  300 ...


Если случится эскалация, значит на всей PAGE1 будет висеть U, первый запрос успешно обработает строчки 1..199, второй не сможет захватить запись 200 и обработает только 201..299, строчка 200 останется необработанной. Значит либо принудительно запрещать эскалацию, либо строить логику приложения таким образом, чтобы через некоторое время вернуться и доработать пропущенные строчки.
9 дек 11, 09:16    [11734037]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Коляныч
Хинт updlock вешает U-блокировку, которая разрешает другим процессам читать строчки, но уже запрещает ставить на них U или X.
Ух ты аказываеца, а с каких пор?

Коляныч
хитрости с эскалацией
Ага, значит про хинт RowLock тут зря распинались.
9 дек 11, 10:57    [11734673]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Mnior
Коляныч
Хинт updlock вешает U-блокировку, которая разрешает другим процессам читать строчки, но уже запрещает ставить на них U или X.
Ух ты аказываеца, а с каких пор?

Коляныч
хитрости с эскалацией
Ага, значит про хинт RowLock тут зря распинались.
Вообще-то, согласно BOL, S- и U-блокировки совместимы...
rowlock -- это просьба, а не приказ. Поэтому, если позволят ресурсы, лучше создать кластерный индекс с опциями allow_page_locks = off и allow_row_locks = on.
9 дек 11, 11:10    [11734787]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
Вообще-то, согласно BOL, S- и U-блокировки совместимы...
Согласен. Обосрался. Коляныч правильно уточнил.

Хотя порядок действий (согласно иерархии объектов) это у кого как принято.
В данном случае политика обхода DeadLock понятна, заранее определена.

invm
rowlock -- это просьба, а не приказ. Поэтому, если позволят ресурсы, лучше создать кластерный индекс с опциями allow_page_locks = off и allow_row_locks = on.
Ну дык, никто не спорит. Просто "решение" Коляныч-а "с возвратом" явно не из этой оперы, смысла не вижу никакого.
9 дек 11, 11:52    [11735292]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
aleks2
Guest
Glamorama
Процедура sp_DoSomething может быть запущена одновременно разными пользователями и в разных сессиях в обработку могут пойти пересекающиеся наборы строк из tableA, чего естественно надо избежать. Избежать надо корректно, т.е. позволить процедуре сохранить не пересекающиеся данные, а пересекающиеся сохранить только в одном экземпляре (в одной из сессий).


Glamorama
12. Строки последовательно могут обрабатываться хоть 1000 раз каждая


1. Тредстартер мыслит категориями "кнопка "Сделать так как я хачу".
2. Поэтому и блуждает в трех соснах.
3. Если два человека обрабатывают одну запись, то почему результат работы одного хуже/лучше результата работы другого?
4. Если же разницы нет - какая разница результаты чьей обработки будут сохранены?

5. Если же, надо просто экономить трудозатраты - блокируйте. Тока не статусом, а sp_getapplock @LockOwner = 'session'.
И фсе саморазблокируется при отпаде клиента. Можна и ишо самоснимающиеся варианты попридумывать.
9 дек 11, 14:56    [11737193]     Ответить | Цитировать Сообщить модератору
 Re: блокировка обрабатываемых записей  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Mnior
invm
rowlock -- это просьба, а не приказ. Поэтому, если позволят ресурсы, лучше создать кластерный индекс с опциями allow_page_locks = off и allow_row_locks = on.
Ну дык, никто не спорит. Просто "решение" Коляныч-а "с возвратом" явно не из этой оперы, смысла не вижу никакого.
Ну почему же не из той оперы? Все определяется требованиями к системе.
9 дек 11, 15:54    [11737749]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить