2

I have a table of tasks. Multiple users simultaneously try to get a task. This trimmed query is the heart of my logic:

; WITH TASKS_CTE AS (
    SELECT TOP(1)   T.TASK_ID AS TASK_ID,
                    T.ASSIGNED_USER_CODE AS ASSIGNED_USER_CODE,
                    T.STATUS AS STATUS
    FROM TASK T WITH (ROWLOCK,READPAST)
    JOIN TASK_SCORE TS WITH (NOLOCK) ON TS.TASK_ID = T.TASK_ID
    WHERE
        T.STATUS = 0
    ORDER BY TS.TOTAL_SCORE DESC
)

UPDATE TASKS_CTE
SET STATUS = 1,
ASSIGNED_USER_CODE = @USER_CODE,
OUTPUT INSERTED.TASK_ID, INSERTED.ASSIGNED_USER_CODE, INSERTED.STATUS INTO @NEXT_TASK_TABLE;

I omitted the part where @@ROWCOUNT is checked to see if a task was successfully dequeued.

Well it turns out that (ROWLOCK, READPAST) is not preventing multiple users getting the same task. I came accross these two questions on SO, in SQL Server Process Queue Race Condition it is advised that (ROWLOCK, READPAST, UPDLOCK) be specified. On the other hand, in Using a database table as a queue (UPDLOCK, READPAST) is advised.

Hence my question, do i need to specify ROWLOCK in addition to (UPDLOCK, READPAST) in order to implement a multi-client queue? What exactly differs between (UPDLOCK, READPAST) and (UPDLOCK, READPAST, ROWLOCK)?

Community
  • 1
  • 1
Selçuk Cihan
  • 1,979
  • 2
  • 17
  • 30

1 Answers1

1

The ROWLOCK is to ensure that the lock escalation is limited to only a single row. This is what you want to help ensure your queue is processed in the correct order. It would be a good practice in this example.

Arin Taylor
  • 380
  • 1
  • 7
  • Yes i am aware UPDLOCK is missing, that code was my initial setup and turned out to be not working the way i assumed it would. – Selçuk Cihan Jan 15 '16 at 09:07