我有兩種方法可以一次更新一批專案中的狀態欄位。一個使用 WHERE IN，另一個使用 CTE。我更喜歡 CTE 方法，但是如果我有 2 個行程同時呼叫這個 store proc，第一個會更好還是底部 CTE。我們關心可能的競爭條件。如果 CTE 沒有競爭條件，那么我想使用它。當我定義 CTE 時，它是在那時處理的還是作為 UPDATE 中的原子操作完成的？
帶有 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
)
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
uj5u.com熱心網友回復：
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;
uj5u.com熱心網友回復：
根據 Stu 和 Charlieface 在評論中提到的內容，我選擇了 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/347761.html
標籤：sql-server