CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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 B7 Retrieve record for update B7 Make changes B7 Check if the underlying record in DB has changed B7 If no changes, Update DB B7 Retrieve record for update B7 If DB permits read of the record, DB will hold a lock on record (must B7 Make changes B7 Update DB B7 DB lock is released B7 Check if there are locks on the records B7 Programmatically set lock on record B7 Retrieve record for update B7 Make changes B7 Update DB B7 Programmatically release lock on record
maintain the connection)
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 B7 Easy to implement in .NET B7 Can be disconnected B7 All records can be read anytime B7 No warning prior to update B7 User may need to make a complex decision on how to B7 Must persist the Dataset between read and write Pessimistic Database Locking B7 Allows upfront warning before editing B7 Relies on built database locks B7 Must maintain connection to the database for the B7 Locks may need to be managed B7 Records cannot be read when locked. Pessimistic Application Locking B7 Allows upfront warning before editing B7 Can be use in either connected or disconnected mode B7 Can easily provide more details on who holds the B7 All records can be read anytime B7 No need to persist Dataset B7 Requires implementing custom logic and schema B7 May need to provide support to manage or override
proceed
entire transaction
lock
support in the database
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. 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. You can probably guess what these new menus are for. Go ahead and click on the Optimistic Locking link for our first demonstration.
Fig. #1 – Login Screen
Once you login, you will be redirected to the main screen. On the menubar, you will see two new additions:
Fig. #2 – Welcome Screen
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 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. 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. 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. 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.
Fig #3 – Customer List
= _
Fig #4 – Optimistic Customer Detail
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 Viewstate("CustomersDetailDataset1") = _ CustomersDetailDataset1 Catch exc As DALException ‘hide FormPanel.Visible = False ‘show ErrorLabel.Visible = True ErrorLabel.Text = exc.Message ErrorLabel.Text &= "<br>Contact system administrator."
the dataset into the viewstate
form
error
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. Then do the following: 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: 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 loadDatasetForUpdate() Try ‘update handler.UpdateCustomersOptimisticDataset(_ CustomersDetailDataset1) ErrorLabel.Visible = True ErrorLabel.Text &= "Customer record successfully saved"
Fig #5 – Opening Two Detail Windows for testing
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
from form into the dataset
the dataset through the handler component
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)
‘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.
PublicSub OverwriteCustomersOptimisticDataset(ByVal ds As CustomersOptimisticDetailDataSet)
Try
‘temporarily
swap the update command to allow overwrite
CustomersOptimisticDetailDataAdapter.UpdateCommand =
CustomersOptimisticOverwriteCommand
CustomersOptimisticDetailDataAdapter.Update(ds)
ds.AcceptChanges()
Catch exc As Exception
Dim msg AsString
msg = "Error while updating
Customer."
ThrowNew DALException(msg, exc, ds)
EndTry
EndSub
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] ) 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.
(
[varchar] (50) NULL
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 = Else Response.Redirect("CustomersPessimisticList.aspx") EndIf enableForm(False) IfNot IsPostBack Then Try CustomersDetailDataSet1 = _ handler.GetCustomersPessimisticDetailDataset(CustomerID) DataBind() checkIfLocked() ‘put Viewstate("CustomersDetailDataset1") = _ CustomersDetailDataSet1 Catch exc As DALException ‘hide FormPanel.Visible = False ‘show ErrorLabel.Visible = True ErrorLabel.Text = exc.Message ErrorLabel.Text &= "<br>Contact system administrator."
Session("SelectedCustomerID")
the dataset into the viewstate
form
error
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 row = CustomersDetailDataSet1.Customers.Rows(0) IfNot IsNothing(row) Then IfNot row.IsLockedByNull Then If row.Locked = TrueAnd _ row.LockedBy <> User.Identity.Name() Then
first row from the data set
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 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 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 FROM Customers WHERE (CustomerID AND (LockedBy If @@ROWCOUNT > 0
ID parameter for the customer
record from Database."
= 1, LockedBy = @LockedBy
= @CustomerID)
= @LockedBy OR LockedBy IS NULL)
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 [email protected] or visit his Web site at http://www.plourdenet.com.
