I have a list of commands I need to execute, all of which are contained within a table I've named `myQueue`. This table is a little unique in that some commands should be *grouped together* such that their execution is performed sequentially, rather than concurrently, as executing them concurrently causes unwanted data artifacts and errors. Because of this, the queue cannot be classified in a typical **FIFO**/**LIFO** fashion as the dequeue order is determined at run-time.
To summarize:
1) A Table named `myQueue` will act as a command queue (where dequeue order is determined at run-time)
2) Commands are added to the table in a random way
3) Commands may fall into *groups*, and if so, must be executed by a single worker thread in an ordered, sequential manner
4) Any number of worker threads can be running when commands are being dequeued
5) Dequeuing is performed via an `UPDATE` rather than a `DELETE` as this table is used for historical performance reporting for said commands
My current approach is to iterate over this table using explicit mutex logic via [`sp_getapplock`](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql?view=sql-server-ver15)/[`sp_releaseapplock`](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-releaseapplock-transact-sql?view=sql-server-ver15) calls. While this works as expected, I was hoping to optimize the approach (in order to reduce `xp_userlock` waits), and after reading through Remus Rusanu's [excellent blog post on the topic](https://rusanu.com/2010/03/26/using-tables-as-queues/), I decided to go down the path of utilizing some table hints in hopes I could further optimize my process.
I'll include the test code below, but to summarize my results, using table hints and eliminating calls to `sp_getapplock`/`sp_releaseapplock` results in up to three undesirable behaviors as follows:
1) Deadlocking
2) Multiple threads execute commands that are contained within a single *group*
3) Thread Assignments are missing within a *group* of commands
**What I'm hoping for is that someone will point out how I'm not structuring my dequeing statements correctly so I can still move forward with using table hints exclusively.** If that doesn't work, so be it, but I wanted to see if it could be done just the same.
What I can say is if the code accommodates the deadlocking (e.g. retrying the offending operation as is currently included), the methods not using `sp_getapplock`/`sp_releaseapplock` (which don't exhibit undesirable behaviors 2 & 3) perform at least twice as fast, if not faster.
The tests can be setup with the following code.
The `myQueue` table creation and population with *commands* that are similar enough to my workload:
```
CREATE TABLE myQueue
(
ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
Main INT,
Sub INT,
Detail INT,
Command VARCHAR(MAX),
Thread INT,
StartDT DATETIME2,
EndDT DATETIME2
)
GO
INSERT INTO myQueue WITH (TABLOCKX) (Main, Sub, Detail, Command)
SELECT ABS(CHECKSUM(NEWID()) % 200),
ABS(CHECKSUM(NEWID()) % 1280),
ABS(CHECKSUM(NEWID())),
'WAITFOR DELAY ''00:00:00.01'''
FROM sys.types t1 CROSS JOIN
sys.types t2 CROSS JOIN
sys.types t3 CROSS JOIN
(VALUES (1), (2), (3), (4), (5)) t4(x)
GO
CREATE NONCLUSTERED INDEX [IX_myQueue_Update]
ON [dbo].[myQueue] ([Main],[Sub])
INCLUDE (Thread, EndDT)
GO
```
The Worker Threads all follow the same logic. I recommend that if you run this locally, you just copy this code into separate query windows and execute accordingly, making sure all Worker Threads adhere to the same locking method:
```
SET NOCOUNT ON
DECLARE @updOUT TABLE
(
Main INT,
Sub INT
)
-- Update @CurrentThread as a unique ID, I tend to
SET NOCOUNT ON
DECLARE @updOUT TABLE
(
Main INT,
Sub INT
)
-- Update @CurrentThread as a unique ID, I tend to
-- number them 1 - N, with N being the number of threads I'm running
DECLARE @CurrentThread INT = @@SPID,
@main INT, @sub INT,
@id INT, @command VARCHAR(MAX),
@ErrorMessage NVARCHAR(4000)
WHILE EXISTS(SELECT TOP 1 ID FROM myQueue WHERE EndDT IS NULL)
BEGIN
BEGIN TRY
--/*
-- Method 1: Top 1 WITH TIES within CTE, direct update against CTE, Contained with sp_getapplock/sp_releaseapplock
-- works
-- high volume of xp_userlock waits
BEGIN TRY
BEGIN TRAN
EXEC sp_getapplock @Resource = 'myQueue', @LockMode = 'Update'
;WITH dequeue AS
(
SELECT TOP 1 WITH TIES
Main, Sub, Thread
FROM myQueue
WHERE EndDT IS NULL
AND (Thread IS NULL OR Thread = @CurrentThread)
ORDER BY Main, Sub
)
UPDATE dequeue
SET Thread = @CurrentThread
OUTPUT DELETED.Main,
DELETED.Sub
INTO @updOUT
EXEC sp_releaseapplock @Resource = 'myQueue'
COMMIT
END TRY
BEGIN CATCH
EXEC sp_releaseapplock @Resource = 'myQueue'
ROLLBACK TRAN
END CATCH
--*/
/*
-- Method 2: Top 1 WITH TIES within CTE, direct update against CTE
-- does not work
-- some groupings contain multiple worker threads
-- missing thread assignments (e.g. NULL value in Thread Column)
-- deadlocking experienced
;WITH dequeue AS
(
SELECT TOP 1 WITH TIES
Main, Sub, Thread
FROM myQueue WITH (ROWLOCK, UPDLOCK, READPAST)
WHERE EndDT IS NULL
AND (Thread IS NULL OR Thread = @CurrentThread)
ORDER BY Main, Sub
)
UPDATE dequeue
SET Thread = @CurrentThread
OUTPUT DELETED.Main,
DELETED.Sub
INTO @updOUT
*/
/*
-- Method 3: Top 1 WITH TIES within CTE, join to myQueue table
-- does not work
-- some groupings contain multiple worker threads
-- missing thread assignments (e.g. NULL value in Thread Column)
-- deadlocking experienced
;WITH dequeue AS
(
SELECT TOP 1 WITH TIES
Main, Sub, Thread
FROM myQueue WITH (ROWLOCK, UPDLOCK, READPAST)
WHERE EndDT IS NULL
AND (Thread IS NULL OR Thread = @CurrentThread)
ORDER BY Main, Sub
)
UPDATE myQ
SET Thread = @CurrentThread
OUTPUT DELETED.Main,
DELETED.Sub
INTO @updOUT
FROM myQueue myQ WITH (ROWLOCK, UPDLOCK, READPAST)
INNER JOIN dequeue
ON myQ.Main = dequeue.Main
AND myQ.Sub = dequeue.Sub
*/
/*
-- Method 4: Top 1 within CTE, join to myQueue table
-- does not work
-- some groupings contain multiple worker threads
;WITH dequeue AS
(
SELECT TOP 1
Main, Sub, Thread
FROM myQueue WITH (ROWLOCK, UPDLOCK, READPAST)
WHERE EndDT IS NULL
AND (Thread IS NULL OR Thread = @CurrentThread)
ORDER BY Main, Sub
)
UPDATE myQ
SET Thread = @CurrentThread
OUTPUT DELETED.Main,
DELETED.Sub
INTO @updOUT
FROM myQueue myQ WITH (ROWLOCK, UPDLOCK, READPAST)
INNER JOIN dequeue
ON myQ.Main = dequeue.Main
AND myQ.Sub = dequeue.Sub
*/
/*
-- Method 5: Top 1 WITH TIES within CTE, join to myQueue table, PAGLOCK hint instead of ROWLOCK
-- works*
-- deadlocking experienced
;WITH dequeue AS
(
SELECT TOP 1 WITH TIES
Main, Sub, Thread
FROM myQueue WITH (PAGLOCK, UPDLOCK, READPAST)
WHERE EndDT IS NULL
AND (Thread IS NULL OR Thread = @CurrentThread)
ORDER BY Main, Sub
)
UPDATE myQ
SET Thread = @CurrentThread
OUTPUT DELETED.Main,
DELETED.Sub
INTO @updOUT
FROM myQueue myQ WITH (PAGLOCK, UPDLOCK, READPAST)
INNER JOIN dequeue
ON myQ.Main = dequeue.Main
AND myQ.Sub = dequeue.Sub
*/
/*
-- Method 6: Top 1 WITH TIES within CTE, direct update against CTE, PAGLOCK hint instead of ROWLOCK
-- works*
-- deadlocking experienced
;WITH dequeue AS
(
SELECT TOP 1 WITH TIES
Main, Sub, Thread
FROM myQueue WITH (PAGLOCK, UPDLOCK, READPAST)
WHERE EndDT IS NULL
AND (Thread IS NULL OR Thread = @CurrentThread)
ORDER BY Main, Sub
)
UPDATE dequeue
SET Thread = @CurrentThread
OUTPUT DELETED.Main,
DELETED.Sub
INTO @updOUT
*/
/*
-- Method 7: Top 1 within CTE, join to myQueue table, PAGLOCK hint instead of ROWLOCK
-- works*
-- deadlocking experienced
;WITH dequeue AS
(
SELECT TOP 1
Main, Sub, Thread
FROM myQueue WITH (PAGLOCK, UPDLOCK, READPAST)
WHERE EndDT IS NULL
AND (Thread IS NULL OR Thread = @CurrentThread)
ORDER BY Main, Sub
)
UPDATE myQ
SET Thread = @CurrentThread
OUTPUT DELETED.Main,
DELETED.Sub
INTO @updOUT
FROM myQueue myQ WITH (PAGLOCK, UPDLOCK, READPAST)
INNER JOIN dequeue
ON myQ.Main = dequeue.Main
AND myQ.Sub = dequeue.Sub
*/
SELECT TOP 1
@main = Main
, @sub = Sub
FROM @updOUT
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ', Level ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))
+ ', State ' + CAST(ERROR_STATE() AS VARCHAR(10)) + ', Line ' + CAST(ERROR_LINE() AS VARCHAR(10))
+ CHAR(13) + CHAR(10) + ERROR_MESSAGE()
RAISERROR(@ErrorMessage, 1, 1) WITH NOWAIT
-- Set to Uselss values so cursor doesn't fire
SELECT @main = -1, @sub = -1
END CATCH
DELETE FROM @updOUT
DECLARE WorkQueueCur INSENSITIVE CURSOR
FOR
SELECT ID, Command
FROM myQueue
WHERE Main = @main
AND Sub = @sub
ORDER BY Detail
OPEN WorkQueueCur
FETCH NEXT FROM WorkQueueCur
INTO @id, @command
WHILE @@FETCH_STATUS = 0
BEGIN
RETRY1:
BEGIN TRY
UPDATE myQueue
SET StartDT = GETDATE()
WHERE ID = @id
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'Retry1: Msg ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ', Level ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))
+ ', State ' + CAST(ERROR_STATE() AS VARCHAR(10)) + ', Line ' + CAST(ERROR_LINE() AS VARCHAR(10))
+ CHAR(13) + CHAR(10) + ERROR_MESSAGE()
RAISERROR(@ErrorMessage, 1, 1) WITH NOWAIT
GOTO RETRY1
END CATCH
EXEC(@command)
RETRY2:
BEGIN TRY
UPDATE myQueue
Set EndDT = GETDATE()
WHERE ID = @id
END TRY
BEGIN CATCH
SELECT @ErrorMessage = 'Retry2: Msg ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ', Level ' + CAST(ERROR_SEVERITY() AS VARCHAR(10))
+ ', State ' + CAST(ERROR_STATE() AS VARCHAR(10)) + ', Line ' + CAST(ERROR_LINE() AS VARCHAR(10))
+ CHAR(13) + CHAR(10) + ERROR_MESSAGE()
RAISERROR(@ErrorMessage, 1, 1) WITH NOWAIT
GOTO RETRY2
END CATCH
FETCH NEXT FROM WorkQueueCur
INTO @id, @command
END
CLOSE WorkQueueCur
DEALLOCATE WorkQueueCur
END
```
Confirmation of undesirable behaviors 2 and 3 (or lack thereof), above can be determined by running the following statement:
```
;WITH invalidMThread AS (
SELECT *, DENSE_RANK() OVER (PARTITION BY Main, Sub ORDER BY Thread) AS ThreadCount
FROM dbo.myQueue WITH (NOLOCK)
WHERE StartDT IS NOT NULL
), invalidNThread AS (
SELECT *
FROM dbo.myQueue WITH (NOLOCK)
WHERE Thread IS NULL
AND StartDT IS NOT NULL
)
SELECT t1.*, 'Multiple Threads' AS Issue
FROM dbo.myQueue t1 WITH (NOLOCK)
INNER JOIN invalidMThread i1
ON i1.Main = t1.Main
AND i1.Sub = t1.Sub
WHERE i1.ThreadCount > 1
UNION
SELECT t1.*, 'Unassigned Thread(s)' AS Issue
FROM dbo.myQueue t1 WITH (NOLOCK)
INNER JOIN invalidNThread i2
ON i2.Main = t1.Main
AND i2.Sub = t1.Sub
ORDER BY t1.Main, t1.Sub
```
As always, I fully anticipate I missed some critical point Remus made in his article, so any help in pointing that out would be very much appreciated.