Using Entity Framework Transaction Correctly for Isolation

Question

I'm using Entity Framework 6.0 and SQL Server 2016 for my ASP.Net Website. I recently found a problem with concurrency at one of my function. The function is used for processing unpaid order and sometimes this function is executed multiple times for the same key and the same time (because multiple user access it together).

Here is what it looks like.

public void PaidOrder(string paymentCode)
{
    using (MyEntities db = new MyEntities())
    {
        using (DbContextTransaction trans = db.Database.BeginTransaction())
        {
            try
            {
                Order_Payment_Code payment = db.Order_Payment_Code.Where(item => item.PaymentCode == paymentCode).FirstOrDefault();
                if(payment.Status == PaymentStatus.NotPaid)
                {
                    //This Scope can be executed multiple times
                    payment.Status = PaymentStatus.Paid;
                    db.Entry(payment).State = EntityState.Modified;
                    db.SaveChanges();

                    //Continue processing Order

                    trans.Commit();
                }
            }
            catch (Exception ex)
            {
                trans.Rollback();
            }
        }
    }
}

What I don't understand is why scope inside my if statement can be executed multiple time even it is inside a transaction? Isn't transaction suppose to be isolating the data? Or my understanding of transaction is wrong? If so, then what is the correct way to make the scope inside my if statement only executed once?

Answer 1

A simple and reliable way to serialize an EF SQL Server transaction is to use an Application Lock.

Add this method to your DbContext:

public void GetAppLock(string lockName)
{
    var sql = "exec sp_getapplock @lockName, 'exclusive';";
    var pLockName = new SqlParameter("@lockName", SqlDbType.NVarChar, 255);
    pLockName.Value = lockName;
    this.Database.ExecuteSqlCommand(sql, pLockName);
}

And call it just after you start your transaction.

public void PaidOrder(string paymentCode)
{
    using (MyEntities db = new MyEntities())
    {
        using (DbContextTransaction trans = db.Database.BeginTransaction())
        {
            db.GetAppLock("PaidOrder");
            Order_Payment_Code payment = db.Order_Payment_Code.Where(item => item.PaymentCode == paymentCode).FirstOrDefault();
            if(payment.Status == PaymentStatus.NotPaid)
            {
                //This Scope can be executed multiple times
                payment.Status = PaymentStatus.Paid;
                db.Entry(payment).State = EntityState.Modified;
                db.SaveChanges();

                //Continue processing Order

            }
            trans.Commit();
        }
    }
}

Then only one instance of that transaction can run at a time, even if you have multiple front-end servers. So this is like a Mutex that works across all the clients that access the same database.