CODEWITHSUNDEEP
sqlsql-serverdatabase
Mike Ribeiro
Mike Ribeiro

Reputation: 325

update database queue with lock and ordered output

I have a database queue existing of two tables, meta and data. A storec proc locks 100 rows, updates the status of the rows and returns meta + data. I have a windows service executing the stored proc.

The stored proc

update top(100) QueueMeta WITH (UPDLOCK, READPAST)
SET Status = 'processing'
OUTPUT inserted.Id, 
        inserted.CreatedOn, 
        inserted.Timeout, 
        qd.Message,
        qd.Msisdn,
        inserted.NrOfRetries,
        inserted.MaxNrOfRetries             
        
FROM QueueMeta qm
INNER JOIN QueueData qd
    ON qm.Id = qd.QueueMetaId
WHERE Status = 'readytosend' and SendOn <= CURRENT_TIMESTAMP

The thing is that I need to order this on QueueMeta.Prio (int). How should I proceed since order by is not allowed? Thinking of #Temp but I need the same functionality, as I have two different services that polls the database at the same time without losing performance. Any suggestions?

Upvotes: 1

Views: 603

Answers (1)

barhun
barhun

Reputation: 241

Well, you can use a table-typed variable to select into with the capability of ordering. Also note that you must use (UPDLOCK, READPAST) hints in the from clause to avoid concurrent updates which, if occurring, will mutate the state of selected records.

DECLARE @Dequeued TABLE (
    Id BIGINT/INT PRIMARY KEY,
)

INSERT INTO @Dequeued
SELECT TOP (100)
    Id
FROM QueueMeta WITH (UPDLOCK, READPAST)
WHERE
    Status = 'readytosend' AND
    SendOn <= CURRENT_TIMESTAMP
ORDER BY Prio DESC

UPDATE qm
SET
    Status = 'processing'
OUTPUT
    INSERTED.Id, 
    INSERTED.CreatedOn, 
    INSERTED.Timeout, 
    qd.Message,
    qd.Msisdn,
    INSERTED.NrOfRetries,
    INSERTED.MaxNrOfRetries
FROM QueueMeta qm
INNER JOIN @Dequeued d
    ON d.Id = qm.Id
INNER JOIN QueueData qd
    ON qd.QueueMetaId = qm.Id

Good to mention: Define an index on the columns Status, SendOn and Prio if not defined already.

Upvotes: 2

Related Questions