asp tutorials, asp.net tutorials, sample code, and Microsoft news from 15Seconds
Data Access  |   Troubleshooting  |   Security  |   Performance  |   ADSI  |   Upload  |   Email  |   Control Building  |   Component Building  |   Forms  |   XML  |   Web Services  |   ASP.NET  |   .NET Features  |   .NET 2.0  |   App Development  |   App Architecture  |   IIS  |   Wireless
 
Pioneering Active Server
 Power Search










Active News
15 Seconds Weekly Newsletter
• Complete Coverage
• Site Updates
• Upcoming Features

More Free Newsletters
Reference
News
Articles
Archive
Writers
Code Samples
Components
Tools
FAQ
Feedback
Books
Links
DL Archives
Community
Messageboard
List Servers
Mailing List
WebHosts
Consultants
Tech Jobs
15 Seconds
Home
Site Map
Press
Legal
Privacy Policy
internet.commerce














internet.com
IT
Developer
Internet News
Small Business
Personal Technology
International

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

HardwareCentral
Compare products, prices, and stores at Hardware Central!

Handling Concurrency Issues in .NET
By Wayne Plourde
Rating: 4.4 out of 5
Rate this article


  • email this article to a colleague
  • suggest an article

    Introduction

    In my recent articles, Creating a Data Access Layer in .Net, Part I and Part II, I briefly touched on the issue of currency errors and how ADO.NET can help you detect these situations. In this article, we will focus on the different approaches to concurrency locking and discuss how you can handle the situations when they occur.

    Today's enterprise applications have one thing in common, multiple users accessing the system at the same time. In this type of scenario, you are bound to have users attempting to edit the same data at the same time, especially when there is a single data source associated with the application. This competition for data is known as a concurrency condition and can result in a lack of integrity in your database or worse, a loss of data.

    Here is an example of a data concurrency dilemma:

    Joe is an accountant for the Widget Store. He is currently working on updating the payment terms associated with each of the customer accounts in the Widget Store Customer Database. He opens each record, makes the edits and saves the changes to the database. When he gets to customer XYZ, he retrieves the record to edit it but he is distracted by a lengthy phone call.

    Meanwhile, Sally, who works at the customer support desk, receives a call from someone at Company XYZ. The caller informs Sally that the company address and phone number has changed. Sally makes the changes and updates the record in the database.

    Finally, Joe finishes his phone call. He completes his changes to only the Payment Terms field in the record; however, the application updates the entire record, therefore saving the old Address and Phone Number back over the changed values Sally previously entered.

    Let's look at the flow of events for each of our users simultaneously so we can see the state of the XYZ Company address in the database through the process:

    Joe:

    Sally:

    State of Address Field:

    retrieves XYZ company record

     

    100 Main Street

     

    retrieves XYZ company record

    100 Main Street

     

    Changes Address

    100 Main Street

     

    Updates Record

    50 Corporate Drive

    Changes Payment Terms but not the Address

     

    50 Corporate Drive

    Updates record

     

    100 Main Street (ouch)

    Yup, an unfortunate situation. Especially when Joe is trying to figure out why XYZ has not complied with the new Payment Terms on the recent bill he sent in the mail. Won't he be surprised when he tries to call them.

    Optimistic vs. Pessimistic Locking

    The only way to prevent concurrency errors is to lock the records that are being edited. There are two basic approaches towards locking - optimistic and pessimistic. One would consider an optimistic locking scenario when the likelihood of a concurrency condition is low. This is usually the case in systems where the activity is primarily additive, like an order entry system. On the other hand, one would consider pessimistic locking when the likelihood of a concurrency condition is high. This is usually true of management or workflow-oriented systems.

    Since pessimistic locking anticipates contention for the same record, we take precautions by preventing users from selecting a record for editing when another user has already done so, thereby locking the record. This is often implemented by relying on the database itself. All major relational databases on the market today offer some kind of inherent locking when updates are occurring, although the granularity of the locks may differ. For instance, SQL Server 2000 provides locking at the row level, while others may lock the entire page or table the row resides in. We will refer to this as Pessimistic Database Locking. Unfortunately, this type of locking requires that you remain connected to the database for the entire process, which is somewhat contrary to the .NET model. In addition, this type of locking could hold up other users trying to access the system.

    For this reason, developers requiring pessimistic locking may chose to implement their own locking by maintaining flags and other extended data for each record - much like a check-in/check-out process. We will refer to this as Pessimistic Application Locking.

    In optimistic locking, we allow multiple users to access the same record for edits, since we don't anticipate contention for data. Here the "locking" happens after the user tries to save changes on top of someone else's changes. Before or during an update, the application logic will check to see if the current record in the database has changed since you retrieved your copy of the record. If it has, the app will generate an error causing the update transaction to be rolled back. If no changes are detected, the record is saved directly.

    Here is a side-by-side comparison of the workflows for updating a record for each type of Locking Scenario

    Optimistic

    Pessimistic Database

    Pessimistic Application

    ·         Retrieve record for update

    ·         Make changes

    ·         Check if the underlying record in DB has changed

    ·         If no changes, Update DB

    ·         Retrieve record for update

    ·         If DB permits read of the record, DB will hold a lock on record (must maintain the connection)

    ·         Make changes

    ·         Update DB

    ·         DB lock is released

    ·         Check if there are locks on the records

    ·         Programmatically set lock on record

    ·         Retrieve record for update

    ·         Make changes

    ·         Update DB

    ·         Programmatically release lock on record

    Of course, there are advantages and disadvantages for each type of locking method. As we mentioned before, if you are relying on the database to maintain pessimistic locks, you will need to stay connected to the database. This may not be possible for all types of applications. Implementing your own application locks can be a challenging exercise, as well. In addition, you may need to provide a mechanism for reversing locks since it is very possible that someone may checkout a record and become unavailable for an extended period of time.

    For optimistic locking, there is no upfront indication to the user that someone else may be editing the records. Also, when an error occurs, a decision must be made to overwrite the current version with your changes or start over. You could also provide a screen that allows the user to decide which fields to keep from the ones that are different. Depending on the sophistication of your users, this could be an intimidating process. There is also the potential that they could do it incorrectly.

    Here is a summary of some of the advantages and disadvantages of each locking type:

     

    Advantage

    Disadvantage

    Optimistic Locking

    ·         Easy to implement in .NET

    ·         Can be disconnected

    ·         All records can be read anytime

    ·         No warning prior to update

    ·         User may need to make a complex decision on how to proceed

    ·         Must persist the Dataset between read and write

    Pessimistic Database Locking

    ·         Allows upfront warning before editing

    ·         Relies on built database locks

    ·         Must maintain connection to the database for the entire transaction

    ·         Locks may need to be managed

    ·         Records cannot be read when locked.

     

    Pessimistic Application Locking

    ·         Allows upfront warning before editing

    ·         Can be use in either connected or disconnected mode

    ·         Can easily provide more details on who holds the lock

    ·         All records can be read anytime

    ·         No need to persist Dataset

    ·         Requires implementing custom logic and schema support in the database

    ·         May need to provide support to manage or override locks.

    Connected vs. Disconnected Modes

    Up to this point, we have batted around the terms connected and disconnected modes. Let's take a moment to clarify what we mean. When an application runs in a connected mode, the database connection stays open for the entire session of the user. All successive requests of the database are made on that instance of the connection. This was the default behavior of the previous versions of ADO (although you could disconnect a recordset once it had been opened).

    In a disconnected model, one would open a connection, perform a transaction, then close the connection. This is the basis for how ADO.NET works with DataSets. There is an exception with DataReaders, which stay connected while the data is being read; however, they do not support updates to data so they are not pertinent to the scope of this article.

    Picking a Lock Type for ADO.NET

    In the previous version of ADO, you could specify a lock type when opening a recordset. Here is a reference on MSDN if you need to jog your memory:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/pg_ado_cursors_10.asp

    Lock types are no longer specified in ADO.NET because of its disconnected architecture. In a disconnected model, the database has no idea what you intended to do with the data you retrieved. Therefore, it won't care if someone stomps on your changes. For this reason, ADO.NET is a perfect fit for optimistic locking, and Microsoft wisely integrated this methodology into the wizards used to create Data Adapters.

    Although the nature of DataSets is to be disconnected, it is possible to implement a pessimistic database locking scenario by opening the connection then establishing a transaction in serializable mode. However, we won't cover this topic in this article.

    Implementing Concurrency Checking

    To demonstrate the concurrency methodologies we have just discussed, I have chosen to use the same Widget Store Management Web application I used in the previous DAL articles.

    You can download the projects files here. If you setup the code and database examples from my last article, be sure to drop the WidgetStore database first and recreate it since there have been changes to the schema.

    Of course, there are a few other changes, as well. The first thing you will notice is the login screen which is using Web Forms authentication.


    Fig. #1 - Login Screen

    We are not actually doing any real authentication. We are just using the form to capture a username that we will use to record who has performed database updates and/or set record locks.

    In the DAL, I have created a new Handler component class called, CustomersHandler. Within this class, I have set up several Data Adapters, one generic adapter for retrieving a list and two detail Data Adapters - one for retrieving a single row in optimistic mode and one for retrieving a single row in pessimistic mode. Once you login, you will be redirected to the main screen. On the menubar, you will see two new additions:

    • Manage Customers (Optimistic Locking)
    • Manage Customers (Pessimistic Application Locking)


    Fig. #2 - Welcome Screen

    You can probably guess what these new menus are for. Go ahead and click on the Optimistic Locking link for our first demonstration.

    Setting up Optimistic Concurrency Checking

    The primary focus of optimistic concurrency checking is to ensure that the underlying data in the database has not changed before performing an update. This can be done by applying a WHERE clause to the SQL UPDATE statement to ensure the original values match the current values. Fortunately, Visual Studio .NET assists you with this process. When you create a Data Adapter through the wizard in Visual Studio .NET, by default, it will automatically generate the UPDATE and DELETE SQL statements with a WHERE clause for testing the original values against the current values. In addition, when you call the Update method on a Data Adapter, it will automatically pass the original values for each field (which are maintained by the dataset) along with the new or current values to the stored procedure so the database can determine if a change has taken place.

    For a step by step approach to setting up a Data Access Layer and all the constituent components, refer to my last article, Creating a Data Access Layer in .NET.

    The SQL WHERE clause in optimistic concurrency checks can get fairly complex if there are a lot of fields in your table and even more complex if some of the fields allow nulls. Fortunately, you don't have to write the code, but if you want to edit the statements after they have been generated, you may wish for something simpler. After writing my last article on creating a data access layer, I received an email from a reader suggesting the approach of using a timestamp field along with the primary key to perform the concurrency check. This would be useful if you plan on making extensive changes. It would be nice if the Wizard would recognize when you are using a timestamp field and simplify the auto-generated WHERE clause accordingly.

    The List View

    The list view provides the first interaction with the customer data. We can display a subset of the fields from the table and use the "Select" link to retrieve a detail view of a particular record for editing.


    Fig #3 - Customer List

    When the user clicks the Select link for the row, the form is posted back to the server and the SelectedIndexChanged event is processed on the server:

        PrivateSub DataGrid1_SelectedIndexChanged(_

             ByVal sender As System.Object, ByVal e As System.EventArgs) _

                  Handles DataGrid1.SelectedIndexChanged

            Session("SelectedCustomerID") = _

                  DataGrid1.DataKeys(DataGrid1.SelectedIndex)

            Response.Redirect("CustomersOptimisticDetail.aspx")

        EndSub

    Here we set the current the selected CustomerID to the session state and then redirect to the Detail form.

    The Detail View

    The Optimistic Detail form looks fairly straightforward at the outset - a number of textboxes and an update button.


    Fig #4 - Optimistic Customer Detail

    An important aspect of handling optimistic concurrency errors is caching the DataSet so that we can use the same instance later when we perform the update. Optimistic concurrency relies on coordinating the values originally present in the DataSet with what the database says the current values are before we perform the update. Each item in a data row contains not only the current value but the original value as well. Therefore, the original DataSet must be preserved during the entire process. This may be a shift in thinking for some of you. In Web-based applications, a common practice is to use the DataSet to populate a form for updating, then discard it. When the user submits the form back to the server to update values, the application retrieves another DataSet, copies the updated values from the form into it, then performs the update on the database. In this scenario, the concurrency check is useless because the original values are lost. And more important, you will overwrite someone else's change without warning.

    Therefore in the Page_Load event of the Detail form, we check the IsPostBack flag to determine if the form is being loaded for the initial fill or being returned for the update. On the initial call, we retrieve the DataSet, then store the DataSet in the ViewState. On the return visit, we restore the DataSet to the previous member variable to perform the update.

    PrivateSub Page_Load(ByVal sender As System.Object, _

    ByVal e As System.EventArgs) HandlesMyBase.Load

            Dim CustomerID AsInteger

            IfNot (Session("SelectedCustomerID")) Then

                CustomerID = Session("SelectedCustomerID")

            Else

                Response.Redirect("CustomersOptimisticList.aspx")

            EndIf

     

            HeadingRow.Visible = False

     

            IfNot IsPostBack Then

                Try

                    CustomersDetailDataset1 = _

                     handler.GetCustomersOptimistcDetailDataset(CustomerID)

                    DataBind()

     

                    'put the dataset into the viewstate

                    Viewstate("CustomersDetailDataset1") = _

                      CustomersDetailDataset1

                Catch exc As DALException

                    'hide form

                    FormPanel.Visible = False

                    'show error

                    ErrorLabel.Visible = True

                    ErrorLabel.Text = exc.Message

     

                    ErrorLabel.Text &= "<br>Contact system administrator."

                Catch exc As Exception

                    'hide form

                    FormPanel.Visible = False

                    'show error

                    ErrorLabel.Visible = True

                    ErrorLabel.Text = _

                       "An error occured while retrieving Customer data."

     

                    ErrorLabel.Text &= _

                       "<br>Contact system administrator."

                Finally

     

                EndTry

            Else

                'restore the dataset from the viewstate

                'if this is a return trip.

                CustomersDetailDataset1 = _

                       Viewstate("CustomersDetailDataset1")

            EndIf

        EndSub

    I have chosen to persist the DataSet in the view state, rather than the session state for a number of reasons. First, the view state operates in the context of the page that needs it. Therefore, it is not using server memory after the page falls out of scope. This could be a major consideration if you have many users with large DataSets in the session. Second, the view state is as easy to use as the session state. In addition, the view state would protect a single user if for some reason they had opened two windows at the same time. The one drawback is that if you do have a large DataSet, it will affect the response time of the application, since the content of the DataSet is serialized and not only sent down to the browser but is also posted back to the server on the form submission. Weigh the pros and cons carefully when designing your own system.

    Creating a Concurrency Error

    In order to witness the optimistic concurrency checking in action, you will need to open two browser windows, each pointing to the Widget Store Management application on your local machine.


    Fig #5 - Opening Two Detail Windows for testing

    Then do the following:

    • In window #1, retrieve a customer detail.
    • In window #2, retrieve the same customer detail.
    • In window #2, make a change to the record and submit the changes to the database by clicking the Update button. You can now close window #2 if you like.
    • In window #1, make a change to the record (try to make the change different than what you did in window #2, this will make the resolution screen easier to understand)
    • Click the Update button to submit the changes.
    When you are done, you will see that the form returns with an error and further instructions on how to reconcile the discrepancy.


    Fig #6 - Optimistic Customer Detail

    Recovering from an Optimistic Concurrency Error

    When an optimistic concurrency error occurs, ADO.NET will throw a DBConcurrency Exception. We have configured our Data Access Layer to catch this exception and throw a new custom exception bundled with additional information to provide the consumer of the DAL the insight on how to proceed.

    Here are the basic options on how one can proceed:

    • Inform the user that an error occurred and force the user to start the transaction over.
    • Proceed with saving the changes with a log of the differences.
    • Present the situation to the user so that they may decide how to reconcile the discrepancy.

    For our example, we will allow the user to either manually reconcile or abandon the transaction altogether.

    When a user clicks the Update button on the form, it is posted back to the server. First, the Page_Load event is processed. Since this is a return trip, the IsPostBack property is True. Therefore, the original DataSet is restored from the view state. (See code listing above). Next, the Click event is processed for the Update button:

        PrivateSub UpdateButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateButton.Click

     

            'copy data from form into the dataset

            loadDatasetForUpdate()

     

            Try

                'update the dataset through the handler component

                handler.UpdateCustomersOptimisticDataset(_

                          CustomersDetailDataset1)

                ErrorLabel.Visible = True

                ErrorLabel.Text &= "Customer record successfully saved"

            Catch exc As DALException

     

                'show error

                ErrorLabel.Visible = True

     

                If exc.ErrorCode = _

                            DALException.DALExceptionCode.Concurrency Then

                    HeadingRow.Visible = True

                    UpdateButton.Visible = False

                    OverwriteButton.Visible = True

                    CancelButton.Visible = True

     

                    ErrorLabel.Text = _

        "The record you are editing has been changed by another user. <br>"

                    ErrorLabel.Text &= _

        "Overwrite the data values or Cancel the operation."

     

                    'show both the original values and the current values

                    showConcurrencyData()

                    compareValues()

                Else

                    ErrorLabel.Text = exc.Message

                    ErrorLabel.Text &= "<br>Contact system administrator."

                EndIf

     

            Catch exc As Exception

                'show error

                ErrorLabel.Visible = True

                ErrorLabel.Text = "An unknown error occured."

     

                ErrorLabel.Text &= "<br>Contact system administrator."

            EndTry

           'store the dataset in the viewstate

            Viewstate("CustomersDetailDataset1") = CustomersDetailDataset1

     

        EndSub

     

    Here, the method first attempts to update the DataSet. While we can use data binding to populate our form, unfortunately, we must manually copy the data from the form elements into the DataSet. This first helper method encapsulates this process:

        PrivateSub loadDatasetForUpdate()

            Dim row As CustomersOptimisticDetailDataSet.CustomersRow

            'get the first row from the data set

            row = CustomersDetailDataset1.Customers.Rows(0)

     

            'copy data into the dataset

            row.Company = CompanyTextBox.Text

            row.Firstname = FirstNameTextBox.Text

            row.Lastname = LastNameTextBox.Text

            row.Address = AddressTextBox.Text

            row.City = CityTextBox.Text

            row.State = StateTextBox.Text

            row.Zip = ZipTextBox.Text

            row.PaymentTerms = PaymentTermsTextBox.Text

            row.CreditLimit = CreditLimitTextBox.Text

     

            'set the current login user

            row.UpdatedBy = User.Identity.Name()

     

        EndSub

     

    If the update performed by the handler component generates an exception, it will be caught. When a DALException occurs, we then check the type of exception. If it is a concurrency exception, the first thing we do is hide the Update button and show the Overwrite and Cancel buttons. Next, we show the original and current data from the database along with the proposed data. This is handled by the showConcurrencyData helper method:

    PrivateSub showConcurrencyData()

            Dim row As CustomersOptimisticDetailDataSet.CustomersRow

            'get the first row from the data set

            row = CustomersDetailDataset1.Customers.Rows(0)