How to read/write to a queue table that is consumed in "n" steps without deadlocks
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.