Using a Table as a Queue without sp_getapplock/sp_releaseapplock
sql-server add tag
John Eisbrener 
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.
Top Answer
Joe Obbish 
For full disclosure, I'm lazy and leave writing safe locking code under concurrency to the professionals. So perhaps this is a Non-Answer but I'm writing it up anyway because you're a good guy and I want to help you.

I think that you currently have a query performance problem instead of a concurrency problem. The query that you use to find the next work to do runs longer as time passes. Rows with non-NULL values for EndDT slow down query performance. For clarity, I mean this query:

    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;	
			
The graph below measures the approximate amount of execution time in microseconds for each 100 batches of application lock acquires and releases:

![Capture2.PNG](/image?hash=726b4815932fbaae34063d6704a4bace53cd9f18db8fbc6177e247b148fea0d4)

That's why you see so much lock waiting time. The other sessions wait a while to get the lock because the query that updates the table gets slower over time. The query gets slower over time because the nonclustered scan reads over many completed rows. In the example below, we had to scan past 30k rows just to get 3 relevant rows:

![Capture3.PNG](/image?hash=0f6a15eac96ed99a212f0335dad9db9082af4c9de05e2c2944e39f46fb27d96f)

The most important change you can make is to make your dequeue code run in constant time regardless of the size of the myQueue table. One possible tweak is to change your index:

    CREATE NONCLUSTERED INDEX [Joe_index]
    ON [dbo].[myQueue] ([Main],[Sub])
    INCLUDE (Thread, EndDT)
    WHERE	EndDT IS NULL

On My Machine with the new index, it took 6 minutes for five concurrent threads to process 100k total rows. With the old index, it took 27 minutes. I was watching k-pop videos throughout my tests so that may have added error to the results. I'm not saying that the filtered index is what you should do or even a good idea. What I am saying is that you should make some change to your table, indexing, or querying to get constant time results.

I suggest trying to figure out how many transactions per second you need to hit with this code. For example, if you know that you need to be able to dequeue 1000 groups per second then you know that your dequeue code needs to run in under 1 ms. If it can't run in 1 ms then you may need to stop using `sp_getapplock` and switch to a more complex approach. If it can run in under 1 ms then you might be able to get by with `sp_getapplock`.

Good luck!
comments
transcript

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.