How to read/write to a queue table that is consumed in "n" steps without deadlocks

Question

I have a table queue consumed by 3 groups of processes (3 steps).

• Step 1 process a batch of records at step 0 and update the step to 1
• Step 2 process a batch of records at step 1 and update the step to 2
• Step 3 process a batch of records at step 2 and delete them from the queue

I run several processes for each groups (steps) in //s

• Step 1: 2 sessions
• Step 2: 8 sessions
• Step 3: 4 sessions

The processing time per step is a configurable delay.

I read many article on the subject starting from
https://learn.microsoft.com/en-us/answers/questions/222532/sql-server-how-to-design-table-like-a-queue
and I read the articled linked to it.

I still have dead locks ?$?#*&)&!!

They mostly occur when the queue is near to be empty or few records remains for a step, but it can occur anytime.
When a dead lock occurs I rollback, wait a minute and try another time.
If 2 consecutive dead locks occur I terminate the process.

The table is clustered in the order that records are dequeued as recommended by
https://rusanu.com/2010/03/26/using-tables-as-queues/ and serveral other articles

CREATE TABLE jml_queue
(
   rec_id      INT IDENTITY(1,1) NOT NULL,
   step        INT NOT NULL,
   priority    INT NOT NULL,  -- ~fk_type_cod =1..4
   queued_dt   DATETIME DEFAULT getdate() NOT NULL
);
CREATE CLUSTERED INDEX idx_jml_queue_dequeue_order ON jml_queue (step, priority DESC, queued_dt, rec_id);
CREATE NONCLUSTERED INDEX idx_jml_queue_rec_id ON jml_queue (rec_id);

I get the next batch to process as follow

   INSERT INTO #step
   SELECT TOP(@batch_size) rec_id
     FROM jml_queue WITH (ROWLOCK, UPDLOCK, READPAST) --the 3 recommended hint are there
    WHERE step = @step - 1
    ORDER BY priority DESC, queued_dt;

What am i doing wrong?

Please find attached the script to setup and run my prototype of queue.
queue_benchmark - PostCommunity.txt

All actions (batch processed, sleep, time to read/write from/to the queue, deadlock, etc) are logged to the table: jml_queue_stats. You have 2 queries at the end to monitor the progress and display last message (dead lock or sleep; a null value mean processing a batch).

I lunch all the sessions (total of 14 in this example) in a DOS prompt as follow:

FOR /L %G IN (1,1,2) DO START "session %G - step 1" sqlcmd -S lodc-devcpsql01 -U lessj -P Lithi7miona -d eagle -Q "exec usp_process_jml_queue @step = 1, @batch_size = 200, @processing_dt = 5 , @sleep_dt = 1"
FOR /L %G IN (1,1,8) DO START "session %G - step 2" sqlcmd -S lodc-devcpsql01 -U lessj -P Lithi7miona -d eagle -Q "exec usp_process_jml_queue @step = 2, @batch_size = 200, @processing_dt = 20, @sleep_dt = 1"
FOR /L %G IN (1,1,4) DO START "session %G - step 3" sqlcmd -S lodc-devcpsql01 -U lessj -P Lithi7miona -d eagle -Q "exec usp_process_jml_queue @step = 3, @batch_size = 200, @processing_dt = 10, @sleep_dt = 1"

You can change the batch_size, processing time and sleep time (when there is no record in the queue) per step.