I have two tables. One table serves as the queue from which multiple users simultaneously dequeue items one at a time, by flagging the item's row. The other table is joined and used for ordering. It is required that no two users will get the same item from the queue and the queue will not block users, so if
user a is busy getting the top item,
user b should be able to get the next available item.
The problem is, and i can reproduce, simultaneous dequeue will result in exactly one user to succeed; the others are left empty handed. This happens even when there are multiple candidate items in the queue, so the queue is not empty.
If i remove the
order by clause, everything works fine, users can simultaneously dequeue, without blocking each other.
How can i solve the problem, i need the ordering and i need the queue to be non-blocking.
Following are the two tables:
CREATE TABLE DBO.QUEUE ( QUEUEID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, SOMEACTION VARCHAR(100) ); GO DECLARE @counter INT SELECT @counter = 1 WHILE (@counter <= 10) BEGIN INSERT INTO DBO.QUEUE (SOMEACTION) SELECT 'some action ' + CAST(@counter AS VARCHAR); SELECT @counter = @counter + 1 END CREATE TABLE DBO.QUEUE_ORDERING ( QUEUEID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, ORDERING_COLUMN INT ); GO DECLARE @counter INT SELECT @counter = 1 WHILE (@counter <= 10) BEGIN INSERT INTO DBO.QUEUE_ORDERING (ORDERING_COLUMN) SELECT @counter; SELECT @counter = @counter + 1 END
And the testing script is as follows , running them from multiple sessions will reproduce my problem:
BEGIN TRAN TRAN1 DECLARE @QUEUEID INT; ;WITH QUEUE_CTE AS ( SELECT TOP(1) Q.QUEUEID, Q.SOMEACTION FROM DBO.QUEUE Q WITH (ROWLOCK,READPAST, UPDLOCK) JOIN DBO.QUEUE_ORDERING QR WITH (NOLOCK) ON QR.QUEUEID = Q.QUEUEID ORDER BY QR.ORDERING_COLUMN DESC ) SELECT @QUEUEID = QUEUEID FROM QUEUE_CTE PRINT 'processing item # ' + CAST(@QUEUEID AS VARCHAR) WAITFOR DELAY '00:00:05' COMMIT
I've traced down the issue to indices. If i
order by the column that is part of the clustered index, then only the relevant row is locked. Otherwise, all rows are locked. I still don't have any idea how to resolve the issue. I need the ordering to be on a column that can not be part of the clustered index. The first image shows the problematic one, you can see that all the rows are locked. In the second one, below, only one row gets locked;
order by is on the clustered index column.