Optimistic Concurrency and DataS
Optimistic Concurrency and DataSet Updates
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!)
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?
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
- Updating based on a TIMESTAMP column: In this approach you
add a timestamp field to your table and compare its values before updating
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