Dealing with Optimistic Concurrency in Entity Framework - dotnet

Welcome to Logically Proven Blog.

This post teaches you “How to deal with Optimistic Concurrency in Entity Framework”.

In a real world situation it is quite possible that database values might have got changed after selecting them for modification. In such cases, your update operation might be overwriting changes made by someone else. That is why it becomes important to detect if concurrency violation has occurred.

Detecting Concurrency Violation:

Entity framework can be configured to use optimistic concurrency while updating database records. That means no locks are held on the data being modified. The data is updated only if the database values at the time of fetching the data and the database values currently stored in the database match.

In your SQL Server database table, just add an extra column “UpdateToken” of type rowversion. The rowversion data type ensures that the column holds a different value every time that record is updated. A rowversion column is automatically updated by the SQL Server and it won’t play any direct role in your application data. It allows us to detect if a record has been changed after the initial selection. However, merely adding a rowversion column is not sufficient. In the entity framework designer you also need to set the ConcurrencyMode property of the UpdateToken to Fixed.

Changing this setting will cause EF (Entity Framework) to use the UpdateToken column in the WHERE clause of the UPDATE queries it generates. This way a row is updated only when the value of UpdateToken at the time of initial fetch matches with the current value in the database. If they don’t match no record is updated and EF concludes that there was a concurrency violation. 

Code: 

public void DetailsView1_UpdateItem(int EmployeeID)
{
 EmployeeDbEntities db = new EmployeeDbEntities();
 Employee item = db.Employees.Find(EmployeeID);
 TryUpdateModel(item);
 System.Threading.Thread.Sleep(15000);
 try
 {
  db.SaveChanges();
 }
 catch (DbUpdateConcurrencyException ex)
 {
  lblErr.Text = ex.Message;
 }
}

The code then halts the execution for 15 seconds. This is done purely for the sake of testing. Adding this delay will allow you to switch to the physical database table and modify its data manually to test the concurrency violation. An alternative is set a breakpoint at SaveChanges() method to halt the execution and then change the database values manually.

Handling Concurrency Violation:

Once you detected concurrency violation, the next step is to decide what action to take. There are three approaches that you can take:

• Do nothing. Simply show the error message to the user and exit from the data modification stage.
• Update the entity under consideration from the latest data from the database table. Then show that data to the user so that he can take the necessary action.
• Forcefully update the database table with the values from the entity.

The first approach is quite easy and that is what you used in the preceding example. The second approach requires you to load database values in an entity and present them to the user. You can do that in the catch block as shown below:

...
catch (DbUpdateConcurrencyException ex)
{
 lblErr.Text = "Concurrency violation! Please review the latest values shown above.";
 ex.Entries.Single().Reload();
 DetailsView1.ChangeMode(DetailsViewMode.ReadOnly);
}

As you can see the DbUpdateConcurrencyException class provides access to the entity causing the error. The Reload() method called on an entity loads the values from the database into that entity. You then change the mode of the DetailsView to ReadOnly so that user can read the new values.

In the third approach, you make the current database values as the original values and then attempt SaveChanges() again. This approach is as shown below:

...
catch (DbUpdateConcurrencyException ex)
{
 lblErr.Text = "Concurrency violation! Attempting to force save to the database.";
 var emp = ex.Entries.Single();
 emp.OriginalValues.SetValues(emp.GetDatabaseValues());
 db.SaveChanges();
}

Here, you set the OriginalValues of the entity under consideration to the current values from the database (obtained by calling GetDatabaseValues()). You then attempt SaveChanges() again.

These kinds of concurrency detections are very much useful when the updates to the table are from multiple places. For example, consider a banking application.

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.

Logically Proven,

Learn, Teach, Share

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.