Deadlock with EF 6 entity update but not ExecuteSqlCommand

Question

To handle concurrency in my database:

Client A updates a row
Client B tries to update the same row
Client B needs to wait for Client A to commit his updates

Both Client A & B instance are simulated and using this code:

 using (myEntities db = new myEntities ())
 {
     db.Database.Connection.Open();

     try
     {
         using (var scope = db .Database.BeginTransaction(System.Data.IsolationLevel.Serializable))
         {
             {  
                 var test = db.customer_table.Where(x => x.id == 38).FirstOrDefault();
                 test.bank_holder_name = "CLIENT NAME XXXX";
                 db.SaveChanges(); <=== CLIENT B stop here while client A still in progress. After CLIENT A finish commit, here will throw *Deadlock found error*"
                 scope.Commit();
             }
         }
     }
     catch (Exception ex)
     {
         throw;
     }
 }

This is not what I expected where Client B should wait and not allowed to query any data about row id=38, but somehow it can proceed until SaveChanges and throws an error in the end.

Thus, I suspected this might caused by linq (incorrect row/ table lock)

I edited my code as below:

 using (myEntities db = new myEntities ())
 {
     db.Database.Connection.Open();

     try
     {
         using (var scope = db .Database.BeginTransaction(System.Data.IsolationLevel.Serializable))
         {
             {  
                 var test = db.Database.ExecuteSqlCommand("Update customer_table set bank_holder_name = 'CLIENT XXXXX' where pu_id = 38"); <===== Client B is stop here and proceed after Client A is completed
                 db.SaveChanges();
                 scope.Commit();
             }
         }
     }
     catch (Exception ex)
     {
         throw;
     }
 }

Finally, the transaction is working with code above (not linq function). This is so confusing, what linq have done in behind making Transaction working inconsistent behavior?

An isolation level doesn't define/prescribe the moment of locking. It's an indication of a transaction's logical behavior: https://sqlperformance.com/2014/04/t-sql-queries/the-serializable-isolation-level. — Gert Arnold, Jul 25 '20 at 13:55
And in SQL Server SERIALIZABLE allows concurrent reads of rows, but prevents conflicting updates by failing one transaction with a deadlock. — David Browne - Microsoft, Jul 25 '20 at 15:27
Ok, seems like I totally misunderstood the transaction isolation in SQL server. If that is the case, how we handle concurrency like the cases above in EF? I believe the common solution is using pessimistic lock but EF dont support this.... — Tsushima, Jul 25 '20 at 17:59

strickt01 · Answer 1 · 2020-07-25T14:28:57.400

This is due to the EF code generating two SQL statements: a SELECT for the line:

var test = db.customer_table.Where(x => x.id == 38).FirstOrDefault();

...and a subsequent UPDATE for the SaveChanges() call.

With a serializable isolation level both client A and client B take a shared lock for the duration of the transaction on the record when the SELECT statement is run. Then when one or other of them first tries to perform the UPDATE they cannot get the requisite exclusive lock because the other client has a shared lock on it. The other client itself then tries to obtain an exclusive lock and you have a deadlock scenario.

The ExecuteSqlCommand only requires a single update statement and thus a deadlock does not occur.

The Serializable isolation level can massively reduce concurrency and this example shows exactly why. You'll find that less stringent isolation levels will allow the EF code to work, but at the risk of phantom records, non-repeatable reads etc. These may well however be risks you are willing to take and/or mitigate against in order to improve concurrency.

Hi strickt01, thanks for informing. How do you know there are 2 SQL statement under EF select code? — Tsushima, Jul 25 '20 at 15:06
@Tsushima because you are populating `test` using the DbContext entity `customer_table`. To do that EF will go to the database to get the values. Then the `SaveChanges` call will trigger the SQL `UPDATE`. — strickt01, Jul 25 '20 at 16:56

score 0 · Answer 2 · answered Jul 25 '20 at 15:24

Don't fetch the entity first. Instead create a "stub entity" and update that, eg

var test = new Customer() { id = 38 };
test.bank_holder_name = "CLIENT NAME XXXX";
db.Entry(test).Property(nameof(Customer.bank_holder_name)).IsModified = true;
db.SaveChanges();

Which translates to

  SET NOCOUNT ON;
  UPDATE [Customers] SET [bank_holder_name] = @p0
  WHERE [id] = @p1;
  SELECT @@ROWCOUNT;

Deadlock with EF 6 entity update but not ExecuteSqlCommand

2 Answers2