Using sp_getapplock to implement a queue. Is it correct? Is there a better way?

I've been reading a series of posts by Paul White about SQL Server Isolation Levels and came across a phrase:


To emphasise the point, pseudo-constraints written in T-SQL have to
perform correctly no matter what concurrent modifications might be
occurring. An application developer might protect a sensitive
operation like that with a lock statement. The closest thing T-SQL
programmers have to that facility for at-risk stored procedure and
trigger code is the comparatively rarely-used sp_getapplock system
stored procedure. That is not to say it is the only, or even preferred
option, just that it exists and can be the right choice in some
circumstances.

I am using sp_getapplock and this made me wonder if I'm using it correctly, or there is a better way to get the desired effect.

I have a C++ application that processes so called "Building servers" in a loop 24/7. There is a table with the list of these Building Servers (about 200 rows). New rows can be added at any time, but it doesn't happen often. Rows are never deleted, but they can be marked as inactive. Processing a server may take from few seconds to dozens of minutes, each server is different, some are "small", some are "large". Once a server is processed, application has to wait at least 20 minutes before processing it again (the servers should not be polled too often). Application starts 10 threads that perform the processing in parallel, but I must guarantee that no two threads attempt to process the same server at the same time. Two different servers can and should be processed simultaneously, but each server can be processed not more often than once in 20 minutes.

Here is the definition of a table:

`CREATE TABLE [dbo].PortalBuildingServers NOT NULL,
[LastCheckStarted] [datetime] NOT NULL,
[LastCheckCompleted] [datetime] NOT NULL,
[IsActiveAndNotDisabled] [bit] NOT NULL,
[MaxBSMonitoringEventLogItemID] [bigint] NOT NULL,
CONSTRAINT

Is this approach correct?

Yes. It meets all the objectives stated in the question.

A comment in the procedures to explain the strategy and note the related procedure name might be helpful for future maintenance by others.


Is there a better way?

Not to my mind, no.

Taking a single lock is an extremely fast operation, and results in very clear logic. It is not clear to me that taking the lock in the second procedure is redundant, but even if it is, what do you really gain by omitting it? The simplicity and safety of your implementation appeal to me.

The alternatives are much more complex, and may leave you wondering if you've truly covered all cases, or if there might be a change in internal engine details in the future that would break (perhaps subtle and unstated) assumptions.

If you ever need a more traditional queueing implementation, the following reference is very useful:

Using Tables as Queues by Remus Rusanu

StackExchange Database Administrators Q#112818, answer score: 5