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.