no table hints (READPAST etc.)
no OUTPUT clause
sp_getapplock is not available
Our Synapse instance is configured READ UNCOMMITTED.
Each evening a batch job will run. Fewer than 100 items will be placed in the queue, in the desired order. These will then be processed by between 4 and 10 concurrent consumers until the queue is drained. The cycle repeats the following day.
The number of items and consumers can change from day to day. The elapsed time for items will be quite skewed, from under a minute to over an hour. The design of the queue and the consumer code is completely open. The process is orchestrated by Azure Data Factory (ADF); the solution can rely on ADF if needed. We would rather avoid additional Azure services to limit costs. When the run fails we do not want to re-process completed items but those in-flight can be abandoned and started from scratch i.e. checkpoint/ restart at the item level is desired.