Practical ASP.NET
Handling Data Contention with Optimistic Concurrency
Optimistic concurrency doesn't work for most tables, but Peter shows you how to change that.
One way to handle data contention is to turn on optimistic concurrency in your DataSources. However, the problem is that for most tables, it won't work. Here's what optimistic concurrency actually does and what you can do to make it work.
In a previous column, I outlined the problem that occurs when two users access the same record in a Web application, and suggested that you solve the problem by updating only the changed fields in the record. The ASP.NET DataSources offer a built-in solution called "Optimistic Concurrency." Unfortunately, in ASP.NET 2.0/3.5, it doesn't work most of the time.
Optimistic Concurrency
When you check off the Optimistic Concurrency option in a DataSource, you don't enable or disable any form of record locking. Instead, something almost very clever happens: Optimistic Concurrency simply extends the Where clause of your SQL statement to check that the record still has the data from when the record was originally retrieved. So, if the user changes the customer's name from "Irvine" to "Vogel," the Update statement looks like this:
Update CustomerTable
Set LastName = "Vogel"
Where CustomerId = "A123"
And LastName = "Irvine"
And FirstName = "Jan"
And …tests on remaining fields….
If some other user has retrieved the record after you, made a change to some field and put the record back, this Where clause ensures that your update won't find the revised record. The database engine will still use the primary key field to retrieve the record so retrieval will be fast, making the process reasonably efficient.
You do need to write some code to check, after any update, to see if any records have been changed. Here's some typical code for a GridView's ItemUpdated event that checks to see if there's been any updates and, if not, calls the GridView's DataBind to display the new data:
Protected Sub CustomerView_ItemUpdated( _
ByVal sender As Object, _
ByVal e As WebControls.DetailsViewUpdatedEventArgs)
If e.AffectedRows = 0 Then
Me.UpdateErrorLabel.Text = "Record not updated…"
Me.CustomerView.DataBind()
End If
End Sub
After the databind the user will see the changes made by the other user.
The "almost" part comes from a failure in the way that the DataSource clause is extended. ASP.NET 1.1 got it right, generating this code for the Where clause:
Where CustomerId = "A123"
And (LastName = @Original_LastName
Or (@Original_LastName Is NULL
AND LastName Is NULL))
And …more fields…
This is almost unreadable code but what it does is check whether the data originally retrieved (that's what ends up in the @Original_LastName parameter) is NULL and if the data in the table is still NULL. The code generated in ASP.NET 2.0/3.5 doesn't include this test for NULL; if any field in the record has a NULL in it, then the Where clause always returns False and you never get an update.
Optimistic Concurrency Working
There is a solution: Use the TimeStamp (also known as the row version) field. Every database engine -- at least, all the ones that I know of -- have a TimeStamp-type field. This field is (a) never NULL and (b) has a value that changes with every update. Those two characteristics make it an ideal choice to use for checking to see if the record was changed by someone else after you retrieved it -- and it does it without an unreadable Where clause).
To implement this, make life easy on yourself and use the DataSource wizard to generate the initial versions of your Select, Update, Insert and Delete statements using the query-by-form page to select your fields. Then, in the "Custom SQL" page modify, your Select statement to retrieve the TimeStamp column (in SQL Server, the field is called @@DBTS, without any square brackets around it). You might also want to give the column a useful name, as this example does:
Select CustomerID, LastName,
@@DBTS As RowVersion
From CustomerTable
Finally, modify the Update statement's Where clause to use just the table's primary key and the TimeStamp column:
Update CustomerTable
Set LastName = @LastName
Where (CustomerId = @original_CustomerId)
And (@@DBTS = @original_RowVersion)
While you're at it, you can reduce the Where clause in the Delete statement to just the test on the primary key (optimistic concurrency isn't worth much on deletes).
With this simple change and the code in your Updated event, you can reliably (and efficiently) implement optimistic concurrency for Updates.
About the Author
Peter Vogel is a system architect and principal in PH&V Information Services. PH&V provides full-stack consulting from UX design through object modeling to database design. Peter tweets about his VSM columns with the hashtag #vogelarticles. His blog posts on user experience design can be found at http://blog.learningtree.com/tag/ui/.