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.
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:
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:
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) _
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, _
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."
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
