Implementing Optimistic Concurrency with SQL Timestamps

Author: Will Shaver
Editors: Neil Vance, Katy Kaiser and friends
Date: July 30th, 2007
Example Files: TimestampConcurrency.exe Self Extracting / TimestampConcurrency.rar

Introduction

In this tutorial I’ll be covering how to implement optimistic concurrency with the use of a timestamp field. This tutorial serves as an adjunct to the existing (and wonderful) tutorial series written by Scott Mitchell.

I use quite a bit of the same code and concepts from the asp.net data access tutorial series so I highly recommend you read up on them before continuing here. The tutorials that you should pay extra attention to are:

• Creating a Data Access Layer
• Creating a Business Logic Layer
• Displaying Data with the ObjectDataSource
• Implementing Optimistic Concurrency

As Scott does in his tutorials, I’m using the Northwind sample database in the App_Data folder for this tutorial. As my Visual Basic coding skills are sub-par, this tutorial is only available in C#. I’m not going to cover everything discussed in the above tutorials, instead I’ll focus on the changes needed to implement optimistic concurrency using timestamps.

The basic concept for implementing optimistic concurrency with a timestamp is to pass it to the client with the remainder of the editable data, and then when the round-trip is complete, to check it against the current version of the timestamp value in the database. The main advantage of using the timestamp instead of the default implementation of optimistic concurrency is that comparing against all fields tends to be costly on resources and page weight. This cost can be even higher if there are fields of type text or varchar(max).

The timestamp field is somewhat of a misnomer in that it doesn’t have anything to do with time (or stamps), and everything to do with version control. The actual data stored in the timestamp field cannot be converted to a date, as it is much closer to a simple integer. The timestamp field is imbued with the following special properties:

• Read Only
• Guaranteed unique across the entire database
• Automatically updated to a new value every time any field in the row is changed
• The field can have any name, including timestamp
• timestamp sometimes goes by the ninja-name rowversion, but the table definition still uses timestamp
• Adds 3-5 cold damage per attack

The default ObjectDataSource implementation of optimistic concurrency doesn’t (easily) allow for comparing only the timestamp field on update, instead the implementation of the ConflictDetection property only allows for OverwriteChanges or CompareAllValues. Ideally we’d like a third option called something like CompareTimestamp. (I tried to implement one with Lutz’s reflector, but the use of sealed and internal types made it impossible to inherit from ObjectDataSource and ObjectDataSourceView properly.)

Enough introduction, on with the tutorial!

Step 1: Setting up the Database

Create a new website in Visual Studio. (I’m putting mine in C:\TimestampConcurrency.) Add the Northwind.MDF files to the App_Data folder. Add the App_Code folder to your website. (You did read the tutorials I mentioned above right? I told you I’m not covering the easy stuff, the first tutorial linked above goes over all this.)

Open your newly installed database in the server explorer. Right click on the Products table and select Open Table Definition. This will display a list of fields with Column Name, Data Type, and Allow Nulls options for each. Add a field called timestamp with the Data Type of timestamp, and make sure that Allow Nulls is not checked.

Save your changes and close the table. You will note that if you right click on the table now and choose Show Table Data it lists the timestamp field as <Binary data> which isn’t too helpful. If you’d like to see this data, you can with the following select statement:

select convert(int, timestamp) as version from products

Note that it actually should be a bigint, but Visual Studio and SQL Server Management Studio are bugged on the conversion of bigint and some other data types. (The query still works, the display doesn’t.) I encourage you to vote for fixing this bug, but it won’t prevent us from implementing timestamp concurrency, so read on.

Step 2: Implement the Data Access Layer with an XSD file

Add a DataSet called Northwind.xsd to your App_Code folder. Have it save the connection string to your Northwind database, and select Use SQL Statements. This method also works fine with stored procedures if you’d like to go that route, but we’ll keep it simple with standard SQL statements. For your query, use the famous Select * from Products and be sure to check all three options in the advanced settings.

Name the queries FillProducts and GetProducts on the next screen, and then finish the wizard. Now add a second SQL statement query to your ProductsTableAdapter by right clicking on it and choosing Add Query. Use Select * from Products where ProductID = @ProductID as your query. Name the functions FillByProductID and GetProductByProductID, exactly as in the standard asp.net tutorials. Save your finished Northwind.xsd file.

Step 3: Examine the Generated Queries

This step isn’t really necessary, but helps explain what’s going on behind the scenes. Right click anywhere on your xsd file and choose View Code.

After opening the code, take a minute to scroll through it, enjoying all the code that you weren’t forced to write. If everything went as planned, there will be five different SQL queries listed in this file. (Insert, Delete, Update, Select, Select by ProductID.) Find the <DeleteCommand> element and review its <CommandText>. At the end of the query should be a WHERE clause, with the timestamp field automatically included as AND ([timestamp] = @Original_timestamp). The <UpdateCommand> has the exact same ending: the current timestamp is checked against the one that was current when editing began.

Step 4: Create the Business Logic Layer

Grab the completed ProductsBLL.cs file directly from the Creating a Business Logic Layer tutorial. There are a couple of minor modifications that need to happen as follows: First, delete the GetProductsByCategoryID and GetProductsBySupplierID functions. You’re welcome to implement them on your own, but they aren’t critical to optimistic concurrency so I’m leaving them out. Next get rid of the business rule in the UpdateProduct function regarding discontinued products. Business rules are fine, but again, aren’t critical to this tutorial.

In the DeleteProduct method you’ll note that the Adapter.Delete method is now requiring two different parameters. A productID is needed to specify which row to delete, and a byte array called Original_timestamp. Remember that the timestamp is needed because it is now a parameter in the delete statement we inspected above. The .Net Framework treats timestamps as byte arrays, so we’ll add a byte array called timestamp to our method, and pass it into the Adapter.Delete method.

    [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
    public bool DeleteProduct(int productID, byte[] timestamp)
    {
        int rowsAffected = Adapter.Delete(productID, timestamp);

        // Return true if precisely one row was deleted, otherwise false
        return rowsAffected == 1;
    }

In the UpdateProduct method we’ll also add in our timestamp variable. Then we’ll use the built in DataRow concurrency check features outlined in tutorial 21 on optimistic concurrency. The good news is that instead of assigning every original field into the old version of the DataRow as is necessary when using standard optimistic concurrency, only the version needs to be compared. Add the timestamp to the method parameters, and the two lines for assigning the timestamp and accepting changes.

    [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
    public bool UpdateProduct(string productName, int? supplierID, int? categoryID, string quantityPerUnit,
                              decimal? unitPrice, short? unitsInStock, short? unitsOnOrder, short? reorderLevel,
                              bool discontinued, int productID, byte [] timestamp)
    {
        Northwind.ProductsDataTable products = Adapter.GetProductByProductID(productID);
        if (products.Count == 0)
            // no matching record found, return false
            return false;

        Northwind.ProductsRow product = products[0];

        product.timestamp = timestamp;
        product.AcceptChanges();

        product.ProductName = productName;
        if (supplierID == null) product.SetSupplierIDNull(); else product.SupplierID = supplierID.Value;
        if (categoryID == null) product.SetCategoryIDNull(); else product.CategoryID = categoryID.Value;
        if (quantityPerUnit == null) product.SetQuantityPerUnitNull(); else product.QuantityPerUnit = quantityPerUnit;
        if (unitPrice == null) product.SetUnitPriceNull(); else product.UnitPrice = unitPrice.Value;
        if (unitsInStock == null) product.SetUnitsInStockNull(); else product.UnitsInStock = unitsInStock.Value;
        if (unitsOnOrder == null) product.SetUnitsOnOrderNull(); else product.UnitsOnOrder = unitsOnOrder.Value;
        if (reorderLevel == null) product.SetReorderLevelNull(); else product.ReorderLevel = reorderLevel.Value;
        product.Discontinued = discontinued;

        // Update the product record
        int rowsAffected = Adapter.Update(product);

        // Return true if precisely one row was updated, otherwise false
        return rowsAffected == 1;
    }

Now save your ProductsBLL.cs file and build the project. Fix any errors before continuing onto step 5.

Step 5: Configuring your ObjectDataSource and GridView for Deleting Rows

To use this beautiful Business Logic Layer that we’ve created, we’ll need to add an ObjectDataSource to our page. Find the Default.aspx file and open it in design view. Drag and drop from the toolbox an ObjectDataSource and a GridView. Click on the smart tag for your ObjectDataSource and select Configure Data Source, and then choose our ProductsBLL from the drop down list.

Remove the original_ from the ObjectDataSource‘s OldValuesParementerFormatString property. Leave ConflictDetection on OverwriteChanges. If we leave the original_ in the list, the ObjectDataSource will have the wrong number of parameters for the ProductBLL class’s delete and update methods.

Now use the GridView‘s smart tag to choose a data source, pointing it to our newly created ObjectDataSource1 control. Enable both Deleting and Editing via the checkboxes on the smart tag. Note that .Net has automatically created a number of fields in our GridView for us, but has left out our timestamp field. This is actually the desired behavior as the timestamp will never contain data useful to a user. (Remember, no jets on the jet-way, no time in the SQL timestamp.)

Run the website with debugging enabled, and click Delete on a row you’ve selected for termination. (That extra cold damage comes in handy.) … Exception! Value cannot be null. Hit your browser’s back button, and put a breakpoint in the ProductBLL‘s delete function. Now attempt to delete another row, and note that the timestamp is null. It isn’t being passed to the method properly, because ASP.Net doesn’t realize that it is a necessary data key. Stop debugging, and open up the properties for your GridView Control. To get ASP.Net to properly pass the timestamp add it it to the DataKeyNames property.

Start your website in debug mode, and delete a row. It should work, unless you still have order details data blocking you because of a foreign key constraint. To eliminate this problem run the simple queries DELETE FROM [Order Details] and DELETE FROM Orders on your database. You should now be able to delete rows from the table.

Step 6: Configuring your ObjectDataSource and GridView for Editing Rows

Now that deleting is working, edit the values of one of the rows. Exception! Column ‘timestamp’ is read only?!

This field is read only in the database, and Visual Studio automatically imported this property when we created the table definition in our xsd file. To be able to properly use the concurrency checking of the DataRow we’ll need to make it not read only. Find your Products table definition in your Northwind.xsd file and set ReadOnly to false.

I’ll take a moment here to rant: why is this a run-time error and not a compile time error? If the field is marked as read only, the property should only have a get and no set forcing the developer to fix this problem at compile time. Remember that this exception isn’t going out to the database as we’re able to fix it by changing the Data Set definition. /rant

Run the website in debug mode again, and editing should now work perfectly. To prove that our new concurrency detection methods are functioning, select a row for editing and then open up a new browser window of the same page, editing the same row. Make a change to the row on the first page, and save it, then switch to your second page, make a change and save it. Exception! Concurrency violation. This time the exception is the desired behavior as we shouldn’t be able to update the row without getting the newest version. To handle the exception a bit more gracefully than the current error page, we’ll implement the error messages from the tutorial on Optimistic Concurrency. Follow the steps outlining how to add a label that will handle concurrency exceptions.

Copy and paste both the Exception labels and event methods directly into your Default.aspx.cs file from Step 6 of the Optimistic Concurrency tutorial. Rename the deleted method to ProductsDataSource_Deleted and add it to the OnDeleted action in your ObjectDataSource1 control. Set the OnRowUpdated of your GridView1 control to point at the ProductsGrid_Rowupdated function. Attempting to delete or update a previously updated row will now result in a much nicer error message.

Step 7: Add a DetailsView control for Inserting Data

Drag a DetailsView control onto your page and select your ObjectDataSource1 control from the Choose Data Source dropdown in the Smart Tag. In the properties sheet for the Details View set the DefaultMode to Insert. This should cause your DetailsView to be a list of blank fields. If you’d like, you can refer to the other tutorials on how to clean up the SupplierID and CategoryID fields so that they have a DropDown full of items instead of requiring an ID.

You should be able to add rows to the database with this insert control, leaving the SupplierID and the CategoryID blank.

Summary

Concurrency violations can be a massive headache to track down and fix, causing frustration for software users and developers. The best practice approach is to avoid these types of problems up front with a good concurrency strategy such as the one outlined above. These slight modifications to the Typed DataSet will allow for easy use of a timestamp field in multiple tables, table adapters, queries, and pages with minimal editing. The timestamp field is an excellent addition to your SQL arsenal, and when used properly can provide major speed gains for your page.

For each table there are really only a few things that need attention: add a timestamp field, generate the queries with optimistic concurrency turned on, remove the original_ from the OldValuesParementerFormatString, enable editing on the timestamp field and add it to the DataKeyNames parameter.

Good Luck