Detecting Concurrency Issues with Entity Framework

• Download example project - 187.3 KB

Introduction

Entity Framework uses optimistic concurrency, i.e., no rows in a table can be locked for editing.
Consider the following scenario:

• User A opens record X for editing
• User B also opens record X for editing
• User B saves its edited data
• User A saves its edited data

Record X now contains the data edited by user A and user B's edits are lost.
This tip aims to show a way of detecting concurrency issues like the one above, using features of Entity Framework and SQL Server.

Background

The problem addressed in this tip is from a specific scenario I was working on. The scenario included working with EF using the model first approach in a disconnected environment, a web application.

Using the Code

To illustrate the scenario, we use a simple ASP.NET MVC web application.
Since we use model first, we create a conceptual model of the database in the EF designer, containing a very simple entity:

The database is created and populated when running the application, if it doesn't exist.

Running into the Problem

When the web application starts, the one record in the database is read and presented in a very simple "edit" UI:

Changing the cars properties and pressing "Save", we can see that the "Current values" change, reflecting that the record has been changed in the database.

To simulate a change of the record by another user, the "Edit" button can be pressed. Doing this changes the cars brand to "Porsche", which is indicated by the current values.
Once again, changing the cars properties and pressing "Save" updates the record with the new values and the change of brand to "Porsche" is lost, as indicated by the current values.

Handling the Problem

To detect this concurrency issue, we need to use some features of EF and SQL Server. First off, we need a column in our car-table which acts as a "concurrency indicator". For this, we use the special SQL Server column type "timestamp". The value of a timestamp column is automatically updated every time a record is updated.

Unfortunately, a column of this type cannot be inserted through the EF designer, so another technique has to be used. Using e.g. SQL Server Management Studio, we add a timestamp column to the cars-table:

To make EF aware of this change, we go into the EF designer, right click and select "Update model from database...". Select the "Refresh" tab and press "Finish". The new column is now reflected in the entity:

The second step is to tell EF that this column is our "concurrency column". This is done by changing the "Concurrency mode" in the columns properties to "Fixed":

Restarting the application, pressing "Edit", changing car properties and pressing "Save" now gives a message about the car has been changed by someone else and current values remain unchanged.

How It Works

When we try to update the record, EF compares the value in the "Concurrency" column in the record in the database with the value in the entity we supply. If they don't match, we have a concurrency violation.
Since we use a timestamp column, the value was updated when we did "Edit" and it no longer matches the value of the car entity that is being saved.

The Code

In our controller, we have two methods of interest. The first one is the default action, which presents the "edit" view:

public ActionResult Home() 
{ 
    ViewBag.Message = TempData["Message"]; 
    return View(modelContainer.Cars.First()); 
}  

Here the car-record is read from the database and served to the view.

Next up is the save action:

[HttpPost] 
public ActionResult Save(Car car)
{     
    modelContainer.Cars.Attach(car);
    modelContainer.Entry(car).State = EntityState.Modified;
 
    try 
    {
          modelContainer.SaveChanges();
    }
    catch(DbUpdateConcurrencyException)
    {
        TempData["Message"] = "Car changed by someone else! View data updated.";
    }
 
    return RedirectToAction("Home");
} 

Here, we update the entities values with the ones supplied from the view. If there's a concurrency violation, we handle the DbUpdateConcurrencyException.

Conclusion

Using the above technique allows us to detect concurreny violations when updating records. The risk of such violations occuring depends on the application at hand, but even the smallest chance of it happening probably means that you must handle it in a controlled fashion.

The next question is of course what to do when you detect a concurrency violation. The answer is, it depends. In my simple example, I just show a message and display the current values of the record, meaning that the data entered in the view is lost. More sophisticated handling can of course be implemented, if the application needs it. Anyway, it is out of scope for this article.

Using the Example Project

If you download and build the example project, it must be adapted to your environment. Since a database is involved, you must set the correct connection string for your database server in web.config.

The example project is initially setup for the scenario in "Running into the problem" above. To go on and run the scenario in "Handling the problem", change the "Concurrency mode" setting for the entities "Concurrency"-column to "Fixed".

History

• March 14, 2014: Initial version published