Have you ever got a call from a user telling you that he/she edited a record
and that the changes made are lost? If so, maybe your application is
implementing "Last in Wins" concurrency control.
This article focuses on the "Optimistic concurrency control", doing it
manually with out the
DataSet. On a second part, I will focus on
"Pessimistic concurrency control".
There are three types of concurrency control:
- 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
- "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
The full article can be found here.
Pessimistic concurrency control
While in a disconnected architecture the "Pessimistic concurrency control"
can not be implemented using database locks, it can be implemented as
- Using lock-bits at the table row level and maintain them in a session pool.
- Clear the lock-bits when the user leaves the page under program control.
This has to be done to free the locks as soon as possible, but there is no
guarantee that this occurs.
- As the user can leave the browser or the site at any moment, we have to
clear the lock-bits stored is the session pool on
- And as a final countermeasure, we might have a demon running on the server
cleaning old locks.
We have to keep in mind that an application that holds locks for long periods
is not scalable, but this concurrency control schema might have to be
implemented on some portions of the application.
"Last in wins"
To implement this concurrency control, we do not have to do anything. But
this might be unacceptable in some circumstances if different users start to
access the same records frequently.
As explained at MSDN:
- User A fetches a record from the database.
- User B fetches the same record from the database, modifies it, and writes
the updated record back to the database.
- User A modifies the 'old' record (or just clicks on Accept) and writes it
back to the database.
In the above scenario, the changes user B made were never seen by user A. Be
sure that this situation is acceptable if you plan to use the "Last in wins"
approach of concurrency control.
Optimistic concurrency control
This article focuses on this approach and I will go deeply through it.
The optimistic concurrency control is based on a Record Version.
Several users can open a page for the same record, but only the first one to
save it will succeed.
How it works
- User A fetches a record from the database along with its version, more on
- User B fetches the same record from the database, also with its version and
writes the updated record back to the database, updating the version number.
- User A "tries" to write the record to the database, but as the version on
the database is different from the version hold by user A, the write fails,
leaving user B's changes intact.
The record "Version"
DataSet uses the "All values" approach, comparing old vs. new
values for all the fields. This gives a version for the record, but if you are
not using the
DataSet, it can be a nightmare. First, you have to consider the
possible null values and the
where clause has to be changed if a new field is
added to the table.
Instead of "All values", we can use a single field to set the record version.
For this purpose we can use a GUID string or a date-time value. I decided to
use the date-time for the following reasons:
- The GUID is not portable if we want to port the code to other platforms.
- The GUID uses 40 bytes instead of the 8 bytes used by the date.
- The date-time tell us when the record was last updated.
Checking for optimistic concurrency
When updating the record, the version has to be added to the
UPDATE table SET fields = values WHERE pk
= pk_vlaue AND concurrency = concurrency_value
If the concurrency value given by the user does not match the concurrency
value in the database, the record will not be updated and 0 rows affected will
SELECT we have to consider that, while a user was
modifying the record, another user might have deleted the same record. In
this case, the rows affected will also be 0.
If you want to give the user an accurate message about the situation, we can
not tell that the record was updated by another user if it was deleted. To solve
this, we have to check if a record with the primary key exists in the table.
SELECT COUNT(*) FROM table WHERE pk = pk_value
At this point, if the record count is zero, the record was deleted, other
wise; a concurrency exception has to be thrown.
Handling the user interface
Finally, we have to inform the user that his/her changes were not successful
because another user changed the data.
What happens if user A changed 10 fields and user B (who saved first) only
changed one? Will user A loose all the 10 fields?
The approach implemented here, is to reload the page and replace only the
data that was modified by user B, leaving user A's changes intact as
The example code was developed to use the Northwind database installed with
SQL Server. The following stored procedures need to be created (The stored
procedures are in the zip file):
Add to the
Products table the field
Concurrency field to some value. Example: 01/01/2003 for all
the records. It will not work with
null values. When you create the
method, it has to set
The connection string
Set the appropriate
pwd values in the
value="Data Source=(local);uid= ;pwd= ;database=Northwind"/>
Installing the application
You have to setup manually the virtual directory where the application will
run. This can be done with "Internet Information Services" or just from the
Windows Explorer. Right click on the folder, press properties, go to the "Web
Sharing" tab and select "Share this folder".
How it works
- The product list is loaded and shown on Default.aspx
- When "Edit" is clicked on a product, the Product.aspx page is loaded with
the product ID to edit in the parameter
Page_Load() is called and the categories and suppliers combo
boxes are loaded.
- The product's data is loaded and set to the web controls. Note that at the
end of the procedure, the
SetConcurrencyObject(product) method is
called. This stores the object as
BLL.CRUD, as it was read from the
database so it can be compared later in case of a concurrency conflict.
- When the user clicks the "Accept" button, the
Save() method is called. At this point is where the concurrency
Update method is called on the product:
Response.Redirect("Error.aspx?msg=" + System.Web.HttpUtility.UrlEncode
("The product has been deleted by another user."));
catch (Exception ex)
If the product was deleted by another user, the flow is redirected to the
error page. If there was a concurrency exception, the method
ConcurrencyException() is called to process the exception and
display the fields that are conflicting.
private void ConcurrencyException()
Hashtable controls = GetControlsMap();
lblConcurrencyMsg.Visible = true;
GetControlsMap(), gets a one-to-one mapping between
the web controls and the object properties. This allows changing the control's
look & feel if there is a concurrency conflict with any object's
Any page that needs to process a concurrency exception has to inherit from
The heart of the concurrency handle is at
The original object as it was read is retrieved from the view state:
BLL.CRUD userObject = (BLL.CRUD) ViewState[CONCURRENCY_OBJECT];
Then the object's new data has to be read so it can be compared with the
original data read before the concurrency conflict. This is done with generic
code, invoking the
Read() method on a
BLL.CRUD object; to do this,
the objects must inherit from
Type type = userObject.GetType();
BLL.CRUD dbObject = (BLL.CRUD) type.Assembly.CreateInstance(type.FullName);
dbObject.ID = userObject.ID;
Response.Redirect("Error.aspx?msg=" + System.Web.HttpUtility.UrlEncode
("The record has been deleted by another user."));
Once the new object is read, the differences are gotten using reflection:
IList differences = BLL.ObjectDifference.GetDifferences(dbObject,
At last, the web controls styles are changed to show the user, the
foreach (BLL.ObjectDifference diff in differences)
WebControl ctrl = controls[diff.PropertyName] as WebControl;
if (ctrl != null)
The stored procedures
All the stored procedures are basically
SELECTs to retrieve data. So I will
only focus on
CREATE PROCEDURE ProductsUpdate
ProductName = @Name,
SupplierID = @SupplierID,
CategoryID = @CategoryID,
QuantityPerUnit = @QuantityPerUnit,
UnitPrice = @UnitPrice,
UnitsInStock = @UnitsInStock,
UnitsOnOrder = @UnitsOnOrder,
ReorderLevel = @ReorderLevel,
Discontinued = @Discontinued,
Concurrency = GETDATE()
Concurrency to the server's date
ProductID = @productID AND
Concurrency = @Concurrency
IF @@ROWCOUNT = 0
IF EXISTS( SELECT ProductID FROM products
WHERE ProductID = @productID )
RETURN 2 -- Concurrency conflict
RETURN 1 -- The record has been deleted
RETURN 0 -- The record could be updated
You can notice that the
WHERE clause for the
Concurrency are queried together. The
ProductID is the primary key and the
Concurrency field guaranties that we are
not updating a record modified by another user since the data was gathered.
@@ROWCOUNT is greater than zero (It should be 1 because
of the primary key), the record could be updated without concurrency
@@ROWCOUNT is zero, there are two possibilities:
- The record was deleted by another user, which is checked by
- There is a concurrency conflict.
For each case, the stored procedure returns a value:
0: The record could be updated
1: The record has been deleted
2: Concurrency conflict
When the update query is executed, the return values are checked in
Update() and the appropriate exception is thrown.
switch ( (UpdateRecordStatus) parms.Value)
throw new DBConcurrencyException("The record has
been modified by another user or process.");
throw new DeletedRowInaccessibleException();
Trying it out
To try out the concurrency control:
- Open two different instances of the application.
- Click "Edit" for the same product on both pages.
- Change some values on the first page and "Accept" it.
- Change some values on the second page and "Accept" it.
When accepting the second page, the fields that were changed on the first page will
become gray and the fields that are not conflicting will be unchanged.
Limitations and things to improve
- The BLL objects must have as a primary key only one field. This is normally
how I design the tables, but there might be an exception to this.
- Use styles (CSS) to improve the display of concurrency conflicts.
Concurrency control increases the application's complexity, debugging and
maintenance, but your users will have a better experience using it.
Remember that it is not mandatory to implement the optimistic concurrency
control for all the application updates. You might need to mix the 3 mechanisms
within a single application based on the needs.
- August 20, 2003 - version 1.0