LINQ to SQL: Issue with concurrency
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 tab开发者_C百科le 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 orderSo 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.
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();
}
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.
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
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
精彩评论