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
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."
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
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, _
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
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.
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
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.
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier. [Read This Article][Top]
In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a new MySQL user to the database server, assign the user the appropriate permissions, connect to the database, and build a simple ASP.NET page to perform a query. [Read This Article][Top]
Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL. [Read This Article][Top]
Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different methods and highlights the different issues associated with each of them. [Read This Article][Top]
There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and walks you through the debugging process step-by step. [Read This Article][Top]
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams. [Read This Article][Top]
One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope object in ADO.NET 2.0. [Read This Article][Top]
Developers often use brute force coding to marshal data between the GUI and application objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding functionality to make this job much easier. [Read This Article][Top]
Learn how to create a console application to queue a message in Microsoft Message Queuing (MSMQ) and then use an extended stored procedure to call the console application from a SQL Server trigger. [Read This Article][Top]
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. This article shows how to monitor the connection pool, diagnose a potential problem, and apply the appropriate fix. [Read This Article][Top]
Mailing List Want to receive email when the next article is published? Just Click Here to sign up.
