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?

depends on several things: the isolation level of the transaction (by default it will be ReadCommitted), and what you do in that transaction. — Mitch Wheat
– Mitch Wheat, Commented Oct 21, 2018 at 9:02
@MitchWheat I didn't set any isolation level option, so I think it is the default. And what I do other than updating PaymentStatus above is basically insert and update some other table. — Viki Theolorado
– Viki Theolorado, Commented Oct 21, 2018 at 9:08
Your real problem is here "and sometimes this function is executed multiple times for the same key and the same time (because multiple user access it together)." Rather than lock the the table and block all other access, it would be better to fix the cause rather than the symptoms. — Mitch Wheat
– Mitch Wheat, Commented Oct 21, 2018 at 9:10
@MitchWheat, I think it's not possible because the page connected to this function is showing list of unpaid Order and a button to set the payment status to Paid. I can't prevent the button only clicked once because the page is accessed by multiple user. So I'm trying to block the table using transaction (at least even when the button is clicked multiple times, it only processed once at the first click). But unfortunately it's not run as expected. — Viki Theolorado
– Viki Theolorado, Commented Oct 21, 2018 at 9:19
At the very least you should implement optimistic concurrency. — Gert Arnold
– Gert Arnold, Commented Oct 21, 2018 at 9:52

David Browne - Microsoft · Accepted Answer · 2018-10-21 13:58:28Z

3

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.

edited Oct 21, 2018 at 13:58

answered Oct 21, 2018 at 13:52

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yoda Over a year ago

But does it fall apart as soon as you have a few pods running the backend?

Collectives™ on Stack Overflow

Using Entity Framework Transaction Correctly for Isolation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related