CODEWITHSUNDEEP
c#sqlentity-frameworklockingtransactionscope
J. Bag
J. Bag

Reputation: 9

Lock table for read

We have an e-commerce website where people can buy a gift card. These cards are stored in a table and ordered by an integer for logistic purpose. If someone buys a gift card, we insert a purchase order in the database and edit a field of the first eligible card in order to know it is not available anymore.

But sometimes when 2 users buy a card in the same time, the same card is given for the both. So we made a transaction with IsolationLevel.ReadCommitted which doesn't work. For 4 concurrent clicks, there is 1 same Card for 2 PurchaseOrder and another Card for 2 PurchaseOrder.

TransactionOptions transOption = new TransactionOptions();
transOption.IsolationLevel = IsolationLevel.ReadCommitted; 

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, transOption))
{
    PurchaseOrder purchaseOrder = GetFull(request.OrderId);
    purchaseOrder.ChangeTracker.ChangeTrackingEnabled = true;
    purchaseOrder.IdStatus = DbConstants.RefPurchaseOrderStatus.Completed;

    //select the quantity of purchased gift cards and update them with ObjectContext.SaveChanges()
    List<Card> cards = _cardBusiness.AssignCards(purchaseOrder.Quantity, purchaseOrder.IdProduct, purchaseOrder.IdUserAccount, purchaseOrder.IdChannel);

    Card[] arrCards = cards.ToArray();

    int count = 0;
    //Set a link between a PurchaseOrder Line and the Card
    foreach(PurchaseOrderLine line in purchaseOrder.PurchaseOrderLines)
    {
        line.ChangeTracker.ChangeTrackingEnabled = true;
        line.IdCard = arrCards[count].Id;
        count++;
    }

    PurchaseOrder po = Update(purchaseOrder);

    scope.Complete(); 

    return true;
}

Quick comment: In the transaction, we get the previously inserted PurchaseOrder, change its status, then select the available gift card (s), edit some properties, SaveChanges() and return the gift card (s) to link to the PurchaseOrderLine.

But we obviously doesn't lock the card selection. How could we do that?

Update:

public static List<Card> AssignCards(int qty, int idProduct, Guid idUserAccount, int? idChannel)
{
    using (RestopolitanEntities context = GetContext())
    {
        try
        {
            var Xcards = (from c in context.Cards
                         select c);

            List<Card> cards = Xcards.OrderBy(c => c.IdOrder).Take(qty).ToList();

            foreach (Card card in cards)
            {
                card.ChangeTracker.ChangeTrackingEnabled = true;
                card.UseDate = DateTime.Now;
            }

            context.SaveChanges();

            return cards.ToList();
        }
        catch (Exception ex)
        {
            HandleException(ex);
        }
    }
    return null;
}

This time I tried with IsolationLevel.RepeatableRead, a Card is not longer attributed to 2 PurchaseOrder. But no Card are returned from AssignCards(). For 4 concurrent clicks, there is 2 PurchaseOrder with 1 distinct Card and 2 PurchaseOrder without Card.

Upvotes: 0

Views: 471

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88851

Either acquire an application lock at the beginning of the transaction,

context.Database.ExecuteSqlCommand("exec sp_getapplock 'AssignCards','Exclusive';");

Or use some lock hints on the query that identifies the cards to use:

var sql = "select top (@qty) CardId from Cards with (rowlock, updlock, readpast) where UseDate is null";
var cardIds = context.Database.SqlQuery<int>(sql,qty).ToList();

List<Card> cards = context.Cards.Where(c => cardIds.Contains(c.CardId)).ToList();

If you use the lock hints you can use the READPAST hint to allow concurrent sessions to skip the cards that are locked by other sessions. And if you use the Applciation Lock, you can release the lock in the middle of the transaction (after selecting the cards).

Upvotes: 1

Related Questions