Getting a dirty read despite of having set isolation level to ReadCommitted

Question

I have a transaction that is going to be executed concurrently, when there exists an entry for the given reference, I have to update the data, otherwise I have to create a record an set the reference accordingly. So far, the code I have goes like this:

using (var context = new MyDbContext())
{
    using (var scope = context.Database.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
    {
         try
         {
             MyModel model = null;
             int hasEntry = context.MyModel.Where(x => x.refID == refID).Count();
             if (hasEntry == 0)
             {
                  model = new MyModel();
                  model.refID = refID;
             }
             else
             {
                  model = context.MyModel.Where(x => x.refID == refID).Single();
             }
             // process model object...
             if (hasEntry == 0)
             {
                  context.MyModel.Add(model);
             }
             else
             {
                  context.Entry(model).State = EntityState.Modified;
             }
             context.SaveChanges();
             scope.Commit();
         }
         catch(Exception)
         {
             scope.Rollback();
         }
    }
}

When two (or more) instances run the first one inserts but then remaning ones do not update. They throw an DBUpdateException complaining about a PK being violated.

I don't know why the ones that have to update are actually trying to insert. They get an ID that was already added to the table so they throw the exception.

AFAIK the IsolationLevel.ReadCommitted should lock the rows and just free them when the transaction is done. According to the documentation (https://msdn.microsoft.com/en-us/library/cc546518.aspx) "If your connection is using the read committed isolation level and SQL Server encounters a dirty row while executing a DML statement, it will wait until the transaction that currently owns the row has been committed or rolled back before continuing execution."

So, I guess I am having a dirty read because the instances are not waiting for the instance that inserts in the first place, so they try to insert (with an already used id) and there comes the PK violation.

What am I missing?

PD: This is my first project using c# and entityframework. I have searched a lot and have not found any answer, excuse me if the question is kind of silly.

score 1 · Accepted Answer · answered May 03 '16 at 19:48

1

This is not a dirty read but is instead a time of check to time of use problem (TOCTOU) The row didn't exist when you checked but was inserted before you insert.

One way to prevent this is to use some kind of locking. You can also try the insert and if it fails then do the update.

answered May 03 '16 at 19:48

Mike

3,462
22
25

You are right, I solved the problem by applying a lock like this: context.Database.ExecuteSqlCommand("SELECT TOP 1 ID FROM MyTable WITH (TABLOCKX, HOLDLOCK)") In here there is more about that: http://stackoverflow.com/questions/13404061/how-can-i-lock-a-table-on-read-using-entity-framework – Edson Ticona Zegarra May 04 '16 at 13:12

score 0 · Answer 2 · answered May 03 '16 at 19:41

0

There's no such thing as "dirty row to be inserted". When both processes simultaniously ask if there exist id=111 apparently both will get "no". Generally insertion with app managed value of PK should always be in try catch block.

answered May 03 '16 at 19:41

Serg

22,285
5
21
48

Getting a dirty read despite of having set isolation level to ReadCommitted

2 Answers2