Использование SQL Server в качестве очереди БД с несколькими клиентами

Учитывая таблицу, которая действует как очередь, как лучше всего настроить таблицу/запросы, чтобы несколько клиентов обрабатывали из очереди одновременно?

Например, в таблице ниже указывается команда, которую должен обрабатывать рабочий. Когда рабочий закончится, он установит обработанное значение в значение true.

| ID | COMMAND | PROCESSED |
|  1 | ...     | true      |
|  2 | ...     | false     |
|  3 | ...     | false     |

Клиенты могут получить одну команду для работы следующим образом:

select top 1 COMMAND 
from EXAMPLE_TABLE 
with (UPDLOCK, ROWLOCK) 
where PROCESSED=false;

Однако, если есть несколько работников, каждый пытается получить строку с ID = 2. Только первый получит пессимистический замок, остальные будут ждать. Затем один из них получит строку 3 и т.д.

Какой запрос/конфигурация позволит каждому рабочему клиенту получать разные строки и работать с ними одновременно?

EDIT:

В нескольких ответах предлагаются варианты использования самой таблицы для записи состояния в процессе. Я думал, что это невозможно в рамках одной транзакции. (то есть, какая точка обновления состояния, если другой рабочий не увидит его до тех пор, пока не будет зафиксирован txn?) Возможно, это предложение:

# start transaction
update to 'processing'
# end transaction
# start transaction
process the command
update to 'processed'
# end transaction

Так ли люди обычно подходят к этой проблеме? Мне кажется, что проблема будет лучше обрабатываться БД, если это возможно.

Ответ 1

Я рекомендую вам перейти Использование таблиц в качестве очередей. Правильно реализованные очереди могут обрабатывать тысячи одновременных пользователей и обслуживать до 1/2 миллиона операций очереди/деактивации в минуту. До SQL Server 2005 решение было громоздким и включало смешение SELECT и UPDATE в одной транзакции и давало только правильное сочетание подсказок блокировки, как в статье, связанной gbn. К счастью, начиная с SQL Server 2005 с появлением предложения OUTPUT доступно гораздо более элегантное решение, и теперь MSDN рекомендует использовать предложение OUTPUT:

Вы можете использовать OUTPUT в приложениях которые используют таблицы в качестве очередей или удерживать промежуточные результирующие множества. Это приложение постоянно добавляет или удаление строк из таблицы

В основном есть 3 части головоломки, которые вам нужно получить, чтобы это работало с высокой степенью параллелизма:

1) Вам необходимо удалить из строя атомарно. Вы должны найти строку, пропустить любые заблокированные строки и пометить ее как "dequeued" в одной атомной операции, и именно здесь вступает в действие предложение OUTPUT:

with CTE as (
  SELECT TOP(1) COMMAND, PROCESSED
  FROM TABLE WITH (READPAST)
  WHERE PROCESSED = 0)
UPDATE CTE
  SET PROCESSED = 1
  OUTPUT INSERTED.*;

2) Вы должны структурировать свою таблицу с помощью самого левого кластерного индексного ключа в столбце PROCESSED. Если для ID был использован первичный ключ, переместите его как второй столбец в кластеризованном ключе. Дискуссия о том, следует ли хранить некластеризованный ключ в столбце ID, открыта, но я сильно одобряю отсутствие каких-либо вторичных некластеризованных индексов над очередями:

CREATE CLUSTERED INDEX cdxTable on TABLE(PROCESSED, ID);

3) Вы не должны запрашивать эту таблицу любыми другими способами, но Dequeue. Попытка выполнить операции Peek или попытку использовать таблицу как в очереди, так и в магазине, скорее всего, приведет к взаимоблокировкам и резко снизит пропускную способность.

Комбинация атомарного dequeue, подсказки READPAST для поиска элементов для деактивации и самой левой клавиши в кластерном индексе на основе бит обработки обеспечивают очень высокую пропускную способность при высокой одновременной нагрузке.

Ответ 2

Мой ответ здесь показывает вам, как использовать таблицы в качестве очередей... Состояние очереди очередей SQL Server

В основном вам нужны подсказки "ROWLOCK, READPAST, UPDLOCK"

Ответ 3

Вместо использования логического значения для Processed вы можете использовать int для определения состояния команды:

1 = not processed
2 = in progress
3 = complete

Затем каждый рабочий получит следующую строку с обработанным = 1, обновление обработано до 2, затем начнет работу. Когда работа в Complete Processed обновляется до 3. Этот подход также позволит расширить другие обработанные результаты, а не просто определить, что рабочий завершен, вы можете добавить новые статусы для "Завершено успешно" и "Завершено с ошибками"

Ответ 4

Вероятно, лучшим вариантом будет использование обработанного столбца trisSate вместе с столбцом version/timestamp. Три значения в обработанном столбце указывают, указывает ли строка на обработку, обработку или необработанную.

Например

    CREATE TABLE Queue ID INT NOT NULL PRIMARY KEY,
    Command NVARCHAR(100), 
    Processed INT NOT NULL CHECK (Processed in (0,1,2) ), 
    Version timestamp)

Вы берете верхнюю 1 необработанную строку, задаете статус для обработки и устанавливаете статус для обработки, когда все будет сделано. Базовый статус обновления в столбцах "Версия" и "Первичный ключ". Если обновление не удалось, кто-то уже там.

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

Ответ 5

Я бы держался подальше от возиться с замками в таблице. Просто создайте два дополнительных столбца, таких как IsProcessing (бит/булево) и ProcessingStarted (datetime). Когда работник падает или не обновляет свою строку после таймаута, вы можете попросить другого рабочего обработать данные.

Ответ 6

Один из способов - пометить строку одним оператором обновления. Если вы прочитаете статус в предложении where и измените его в предложении set, между ними не будет другого процесса, потому что строка будет заблокирована. Например:

declare @pickup_id int
set @pickup_id = 1

set rowcount 1

update  YourTable
set     status = 'picked up'
,       @pickup_id = id
where   status = 'new'

set rowcount 0

return @pickup_id

Используется rowcount для обновления не более одной строки. Если ни одна строка не найдена, @pickup_id будет -1.

Ответ 7

Если вы хотите сериализовать операции для нескольких клиентов, вы можете просто использовать блокировки приложений.

BEGIN TRANSACTION

EXEC  sp_getapplock @resource = 'app_token', @lockMode = 'Exclusive'

-- perform operation

EXEC  sp_releaseapplock @resource = 'app_token'

COMMIT TRANSACTION