I'm creating a queuing system using a SQL Server in the back-end for an app. The plan is to create a FIFO type of queuing system similar to how like a fast food rood restaurant is set up. Customers would enter into a queue and will be serviced by X amount of workers. When a customer is processed, the next customer will be serviced by the available worker. I think my issue is similar to the one in this post but I'm not sure if it is the exact same scenario.
I looked at Rusano's "Using tables as Queues" article and my understanding is that I would need to do something similar to his FIFO example shown below:
create table FifoQueue
(
Id bigint not null identity(1,1),
Payload varbinary(max)
);
go
create clustered index cdxFifoQueue on FifoQueue (Id);
go
create procedure usp_enqueueFifo
@payload varbinary(max)
as
set nocount on;
insert into FifoQueue (Payload) values (@Payload);
go
create procedure usp_dequeueFifo
as
set nocount on;
with cte as
(
select top(1) Payload
from FifoQueue with (rowlock, readpast)
order by Id
)
delete from cte
output deleted.Payload;
go
Example of the queue structure
Status table:
1 = Waiting
2 = Getting served
3 = Processed
EnterQueue Time | LeaveQueue Time | Worker | Status
----------------+-----------------+--------+--------
11:22 | 11:24 | 1 | 3
11:23 | | 2 | 2
11:26 | | 1 | 2
11:27 | | NULL | 1
The customers are added to the queue by a stored procedure. The next customer being served is based on their entry time.
Can I use a stored procedure based on the FIFO example to handle the processing/de-queuing of the customers?