SQL Server Work Queues: Claim, Commit, and Recover

A SELECT followed by a separate UPDATE is not a safe queue claim. Two workers can select the same ready row before either changes its state. The queue needs one atomic state transition, followed by a short commit. The potentially slow business work should happen after that claim transaction finishes.

Claim work with one statement

Create the demonstration table once in a disposable database. Its index supports finding ready jobs without repeatedly scanning completed history. The payload is deliberately tiny; production queues often store a reference to larger content instead of copying a document into every index.

CREATE TABLE dbo.QueueDemo
( JobId bigint IDENTITY PRIMARY KEY, State char(1) NOT NULL,
  ClaimToken uniqueidentifier NULL, LeaseUntil datetime2(3) NULL,
  Payload nvarchar(100) NOT NULL );
CREATE INDEX IX_QueueDemo_Ready ON dbo.QueueDemo(State, JobId);
INSERT dbo.QueueDemo(State, Payload) VALUES ('R', N'first'), ('R', N'second');

Run the claim statement under READ COMMITTED with no surrounding transaction. The guard makes this example's ownership clear. UPDLOCK coordinates competing claimers, READPAST allows eligible locked rows to be skipped, and READCOMMITTEDLOCK requests locking semantics when the database uses read-committed row versioning. This combination is specific to that isolation contract; do not transplant it into an arbitrary SNAPSHOT transaction.

IF @@TRANCOUNT <> 0 OR (@@OPTIONS & 2) = 2
    THROW 50000, 'Use autocommit for this example.', 1;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DECLARE @claimed table
(JobId bigint, ClaimToken uniqueidentifier, Payload nvarchar(100));
DECLARE @token uniqueidentifier = NEWID();
;WITH candidate AS
( SELECT TOP (1) * FROM dbo.QueueDemo
  WITH (UPDLOCK, READPAST, READCOMMITTEDLOCK)
  WHERE State = 'R' ORDER BY JobId )
UPDATE candidate
SET State = 'W', ClaimToken = @token,
    LeaseUntil = DATEADD(minute, 5, SYSUTCDATETIME())
OUTPUT inserted.JobId, inserted.ClaimToken, inserted.Payload
INTO @claimed;
SELECT * FROM @claimed;

The CTE orders candidate selection by JobId. It does not promise strict global FIFO across workers: a locked earlier job can be skipped, and completion order depends on work duration. READPAST skips row locks, not every possible page or schema lock. Empty output means that this attempt claimed no available row; it does not prove that no unfinished job exists.

OUTPUT INTO captures the identity and token changed by the same statement. The final SELECT runs after the autocommit UPDATE completes. Applications must still treat any execution error as failure rather than processing partially observed output. Test the pattern with simultaneous claimers and record every returned JobId and token.

Distinguish abandonment from completion

The lease is an expiry policy, not a mechanism that physically stops a worker. A stalled process can resume after its lease expires. Recovery therefore needs a fencing token: every ownership change must replace ClaimToken. Completion must match both JobId and the current token, so an obsolete worker cannot mark a newer attempt complete.

-- Parameters supplied from the successful claim:
-- @JobId bigint, @ClaimToken uniqueidentifier
UPDATE dbo.QueueDemo
SET State = 'D', LeaseUntil = NULL
WHERE JobId = @JobId AND ClaimToken = @ClaimToken AND State = 'W';
SELECT @@ROWCOUNT AS CompletedRows;

A completion affecting zero rows means ownership or state changed. Do not silently convert that into successful queue completion. Decide whether to extend leases through a token-checked heartbeat, how long work may run, and how a recovery process returns expired rows to readiness. A reclaimer must update rows conditionally and invalidate the old token; blind updates based on an earlier SELECT recreate the original race.

Design retries around the external effect

A lease cannot guarantee exactly-once execution of an email, payment, or remote API call. A worker can complete the external effect and crash before recording completion. When the lease expires, another worker will try again. Use a stable business operation ID with an idempotent downstream operation, or a durable reconciliation procedure when the destination cannot deduplicate.

Keep attempt history and a bounded failure policy. A permanently invalid payload should eventually move to a failed state with a useful diagnostic, rather than consuming the first queue position forever. Track oldest ready age, expired leases, repeated failures, and completion latency. Queue depth alone misses a single stuck high-priority job.

Before deployment, kill a worker after claim, delay one beyond expiry, and lose the completion response intentionally. Verify that work becomes recoverable, stale tokens cannot change current ownership, and retries cannot repeat a business effect. These tests define the queue's reliability more clearly than a throughput benchmark with workers that never fail.

Technical references: Microsoft Learn: Table hints · Microsoft Learn: OUTPUT.