Data Concurrency in ADO.NET - Part I

When multiple users attempt to modify data at the same time, controls need to be established in order to prevent one user's modifications from adversely affecting modifications from simultaneous users. The system of handling what happens in this situation is called concurrency control.

Lets examine a Real World Scenario to better understand the problem.

If you have a Sales Application and a Salesman A is updating the record of a Customer's purchases into the Sales Table. Simultanenously, if Sales Man B is querying the Sales Table for the Customer's record to perform some other operation, the record that Sales Man B gets is not stable since it has been changed by Sales Man A. This kind of scenario is what leads to concurrency issues in a Database Management System.

The resolution to resolve this kind of an issues is to enforce Locks on the Data. Locks are basically a control over the Data such that no two applications/users modify the same record, simultaneously.

In general, there are three common ways to manage concurrency in a database:

Pessimistic concurrency control - a row is unavailable to users from the time the record is fetched until it is updated in the database.

Optimistic concurrency control - a row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made. Attempting to update a record that has already been changed results in a concurrency violation.

"Last in wins" - a row is unavailable to other users only while the data is actually being updated. However, no effort is made to compare updates against the original record; the record is simply written out, potentially overwriting any changes made by other users since you last refreshed the records.

ADO.NET and Visual Studio .NET use optimistic concurrency, because the data architecture is based on disconnected data. Therefore, you need to add business logic to resolve issues with optimistic concurrency.

There are two ways to handle the issues with optimistic concurrency. They are:

The Version Number Approach
Saving All Values Approach


We will examine each of these methods in
Part II of this article.

Cheers and Happy Programming !!!a

Print | posted on Thursday, July 7, 2005 1:12 PM

Comments on this post

# re: Data Concurrency in ADO.NET - Part I

Requesting Gravatar...
By default concurrency control in ado.net is optimistic but if we want that concurrency control should be pessimistic or last in wins than in that case what should i do.....
Left by Rohit Daga on Nov 09, 2005 7:40 AM

Your comment:

 (will show your gravatar)