Handling Concurrency Issues in .NET

Thursday Jan 10th 2008 by Quin Street

Share:

Wayne Plourde focuses on different approaches to concurrency locking and discusses how to handle the situations when they occur.

By Wayne Plourde

In my recent article, Creating a Data Access Layer in .Net, Part I, 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

B7Retrieve record for update

B7Make changes

B7Check if the underlying record in DB has changed

B7If no changes, Update DB

B7Retrieve record for update

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

B7Make changes

B7Update DB

B7DB lock is released

B7Check if there are locks on the records

B7Programmatically set lock on record

B7Retrieve record for update

B7Make changes

B7Update DB

B7Programmatically 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

B7Easy to implement in .NET

B7Can be disconnected

B7All records can be read anytime

B7No warning prior to update

B7User may need to make a complex decision on how to
proceed

B7Must persist the Dataset between read and write

Pessimistic Database Locking

B7Allows upfront warning before editing

B7Relies on built database locks

B7Must maintain connection to the database for the
entire transaction

B7Locks may need to be managed

B7Records cannot be read when locked.

Pessimistic Application Locking

B7Allows upfront warning before editing

B7Can be use in either connected or disconnected mode

B7Can easily provide more details on who holds the
lock

B7All records can be read anytime

B7No need to persist Dataset

B7Requires implementing custom logic and schema
support in the database

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

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 below. If you setup the code and database examples, 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.

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:

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)

'display the
original values in the dataset

CompanyOriginal.InnerText = _

row.Item("Company",
DataRowVersion.Original)

FirstnameOriginal.InnerText = _

row.Item("FirstName",
DataRowVersion.Original)

LastNameOriginal.InnerText = _

row.Item("LastName",
DataRowVersion.Original)

AddressOriginal.InnerText = _

row.Item("Address",
DataRowVersion.Original)

CityOriginal.InnerText = _

row.Item("City",
DataRowVersion.Original)

StateOriginal.InnerText = _

row.Item("State",
DataRowVersion.Original)

ZipOriginal.InnerText = _

row.Item("Zip",
DataRowVersion.Original)

PaymentTermsOriginal.InnerText = _

row.Item("PaymentTerms",
DataRowVersion.Original)

CreditLimitOriginal.InnerText = _

row.Item("CreditLimit",
DataRowVersion.Original)

'fill a
second dataset to get the current values

CustomersDetailDataset2 = _

handler.GetCustomersOptimistcDetailDataset(row.CustomerID)

row = CustomersDetailDataset2.Customers.Rows(0)

'display the
current values

CompanyCurrent.InnerText = row.Company

FirstNameCurrent.InnerText =
row.Firstname

LastNameCurrent.InnerText =
row.Lastname

AddressCurrent.InnerText = row.Address

CityCurrent.InnerText = row.City

StateCurrent.InnerText = row.State

ZipCurrent.InnerText = row.Zip

PaymentTermsCurrent.InnerText =
row.PaymentTerms

CreditLimitCurrent.InnerText =
row.CreditLimit

EndSub

This data is displayed in the additional table cells that are adjacent to each of the form's textboxes. Note that a second DataSet is used to retrieve the current values from the database.

Finally, we compare the original and current data that was displayed and highlight the differences in the compareValues helper method; this will assist the user in identifying which fields have changed.

PrivateSub compareValues()

If CompanyCurrent.InnerText <> CompanyOriginal.InnerText Then

CompanyCurrent.Attributes.Add(_

"style",
"color:red; font-weight:bold")

EndIf

If FirstNameCurrent.InnerText <> _

FirstnameOriginal.InnerText Then

FirstNameCurrent.Attributes.Add(_

"style", "color:red; font-weight:bold")

EndIf

...

If CreditLimitCurrent.InnerText <> _

CreditLimitOriginal.InnerText Then

CreditLimitCurrent.Attributes.Add(_

"style",
"color:red; font-weight:bold")

EndIf

EndSub

Once the form is displayed, the user will have the choice of canceling the operation or making changes and overwriting the data in the database with the new values. If the user clicks the Overwrite button, then the following event handler is executed:

PrivateSub OverwriteButton_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles OverwriteButton.Click

loadDatasetForUpdate()

Try

'call the
Overwrite method instead of Update

handler.OverwriteCustomersOptimisticDataset(_

CustomersDetailDataset1)

ErrorLabel.Visible = True

ErrorLabel.Text = "Customer
record successfully saved"

HeadingRow.Visible = False

UpdateButton.Visible = True

OverwriteButton.Visible = False

CancelButton.Visible = False

clearConcurrencyData()

Catch exc As DALException

'show
error

ErrorLabel.Visible = True

ErrorLabel.Text = exc.Message

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

Catch exc As Exception

'show
error

ErrorLabel.Visible = True

ErrorLabel.Text = "An unknown
error occured."

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

EndTry

Viewstate("CustomersDetailDataset1") = CustomersDetailDataset1

EndSub

The code here is very similar to the previous Update event; however, we call the OverwriteCustomersOptimisticDataset method on the handler component. This method uses an alternate Update Command object to force the DataSet update to the database without performing the concurrency check.

Finally, we clean up the table cells previously used for the Original and Current data values.

PrivateSub clearConcurrencyData()

CompanyOriginal.InnerText =
""

FirstnameOriginal.InnerText =
""

LastNameOriginal.InnerText =
""

AddressOriginal.InnerText =
""

CityOriginal.InnerText = ""

StateOriginal.InnerText = ""

ZipOriginal.InnerText = ""

PaymentTermsOriginal.InnerText =
""

CreditLimitOriginal.InnerText =
""

CompanyCurrent.InnerText =
""

FirstNameCurrent.InnerText =
""

LastNameCurrent.InnerText =
""

AddressCurrent.InnerText =
""

CityCurrent.InnerText = ""

StateCurrent.InnerText = ""

ZipCurrent.InnerText = ""

PaymentTermsCurrent.InnerText =
""

CreditLimitCurrent.InnerText =
""

EndSub

The form is now restored to its previous mode and our optimistic concurrency update is completed.

Setting up Pessimistic Application Locks

The first consideration for implementing Pessimistic Application Locking scenario is the design of the database. In order for the system to work, you must keep track of which user has a lock on which record. There are a two basic ways to approach this. You can design a "Locks" table which would have fields for the table name, the record ID, the time the lock was set, and who it was set by (this can be both a username and machine name - useful if a user needs to log in at two separate machine). Another method is to provide fields describing lock conditions within the required tables. The advantage of a separate locks table is that an administrator can easily clear all the locks within the system if a problem occurs. The advantage of locking within the data table is that the current lock info can easily be returned with the record without requiring another read from the Locks table. For our demonstration, I will use fields within the Customer table for tracking the locking status.

CREATE TABLE [dbo].[Customers]
(

[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,

[Firstname] [varchar] (50)NULL ,

[Lastname] [varchar] (50)NULL ,

[Company] [varchar] (50)NULL ,

[Address] [varchar] (200)NULL ,

[City] [varchar] (50)NULL ,

[State] [varchar] (50)NULL ,

[Zip] [varchar] (11)NULL ,

[PaymentTerms] [varchar] (50)NULL ,

[CreditLimit] [money] NULL ,

[CreateDT] [datetime] NULL ,

[CreatedBy] [varchar] (50)NULL ,

[UpdateDT] [datetime] NULL ,

[UpdatedBy] [varchar] (50)NULL ,

[Deleted] [bit] NULL ,

[Locked] [bit] NULL ,

[LockedBy]
[varchar] (50)NULL

) ON [PRIMARY]

END

The other major element of the system is that we will require two Command objects (each using separate stored procedures) for selecting data for the detail view. The first will provide a read only view. The second will lock the record and allow it to be updated.

Viewing the List

The list view for the pessimistic locking scenario is similar to the one we used with the optimistic locking example, however, this time we are displaying additional fields to indicate whether the record is locked and who has it locked.

Fig #7 - Pessimistic Customer List

Opening a Detail

The Detail view for the pessimistic lock is opened in a very similar process to the optimistic locking example. The user clicks the "Select" link in the list, which causes the form to redirect to the Detail form. Here is the Page_Load event for the Detail form:

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("CustomersPessimisticList.aspx")

EndIf

enableForm(False)

IfNot IsPostBack Then

Try

CustomersDetailDataSet1 = _

handler.GetCustomersPessimisticDetailDataset(CustomerID)

DataBind()

checkIfLocked()

'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

Viewstate("CustomersDetailDataset1") = _

CustomersDetailDataSet1

Else

CustomersDetailDataSet1 = _

Viewstate("CustomersDetailDataset1")

EndIf

EndSub

Fig #8 - Pessimistic Customer Detail - Read-Only

The event method retrieves the CustomerID from the session and uses it to select the record from the database through the GetCustomersPessimisticDetailDataset method on the Handler component. If everything goes well, we then check to see if the selected record is locked through the checkIfLocked method.

PrivateSub checkIfLocked()

Dim row As CustomersPessimisticDetailDataSet.CustomersRow

'get the
first row from the data set

row = CustomersDetailDataSet1.Customers.Rows(0)

IfNot IsNothing(row) Then

IfNot row.IsLockedByNull Then

If row.Locked = TrueAnd _

row.LockedBy <>
User.Identity.Name() Then

ErrorLabel.Visible = True

ErrorLabel.Text = _

"Customer record
is locked by " & row.LockedBy

CheckoutButton.Enabled = False

EndIf

EndIf

EndIf

EndSub

If locked, this method will indicate who has the record locked and will disable the Checkout button.

Fig #9 - Pessimistic Customer Detail - Locked

When the Detail form opens, you will notice that the textboxes are grayed out and uneditable. This is handled by the enableForm method which will disable all the textboxes on the form and make sure the Checkout button is visible while the Update and Cancel buttons are not.

PrivateSub enableForm(ByVal b AsBoolean)

CompanyTextBox.Enabled = b

FirstNameTextBox.Enabled = b

LastNameTextBox.Enabled = b

AddressTextBox.Enabled = b

CityTextBox.Enabled = b

StateTextBox.Enabled = b

ZipTextBox.Enabled = b

PaymentTermsTextBox.Enabled = b

CreditLimitTextBox.Enabled = b

CheckoutButton.Visible = Not b

UpdateButton.Visible = b

CancelButton.Visible = b

EndSub

When a user clicks the Checkout button, the form is posted back to the server and the following event is called:

PrivateSub CheckoutButton_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles CheckoutButton.Click

'select
locked record

Dim CustomerID AsInteger

IfNot (Session("SelectedCustomerID")) Then

CustomerID = Session("SelectedCustomerID")

Else

Response.Redirect("CustomersPessimisticList.aspx")

EndIf

Try

'get the
dataset with while setting database lock

CustomersDetailDataSet1 = _

handler.GetCustomersPessimisticDetailLockDataset(_

CustomerID, _

User.Identity.Name())

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

enableForm(True)

EndSub

The primary focus of this method is to call the GetCustomersPessimisticDetailLockDataset method of the Handler component. This method will use the alternate Data Adapter for setting the lock while retrieving the record.

PublicFunction GetCustomersPessimisticDetailLockDataset(_

ByVal id AsInteger, ByVal lockedBy AsString) _

As CustomersPessimisticDetailDataSet

Dim ds AsNew CustomersPessimisticDetailDataSet()

Try

'set the
ID parameter for the customer

CustomersPessimisticDetailLockDataAdapter._

SelectCommand.Parameters("@CustomerID").Value = id

CustomersPessimisticDetailLockDataAdapter._

SelectCommand.Parameters("@LockedBy").Value = lockedBy

CustomersPessimisticDetailDataAdapter.Fill(ds)

Return ds

Catch exc As Exception

Dim msg AsString

msg = "Error reading Customer
record from Database."

ThrowNew DALException(msg, exc, ds)

EndTry

EndFunction

The actual lock is set within the special SelectLock stored procedure used by the Select Command.

ALTER PROCEDURE dbo.dalsp_CustomersPessimisticDetail_SelectLock

(

@CustomerID int,

@LockedBy varchar(50)

)

AS

SET NOCOUNT ON;

BEGIN TRANSACTION SERIALIZABLE

UPDATE Customers

SET Locked
= 1, LockedBy = @LockedBy

FROM Customers

WHERE (CustomerID
= @CustomerID)

AND (LockedBy
= @LockedBy OR LockedBy IS NULL)

If @@ROWCOUNT
> 0

Begin

SELECT CustomerID,
Firstname, Lastname, Company,

Address, City, State, Zip,

PaymentTerms, CreditLimit, CreateDT, CreatedBy,

UpdateDT, UpdatedBy, Deleted, Locked, LockedBy

FROM Customers

WHERE (CustomerID
= @CustomerID)

End

COMMIT TRANSACTION

Now the user can freely make changes and be guaranteed that there will not be another user editing the record at the same time.

Fig #10 - Pessimistic Customer Detail - Edit Mode

Once the changes are complete, the user clicks the Update button. This will call the Update Button event which will then call the Update stored procedure:

ALTER PROCEDURE dbo.dalsp_CustomersPessimisticDetail_Update

(

@Firstname varchar(50),

@Lastname varchar(50),

@Company varchar(50),

@Address varchar(200),

@City varchar(50),

@State varchar(50),

@Zip varchar(11),

@PaymentTerms varchar(50),

@CreditLimit money,

@CreateDT datetime,

@CreatedBy varchar(50),

@UpdateDT datetime,

@UpdatedBy varchar(50),

@Deleted bit,

@Locked bit,

@LockedBy varchar(50),

@Original_CustomerID int,

@CustomerID int

)

AS

SET NOCOUNT OFF;

UPDATE Customers

SET Firstname
= @Firstname,

Lastname = @Lastname,

Company = @Company,

Address = @Address,

City = @City,

State = @State,

Zip = @Zip,

PaymentTerms = @PaymentTerms,

CreditLimit = @CreditLimit,

CreateDT = @CreateDT,

CreatedBy = @CreatedBy,

UpdateDT = @UpdateDT,

UpdatedBy = @UpdatedBy,

Deleted = @Deleted,

-- clear lock

Locked = 0,

LockedBy = Null

WHERE (CustomerID = @Original_CustomerID)

-- confirm user is the same

AND (Locked
= 1)

AND (LockedBy
= @LockedBy);

SELECT CustomerID,
Firstname, Lastname,

Company,
Address, City, State, Zip, PaymentTerms,

CreditLimit,
CreateDT, CreatedBy, UpdateDT, UpdatedBy,

Deleted,
Locked, LockedBy

FROM Customers

WHERE (CustomerID
= @CustomerID)

Note that the procedure double-checks if the record is still locked by the current user in the WHERE clause of the Update statement.

Checking that the Pessimistic Locks Work

You can ensure that the pessimistic locks are working in a test similar to the one we performed on the optimistic locks version. Simply open two browser windows pointing to the application. In one, open a customer record. In the second window try to open the same customer record. The system will tell you the record is locked.

Other Locking Considerations

Here are several other things you may need to consider if you are implementing a concurrency locking strategy.

Pessimistic Lock Timeouts and Overrides

One of the drawbacks of using pessimistic locks is that locks can be set then forgotten. Therefore, you may need to provide some way for the application to recover so that the entity in question can be accessed again. One approach is to allow locks to timeout. In this case, you would need to provide a DateTime data field to track when the lock was set. Then if the lock is older then some predetermined amount of time, you would ignore it, allowing users to access the record for updates.

Another approach is providing the ability to define permissions to allow an administrator to override or clear previously set locks. This may be especially important if you are not providing timeouts.

Summary

While ADO.NET easily provides the ability to capture optimistic concurrency errors, you will need to do a bit of work to make your user's experience is more pleasurable. Fortunately, the system is flexible enough to accommodate this in addition to implementing pessimistic application locking.

Until next time...

About the Author

Wayne Plourde is a consulting Software Architect who began his career as a building architect twenty years ago. In 1995, he succumbed to the call of the World Wide Web, and since then has been designing sophisticated Web-based and client-server applications for corporations around the country. Wayne holds both MCSD and SCJP certifications and has just completed his .NET MCAD certification. You can contact Wayne at wayne@plourdenet.com or visit his Web site at http://www.plourdenet.com.