EF Concurrency Mode Fixed + MVC

Imagine a very simple EDM to describes web advertisements:

Now imagine that I’d like to build a web application to manage instances of the Ad type. If multiple people are editing ads at once, especially the same set of ads, I’m likely to run into concurrency errors. By default, EF lets the last change win.

For example, if Chris and Bill are both editing Ad.Id == 1, if Chris pushes his changes to the database first, EF will not notice that the ad has been updated underneath Bill will he saves his changes and Chris’s changes will be lost. What we really would like to happen is that, when Bill attempts to save his changes, that we check if the data has changed since we cached it so that Bill gets an error and is able to merge his changes in with Chris’s.

This style of multi-user concurrency management is called “optimistic concurrency” because it assumes few people will be changing the same data at the same time. It’s the most efficient means of concurrency management when that condition is true. Another type of concurrency management is named “pessimistic concurrency,” and is generally implemented using locks on the database, which tends to slow things down.

By default, EF provides no concurrency support; if two people push changes to the same row in the database, whoever’s change goes in last wins. This results in data loss, which in the world of data is a big, fat, no-no.

The way that EF lets you decide how a row is changed is via the Concurrency Mode property on every one of the entity’s properties in the designer. By default, the Concurrency Mode is set to “None”, which results in SQL like the following when an update is needed:

update [dbo].[Ads]
set [Title] = @0, [ImagePath] = @1, [Link] = @2, [ExpirationDate] = @3
where ([Id] = @4)

The Id column is used to select whether to perform an update, so any changes made to the underlying columns for that row are not detected and are therefore lost. The way to tell EF which columns to check is with the Concurrency Mode property set from None (the default) to Fixed on an entity’s property. For example, if you set Concurrency Model to Fixed for each of the read-write properties for our sample Ad entity, the update would look like the following:

update [dbo].[Ads]
set [Title] = @0, [ImagePath] = @1, [Link] = @2, [ExpirationDate] = @3
where ((((([Id] = @4) and ([Title] = @5)) and [ImagePath] is null)
and ([Link] = @6)) and ([ExpirationDate] = @7))

This is handy, but it also requires that we keep around an entity in memory in both its original state and its updated state for the length that the user is editing it. For desktop applications, that’s not an issue, but for stateless web pages, like MVC-based web pages, it is.

It’s for this reason that the EF team itself recommends using a special read-only column just describing the “version” of the row. Ideally, whenever any of the data in a row changes, the version is updated so that when an update happens, we can check that special column, e.g.

update [dbo].[Ads]
set [Title] = @0, [ImagePath] = @1, [Link] = @2, [ExpirationDate] = @3
where (([Id] = @4) and ([TimeStamp] = @5))

Here, the TimeStamp column is our “version” column. We can add such a column in our SQL Server database using the “timestamp” type, as shown in SQL Server Management Studio here:

The semantics of the timestamp type are just what we want: every time a row is updated, the timestamp column is updated. To see this new column in the Entity Data Model, you’ll have to right-click on the designer surface and choose Update Model from Database, which results in the TimeStamp being added to our model:

The TimeStamp field will come through as type Binary, since EF4 doesn’t have direct support for it, and with a StoreGeneratedPattern of Computed (which is exactly right). To enable EF to use the new column to perform optimistic concurrency, we need only change the Concurrency Mode to Fixed.

Now, here’s a simple Edit method on our MVC controller:

// GET: /Ad/Edit/5
public ActionResult Edit(int id) {
  return View(db.Ads.Single(ad => ad.Id == id));
}

This kicks off the view, but with one key missing ingredient — the view doesn’t have the TimeStamp field in it; because it’s mapped in EF as binary data, the MVC form generator wouldn’t provide a field for it. To make sure we pass the version of the data along with the data itself, we have to add a field to our HTML form and, because we don’t want the user to see it, let alone edit it, we need to make it hidden:

<% using (Html.BeginForm()) {%>
...
<%: Html.HiddenFor(model => model.TimeStamp) %>
...
<% } %>

The Html.HiddenFor is an MVC helper that produces HTML that looks like so:

<input id="TimeStamp" name="TimeStamp" type="hidden" value="AAAAAAAAB9E=" />

Now, when we press the Save button, the SQL we saw earlier is invoked to use the ad’s unique ID as well as the version (our timestamp column). If there’s a concurrency problem, i.e. somebody else has updated the underlying row in the database since we cached our values on the HTML form, we get an exception:

The message is saying that no rows were updated, which happens when the timestamp of the underlying row no longer matches. To provide a more helpful message, you’ll want to catch the specific error yourself:

// POST: /Ad/Edit/
[HttpPost]
public ActionResult Edit(Ad ad) {
  try {
    if (!ModelState.IsValid) { return View(); }
    // Attach the ad to the context and let the context know it's updated
    db.Ads.Attach(ad);
    db.ObjectStateManager.ChangeObjectState(ad, EntityState.Modified);
    db.SaveChanges();
    return RedirectToAction("Index");
  }
  catch (OptimisticConcurrencyException ex) {
    ModelState.AddModelError("", "Oops! Looks like somebody beat you to it!");
    return View(ad);
  }
}

Here we’re catching the OptimisticConcurrencyException and setting our own message before sending the user back to their data for them to grab what they want and try again.

Where Are We?

EF works great with MVC, but in the case of optimistic concurrency, you’ve got to work around the stateless model of the web a little to get it working just the way you like.