Wednesday, September 27, 2017

SQL Pick up Batch in one go, and mark them as picked atomically while returning them

CREATE PROCEDURE [dbo].[sp_ProcessBatch]
@BatchSize int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @ProcessorState bit
set @ProcessorState = 1


UPDATE NotificationMeta
SET NotificationMeta.StateID = 2, -- UPDATE STATE AS PART OF SELECT AND OUTPUT
NotificationMeta.TimePicked = GETUTCDATE(),
RetryCount = RetryCount + 1
OUTPUT NotificationMeta2.ID, NotificationMeta2.EchoToken  -- RETURN PICKED ROWS
FROM NotificationMeta
JOIN
(SELECT TOP (@BatchSize) nd.* FROM NotificationMeta
 WITH (UPDLOCK,READPAST) -- TO ENSURE NO MULTIPLE UPDATES TO SAME ROW
 JOIN NotificationData nd ON ID = NotificationMeta.NotificationID
 WHERE StateID = 1 AND GETUTCDATE() < ExpiryTime
 ORDER BY nd.EntryTime ASC, Priority ASC) AS NotificationMeta2
ON NotificationMeta.NotificationID = NotificationMeta2.ID

END

No comments: