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

Question

[Владимир]

Упрощённо имеются две таблицы:

1. CREATE TABLE dbo.Events(
EventNumber INT AUTOINCREMENT
)
2. CREATE TABLE dbo.ProcessedEvents (
LastEventNumber INT NOT NULL
)

В таблицу Events параллельно вставляют данные разные producer'ы.
Параллельно работает consumer' который делает след. запрос и после обработки сохраняет LastEventNumber:

SELECT TOP 1 
    e.EventNumber as LastEventNumber 
FROM 
    dbo.Events e JOIN dbo.ProcessedEvents pe
         ON e.EventNumber > LastEventNumber  
ORDER BY 
    e.EventNumber ASC

Как избежать проблемы в этом сценарии:

A> BEGIN;
B> BEGIN;
A> INSERT INTO dbo.Events() ... -- EventNumber = 1
B> INSERT INTO dbo.Events()... -- EventNumber = 2
B> COMMIT;

Если в этот момент времени произойдёт SELECT (READ COMMITED), то LastEventNumber станет 2

A> COMMIT;

В результате EventNumber = 1 будет пропущен consumer'ом.
Как избежать этой ситуации без lock'ов и сериализации транзакций, при этом гарантируя очерёдность событий?

Comments

[Akina]

Какая-то весьма противоречивая задачка-то.

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

Думаю, будет разумнее, если consumer будет не в отдельную таблицу писать, что он там последнее обработал, а ставить пометку в дополнительное поле таблицы эвентов. Тогда он легко возьмёт на дальнейшую обработку доступную (зафиксированную) запись, без пометки и с минимальным номером.

Answer 1

[rPman]

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

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

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

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

Answer 2

[mayton2019]

Параллелизм и последовательность - это две противоречивые постановки. Тебе стоит
пересмотреть подход к разработке. Подумай насколько важен для тебя LastEventNumber
как объект наблюдения человеком. Если он меняется чаще чем 10 раз в секунду то
вряд-ли он имеет смысл для публикации.

В паралелльных системах для массовой загрузки например объекты продюсеры захватывают
диапазоны номеров. Диапазо берется из объекта SEQUENCE. Умножатеся допутим на 10000.
И получается что перый продюсер захватил номера с 1 до 10000. Второй - захватит с 10001
до 20000 и так далее.

Да у тебя не будет строгой последовательности но вопросы коллизий ключкей и performance
будут решены сразу и не будет issues в будущем.

Answer 3

[Vitsliputsli]

А почему транзакция? Или они неявные? Инсерт должен быть атомарным или все таки итерирование сиквенса и следующая вставка не атомарны?
В любом случае, вы решаете не ту задачу. Если идет гонка, значит события одномоменты и нам должно быть без разницы в каком порядке они отработают. Но, раз это важно, значит эти события зависимы, если так, то манипуляции с вставкой не помогут, сегодня вы победите порядок коммитов, а завтра будет гонка при получении сиквенса и тут уже ничего не сделаешь. Зависимые события должны обрабатываться последовательно в одном потоке, т.е. в вашем случае их должен обрабатывать один продюсер, иначе никак, если даже сегодня заработает, то завтра малейшая флуктуация поменяет порядок. Как альтернатива вы можете построить сложную логику проверки зависимостей, но контролировать такой код будет гораздо сложнее.