SQL Insert Race Conditions on Max Amount

Question

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)

Answer 1

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