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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Get the current max bid for the product
SELECT MAX(Amount) FROM dbo.AuctionBid WHERE ProductId = 9;
-- Your application checks if the new bid is higher and proceeds to insert
INSERT INTO dbo.AuctionBid (ProductId, Amount) VALUES (9, <new_bid>);
COMMIT TRANSACTION;

Does something like this do the trick? Put this in your insert proc, and if the @CurrentBid provided is less than the max bid, there will be no insert. You could add additional handling to make a fuss or log something in the event the bid wasn't accepted, but the key point here is this takes out locks on the table preventing any other SPIDs from trying to do the same.

insert into AuctionBid
(
    Bid
)
select
    @CurrentBId
where @CurrentBId > (select max(Bid) from AuctionBId)

-- if you need to handle subsequent logic differently if the bid insert failed, do something like this:
if @@rowcount = 0
begin
    raiserror('Oh noes! ur bid wasn''t accepted!', 0, 1) with nowait
end

I ran a test on a global temp table where I first inserted a row with a bid of 50 (to seed the table). I then opened a new table and ran an insert into the table with a bid of 9000, but left the transaction open. Finally, I opened a third tab and attempted to insert a value of 8999, and it hung waiting for my other transaction to complete. That would seem to imply the table is locked from inserts while that insert is completing.

The SPID trying to insert while the other transaction is still open is trying to get a LCK_M_S shared lock, and can't till the other transaction commits.

You can't even select the max(Bid) from the table while the other SPID has the lock; as you would hope.