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

Откуда: Гималай
Сообщений: 2101
Приветствую всех.
Есть данные, которые необходимо "обработать".
Данные поступают очень часто, и так как процесс "обработки" не быстр, "обработчиков" очень много, скажем штук 20.
Которые периодически проверяют наличие данных на обработку, и если таковые есть, резервируют его для себя, затем обрабатывают, и по завершении устанавливают соотвествующий статус.
Скажем структура данных для обработки такова:
TblData
ID
DT
...
Processor
UID
Status

Где значение поле Status (int) и указывает то что данные нужно обработать или же то что обработка уже произведена, т.е. если 0 - необходимо обработать, если 1 - идет процесс обработки, 2 - обработка завершена.
Сам процесс проверки данных для обработки и резервация выполняется следующей инструкцией:
DECLARE @ReservedRecord bigint
SET ROWCOUNT 1
UPDATE TblData WITH (UPDLOCK)
SET Status=1, @ReservedRecord=1
WHERE Status=0
SET ROWCOUNT 0
При этом как вы видите, для исключения двойной обработки, т.е. чтобы одни и те же данные не прошли через разные обработчики, налогается блокировка на обновление.
Вот насчет этой и инструкции и вопрос, так как периодически (скажем каждые 500 мс), выполняется данная инструкция, налогается блокировка на таблицу TblData, соотвественно другие операции обновления, в том числе добавление новых данных, изменение статусов записей, тоже блокируется, ведь так?
Или же блокируется только в тот, момент если в TblData есть данные Status=0?
Если же при каждом вызове блокируется таблица TblData на обновление, не зависимо от того есть ли данные для обработки, может быть такой вариант лучше использовать?
DECLARE @ReservedRecord bigint
IF EXISTS (SELECT * FROM TblData WITH (NOLOCK) WHERE Status=0) BEGIN
    SET ROWCOUNT 1
    UPDATE TblData WITH (UPDLOCK)
    SET Status=1, @ReservedRecord=1
    WHERE Status=0
    SET ROWCOUNT 0
END
Т.е. сначала проверка наличия таких данных, и затем уже попытка резервации данных для обработки
Спасибо всем за внимание и за советы тоже ;)))))
21 июл 09, 07:30    [7438122]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
ChA
Member

Откуда: Москва
Сообщений: 10991
Наверное стоило бы вынести флаг обработки в отдельную таблицу, т.е., добавлять в неё только ID тех записей, которые надо обработать, возможно с полем Status, если действительно нужно такое кол-во состояний, что не факт. По окончании обработки(Status = 2) просто удалять их из этой вспомогательной таблицы.
21 июл 09, 08:31    [7438205]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
но даже в этом случае все равно будет резервировать данные, для меня сейчас проблема в оптимизации, т.е. в наложении блокировок, грубо говоря лучше так:

DECLARE @ReservedRecord bigint
SET ROWCOUNT 1
UPDATE TblData WITH (UPDLOCK)
SET Status=1, @ReservedRecord=1
WHERE Status=0
SET ROWCOUNT 0

или так:
DECLARE @ReservedRecord bigint
IF EXISTS (SELECT * FROM TblData WITH (NOLOCK) WHERE Status=0) BEGIN
    SET ROWCOUNT 1
    UPDATE TblData WITH (UPDLOCK)
    SET Status=1, @ReservedRecord=1
    WHERE Status=0
    SET ROWCOUNT 0
END

чтобы каждый раз не блокировать, при проверке данных на обработку
данная инструкция запускается каждые 500 мс, в нескольких (20) прогах
21 июл 09, 08:43    [7438224]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
ChA
Member

Откуда: Москва
Сообщений: 10991
orunbek
но даже в этом случае все равно будет резервировать данные, для меня сейчас проблема в оптимизации, т.е. в наложении блокировок, грубо говоря лучше так:

DECLARE @ReservedRecord bigint
SET ROWCOUNT 1
UPDATE TblData WITH (UPDLOCK)
SET Status=1, @ReservedRecord=1
WHERE Status=0
SET ROWCOUNT 0

или так:
DECLARE @ReservedRecord bigint
IF EXISTS (SELECT * FROM TblData WITH (NOLOCK) WHERE Status=0) BEGIN
    SET ROWCOUNT 1
    UPDATE TblData WITH (UPDLOCK)
    SET Status=1, @ReservedRecord=1
    WHERE Status=0
    SET ROWCOUNT 0
END

чтобы каждый раз не блокировать, при проверке данных на обработку
данная инструкция запускается каждые 500 мс, в нескольких (20) прогах
Трудно давать рекомендации не зная задачи. Вы напираете на свой метод обработки, но он, насколько я понимаю, совершенно неэффективен. Вы пытаетесь блокировать таблицу, причём, похоже, сами понимаете, что это неоптимально. Более того, еще усугбляете проверкой на существование, которая вовсе не даёт гарантии, что между проверкой и последующим обновлением вполне может вклинится другой параллельный процесс, который попросту "выдернет" эту запись из под носа. Неизвестно, сколько записей в таблице всего, какое количество из них реально обрабатывается, есть ли индексы по полю Status. Возможно обновление делается "тупым" сканированием таблицы TblData, а это неэффективно, не смотря на "SET ROWCOUNT 1". Так что лучше озвучьте исходную задачу, которую решаете, чтобы не заниматься псевдооптимизацией.
21 июл 09, 09:07    [7438286]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
iljy
Member

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

у вас какой сервер? Если 2005 - используйте конструкцию

UPDATE TblData WITH (UPDLOCK)
SET Status=1
WHERE Status=0
output ID into @T

после этого у вас в @T будет список ID на обработку, и таблица не блокируется
21 июл 09, 09:30    [7438367]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
iljy
Member

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

хинт UPDLOCK можно убрать, я забыл.
21 июл 09, 09:32    [7438380]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
ChA
Трудно давать рекомендации не зная задачи. Вы напираете на свой метод обработки, но он, насколько я понимаю, совершенно неэффективен. Вы пытаетесь блокировать таблицу, причём, похоже, сами понимаете, что это неоптимально. Более того, еще усугбляете проверкой на существование, которая вовсе не даёт гарантии, что между проверкой и последующим обновлением вполне может вклинится другой параллельный процесс, который попросту "выдернет" эту запись из под носа. Неизвестно, сколько записей в таблице всего, какое количество из них реально обрабатывается, есть ли индексы по полю Status. Возможно обновление делается "тупым" сканированием таблицы TblData, а это неэффективно, не смотря на "SET ROWCOUNT 1". Так что лучше озвучьте исходную задачу, которую решаете, чтобы не заниматься псевдооптимизацией.

Да, возможно вы правы, в плане неэффективности используемого метода ;-)
Ну блокирую потому что бывают случаи двойной обработки.
Ну а вариант с предвариательной проверкой просто один из вариантов, не решение.
Просто при таком выполнении:
DECLARE @ReservedRecord bigint
SET ROWCOUNT 1
UPDATE TblData WITH (UPDLOCK)
SET Status=1, @ReservedRecord=1
WHERE Status=0
SET ROWCOUNT 0
Не зависимо от того, есть ли или нет ли записей со статусом Status=0, блокируется таблица
Или это не так?
Если это так, то из-за того что обработчики часто запускают данную инструкцию соотвественно блокируют таблицу (не зависимо от того есть или нет), добавил вариант с предварительной проверкой существования, пускай между проверкой и UPDATE'ом данных потом не будет.
Задача только и в этом ;))) Изменение процесса оптимизации резервирования данных для обработки.
Что могу еще добавить на Status есть обычный индекс.
Если не понятно, излагаю извиняйте
21 июл 09, 09:34    [7438390]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iljy
orunbek,

у вас какой сервер? Если 2005 - используйте конструкцию

UPDATE TblData WITH (UPDLOCK)
SET Status=1
WHERE Status=0
output ID into @T

после этого у вас в @T будет список ID на обработку, и таблица не блокируется

пока писал ответ ChA, новое сообщение появилось ;-)
Да, сервер 2005, забыл написать про это
насчет предложенного варианта
UPDATE TblData SET Status=1 WHERE Status=0 output ID into @T
в данном случае только одна запись апдейтится? или все со Status=0?
21 июл 09, 09:38    [7438405]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
orunbek
в данном случае только одна запись апдейтится? или все со Status=0?
Вы же видите, что все (согласно условию в WHERE)
21 июл 09, 09:43    [7438426]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iap
orunbek
в данном случае только одна запись апдейтится? или все со Status=0?
Вы же видите, что все (согласно условию в WHERE)

вижу, поэтому и пишу, но сомневаюсь вдруг я что-то еще не знаю, как говорится век живи век учусь
значит:
SET ROWCOUNT 1
UPDATE TblData
SET Status=1
WHERE Status=0
output ID into @T
SET ROWCOUNT 0

такой вариант? и чем он отличается от
SET ROWCOUNT 1
UPDATE TblData
SET @T=ID, Status=1
WHERE Status=0
SET ROWCOUNT 0
21 июл 09, 09:46    [7438437]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Только синтаксис нарушен - надо сначала UPDATE, потом SET, потом OUTPUT? а WHERE - только в конце.
UPDATE TblData SET Status=1 OUTPUT inserted.ID INTO @T WHERE Status=0
21 июл 09, 09:47    [7438447]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
orunbek
iap
orunbek
в данном случае только одна запись апдейтится? или все со Status=0?
Вы же видите, что все (согласно условию в WHERE)

вижу, поэтому и пишу, но сомневаюсь вдруг я что-то еще не знаю, как говорится век живи век учусь
значит:
SET ROWCOUNT 1
UPDATE TblData
SET Status=1
WHERE Status=0
output ID into @T
SET ROWCOUNT 0

такой вариант? и чем он отличается от
SET ROWCOUNT 1
UPDATE TblData
SET @T=ID, Status=1
WHERE Status=0
SET ROWCOUNT 0
А ROWCOUNT 1 зачем?
Если уж так хочется, то
UPDATE TOP(1)
можно
21 июл 09, 09:49    [7438457]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
iljy
Member

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

тем, что в моем варианте @T - это табличная переменная, таблица с одним полем, в которую записываются ВСЕ идентификаторы строк, которые надо обработать.


2jap,
ну да, с синтаксисом напутал, торопился:) но ведь важна идея, правда?;)
21 июл 09, 09:50    [7438465]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
2iljy
мне нужно "забронировать" только одну запись, не все в одну таблицу
так как скорость обработки медленная, и она зависит от внешних систем.
из-за этого и обработчиков много, кто свободен, сразу бронирует запись для обработки и после обработки сразу проверяет наличие записей на обработку

2iap
если серъезно, посоветуйте пожалуйста, вариант оптимизации бронирования записей для обработки
21 июл 09, 09:55    [7438491]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
iljy
Member

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

тогда сделайте так.
UPDATE TOP(1) TblData
SET @T=ID, Status=1
WHERE Status=0

никаких блокировок по идее при этом наложено на таблицу не будет. а на строку... по-моему могут быть проблемы, если включен SNAPSHOT ISOLATION.
21 июл 09, 09:58    [7438512]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
Crimean
Member

Откуда:
Сообщений: 13148
а я п смотрел в сторону "readpast" :) updlock там, конечно же, совсем не в тему
и лок таймаут "принято" в таких вещах обрабатывать отдельно
21 июл 09, 10:22    [7438634]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
iljy
orunbek,

тогда сделайте так.
UPDATE TOP(1) TblData
SET @T=ID, Status=1
WHERE Status=0

никаких блокировок по идее при этом наложено на таблицу не будет. а на строку... по-моему могут быть проблемы, если включен SNAPSHOT ISOLATION.

Никаких блокировок не будет, но теоритически возможно ли то что одну и ту же запись могут два обработчика взять? Или такое исключено?
Чтобы это исключить Я и налогал блокировку на обновление.
Ну и отсюда и поиск метода оптимизации.
В плане того, что операция
UPDATE TblData WITH (UPDLOCK) SET Status=1, @Reserved=ID WHERE Status=0
налогает блокировку на таблицу не зависимо от того есть ли данные со Status=0 или нет, верно?
и если так то добавление предварительной проверки, хоть как то уменьшает частоту проверки блокировок
и еще, насколько отличается
UPDATE TOP(1) TblData
от
SET ROWCOUNT 1
UPDATE ...
SET ROWCOUNT 0
21 июл 09, 10:44    [7438794]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
iljy
Member

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

нет, не возможно. на строку блокировка обновления по любому накладывается, а после снятия строка уже по условию не будет проходить. вот только если обработчик свалиться - с откатом могут проблемы оказаться.
21 июл 09, 11:07    [7438969]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2orunbek
добавляем новые данные, в которых необходимо хранить ID таблички, которую обрабатываем + spid процесса, который обрабатывает. в итоге у нас есть глобальная табличка, которая хранит те строки, которые обрабатываются. при этом делать проверку на предмет наличия SPID в табличке, чтобы не было висяков..

для спящего время бодрствования равносильно сну
21 июл 09, 11:16    [7439045]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
iljy
Member

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

кстати по поводу readpast вам дали хороший совет:) заблокированные другими транзакциями строки будут пропускаться при этом, и откат становится возможен.
21 июл 09, 11:17    [7439050]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
Crimean
Member

Откуда:
Сообщений: 13148
интересно, хоть кто-то попробовал свои версии в многопоточном тесте?
21 июл 09, 11:18    [7439056]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
iljy
Member

Откуда:
Сообщений: 8711
Crimean,
я попробовал, с readpast. работает, если не менять ключевые поля.
21 июл 09, 11:37    [7439185]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
mike909
Member

Откуда:
Сообщений: 662
orunbek
Да, сервер 2005, забыл написать про это
насчет предложенного варианта


Ну раз SQL2k5, то почему бы не посмотреть в сторону Service Broker_а ?

Лучшего способа для решения Ваших задач и не придумаешь:
1) "Технология рабочих потоков" - только в путь ... Т.е. не 20 процессов дерущихся за задания, а 20 reader_ов SB выполняющих их.
2) Синхронизация работы со справочными таблицами без каких либо (даже теоритических) блокировок. Т.е. только Service SB с одним Reader_ом правит таблицы состояний и раздает задания другим "рабочим" service_ам SB...
3) Распределенное выполнение частей одного бизнесс процесса на разных SQL_ях....
4) .... ну еще много чего вкусного и разного ...
21 июл 09, 13:21    [7439977]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
Crimean
Member

Откуда:
Сообщений: 13148
iljy
Crimean,
я попробовал, с readpast. работает, если не менять ключевые поля.


вопрос задан от того, что задачка на конкуренцию и блокировки, а про это говорить нет смыслу пока неясны индексы. а про индексы никто ничо не сказал ну и т.д.
21 июл 09, 13:33    [7440050]     Ответить | Цитировать Сообщить модератору
 Re: Получение данных для обработки. Оптимальный вариант на T-SQL  [new]
iljy
Member

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

справедливо:) но надеюсь автору не пришло в голову сделать индекс по полю, имеющему 2 значения
хотя конечно такие задачи надо в отдельной таблице решать, в которую добавлять ид как в очередь. а то поиск записи для обработки в таблице выливается в ее сканирование.
21 июл 09, 13:53    [7440213]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить