Backend Entity Framework SQL

Dealing with optimistic concurrency using Entity Framework

The letter „U” in CRUD means Update. Is that something hard to do in our applications? Of course not! We simply get some data, change the value of selected columns and save our changes. That’s it. However, sometimes thing may not go so smooth, especially when we (as programmers) wouldn’t predict some edge-case scenarios. One of this is updating same data by many users at the same time. That’s why today’s post will be dedicated to the concept of optimistic concurrency and implementing that using Entity Framework. Let’s start!

 

Pessimistic concurrency vs. optimistic concurrency

Before we move to the implementation, it’s important to understand what is the optimistic concurrency and when should we use that. To make it clearer let’s introduce some example. So, imagine two administrators of some online bookstore. For simplicity let’s call them Frank and Carl. Now, Frank’s job is to process the requests coming from the bookstore users e.g. „I want to change the email address on my account.” On the other hand, Carl is responsible for detecting the suspicious activities and banning those users who seem to be untrusted. One day Frank receives a request from the user with ID equals 7369. The message says „Please change my email address to user7369@email.com”. Frank opens the admin panel and looks for the proper user. Then clicks „EDIT” button. Now he just processes the request using the form. At the same time, Carl detects that some user tried to scam a book from their store using some devils techniques. Guess what, the user’s ID is 7369. Now to ban the user he also looks for the user in an admin panel and clicks the „EDIT” button. Then he checks the little „User banned” checkbox at the bottom of the form and saves his changes. Poof, the user, is now banned and the bookstore is safe again. But slightly after that, Frank is ready to submit his email changes, but here comes the problem. In his data, the little checkbox is unchecked. What’s gonna happen?! Now here we come to the concept of pessimistic and optimistic concurrency.

One way to deal with such a concurrency is to give to Frank exclusive access to the row in the database, and lock it for the others. It’s like saying „ok table now I want to change some email on a row with ID equal 7369. Do not allow any other person to update that until I’m done”. That’s called a pessimistic concurrency. There are several lock type in MS SQL Server and if you’re interested in that just click here. Now that sounds like a great solution, right? Well, not completely because there are some drawbacks like performance, deadlocks, etc. That’s why sometimes is better for us to accept the possible concurrency but then we need to secure our system for that kind of scenarios. It’s called an optimistic concurrency. Now the question is, how can we handle the optimistic scenario? There are at least two solutions:

 

  • We do absolutely nothing. Just let users do what the want without any control. The pros is that’s easy to implement because it requires no special code at all. The drawback is that it may cause the lost update like in the example above. As you probably guess, that’s not what we are going to achieve 馃檪
  • We keep the version of the each row in the database and during the update, we compare this version with a version that we selected in the past. If they’re equal, that means that no update was executed during that time. If they’re different, we rollback the transaction and inform the user that someone modified his data. That’s a simple concept, but it’s also really efficient. Fortunately, the Entity Framework provides a solution which helps developers dealing with versioning of their data using the technique called Concurrency Tokens. Let’s move forward to the implementation!

 

The implementation

The implementation is going to be simple, and it requires only three steps. First, we need to add a column to the entity that we want to version. The implementation below shows the example:

 


class聽User
{
聽聽聽聽public聽int聽Id聽{聽get;聽set;聽}
聽聽聽聽public聽string聽Names聽{聽get;聽set;聽}
聽聽聽聽public聽string聽Email聽{聽get;聽set;聽}
聽聽聽聽public聽bool聽IsBanned聽{聽get;聽set;聽}聽

聽聽聽聽[Timestamp]
聽聽聽聽public聽byte[]聽Version聽{聽get;聽set;聽}
}


 

As you can see it’s a byte array (I know, that might look weird) called Version with an attribute called Timestamp. Now we need to do is to inform the EF that we want to use our column as mentioned Concurrency Token. For that purpose, we’re going to use a fluent API and OnModelCreating method provided by a DbContext class:

 


protected聽override聽void聽OnModelCreating(DbModelBuilder聽modelBuilder)
聽{
聽聽聽聽聽modelBuilder.Entity<User>().Property(i聽=>聽i.Version).IsConcurrencyToken();
聽聽聽聽聽base.OnModelCreating(modelBuilder);
聽}


 

Now, all we need to do is to insert some data and see how it works! I’ve already added three rows into our table which look as follows:

 

example1

 

As you can see, each row has its version stored as binary data. Now let me update an item with ID equal 2:

 

example2

 

The version has changed automatically. Now let’s test our concurrency. To achieve this without any help, I did the following steps. I got a user using the IQueryable<User>. Then I updated some properties and stopped the program on SaveChanges method (using breakpoint). Then I updated the same data directly in MS SQL Server. Now because of UPDATE in the database the version of the row changed. The code and a result are shown below:

 

static聽void聽Main(string[]聽args)
{
聽聽聽聽using聽(var聽context聽=聽new聽Context())
聽聽聽聽{
聽聽聽聽聽聽聽聽var聽user聽=聽context.Users.FirstOrDefault(i聽=>聽i.Id聽==聽2);

聽聽聽聽聽聽聽聽user.Names聽=聽"test";

聽聽聽聽聽聽聽聽try
聽聽聽聽聽聽聽聽{
聽聽聽聽聽聽聽聽聽聽聽聽context.Entry(user).State聽=聽EntityState.Modified;
聽聽聽聽聽聽聽聽聽聽聽聽context.SaveChanges();聽//here聽I聽stopped聽and聽change聽data聽in聽the聽SQL聽Server
聽聽聽聽聽聽聽聽}
聽聽聽聽聽聽聽聽catch聽(Exception聽ex聽)
聽聽聽聽聽聽聽聽{
聽聽聽聽聽聽聽聽聽聽聽聽Console.WriteLine(ex.Message);
聽聽聽聽聽聽聽聽聽聽聽聽throw;
聽聽聽聽聽聽聽聽}
聽聽聽聽聽聽聽聽
聽聽聽聽}
}

 

example3

 

As you can see the EF threw the exception of type DbUpdateConcurrencyException. Now we can catch that and handle as we wish. That’s it!

 

Summary

I hope that you enjoyed that post and it’ll help you one day. In the next week, we are going to end our CQRS/ES journey so remember to visit my blog and read that! If you, however, prefer a social media rather than some reminders (like I do) follow me on Twitter and Facebook 馃檪 It’ll let you be up to date with new posts!