ASP.NET:Problem with race conditon in business logic

Question

I have the following problem:

Our system has products that when released only are allowed to be purchased X times. Upon purchase a central purchasing algorithm checks how many Orders exist and if below X proceeds with the purchase.

In pseudoish C# code:

public class OrderMethods
{

    public static Purchase(Product product, Client client)
    {

        int purchases = /* count order records of this product */;

        if(purchases>=MAX_ORDERS) throw PurchaseException();

        /* perform purchase by inserting order record in database */

     }

}

The problem is that sometimes when there's a high demand for a certain product a lot of request happen at the same time and more than MAX_ORDERS are registered. This happens about once a year :(.

What's the best solution for solving this? I'm using ASP.NET/C#, Ling2SQL and MSSQL. We have 1000> orders per day. It's important that orders are processed in the order they are requested.

The solutions I've comeup with so far:

• One global mutex?

• One mutex per product stored in a hashtable with an access function like:

  private Mutex GetPurchaseMutex(Guid productId)
  {
  
  
  if (mutexTbl[productId] == null)
      {
          mutexTbl[productId] = new Mutex();
      }
      return (Mutex)mutexTbl[productId];
  }
  

Where mutexTbl is an Hashtable. Here I haven't figure out how discard old mutexes in a nice way though.

• Using a T-SQL INSERT Trigger on the Order table that checks how many orders there are:

  CREATE TRIGGER Triggers_OrderInsertTrigger ON Orders AFTER INSERT AS IF /* check if there's to many orders */ BEGIN RAISERROR ('Too many orders', 16, 1); ROLLBACK TRANSACTION; RETURN END;

But I'm not so fond of either of these solutions. How would you solve this?

Answer 1

I'd say move this logic to the database layer when it can be secured with transactions.

Check for the number of placed offers and if okay place a new order within the same transaction. During this time the new requests will have their transactions (querying the number of placed orders) halted until the first is finished.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

DECLARE @OrderCount int
SELECT @OrderCount = COUNT (ID) FROM [Order] WHERE ProductID = '234323'

IF (@OrderCount < @Threshold)
BEGIN
    INSERT INTO [Order] (ID, ProductID, ...)
    VALUES (NEWID(), '234323', ...)

    COMMIT TRANSACTION
    RETURN 0
END
ELSE
    ROLLBACK TRANSACTION
    RETURN 1
END

Answer 2

This problem sounds a lot like the "only two unshipped orders" problem I described here. (Warning, the article is rather long, but very informative). But I think your options are basically are:

• Put that complete business logic (including validation) in your database (just as Developer Art wrote).
• Use serializable transaction and write this logic in C# in your business layer including .Count().
• Do nothing and let it fail once a year and clean up the mess afterwards.

While your might find the last option funny, this actually is a serious option. Fixing this kind of concurrency problems is hard and might be all over the place. It might force you to make big architectural changes. Besides, the effectiveness of the COUNT(*) solution depends on the indexes in that particular table. Adding indexes in the database (for performance reasons) could accidentally change the effectiveness and correctness of this solution. Therefore, from a business perspective, it might be a lot cheaper to fix the problem in your database once a year. Of course I can't guess what the costs are, each time a customer is able to purchase more orders than your business whats it to. That’s up to you (or your business) to determine.

Answer 3

If you don't want to handle this at the DB level, I would create a class that stores the purchase count for each product, store those counts in a table, and lock them in your purchase method. This is similar to your mutex method, but you allow .NET to handle the locking for you

public class PurchaseCounter(
{
    public Guid Product {get; set; } 
    public int MaxOrders {get; set; } 
    public int int CurrentOrders {get; set; } 
 }

public static bool Purchase(Product product, Client client)
{

    PurchaseCounter counter = purchaseCounterDictionary[product.ProductGuid];

    lock(counter)
    {
        if( counter.CurrentOrders < counter.MaxOrders )
        {
             //do logic to place order
             counter.CurrentOrders++;
             return true;  
        } 
        else
        {
             return false;
        }
    }

 }

}