T-SQL Concurrency Issue : Auction / Bidding System

Question

I am currently developing an online Auction system using ASP.NET 3.5 and SQLServer 2008. I have reached the point in development where I need to ensure that my system sensibly handles the concurrency issue which may arise when:

Two people - Geraldine and John - want to bid on the same auction item which is currently going for £50. Geraldine enters a bid of £55 and John enters a bid of £52. The system now has two copies of the page 'submit_bid.aspx' running; each copy of the page checks to see that their bid is high enough, they both see that it is, and they submit the bids. If John's bid goes through first then the auction item price is currently £55 and a moment later it's being replaced by a bid of £52.

What I need to do is to lock the auction item row until the current bid price is updated before allowing any other bidder to check the current bid price and placing a new bid.

My question is: what is the best practice way for doing this using T-SQL and / or ADO.NET?

I currently have an AuctionItem table which has the following fields (plus other fields I haven't included for brevity):

AuctionItemID   INT
CurrentBidPrice MONEY
CurrentBidderID INT

I have performed some research and come up with the following T-SQL (pseudocode-ish):

@Bid MONEY
@AuctionItemID INT

BEGIN TRANSACTION

SELECT @CurrentBidPrice = CurrentBidPrice
FROM AuctionItem
WITH (HOLDLOCK, ROWLOCK)
WHERE AuctionItemID = @AuctionItemID

/* Do checking for end of Auction, etc. */

if (@Bid > @CurrentBidPrice)
BEGIN
  UPDATE AuctionItem
  SET CurrentBidPrice = @Bid
  WHERE AuctionItemID = @AuctionItemID
END

COMMIT TRANSACTION

I have also read that if I include the SET LOCK_TIMEOUT I can also reduce the number of failed concurrent updates. For example:

SET LOCK_TIMEOUT 1000

...will make a concurrent update wait for 1000 milliseconds for a lock to be released. Is this best practice?

Answer 1

Source: "chrisrlong", http://www.dbasupport.com/forums/archive/index.php/t-7282.html

Here are the methodologies used to handle multi-user concurrency issues:

1. Do Nothing (Undesirable)

   • User 1 reads a record
   • User 2 reads the same record
   • User 1 updates that record
   • User 2 updates the same record

   User 2 has now over-written the changes that User 1 made. They are completely gone, as if they never happened. This is called a 'lost update'.

2. Pessimistic locking (Lock the record when it is read.)

   • User 1 reads a record and locks it by putting an exclusive lock on the record (FOR UPDATE clause)
   • User 2 attempts to read and lock the same record, but must now wait behind User 1
   • User 1 updates the record (and, of course, commits)
   • User 2 can now read the record with the changes that User 1 made
   • User 2 updates the record complete with the changes from User 1

   The lost update problem is solved. The problem with this approach is concurrency. User 1 is locking a record that they might not ever update. User 2 cannot even read the record because they want an exclusive lock when reading as well. This approach requires far too much exclusive locking, and the locks live far too long (often across user control - an absolute no-no). This approach is almost never implemented.

3. Use Optimistic Locking.
   Optimistic locking does not use exclusive locks when reading. Instead, a check is made during the update to make sure that the record has not been changed since it was read. Generally this is done by adding a version/etc column (INT/numeric, holding a numeric value that is increased when an UPDATE statement is made). IE:

   UPDATE YOUR_TABLE
      SET bid = 52
    WHERE id = 10
      AND version = 6
   

   An alternate option is to use a timestamp, rather than a numeric column. This column is used for no other purpose than implementing optimistic concurrency. It can be a number or a date. The idea is that it is given a value when the row is inserted. Whenever the record is read, the timestamp column is read as well. When an update is performed, the timestamp column is checked. If it has the same value at UPDATE time as it did when it was read, then all is well, the UPDATE is performed and the timestamp is changed!. If the timestamp value is different at UPDATE time, then an error is returned to the user - they must re-read the record, re-make their changes, and try to update the record again.

   • User 1 reads the record, including the timestamp of 21
   • User 2 reads the record, including the timestamp of 21
   • User 1 attempts to update the record. The timestamp in had (21) matches the timestamp in the database(21), so the update is performed and the timestamp is update (22).
   • User 2 attempts to update the record. The timestamp in hand(21) does not match the timestamp in the database(22), so an error is returned. User 2 must now re-read the record, including the new timestamp(22) and User 1's changes, re-apply their changes and re-attempt the update.

Comparison

• Optimistic locking is database independent -- no need for mucking with isolation levels and database specific syntax for isolation levels.
• I'd use a numeric column over a timestamp -- less data & hassle to manage

Answer 2

You don't need a transaction if just use 1 statement like this:

-- check if auction is over (you could also include this in the sql)

UPDATE AuctionItem   
SET CurrentBidPrice = @Bid   
WHERE AuctionItemID = @AuctionItemID 
AND CurrentBidPrice < @Bid

IF @@ROWCOUNT=1 
BEGIN
    --code for accepted bit
    SELECT 'NEW BIT ACCEPTED'
END ELSE
BEGIN
    --code for unaccepted bit
    SELECT 'NEW BIT NOT ACCEPTED'
END