sqlsql-serverdatabaseconcurrencyazure-sql-database

SQL Insert Race Conditions on Max Amount


We are creating an auction bidding system. We only allow insertions into the AuctionBid ID table, for a Product, if its higher amount than the last bid.

This query will work for single operations.

select MAX(Amount) from dbo.AuctionBid where ProductId = 9   // Amount: 150

However, in a multithreading concurrency environment where there are a multiple auction bids on the same product, it can cause race conditions. Where one insert reads (150) and is about to insert eg 170, while another already inserted (eg 190) in the meantime.

How can I resolve this in SQL? Researching different types of locks. Trying to prevent full table lock if possible.

Our table has

AuctionBidId ProductId Amount
1 2 (Couch) 175
2 9 (TV) 100
3 9 (TV) 150

Currently using Microsoft SQL Server 2019. (T-SQL)


Solution

  • Consider the ff procedure

    create or alter procedure dbo.addBid (
       @ProductID int,
       @BidAmount int
    )
    as
    begin
       declare @maxBid int;
       begin transaction
          set @maxBid = (
             select max(Amount)
             from dbo.AuctionBid with (holdlock, updlock)
             where ProductId = @ProductID
          );
    
         if (@bidAmount > @maxBid)
         begin
            insert into dbo.AuctionBid
               (ProductId, Amount)
            values
               (@ProductID, @BidAmount);
         end
         else
         begin
            print 'Specified bid amount is not larger than current bid and is thus rejected';
         end
       commit
    end
    
    

    There's probably some other error handling you'd want in there, but this is the gist. The idea here is that you obtain the current max bid in such a way as to preclude concurrent processes from also doing so at the same time and do the insert in the same transaction as that select.


    The query hints we're using here are:

    HOLDLOCK (Serializable) - "Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes."

    UPLOCK - in this case, we are forcing an exclusive lock, since SQL Server can only take one UPLOCK , instead of a shared READLOCK