我在 SQL Server 環境中遇到超額預訂問題。我們的表結構如下所示：
表：dbo.Trips
Trip_ID Trip_Name Trip_Capacity
1001 Field Trip to Vegas in March 8
1002 Field Trip to NYC in April 4
1003 Field Trip to Miami in May 20
表：dbo.Trip_Signup
Signup_ID (PK) Trip_ID Person_ID Date_Created
1 1002 SKCIIEKF 02/08/2022 09:16:00
2 1002 LKKSKEIC 02/11/2022 11:42:00
3 1002 NCGSBEBB 02/12/2022 15:19:00
Trip_ID 1002 的容量為 4，目前已占用 3 個位置。我們正在設定一個時間，例如美國東部標準時間 02/18/22 上午 8 點，用戶可以登錄并保留第 4 個位置，先到先得。問題是，在 2 月 18 日上午 8 點，數百人試圖預訂一個位置，出于某種原因，我們的流程允許多人創建記錄，導致超額預訂。
該程序應該像這樣作業：
- 用戶登錄。
- 如果行程 1002 尚未滿員，則允許用戶添加到購物車。
- 用戶將行程 1002 添加到購物車，點擊“執行購物車”
- 存盤的 proc 'procExecCart' 執行，從表 [Trips] 中獲取 Trip_Capacity，檢查 [Trip_Signup] 以查看插槽是否可用。如果是，則將記錄添加到 [Trip_Signup]。
我們似乎總是超額預訂 3 或 4 個插槽。超額預訂記錄是在數百人爭奪同一記錄的最初幾分鐘內創建的。
鑒于我們無法更改資料結構，有沒有辦法確保不會發生超額預訂，并且剩余容量真正流向第一個請求它的人？
到目前為止，我們已經嘗試在計算注冊時在表 [Trip_Signup] 上使用 NOLOCK，希望這將提供現有和待定注冊的準確計數。這沒有幫助。
謝謝！
編輯
澄清一下，我們使用 NOLOCK 來查找表 [Trip_Signup] 上未提交的 INSERT。proc 邏輯如下所示：
CREATE PROC dbo.procExecCart
@Trip_ID int, @Person_ID varchar(20)
AS
– Check for availability
– NOLOCK is used to include uncommitted inserts in the count of current signups
DECLARE @Capacity in = (SELECT Trip_Capacity FROM dbo.Trips WHERE Trip_ID = @Trip_ID),
@Current_Signup_Count int = (SELECT Count(0) FROM dbo.Trip_Signup (NOLOCK) WHERE Trip_ID = @Trip_ID)
– Create a signup record
IF @Capacity > @Current_Signup_Count
BEGIN
INSERT dbo.Trip_Signup (Trip_ID, Person_ID) VALUES(@Trip_ID, @Person_ID)
END
uj5u.com熱心網友回復：
您不希望NOLOCK在這里，這與您需要的完全相反，因為在許多高并發情況下它可能會丟失或重復計算行。你需要更多的鎖定。我建議你使用SERIALIZABLE鐵定的保證。
您需要為當前代碼進行顯式事務。但這實際上并不需要它，因為您可以在一個陳述句中完成所有操作（無論如何，每個陳述句都在其自己的事務中運行）。
CREATE PROC dbo.procExecCart
@Trip_ID int,
@Person_ID varchar(20)
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT, NOCOUNT ON;
INSERT dbo.Trip_Signup (Trip_ID, Person_ID)
SELECT @Trip_ID, @Person_ID
WHERE (SELECT Trip_Capacity FROM dbo.Trips t WHERE t.Trip_ID = @Trip_ID)
> (SELECT COUNT(*) FROM dbo.Trip_Signup ts WITH (UPDLOCK) WHERE ts.Trip_ID = @Trip_ID);
IF @@ROWCOUNT = 0
THROW 50001, N'Trip Capacity Exceeded', 0;
UPDLOCK添加提示以幫助防止死鎖，這在以下情況下很常見SERIALIZABLE
uj5u.com熱心網友回復：
謝謝@Charlieface。我看到這如何解決競爭條件和在事務期間鎖定表。這就是我認為最終代碼可能的樣子。我將 INSERT 陳述句隔離到它自己的 proc 中，因為 [procExecCart] 需要遍歷購物車中的多個注冊請求。
CREATE PROC dbo.procExecCart
@Trip_ID int,
@Person_ID varchar(20)
AS
-- Try to create the signup
DECLARE @Response nvarchar(4000)
EXEC procInsertSignup @Trip_ID = @Trip_ID, @Person_ID = @Person_ID, @Response = @Response OUTPUT;
-- Do some stuff: create a log record, etc.
-- Return the response to the caller
SELECT @Response AS Response
RETURN
-----------------------------------------------------------
CREATE PROC dbo.procInsertSignup
@Trip_ID int,
@Person_ID varchar(20),
@Response nvarchar(4000) OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT, NOCOUNT ON;
BEGIN TRY
INSERT dbo.Trip_Signup (Trip_ID, Person_ID)
SELECT @Trip_ID, @Person_ID
WHERE (SELECT Trip_Capacity FROM dbo.Trips t WHERE t.Trip_ID = @Trip_ID)
> (SELECT COUNT(*) FROM dbo.Trip_Signup ts WITH (UPDLOCK) WHERE ts.Trip_ID = @Trip_ID);
IF @@ROWCOUNT = 0
SELECT @Response = 'Trip Capacity Exceeded'
ELSE
SELECT @Response = 'Your spot was successfully reserved'
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT @Response = ERROR_MESSAGE()
END CATCH
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/432429.html
標籤：sql服务器
上一篇：將多個“引數”xml節點值顯示到報表服務器目錄表中的一行中
下一篇：從一個表創建按年和月組織的視圖