Registration open for ASP.NET Core and Angular instructor-led online courses. Courses conducted by Bipin Joshi on weekends. Click here for more details.

Optimistic Concurrency and DataS

Optimistic Concurrency and DataSet Updates

Introduction

In any disconnected environment the problem of concurrent updates is obvious.
Updates via DataSet is no exception. There are various solutions to tackle with
this issue and which one to use depends upon your application, requirements and
database schema. In this article I am going to illustrate three commonly used
ways to deal with concurrency issues.

Understanding the problem

Before we go ahead with the possible solutions, let's understand the problem.

You create a DataSet and populate it with data from database.

You perform updations and deletions on the data.

Now you are ready to update the data back to the database.

However, while you were processing the data somebody else updates the
same data in the database.

When you issue the update the data (which is changed by somebody else!)
is overwritten.

In other words the data at the time of update is not the same as it was at
the time you fetched it. This situation is referred as concurrency violation and
the concurrency is said to be "Optimistic".

There are few points that you should think of:

Is it OK with your application to overwrite such data (which is modified
by somebody else)?

Do you want to cancel your updates if such violation is observed?

Do you want to ask the user whether he/she wants to overwrite the data?

Possible Solutions

Any solution to concurrency problem should make sure that the data at the
time of update is the same as it was when you fetched it. Here are some common solutions that you can implement:

Update using ALL the fields in WHERE clause: In this approach you
include all the fields from SELECT statement in the WHERE condition of
UPDATE statement. This can be achieved in two ways either configuring the DataAdapter manually or using CommandBuilder.

Updating using SOME fields in WHERE clause: In this approach only
few fields from SELECT statement are included in WHERE condition of UPDATE
statement.

Updating based on a TIMESTAMP column: In this approach you
add a timestamp field to your table and compare its values before updating
the row.

Sample Application

A sample application is available for download along with this article which
illustrates all of the above solutions. You will need Northwind database of SQL
server in order to work with the examples. Note that in order to work with
timestamp example you need to add a column called TSID to the Employees table of
Northwind database.

Bipin Joshi is a software consultant, trainer, author and yoga mentor having 22+ years of experience in software development. He also conducts online courses on ASP.NET MVC, ASP.NET Core and Design Patterns. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Ajapa Yoga to interested individuals. To know more about him click here.