Manage Concurrency in Entity Framework and MVC3 application

Why Need Concurrency

Consider a case when there are two users A and B working one same record; User A opens a record to update it. He started modifying some data. But before he completes and saves updated data another user B opens the record edit it and updates the record. In this case both users get success message but as user A has updated the record lastly so his changes will preserve but the changes done by user B will get  lost. This case may be acceptable for some applications but in some applications even a single change is crucial and we should handle such case very carefully.

Optimistic Concurrency

From Wiki

Optimistic concurrency control (OCC) is a concurrency control method that assumes that multiple transactions can complete without affecting each other, and that therefore transactions can proceed without locking the data resources that they affect. Before committing, each transaction verifies that no other transaction has modified its data. If the check reveals conflicting modifications, the committing transaction rolls back

Entity Framework provides support for optimistic concurrency model, It means when we use EF to save data in database there are no locks held on database (It has advantage of performance) but before data saved it checks in weather data from the database has been changes since it is read; if it is same then save operation completes and If it is changed it throws exception.

Let us see how to use concurrency of EF in MVC application

Every column in the EF table has concurrency property as show below

•  Step 1 Create MVC 3  application. (it is assumed that you know how to create MVC 3 application)

• Sep 2 Create Database ; While creating database table add column with name TimeStamp and data type as timestamp as shown below

 

Value in column with data type timestamp is updated every time a row containing a timestamp column is inserted or updated.

• Step 3 add ADO.NET Entity Model to the solution; After adding EF edmx  file above table appears as below  

 

Here TimeStamp column has data type Binary and StoreGernatedPattern as Computed.

 StoreGernatedPattern can have any of the following value

• None: It is default and it means value of the column is not generated.
• Identity:  It means  when entity is inserted its value is automatically filled by the database it is generally used as primary key.
• Computed: It means when ever entity is updated or inserted database shall generate the new value and update it.

• Step 4 Change the Concurrency Mode to Fixed as shown below.

 

Concurrency Mode can have any of following value

• None : It is default value; it means this property is not involved in any concurrency check (This mean its value is not checked before save data in database for changes since it is read).
• Fixed: it means its original value is checked for changes since it is read for concurrency check. This is done using WHERE clause.

• Step 5 Create controller , view for list, add, and edit mode. (It is assumed that you know how to create view and controller in MVC, One quick way to create default view and controller is right click Controller folder and click Add.. - > Controller)

• Step 6 In Edit view for the Employee add following hidden control.

@Html.HiddenFor(model => model.TimeStamp)

This step is required for MVC application ; Whenever we read the record/entity for edit view we get the value stored in TimeStamp field and we store it in the hidden field; So that when user click submit button  TimeStamp value goes in Model along with other updated values.

This passed value is automatically included by EF in Where clause as we have set the Concurrency Mode to Fixed.

We are done with the application now Let us test it.

Run the application and Go to Employee Edit screen; Do some changes and before pressing the save button open other instance of a browser and open same employee in Edit screen and do some changes and save it. Now go to first browser window and click save button it will throw an exception as shown below.

This caused OptimisticConcurrencyException ;  This is because when we saved in second browser instance the value of TimeStamp field is changes to some other value  (as timestamp field get updated on each update and insert) and  when we clicked Save on first browser it sent old TimeStamp value in where clause and they didn’t match. To give user friendly error message we will handle the exception as shown below.

 

Above code catches the OptimisticConcurrencyException and add error to ModelState To Display this error on UI add following line on View

@Html.ValidationMessage("Error");