SQL Server Updating with subquery or CTE, needs to be race condition safe-CodePudding

I have two approaches for updating a status field in a batch of items at a time. One that uses a WHERE IN and other that uses a CTE. I like the CTE approach more, but if I had 2 processes calling this store proc at the same time would the first one be better or the bottom CTE. We are concerned with possible race conditions. If the CTE will not have race condition then I would like to use that. When I define the CTE is that processed at that point or is it done as an atomic action in the UPDATE?

the one with the WHERE IN:

CREATE TABLE #RowsIWant (PriceValueId BIGINT)
    
UPDATE PV
SET SyncRAGStatus = 'A',
    AuditUser = pv.AuditUser
OUTPUT Inserted.Id INTO #RowsIWant
FROM PriceValues AS PV 
WHERE PV.Id IN (
    SELECT TOP (@batchSize) PV.Id
    FROM Prices AS P
    INNER JOIN PriceValues AS PV ON PV.PriceId = P.Id
    WHERE P.PriceListId = @priceListId
    AND PV.SyncRAGStatus = 'R'
    ORDER BY PV.UpdateInsertStatus DESC
)

The CTE one:

;WITH TopNRowsINeed AS (
    SELECT TOP (@batchSize) PV.Id AS PriceValueId, PV.SyncRAGStatus, PV.AuditUser
    FROM Prices AS P
    INNER JOIN PriceValues  AS PV ON PV.PriceId = P.Id
    WHERE P.PriceListId = @priceListId 
    AND PV.SyncRAGStatus = 'R'
    ORDER BY PV.UpdateInsertStatus DESC
)

UPDATE TopNRowsINeed
SET SyncRAGStatus = 'A'
OUTPUT Inserted.PriceValueId INTO #RowsIWant

CodePudding user response：

WITH TopNRowsINeed
AS
(
    SELECT TOP (@batchSize) PV.Id, PV.SyncRAGStatus
    FROM PriceValues PV WITH (UPDLOCK, SERIALIZABLE)
    WHERE PV.SyncRAGStatus = 'R'
    AND EXISTS
        (
            SELECT 1
            FROM Prices P
            WHERE P.Id = PV.PriceId
        )
    ORDER BY PV.UpdateInsertStatus DESC
)
UPDATE TopNRowsINeed
SET SyncRAGStatus = 'A'
OUTPUT Inserted.Id
INTO #RowsIWant;

CodePudding user response：

Based on what Stu and Charlieface mentioned in the comments, I went for the CTE:

;WITH TopNRowsINeed AS (
    SELECT TOP (@batchSize) PV.Id AS PriceValueId, PV.SyncRAGStatus, PV.AuditUser
    FROM Prices AS P
    INNER JOIN PriceValues  AS PV ON PV.PriceId = P.Id
    WHERE P.PriceListId = @priceListId 
    AND PV.SyncRAGStatus = 'R'
    ORDER BY PV.UpdateInsertStatus DESC
)

UPDATE TopNRowsINeed
SET SyncRAGStatus = 'A'
OUTPUT Inserted.PriceValueId INTO #RowsIWant