Accessing and Updating Data in ASP.NET: Using Optimistic ConcurrencyBy Scott Mitchell
Because multiple users can visit the same web page concurrently, it is possible for a user visiting a data modification page to inadvertently overwrite the modifications made by another user. Consider a page with an editable GridView. If two users visit this page simultaneously from different computers and both edit the same row, whomever saves the first will have her changes overwritten by whomever saves the row last. This type of behavior is known as "last write wins" and is the default behavior for web applications.
"Last write wins" is sufficient in applications where it is very rare for two users to be simultaneously working on the same data. If it is commonplace for multiple users to be modifying the same set of data, you should consider implementing some form of concurrency control. There are two flavors of concurrency control: optimistic and pessimistic. Optimistic assumes that concurrency violations are rare and that if such an error occurs that it's adequate to ask one of the conflicting parties to re-enter their information. Pessimistic concurrency, on the other hand, implements policies to ensure that concurrency violations cannot occur. These policies may add friction to the end user's data entry experience.
Microsoft offers a form of optimistic concurrency control from the SqlDataSource control that can be enabled by ticking a checkbox. This article looks at different types of concurrency control and then shows how to implement the built-in optimistic concurrency control offered by the SqlDataSource control. Read on to learn more!
The Need for Concurrency Control
Before we explore concurrency control options and see how to utilize the SqlDataSource control's built-in optimistic concurrency control functionality, let's first take a moment to discuss why concurrency control may be needed.
Consider an online store web application with an administrative interface that presents an editable GridView control with the ability for managers to modify the product name, unit price, and and discontinued status of the inventory. It is company policy to discontinue all items that have a unit price under $5.00. Now, imagine that two managers visit this page at the same time from different computers and both notice that a the product "Scott's Tea" has a unit price under $5.00. The first manager may see this and realize that the product's unit price is incorrect, that is should really be $15.00. She clicks the Edit button and starts to make her change. At the same time, the other manager visits the page and sees the unit price below $5.00 and, noting store policy, decides to mark it as discontinued. He clicks the Edit button and starts to make his change.
What will the end state of the system be? Will "Scott's Tea" be:
- Marked as discontinued with a price of $5.00,
- Have a price of $15.00 and not be discontinued, or
- Marked as discontinued with a price of $15.00?
As noted in the Introduction, this behavior is termed "last write wins," and is the default behavior of the SqlDataSource control (and most web applications, for that matter). "Last write wins" is perfectly suited for many web applications, however, as concurrency conflicts can only occur when multiple users can update or delete the same data. In many web applications users can only modify data specific to their account, so there's no concern or need for concurrency control. But in applications where concurrency conflicts may occur, it's worthwhile to consider adding some form of concurrency control.
Understanding Pessimistic Concurrency Control
The only way to guarantee that concurrency conflicts cannot occur is to limit data modification to one user at a time. Such draconian forms of concurrency control are referred to as pessimistic concurrency control. With pessimistic concurrency control there needs to be some mechanism where a user can lock a particular row or an entire database table and say, essentially, "No one can modify these records until I'm done!" One challenge with pessimistic concurrency control is knowing when a user is truly done editing the records. If it's been 30 minutes since the user has last loaded the page, does that mean that they're busily editing the data, and need it to remain locked, or that they've closed their browser and gone home for the night? As you can see, with pessimistic concurrency control steps must be taken to ensure that data isn't inadvertently locked and therefore unable to be modified by other users.
Because of these challenges and the diminished user experience that follows, pessimistic concurrency control is rarely used. It's only used in situations where concurrency conflicts cannot, under any circumstance, happen. A common scenario where pessimistic concurrency control is valid is in reservations. If you are ordering a ticket to a concert or plane flight online, it's important that from the time you pick your seat to the time you place your order and provide your payment information that some other user doesn't sneak in and take the seat you're ordering. To accommodate this, many reservation systems let you choose a seat and then lock it for a specified duration. If you complete your purchase process by the deadline, the seat is yours. If you fail to complete the purchase by then - either because you go out to lunch or are just too slow in entering your information - the seat is "unlocked" and returned to the general pool.
ASP.NET does not offer any built-in support for pessimistic concurrency control. If you need to implement this type of concurrency control you're on your own.
A Look at Optimistic Concurrency Control
Pessimistic concurrency control is useful in scenarios where concurrency violations absolutely, positively must not occur. But in most web applications, a concurrency violation is not the end of the world. Optimistic concurrency control takes the user experience from "last write wins" (there's no special locking or other behavior needed by the users modifying data), but disallows one user to inadvertently overwrite another user's changes. In the case where one user attempts to overwrite another user's - as in the Manager A and Manager B example earlier in this article - the person who is attempting to overwrite the other's data has their changes canceled. In short, optimistic concurrency control puts a stop to any inadvertent overwrites.
There are a handful of different way to implement optimistic concurrency. One common way is to add some sort of timestamp column to a database table that records the last modification to each row. When a user starts editing a row (by clicking the Edit button), that timestamp is remembered. When the user goes to save their changes (by clicking the Update button) the update only completes if the remembered timestamp matches the current timestamp value for that row. If another user had made any changes between the time the user clicked Edit and Update, the timestamp would be updated.
Microsoft offers a flavor of optimistic concurrency control through the SqlDataSource. Rather than using timestamps, the SqlDataSource control updates the
WHERE clause of the
DELETE statements to only perform the
the other columns in the record have the same values as when the Edit button was clicked.
Implementing Optimistic Concurrency with the SqlDataSource Control
The SqlDataSource control's Data Source Wizard makes implementing optimistic concurrency control a snap. As discussed in the Updating Basics article, you can have the Data Source Wizard control automatically generate
DELETEstatements by clicking the Advanced button and checking the "Generate INSERT, UPDATE, and DELETE statements" checkbox. In addition to the "Generate INSERT, UPDATE, and DELETE statements" checkbox, the Advanced SQL Generation options dialog box also includes a checkbox titled "Use optimistic concurrency." Check this to turn on optimistic concurrency control.
Enabling optimistic concurrency updates the
WHERE clauses of the SqlDataSource control's
to include parameters named
@original_ColumnName. For example, when creating a SqlDataSource control that returns the
Discontinued fields from the Northwind
Products database table, the following
UPDATE statement is generated:
Without optimistic concurrency, the
WHERE clause would contain just:
WHERE [ProductID] = @ProductID. Enabling optimistic concurrency
added additional checks to the original values of the other columns returned by the
When using optimistic concurrency with an editable data Web control, the data Web control remembers the original values when the editing interface is loaded.
Consider the GridView. When the user clicks the Edit button the GridView remembers the values loaded into the editing interface. These remembered values
are then loaded into the
@original_ColumnName parameters when the grid's Update button is clicked.
To see how this all works, let's return to our original example with Manager A and Manager B, but this time imagine that optimistic concurrency has been
enabled. When Manager A clicks the Edit button for Scott's Tea, the GridView remembers that the
ProductName is "Scott's Tea", the
is $5.00, and
Discontinued is False. Likewise, when Manager B clicks the Edit button, the GridView remembers the same details.
After Manager B checks the Discontinued checkbox and clicks the Update button, the above
UPDATE statement is sent to the database.
UPDATE statement (with the remembered values injected), will look like:
Because the data in the database matches the data stored in the GridView, the
WHERE clause will return one record and that record will be
When Manager A changes the price to $15.00 and clicks Update, the following
UPDATE statement (with the parameter values injected) is sent
to the database:
WHERE clause won't return any records, however, because the record whose
ProductID equals 3 has its
Discontinued value set to True (from Manager B's earlier update). Therefore, Manager A's update does not affect any records. Nothing changes
in the database because there was a discrepancy between the remembered original values and the current values, thereby indicating that during Manager A's
editing, another user modified the data. In short, Manager A's changes are lost, but the good news is that Manager B's changes were not blindly
The following figure depicts the workflow when optimistic concurrency is in use.
Detecting When a Concurrency Violation Has Occurred
By default, with optimistic concurrency control a concurrency violation passes by silently. In the example above, when Manager A clicks Update, the grid will return to its pre-editing state (as expected), but her changes won't appear in the grid. That is, of course, because the changes weren't applied because of the mismatch between the remembered values and the values in the database, but the user wasn't alerted to this.
When a concurrency violation occurs and a user's changes are not persisted, we should alert them in some manner, letting them know that their changes were
not saved because another user modified the same data simultaneously. We can determine how many records were affected during the update workflow by creating
an event handler for the GridView's
RowUpdated event handler. This event handler is passed an object that includes an
property. There's also a
KeepInEditMode property that we can set to True to keep the edited row in edit mode (thereby making it easier for the
user to reapply their changes).
The download available at the end of this article includes a website with two working demos: one that illustrates using optimistic concurrency with editing
and another that shows using it when deleting. (To utilize these demos you'll need to open two browser windows to simulate two concurrent users and then
modify (or delete) the data in such a way that a concurrency violation unfolds.) In these demos I created an event handler for the GridView's
event with the following code:
CurrencyConflictMsg is a Label control that displays a message informing the user of the concurrency violation. After displaying it and instructing
the GridView to leave the row in edit mode, I rebind the data to the
Products GridView so that the other user's changes are loaded.
If I open two browser windows and play out the scenario with Manager A and B, upon clicking Update from Manager A's window I get the following output:
There are two things to note here: the informational message and the data in the edited row. First, take note of the big, red letters explaining that a concurrency violation has taken place. Next, note that the data shown in the edited row is not the data entered by Manager A (who changed the price to $15.00) but instead is the data in the database currently, the data entered by Manager B, namely the product's original price ($5.00), marked as discontinued. With the SqlDataSource control's built-in optimistic concurrency control and with a bit code written by us, we were able to catch that Manager A was attempting to blindly overwrite Manager B's changes and, in response, stop the update, refreshed the grid with the current values in the database, and keep the edited row in edit mode. Manager A can now review the current values, make any modifications necessary, and then save her changes.
By default, the ASP.NET data source controls do not exhibit any form of concurrency control; instead, they use the "last write wins" mode. "Last write wins" is an acceptable policy in scenarios where concurrent users rarely, if ever, modify the same data. If, however, it is likely that two or more users may be working on the same data at the same time, consider implementing some form of concurrency control. The simplest form of concurrency control, and the form that the SqlDataSource control provides out of the box, is optimistic concurrency control. As we saw in this article, using optimistic concurrency control is as easy as ticking a checkbox and writing a few lines of code. Be sure to download the demos at the end of this article, which show using optimistic concurrency in both updating and deleting scenarios.