UPDLOCK and READPAST for Queue Tables

Several workers can reach for the same pending job at once. UPDLOCK and READPAST help a queue table hand each worker a different row, provided the claim and state change happen together.

Define a Real Queue State

A queue row needs a stable key, status, creation order, attempt count, and enough metadata to recover after a worker stops. Pending, claimed, completed, and failed states should have clear transitions. Decide when a claim expires and who retries it. Lock hints solve the moment of claiming. They do not solve the whole delivery contract.

I ask whether processing is allowed to happen more than once. Most practical queues need idempotent workers because a worker can finish external work and fail before recording completion. A row lock cannot make an email send or file write transactional with SQL Server. Design the action so a retry is safe. That is less glamorous than a clever hint and more important.

Understand UPDLOCK and READPAST

UPDLOCK takes update locks while selecting candidate rows and holds them until the transaction ends. It reduces the race where two workers read the same pending row and then both try to update it. READPAST skips locked rows rather than waiting behind them. Together, UPDLOCK and READPAST let workers choose available work under the intended isolation behavior.

I explain the skip explicitly to application teams. A skipped row is not lost. Another worker owns it for now. The queue must later revisit pending or expired claims. If one transaction holds a row indefinitely, READPAST can keep passing it. Monitoring needs to find those stranded jobs.

Create a Small Test Queue

Use a test database to create a simple queue table. The identity key orders claims, and status records progress. A production design needs payload handling, retry policy, and cleanup. Keep the claim query supported by an index on status and order columns. Otherwise every worker can scan far more rows than needed.

The sample table is deliberately small and readable. I test it with separate sessions before using a similar pattern in an application. A single-session demonstration cannot reveal the race the hints address.

CREATE TABLE dbo.WorkQueue
(
    JobId bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Status char(1) NOT NULL,
    CreatedAt datetime2(0) NOT NULL
        CONSTRAINT DF_WorkQueue_CreatedAt DEFAULT SYSDATETIME(),
    ClaimedAt datetime2(0) NULL
);
CREATE INDEX IX_WorkQueue_Status_CreatedAt
ON dbo.WorkQueue(Status, CreatedAt, JobId);

Claim and Update Atomically with UPDLOCK and READPAST

The CTE selects one pending row with UPDLOCK and READPAST hints and ordering. The UPDATE changes its state in the same statement and returns the claimed ID. In a database using read committed snapshot, READCOMMITTEDLOCK keeps READPAST valid under a read committed session. Test the exact isolation configuration on your instance. Do not split SELECT and UPDATE into two application round trips.

I keep the claim transaction short. The external work happens after the claim is committed, then a separate operation marks completion. Holding a SQL transaction open during a network call makes blocking and recovery harder. The queue’s lease or retry design covers a worker that disappears after claiming.

WITH NextJob AS
(
    SELECT TOP (1) JobId, Status, ClaimedAt
    FROM dbo.WorkQueue WITH
         (UPDLOCK, READPAST, READCOMMITTEDLOCK, ROWLOCK)
    WHERE Status = 'P'
    ORDER BY CreatedAt, JobId
)
UPDATE NextJob
SET Status = 'C', ClaimedAt = SYSDATETIME()
OUTPUT inserted.JobId, inserted.ClaimedAt;

Test UPDLOCK and READPAST with Two Workers

Open two sessions against the same test queue. Hold a transaction in one while the second attempts a claim. Confirm that they receive different job IDs and that a committed claim remains visible as claimed. Repeat with a rollback. A demonstration with one session can prove syntax but not concurrency behavior.

I watch whether the chosen plan uses the queue index and whether locks stay narrow. ROWLOCK is a request, not an absolute guarantee. SQL Server can take other locks as needed. If workers block on a page or index hotspot, check the access path and insertion pattern. More hints are not always the next fix.

Handle the Empty Queue

An UPDATE that outputs no row means no currently claimable pending job was found. It does not prove the entire system is finished. Rows can be claimed by other workers, scheduled for later, or failed awaiting retry. Use a backoff rather than a busy polling loop that hammers the same index.

I set a small polling interval and instrument claim attempts. If every worker wakes at exactly the same moment, they create a burst that can overwhelm the queue table. Staggering or event-driven wakeups can reduce needless contention. The empty result is an ordinary state, not an error message.

Make Completion Idempotent

After work succeeds, update the claimed row to completed using the job ID and a worker token or claim identity. Check that exactly one intended row changed. If the action failed, record the error and move the row to retry or failed state under policy. An abandoned claimed row needs a timed recovery process. That process should avoid stealing a job still running legitimately.

I ask the application owner what duplicate execution would do. If it charges a card twice or sends two notices, the worker needs an idempotency key or a downstream guarantee. SQL lock hints cannot extend across every external system. The queue design must account for that boundary.

Watch Fairness and Starvation

READPAST can favor rows that are easy to lock while repeatedly skipping a hot row. Check the age of the oldest pending and claimed jobs, not only total queue length. A growing old tail signals a stuck claim or a poison job. Add a review process for jobs that fail repeatedly.

I do not assume ORDER BY makes processing globally strict when workers skip locked rows. It gives a preferred order among available candidates. If strict ordering is a business rule, a parallel skip-locked queue can be the wrong design. State that trade-off before increasing worker count.

Review Under Real Load

Measure claim latency, worker throughput, duplicate attempts, oldest-job age, and lock waits on a representative workload. Test a worker crash and a database restart. Confirm that recovery picks up stranded rows and that no job is silently lost. Keep the queue index and cleanup job in the deployment plan.

What happens after a worker claims a row and vanishes? If you can answer that from the state machine and a tested retry, the hints have a useful home. The locking pattern is one piece of a reliable queue, not the whole queue itself.

Related reading on this blog: Simple Example of READPAST Query Hint and NOLOCK: Why It Counts Some Rows Twice and Misses Others.

A skipped locked row is not a completed job, it is work another worker must finish or release.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.