ASP.NET Optimistic Concurrency Control

• Download source files - 37.7 Kb

Introduction

Have you ever got a call from a user telling you that he/she edited a record and that the changes made are lost? If so, maybe your application is implementing "Last in Wins" concurrency control.

This article focuses on the "Optimistic concurrency control", doing it manually with out the DataSet. On a second part, I will focus on "Pessimistic concurrency control".

Background

There are three types of concurrency control:

• Pessimistic concurrency control - a row is unavailable to users from the time the record is fetched until it is updated in the database.
• Optimistic concurrency control - a row is unavailable to other users only while the data is actually being updated. The update examines the row in the database and determines whether any changes have been made. Attempting to update a record that has already been changed, results in a concurrency violation.
• "Last in wins" - a row is unavailable to other users only while the data is actually being updated. However, no effort is made to compare updates against the original record; the record is simply written out, potentially overwriting any changes made by other users since you last refreshed the records.

The full article can be found here.

Pessimistic concurrency control

While in a disconnected architecture the "Pessimistic concurrency control" can not be implemented using database locks, it can be implemented as follows:

• Using lock-bits at the table row level and maintain them in a session pool.
• Clear the lock-bits when the user leaves the page under program control. This has to be done to free the locks as soon as possible, but there is no guarantee that this occurs.
• As the user can leave the browser or the site at any moment, we have to clear the lock-bits stored is the session pool on Session_End.
• And as a final countermeasure, we might have a demon running on the server cleaning old locks.

We have to keep in mind that an application that holds locks for long periods is not scalable, but this concurrency control schema might have to be implemented on some portions of the application.

"Last in wins"

To implement this concurrency control, we do not have to do anything. But this might be unacceptable in some circumstances if different users start to access the same records frequently.

As explained at MSDN:

• User A fetches a record from the database.
• User B fetches the same record from the database, modifies it, and writes the updated record back to the database.
• User A modifies the 'old' record (or just clicks on Accept) and writes it back to the database.

In the above scenario, the changes user B made were never seen by user A. Be sure that this situation is acceptable if you plan to use the "Last in wins" approach of concurrency control.

Optimistic concurrency control

This article focuses on this approach and I will go deeply through it.

The optimistic concurrency control is based on a Record Version. Several users can open a page for the same record, but only the first one to save it will succeed.

How it works

• User A fetches a record from the database along with its version, more on this later.
• User B fetches the same record from the database, also with its version and writes the updated record back to the database, updating the version number.
• User A "tries" to write the record to the database, but as the version on the database is different from the version hold by user A, the write fails, leaving user B's changes intact.

The record "Version"

The .NET DataSet uses the "All values" approach, comparing old vs. new values for all the fields. This gives a version for the record, but if you are not using the DataSet, it can be a nightmare. First, you have to consider the possible null values and the where clause has to be changed if a new field is added to the table.

Instead of "All values", we can use a single field to set the record version. For this purpose we can use a GUID string or a date-time value. I decided to use the date-time for the following reasons:

• The GUID is not portable if we want to port the code to other platforms.
• The GUID uses 40 bytes instead of the 8 bytes used by the date.
• The date-time tell us when the record was last updated.

Checking for optimistic concurrency

When updating the record, the version has to be added to the WHERE clause:

UPDATE table SET fields = values WHERE pk 
= pk_vlaue AND concurrency = concurrency_value

If the concurrency value given by the user does not match the concurrency value in the database, the record will not be updated and 0 rows affected will be returned.

With this SELECT we have to consider that, while a user was modifying the record, another user might have deleted the same record. In this case, the rows affected will also be 0.

If you want to give the user an accurate message about the situation, we can not tell that the record was updated by another user if it was deleted. To solve this, we have to check if a record with the primary key exists in the table.

SELECT COUNT(*) FROM table WHERE pk = pk_value

At this point, if the record count is zero, the record was deleted, other wise; a concurrency exception has to be thrown.

Handling the user interface

Finally, we have to inform the user that his/her changes were not successful because another user changed the data.

What to do now? Just display a JavaScript alert or a pop-up window?

What happens if user A changed 10 fields and user B (who saved first) only changed one? Will user A loose all the 10 fields?

The approach implemented here, is to reload the page and replace only the data that was modified by user B, leaving user A's changes intact as possible.

Setup

The database

The example code was developed to use the Northwind database installed with SQL Server. The following stored procedures need to be created (The stored procedures are in the zip file):

• CategoriesList
• ProductsByCategory
• SuppliersList
• ProductsRead
• ProductsUpdate

Add to the Products table the field Concurrency as DateTime.

Set the Concurrency field to some value. Example: 01/01/2003 for all the records. It will not work with null values. When you create the INSERT method, it has to set Concurrency to GETTIME().

The connection string

Set the appropriate Data Source, uid, pwd values in the Web.config file.

<appSettings>
     <add key="SQLConnString" 
    value="Data Source=(local);uid= ;pwd= ;database=Northwind"/>
</appSettings>

Installing the application

You have to setup manually the virtual directory where the application will run. This can be done with "Internet Information Services" or just from the Windows Explorer. Right click on the folder, press properties, go to the "Web Sharing" tab and select "Share this folder".

How it works

Flow

• The product list is loaded and shown on Default.aspx
• When "Edit" is clicked on a product, the Product.aspx page is loaded with the product ID to edit in the parameter ProductID.
• Page_Load() is called and the categories and suppliers combo boxes are loaded.
• The product's data is loaded and set to the web controls. Note that at the end of the procedure, the SetConcurrencyObject(product) method is called. This stores the object as BLL.CRUD, as it was read from the database so it can be compared later in case of a concurrency conflict.
• When the user clicks the "Accept" button, the Save() method is called. At this point is where the concurrency control starts.

The Update method is called on the product:

try
{
    // Update the product

    product.Update();
    
    // Redirect to the product's list

    Response.Redirect("Default.aspx");
}
catch (DeletedRowInaccessibleException)
{
    Response.Redirect("Error.aspx?msg=" + System.Web.HttpUtility.UrlEncode
("The product has been deleted by another user."));
}
catch (DBConcurrencyException)
{
    ConcurrencyException();
}
catch (Exception ex)
{
    throw ex;
}

If the product was deleted by another user, the flow is redirected to the error page. If there was a concurrency exception, the method ConcurrencyException() is called to process the exception and display the fields that are conflicting.

private void ConcurrencyException()
{
    // Get the mapping controls - object properties 

    Hashtable controls = GetControlsMap();
    
    // Update the page to show the fields that have concurrency conflicts

    ShowConcurrencyFields(controls);
    
    // Show the concurrency error label

    lblConcurrencyMsg.Visible = true;
}

The method GetControlsMap(), gets a one-to-one mapping between the web controls and the object properties. This allows changing the control's look & feel if there is a concurrency conflict with any object's property.

Any page that needs to process a concurrency exception has to inherit from BasePage.

The heart of the concurrency handle is at ShowConcurrency().

The original object as it was read is retrieved from the view state:

BLL.CRUD userObject = (BLL.CRUD) ViewState[CONCURRENCY_OBJECT];

Then the object's new data has to be read so it can be compared with the original data read before the concurrency conflict. This is done with generic code, invoking the Read() method on a BLL.CRUD object; to do this, the objects must inherit from BLL.CRUD.

// Instantiate an object of the same type and read its properties

Type type = userObject.GetType();
BLL.CRUD dbObject = (BLL.CRUD) type.Assembly.CreateInstance(type.FullName);
dbObject.ID = userObject.ID;
if (!dbObject.Read())
    Response.Redirect("Error.aspx?msg=" + System.Web.HttpUtility.UrlEncode
("The record has been deleted by another user."));

Once the new object is read, the differences are gotten using reflection:

IList differences = BLL.ObjectDifference.GetDifferences(dbObject, 
userObject);

At last, the web controls styles are changed to show the user, the conflicting fields:

foreach (BLL.ObjectDifference diff in differences)
{
    // Get the control

    WebControl ctrl = controls[diff.PropertyName] as WebControl;
    if (ctrl != null)
    {
        :
        :
        :
    }
}

The stored procedures

All the stored procedures are basically SELECTs to retrieve data. So I will only focus on ProductsUpdate:

CREATE PROCEDURE ProductsUpdate
(
        @ProductID int,
        @CategoryID int,
        @SupplierID int,
        @Name varchar(40),
        @QuantityPerUnit varchar(20),
        @UnitPrice decimal(19,4),
        @UnitsInStock smallint,
        @UnitsOnOrder smallint,
        @ReorderLevel smallint,
        @Discontinued bit,
        @Concurrency datetime
)
AS
    UPDATE
        Products
    SET
        ProductName = @Name,
        SupplierID = @SupplierID,
        CategoryID = @CategoryID,
        QuantityPerUnit = @QuantityPerUnit,
        UnitPrice = @UnitPrice,
        UnitsInStock = @UnitsInStock,
        UnitsOnOrder = @UnitsOnOrder,
        ReorderLevel = @ReorderLevel,
        Discontinued = @Discontinued,
        Concurrency = GETDATE()        -- When updated, set the 

                                          Concurrency to the server's date
    WHERE
        ProductID = @productID AND
        Concurrency = @Concurrency
        
    IF @@ROWCOUNT = 0
        BEGIN
            IF EXISTS( SELECT ProductID FROM products 
                          WHERE ProductID = @productID )
                RETURN 2    -- Concurrency conflict
            ELSE
                RETURN 1    -- The record has been deleted
        END
    ELSE
        RETURN 0            -- The record could be updated

You can notice that the WHERE clause for the UPDATE, the ProductID and Concurrency are queried together. The ProductID is the primary key and the Concurrency field guaranties that we are not updating a record modified by another user since the data was gathered.

If the @@ROWCOUNT is greater than zero (It should be 1 because of the primary key), the record could be updated without concurrency conflicts.

If the @@ROWCOUNT is zero, there are two possibilities:

• The record was deleted by another user, which is checked by IF EXISTST
• There is a concurrency conflict.

For each case, the stored procedure returns a value:

• 0: The record could be updated
• 1: The record has been deleted
• 2: Concurrency conflict

When the update query is executed, the return values are checked in BLL.Product.cs Update() and the appropriate exception is thrown.

// Check for success

switch ( (UpdateRecordStatus) parms[11].Value)
{
    case UpdateRecordStatus.Concurrency:
        throw new DBConcurrencyException("The record has 
            been modified by another user or process.");
    case UpdateRecordStatus.Deleted:
        throw new DeletedRowInaccessibleException();
}

Trying it out

To try out the concurrency control:

• Open two different instances of the application.
• Click "Edit" for the same product on both pages.
• Change some values on the first page and "Accept" it.
• Change some values on the second page and "Accept" it.

When accepting the second page, the fields that were changed on the first page will become gray and the fields that are not conflicting will be unchanged.

Limitations and things to improve

• The BLL objects must have as a primary key only one field. This is normally how I design the tables, but there might be an exception to this.
• Use styles (CSS) to improve the display of concurrency conflicts.

Conclusion

Concurrency control increases the application's complexity, debugging and maintenance, but your users will have a better experience using it.

Remember that it is not mandatory to implement the optimistic concurrency control for all the application updates. You might need to mix the 3 mechanisms within a single application based on the needs.

History

• August 20, 2003 - version 1.0
  • Initial version.