anytoe anytoe - 2 months ago 9
ASP.NET (C#) Question

Basket Item with with TransactionScope.ReadCommitted still creating duplicates

I have an endpoint (ASP.NET WebAPI + Entity Framework 6) allowing to add items to a basket. It looks like this:


public int AddToBasket(BasketUpdate update)
{
var transactionOptions = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
var existingBasketItem = basketItems.Query().FirstOrDefault(item =>
item.UserId == update.UserId
&& item.AccountId == update.AccountId
&& item.ProductId == update.ProductId);



existingBasketItem = existingBasketItem ?? basketItems.Create();
existingBasketItem.Quantity += update.Quantity;
existingBasketItem.AccountId = update.AccountId;
existingBasketItem.UserId = update.UserId;
existingBasketItem.ProductId = update.ProductId;

unitOfWork.Commit();

scope.Complete();

return existingBasketItem.Quantity;
}
}


So I assumed (in my endless nativity) that if I fire multiple calls to this endpoint, I always end up with one entry in the database adding up the quantities of all calls correctly. Penetrating this endpoint with Fiddler seems to confirm that, all requests are waiting until the previous has finished.

However, as soon as I stop looking (or somebody else adds items to the basket), I end up having something like that:

Id Quantity ProductId UserId AccountId
429 12 4560 56 2234
430 1 4560 56 2234


How on earth can that happen? Are there by any chance two instances alive on the IIS server at some point, or do I misunderstand anything about transactions here? Pulling my hair already, so help would be much appreciated.

Answer

This approach does not work at all. There seem to be no EntityFramework approach to solved that sufficiently. Even the level 'Serializable' only locks down the selected data, leading to new rows being inserted requiring the service code to handle the quantity split into several rows.

So I have written a Stored Procedure using MERGE with a TABLOCK providing exclusive read and write on the table and speed.

ALTER PROCEDURE [dbo].[UpdateSalesQuantity]
@AccountId          INT, 
@ProductId          INT,
@Quantity           INT,
@AddNotSetQuantity  BIT
AS
BEGIN
SET NOCOUNT ON;

MERGE INTO dbo.BasketItem WITH (TABLOCK) AS target
USING (Select @AccountId AccountId, @ProductId ProductId) AS source
ON
    target.AccountId = source.AccountId
    AND target.ProductId = source.ProductId
WHEN MATCHED AND (@AddNotSetQuantity = 0 AND @Quantity > 0) OR (@AddNotSetQuantity = 1 AND target.Quantity + @Quantity > 0)
    THEN UPDATE SET target.Quantity = (CASE @AddNotSetQuantity WHEN 0 THEN 0 ELSE target.Quantity END) + @Quantity
WHEN MATCHED AND (@AddNotSetQuantity = 0 AND @Quantity <= 0) OR (@AddNotSetQuantity = 1 AND target.Quantity + @Quantity <= 0)
    THEN DELETE
WHEN NOT MATCHED BY TARGET AND @Quantity > 0
    THEN INSERT (ProductId, AccountId, CreationTime, ModificationTime, [Guid], Quantity)
        VALUES (@ProductId, @AccountId, GETDATE(), GETDATE(), NEWID(), @Quantity);

SELECT Quantity FROM dbo.BasketItem WHERE AccountId = @AccountId And ProductId = @ProductId
END