LINQ to SQL: Issue with concurrency

Question

I’m working on a sandwich ordering app in ASP.NET MVC, C# and LINQ to SQL. The app revolves around the user creating multiple custom-made sandwiches from a selection of ingredients.

When it comes to confirming the order I need to know that there’s enough portions of each ingredient to fulfil all the sandwiches in the user’s order before I commit to the DB as it is possible that an ingredient will go out of stock between adding it to their basket and confirming the order.

A bit about the database:

Ingredient – Stores ingredient details including number of portions
Order – Header table for an order, simply stores the order time
OrderDetail – Stores a record of each sandwich in an order
OrderDetailItem – Stores each ingredient in each sandwich in an order

So basically I’m wondering what the best approach to ensuring that before I add records to Order, OrderDetail and OrderDetailItem I can ensure that the order can be met.

Answer 1

    try
    { 
       Begin netTransaction();
        If (IsEnoughIngredients())
        {
           1. Build your sandwich
           2. Add sandwich to data context with a timestamp (or whatever you chose for concurrency checking)
           3. SubmitChangesToDataContext()
           4. CommitNetTransaction() 
        }
    } catch (Exception e)
    {
       Rollback netTransaction();
       InformUser();
    }

Answer 2

You should update the Ingredient quantity each time you store a sandwich.

However, this will prevent the other users to use the same ingredient before you commit your changes (even if the stock is enough).

It is better to use a staging table that would commit after each ingredient added. This would make the changes visible at once.

When you are ready to submit your order as a whole, the records are just moved from the staging table into the permanent one.

You should, however, implement some ROLLBACK mechanism yourself to deal with the stale records. For instance, a cron job which would monitor activity on the orders and delete those that have not been active for 10 minutes or so.

Answer 3

Number of approaches you could take here but I would do something like the following pseudo code. Safe assumption that typically there will be sufficient ingredients to satisfy the order, so structure the transaction control around that assumption and deal with the rare exceptions.

Begin transaction (Isolation = Repeatable Read)

For Each OrderDetail In Order.OrderDetailCollection
    For Each OrderDetailItem In OrderDetail.OrderDetailItemCollection
        Update Ingredient
        Set Portions = (Portions – OrderDetailItem.Portions)
        Where Ingredient.ID = OrderDetailItem.IngredientID
        And (Portions – OrderDetailItems.Portions) >= 0

        If RecordsAffected != 1 Then
            Rollback Transaction
            SufficientStock = false
            Exit For
        End If
    Next

    If(SufficientStock = false)
        Exit For
    End If
Next

Edit: If you could be persuaded to step away from LINQing everything, an alternative approach to avoid the round tripping would be something along the lines below:

Begin transaction 
    Insert Order (return OrderID)
    Insert OrderDetails
    Insert OrderDetailItems

    Execute  update stock stored procedure (see below)
    If (Success)
        Commit transaction
    Else
        Rollback transaction
    End IF

Code for the update stock level procedure:

CREATE PROCEDURE dbo.StockLevel_UpdateByOrderID
(
    @OrderID INT
    , @Success BIT
)

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATEABLE READ

BEGIN TRANSACTION

DECLARE @IngredientCount INT

-- Determine number of ingredients in whole order
SELECT
    @IngredientCount = COUNT(odi.IngredientID)
FROM
    dbo.OrderDetailItem odi
INNER JOIN
    dbo.OrderDetail od
ON  od.OrderDetailID = odi.OrderDetailID
WHERE
    od.OrderID = 1
GROUP BY
    odi.IngredientID    

-- Update stock levels for all ingredients
UPDATE
    dbo.Ingredient
SET
    Portions = (i.Portions - odi.TotalPortions)
FROM
    dbo.Ingredient i
INNER JOIN
    (
    SELECT
        odi.IngredientID
        , SUM(odi.Portions) AS TotalPortions
    FROM
        dbo.OrderDetailItem odi
    INNER JOIN
        dbo.OrderDetail od
    ON  od.OrderDetailID = odi.OrderDetailID
    WHERE
        od.OrderID = 1
    GROUP BY
        odi.IngredientID
    ) odi
ON  odi.IngredientID = i.IngredientID
WHERE
    (i.Portions - odi.TotalPortions) >= 0

-- Is number of ingredients updated correct?
IF(@@ROWCOUNT != @IngredientCount)
BEGIN
    ROLLBACK TRANSACTION
    SET @Success = 0
END
ELSE
BEGIN
    COMMIT TRANSACTION
    SET @Success = 0
END

Answer 4

DB code is good to know but, not good to be tied into a db if you can help it. The menu display should be based on the current ingredients at the time of viewing, so being out of ingredieants should be a rare exception or your sandwich shop will not last long enough to care how good the software is.

How to avoid round trips for the ingredients update? Does link support some kind of batch insert on the ingredient table? cifey