When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, May 21, 2008

Accessing and Updating Data in ASP.NET: Using Optimistic Concurrency

By Scott Mitchell


A Multipart Series on ASP.NET's Data Source Controls
ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data. These controls allow page developers to declaratively access and modify data without writing any code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.

  • Data Source Control Basics - explores the concepts and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
  • Accessing Database Data - shows how to use the SqlDataSource and AccessDataSource controls to query data from a relational database.
  • Filtering Database Data with Parameters - learn how to retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page, session variables, and so on.
  • Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.
  • Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
  • Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.
  • Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.
  • Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.
  • Inserting Data - learn how to insert data using a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
  • Deleting Data - see how to delete data using a SqlDataSource control. Also looks at how to programmatically cancel a delete.
  • Updating Basics - learn the basics of updating database data using a SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
  • Customizing the Editing Interface - see how to customize the GridView's columns to provide a customized editing interface that includes input validation and alternative user interface elements.
  • Handling Database NULL Valuese - explore how to extend the GridView's customized editing interface to handle database NULL values.
  • Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.
  • Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.
  • (Subscribe to this Article Series! )

    Introduction


    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!

    - continued -

    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?
    The way the SqlDataSource control and GridView work by default is to update all editable fields, regardless of whether they've been modified by the user or not. This means that we can be certain that the last scenario won't unfold. Therefore, whatever manager clicks the Update button last will have his or her changes saved, overwriting the other's. The following figure illustrates this workflow when Manager A - the manager who updates the unit price to $15.00 - clicks the Update button after Manager B has already saved his changes.

    An example of a concurrency violation.

    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 UPDATE and DELETE statements to only perform the UPDATE and DELETE if 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 INSERT, UPDATE, and DELETE statements 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.

    The Advanced SQL Generation options dialog box.

    Enabling optimistic concurrency updates the WHERE clauses of the SqlDataSource control's UPDATE and DELETE statements to include parameters named @original_ColumnName. For example, when creating a SqlDataSource control that returns the ProductID, ProductName, UnitPrice, and Discontinued fields from the Northwind Products database table, the following UPDATE statement is generated:

    UPDATE [Products] SET
       [ProductName] = @ProductName,
       [UnitPrice] = @UnitPrice,
       [Discontinued] = @Discontinued

    WHERE [ProductID] = @original_ProductID AND
          [ProductName] = @original_ProductName AND
          [UnitPrice] = @original_UnitPrice AND
          [Discontinued] = @original_Discontinued

    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 SELECT query.

    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 UnitPrice 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. The UPDATE statement (with the remembered values injected), will look like:

    UPDATE [Products] SET
       [ProductName] = "Scott's Tea",
       [UnitPrice] = 5.00,
       [Discontinued] = True

    WHERE [ProductID] = 3 AND
          [ProductName] = "Scott's Tea" AND
          [UnitPrice] = 5.00 AND
          [Discontinued] = False

    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 updated.

    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:

    UPDATE [Products] SET
       [ProductName] = "Scott's Tea",
       [UnitPrice] = 15.00,
       [Discontinued] = False

    WHERE [ProductID] = 3 AND
          [ProductName] = "Scott's Tea" AND
          [UnitPrice] = 5.00 AND
          [Discontinued] = False

    The 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 overwritten.

    The following figure depicts the workflow when optimistic concurrency is in use.

    Optimistic concurrency prohibits the inadvertent overwrite from occurring.

    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 AffectedRows 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 RowUpdated event with the following code:

    Protected Sub Products_RowUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs) Handles Products.RowUpdated
       If e.AffectedRows = 0 Then
          'No update occurred!
          CurrencyConflictMsg.Visible = True

          'Keep the conflict row in edit mode
          e.KeepInEditMode = True

          'Rebind the data so as to load the current data
          Products.DataBind()
       End If
    End Sub

    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:

    An optimistic concurrency conflict warning message is displayed.

    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.

    Conclusion


    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.

    Happy Programming!

  • By Scott Mitchell


    Attachments:


  • Download the code used in this article

    Further Readings:


  • Tutorials on Editing, Inserting, and Deleting Data (using the ObjectDataSource)
  • Inserting, Updating, and Deleting Data with the SqlDataSource (VB Version) (C# version)
  • Implementing Optimistic Concurrency with the SqlDataSource (VB Version) (C# version)
  • A Multipart Series on ASP.NET's Data Source Controls
    ASP.NET 2.0 introduced a number of new Web controls designed for accessing and modifying data. These controls allow page developers to declaratively access and modify data without writing any code to perform the data access. This article is one in a series of articles on ASP.NET's data source controls.

  • Data Source Control Basics - explores the concepts and advantages of data source controls, and compares their usage in ASP.NET 2.0 to data access techniques in ASP.NET 1.x.
  • Accessing Database Data - shows how to use the SqlDataSource and AccessDataSource controls to query data from a relational database.
  • Filtering Database Data with Parameters - learn how to retrieve just a subset of database data based on hard-coded values and values from the querystring, other Web controls on the page, session variables, and so on.
  • Retrieving XML Data with XmlDataSource Control - see how to retrieve both remote and local XML data and display it in a data Web control.
  • Creating Custom Parameter Controls - learn how to create your own custom, declarative Parameter controls for use in the data source controls' parameters collections.
  • Examining the Data Source Control's Events - explore the events raised during a data source control's lifecycle.
  • Declaratively Caching Data - learn how to cache data to the data cache simply by setting a couple of data source control properties.
  • Programmatically Accessing Data using the Data Source Controls - programmatically retrieve, insert, delete, and update data using the SqlDataSource and AccessDataSource controls.
  • Inserting Data - learn how to insert data using a SqlDataSource control. Also examines how to retrieve the IDENTITY column value for the just-inserted record.
  • Deleting Data - see how to delete data using a SqlDataSource control. Also looks at how to programmatically cancel a delete.
  • Updating Basics - learn the basics of updating database data using a SqlDataSource control. Also examines using the GridView to provide a web-based editing interface.
  • Customizing the Editing Interface - see how to customize the GridView's columns to provide a customized editing interface that includes input validation and alternative user interface elements.
  • Handling Database NULL Valuese - explore how to extend the GridView's customized editing interface to handle database NULL values.
  • Using Optimistic Concurrency - see how to prevent concurrent users from overwritting one anothers changes by using concurrency control.
  • Filtering Data Using a CheckBoxList - learn how to filter data based on the end user's selections in a CheckBoxList.
  • (Subscribe to this Article Series! )



    ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article