entity framework - does this do a dirty read?

Question

I have a bit of linq to entities code in a web app. It basically keeps a count of how many times an app was downloaded. I'm worried that this might happen:

Session 1 reads the download count (eg. 50)
Session 2 reads the download count (again, 50)
Session 1 increments it and writes it to the db (database stores 51)
Session 2 increments it and writes it to the db (database stores 51)

This is my code:

private void IncreaseHitCountDB()
{
    JTF.JTFContainer jtfdb = new JTF.JTFContainer();

    var app =
        (from a in jtfdb.Apps
         where a.Name.Equals(this.Title)
         select a).FirstOrDefault();

    if (app == null)
    {
        app = new JTF.App();
        app.Name = this.Title;
        app.DownloadCount = 1;

        jtfdb.AddToApps(app);
    }
    else
    {
        app.DownloadCount = app.DownloadCount + 1;
    }

    jtfdb.SaveChanges();
}

Is it possible that this could happen? How could I prevent it?

Thank you, Fidel

See this question for more info: http://stackoverflow.com/questions/1221975/optimistic-concurrency-in-ado-net-entity-framework — Kirk Broadhurst, Sep 06 '11 at 14:02

score 2 · Answer 1 · answered May 11 '12 at 02:58

Entity Framework, by default, uses an optimistic concurrency model. Google says optimistic means "Hopeful and confident about the future", and that's exactly how Entity Framework acts. That is, when you call SaveChanges() it is "hopeful and confident" that no concurrency issue will occur, so it just tries to save your changes.

The other model Entity Framework can use should be called a pessimistic concurrency model ("expecting the worst possible outcome"). You can enable this mode on an entity-by-entity basis. In your case, you would enable it on the App entity. This is what I do:

Step 1. Enabling concurrency checking on an Entity

Right-click the .edmx file and choose Open With...
Choose XML (Text) Editor in the popup dialog, and click OK.

Locate the App entity in the ConceptualModels. I suggest toggling outlining and just expanding tags as necessary. You're looking for something like this:

<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    ...
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="YourModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
        <EntityType Name="App">

Under the EntityType you should see a bunch of <Property> tags. If one exists with Name="Status" modify it by adding ConcurrencyMode="Fixed". If the property doesn't exist, copy this one in:
```
<Property Name="Status" Type="Byte" Nullable="false" ConcurrencyMode="Fixed" />
```
Save the file and double click the .edmx file to go back to the designer view.

Step 2. Handling concurrency when calling `SaveChanges()`

SaveChanges() will throw one of two exceptions. The familiar UpdateException or an OptimisticConcurrencyException.

if you have made changes to an Entity which has ConcurrencyMode="Fixed" set, Entity Framework will first check the data store for any changes made to it. If there are changes, a OptimisticConcurrencyException will be thrown. If no changes have been made, it will continue normally.

When you catch the OptimisticConcurrencyException you need to call the Refresh() method of your ObjectContext and redo your calculation before trying again. The call to Refresh() updates the Entity(s) and RefreshMode.StoreWins means conflicts will be resolved using the data in the data store. The DownloadCount being changed concurrently is a conflict.

Here's what I'd make your code look like. Note that this is more useful when you have a lot of operations between getting your Entity and calling SaveChanges().

    private void IncreaseHitCountDB()
    {
        JTF.JTFContainer jtfdb = new JTF.JTFContainer();

        var app =
            (from a in jtfdb.Apps
             where a.Name.Equals(this.Title)
             select a).FirstOrDefault();

        if (app == null)
        {
            app = new JTF.App();
            app.Name = this.Title;
            app.DownloadCount = 1;

            jtfdb.AddToApps(app);
        }
        else
        {
            app.DownloadCount = app.DownloadCount + 1;
        }

        try
        {
            try
            {
                jtfdb.SaveChanges();
            }
            catch (OptimisticConcurrencyException)
            {
                jtfdb.Refresh(RefreshMode.StoreWins, app);
                app.DownloadCount = app.DownloadCount + 1;
                jtfdb.SaveChanges();
            }
        }
        catch (UpdateException uex)
        {
            // Something else went wrong...
        }
    }

Qqbt · Accepted Answer · 2011-09-06T13:26:05.480

1

You can prevent this from happenning if you only query the download count column right before you are about to increment it, the longer the time spent between reading and incrementing the longer the time another session has to read it (and later rewriting - wrongly - incremented number ) and thus messing up the count.

with a single SQL query :

UPDATE Data SET Counter = (Counter+1)

since its Linq To Entities,it means delayed execution,for another session to screw up the Count (increment the same base,losing 1 count there) it would have to try to increment the app.Download count i beleive between the two lines:

    else
    {
        app.DownloadCount += 1; //First line
    }

    jtfdb.SaveChanges();  //Second line
}

thats means that the window for the change to occur, thus making the previous count old, is so small that for an application like this is virtually impossible.

Since Im no LINQ pro, i dont know whether LINQ actually gets app.DownLoadCount before adding one or just adds one through some SQL command, but in either case you shouldnt have to worry about that imho

edited Sep 06 '11 at 13:26

answered Sep 06 '11 at 11:46

Qqbt

802
2
8
33

thanks Andris. Is there a better way of doing this: List parms = new List(); parms.Add(new SqlParameter("appname", this.titleLabel.Text)); jtfdb.ExecuteStoreCommand("update apps set downloadcount = downloadcount + 1 where name = @appname", parms.ToArray()); – Fidel Sep 06 '11 at 12:10
When calling the `FirstOrDefault` method, the framework will execute a `SELECT` and retrieve that object from the database. It doesn't operate of the column insitu. – Kirk Broadhurst Sep 06 '11 at 14:00
1

@Fidel - The better way of doing that would be to create a Stored Procedure which you can add to your Entity Framework model. That way it is strongly typed and contained with all other database objects. – Kirk Broadhurst Sep 06 '11 at 14:01

score 0 · Answer 3 · answered Sep 06 '11 at 13:51

You could easily test what would happen in this scenario - start a thread, sleep it, and then start another.

else
{
    app.DownloadCount = app.DownloadCount + 1;
}

System.Threading.Thread.Sleep(10000);
jtfdb.SaveChanges();

But the simple answer is that no, Entity Framework does not perform any concurrency checking by default (MSDN - Saving Changes and Managing Concurrency).

That site will provide some background for you.

Your options are

to enable concurrency checking, which will mean that if two users download at the same time and the first updates after the second has read but before the second has updated, you'll get an exception.
create a stored procedure that will increment the value in the table directly, and call the stored procedure from code in a single operation - e.g. IncrementDownloadCounter. This will ensure that there is no 'read' and therefore no possibility of a 'dirty read'.

entity framework - does this do a dirty read?

3 Answers3

Step 1. Enabling concurrency checking on an Entity

Step 2. Handling concurrency when calling SaveChanges()

Step 2. Handling concurrency when calling `SaveChanges()`