Prevent overbooking during high traffic requests-CodePudding

I’m having a problem with overbooking in a SQL Server environment. Our table structure looks like this:

Table: 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

Table: 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 has a capacity of 4 and currently has 3 spots taken. We are setting a time, like 8am EST on 02/18/22, when users can log in and reserve the 4th spot, first come first served. The problem is that at 8am on Feb 18 hundreds of people try to reserve that one spot, and for some reason our process is allowing more than one person to create a record, resulting in overbooking.

The process should work like this:

User logs in.
If trip 1002 is not already at capacity then allow user to add to cart.
User adds trip 1002 to cart, clicks ‘Execute cart’
Stored proc ‘procExecCart’ executes, gets Trip_Capacity from table [Trips], checks [Trip_Signup] to see if a slot is available. If yes, then add a record to [Trip_Signup].

We always seem to overbook by 3 or 4 slots. The overbook records are created in those first minutes when hundreds of people are competing for the same record.

Given that we cannot change the data structure, is there a way to ensure that overbooking can’t happen, and that the remaining capacity truly goes to the first person who requests it?

So far we have tried to use NOLOCK on table [Trip_Signup] when counting signups in hopes that this would provide an accurate count of existing and pending signups. This has not helped.

Thanks!

EDIT

To clarify, we’re using NOLOCK to look for uncommitted INSERTs on table [Trip_Signup]. The proc logic looks like this:

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

CodePudding user response：

You don't want NOLOCK here, that is the exact opposite of what you need, as it may miss or double-count rows in many high-concurrency situations. You need more locking. I suggest you use SERIALIZABLE for iron-clad guarantees.

You would need an explicit transaction for your current code. But this does not actually need it, because you can do it all in one statement (every statement runs in its own transaction anyway).

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;

The UPDLOCK hint is added to help prevent deadlocks, which can be common under SERIALIZABLE

CodePudding user response：

Thank you @Charlieface. I see how this addresses both the race condition and locking the table during the transaction. Here's what I'm thinking the final code might look like. I isolated the INSERT statement into its own proc because [procExecCart] will need to iterate over multiple signup requests in the cart.

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