U2U Article: "Solving Concurrent Updates With the AutoResolver" Copyright © 2004 by U2U nv/sa, Belgium. All rights reserved.
Please address any questions or suggestions to U2U.

Solving Concurrent Updates With the AutoResolver

Article by Peter Himschoot (peter@u2u.be)
U2U - Brussels (www.u2u.net)

Applies to

· .NET Framework version 1.x and 2.0

· Microsoft Visual Studio .NET 2003

· Microsoft Visual Studio .NET 2005

· System.Data

Summary

When you update data in a database with ADO.NET you might run into the problem that some other user has updated the data you want to update too. In this case the DataAdapter will throw the DbConcurrencyException. In this article we will discuss the various ways in which we can solve this problem with concurrent updates. After that we will present the AutoResolver class, which allows us to solve most concurrency problems automatically with only a little bit of configuration.

Introduction

Building distributed enterprise applications has never been easier with .NET. Using ADO.NET to retrieve data from a database, storing the data in a dataset, and data-binding the dataset to your controls is an easy way to do this. However, when you want to update the data in the database things become more difficult. How can we handle the problem when of updating a row in the database that has been changed by other users? ADO.NET by default uses optimistic locking, which comes down to checking if the original row is still the same row. If the row has been changed you get a DbConcurrencyException. Most developers then ask the user to retry the operation. However, if users get this message often, they will get annoyed and think that your application is badly built, even buggy! So how can we solve this?

Using Pessimistic Locking

One way to solve this is to use a pessimistic locking solution. This comes down to flagging the row “in-use”, practically exclusively locking the row for a single user while he (or she) is editing the data. Code that wants to access this row checks this flag before making any changes, waiting until the lock has been lifted. When the user holding the lock updates the row the lock is lifted. Using pessimistic locking will ensure that users never have to retry their changes, at the cost of waiting for the lock to be released. Implementing pessimistic locking is hard to do, but sometimes this is the way to go when you have a high probability of concurrent updates. Have a look at the references at the end of this article for more details.

Using Optimistic Locking with Smart Resolution

Another way to solve the optimistic locking problem is resolving any issues that occur. This may sound hard, by most of the time this is not so difficult to do. Let’s start with an example (using the products table from the Northwind sample database):

Assume you select a row that looks like this:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 100 | 10 | 20 |

====================================================================

And you update the UNITSONORDER column:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 100 | 10 | 30 |

====================================================================

Someone else updated the UNITSINSTOCK column:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 99 | 10 | 20 |

====================================================================

Because you updated another column, you can merge the changes resulting in:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 99 | 10 | 30 |

====================================================================

As you can see, this creates a win-win situation, nobody loses any changes, and nobody has to retry!

Last One Wins

Now how do you solve the problem when the same column was updated? There are different strategies you can use, depending on the type of data. For example, you could just ignore any changes made by other users and do the update anyway. I call this Last One Wins. For example:

Assume you select a row that looks like this:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 100 | 10 | 20 |

====================================================================

And you update the UNITSONORDER column:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 100 | 10 | 30 |

====================================================================

At the same time someone else updated the same column with another value:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 100 | 10 | 25 |

====================================================================

Business logic might dictate that the last one to do the update wins, so this would mean that you don’t care about any changes made, you are just going to overwrite them:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 100 | 10 | 30 |

====================================================================

The first user has lost any changes – Last One Wins! You could use this strategy for example on someone’s address. In this situation the last user probably has the correct address anyway.

Use Db Value

The inverse strategy can also be used, where the first user’s modification is used and the last user’s modification is discarded. I call this Use Db Value. For example:

Assume you select a row that looks like this:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 100 | 10 | 20 |

====================================================================

And you update the UNITSONORDER column:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 100 | 10 | 30 |

====================================================================

At the same time someone else updated the same column with another value:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 100 | 10 | 25 |

====================================================================

Use Db Value ignores your changes and uses the first user’s data:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 100 | 10 | 25 |

====================================================================

Merge Db Value

We will only be able to use “Last One Wins” or “Use Db Value” in very rare cases. However, you can also merge the two values, especially if there represent counters. For example when you’ve decremented a counter, and the other user did the same, you can resolve the concurrency by updating the row with a value – 2.

Assume you select a row that looks like this:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 100 | 10 | 20 |

====================================================================

And you decrement the UNITSINSTOCK column:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 99 | 10 | 20 |

====================================================================

At the same time someone else updated the same column with another value:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 98 | 10 | 20 |

====================================================================

Merging the value results in a UNITSINSTOCK column decremented thrice:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER

====================================================================

"Chai" | 1 | 97 | 10 | 20 |

====================================================================

Again, this way the changes are made correctly and nobody has to re-do anything.

Multi-Column Merge

In the previous examples we only had an update in one column. Of course, in real life you might have made changes to multiple columns, and other users might have done this too. In this case, you can apply a resolution strategy to each column individually. For example this could be the row you originally read from the database:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER REORDERLEVEL

=======================================================================

| "Chai" | 1 | 100 | 10 | 20 | 50 |

=======================================================================

You make the following changes:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER REORDERLEVEL

=======================================================================

| "Chaj" | 4 | 101 | 11.5 | 25 | 51 |

=======================================================================

At the same time another user make the following changes:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER REORDERLEVEL

=======================================================================

| "Chas" | 5 | 102 | 11.5 | 30 | 50 |

=======================================================================

Let’s say (for sake of the example) we determine that we want to resolve PRODUCTNAME using “Last One Wins”, UNITSINSTOCK and UNITPRICE we resolve using “Merge”. UNITSONORDER uses ‘Use Db Value”, REORDERLEVEL will use “Use Db Value”, but only if we did not change the value and finally SUPPLIERID takes the largest value from a merge. This gives:

PRODUCTNAME SUPPLIERID UNITSINSTOCK UNITPRICE UNITSONORDER REORDERLEVEL

=======================================================================

| "Chas" | 5 | 103 | 13 | 25 | 51 |

=======================================================================

Using AutoResolver

This article comes with a class – AutoResolver - that allows you to do this with almost no code. All we need to do is to create an instance of this class

_resolver = new AutoResolver();

And then we attach it to the DataAdapter that does the update:

_resolver.Attach(ProductsTable, productsAdapter);

The AutoResolver instance knows how to solve each column using some configuration:

<U2U.businessRules.autoResolver>

<selectRowCommand

commandText="SELECT ProductID, ProductName, SupplierID,

CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock,

UnitsOnOrder, ReorderLevel, Discontinued

FROM Products WHERE ProductID = @ProductID">

<parameters name="@ProductID" type="Int32"

sourceColumn="ProductID" />

</selectRowCommand>

</statementType>

</tableResolution>

</U2U.businessRules.autoResolver>

Configuring AutoResolver

Let us have a look at this configuration. It consists of two parts; the first part configures a SQL statement that allows the AutoResolver to fetch the current row from the Db. It needs this to compare the changes made in the row to the database. The statement uses a parameter that represents the primary key of the table. This parameter is filled using the dataset row’s sourceColumn.

The second part of the configuration tells the AutoResolver how to resolve each column. Each column is listed by name and resolution. Any column not listed will use the defaultResolution. Please note the last two columns; ReorderLevel will use “Use Db Value”, but only if the value is unchanged in the dataset’s row. The SupplierID column uses an event to resolve the probem. You can attach an event handler to the AutoResolver instance:

_resolver[ProductsTable].ResolveRow +=

new ResolveRowEventHandler(AutoResolverTests_ResolveRow);

The event is implemented as follows:

private void AutoResolverTests_ResolveRow(

object sender, ResolveRowEventArgs e)

{

e.UpdateStatus = UpdateStatus.ErrorsOccurred;

if( e.Column.ColumnName == SupplierID )

{

int supplierID = Math.Max(

(int) e.DbRow[SupplierID],

(int) e.CurrentRow[SupplierID] );

e.UpdateRow[SupplierID] = supplierID;

e.UpdateStatus = UpdateStatus.Continue;

}

The event handler can vote if it was able to resolve the issue by setting the UpdateStatus of the ResolveRowEventArgs argument. If unable to resolve, you set it to UpdateStatus.ErrorsOccurred, otherwise you set it to UpdateStatus.Continue.

You can retrieve the value of the dataset’s row using the CurrentRow property, and the value of the database row using the DbRow property. This event handler simply calculates the largest value from the SupplierID column and sets the UpdateRow.

Conclusion

The AutoResolver component makes it simple to resolve most concurrency issues with almost no code and a little configuration. Because of the configuration we can change the resolution with minimal code changes.

However, this is a sample and many improvements can be made. For example the component currently only works for the SqlClient provider. Later we will make this database independent, using ADO.NET 2.0 Db classes, or in ADO.NET 1.1 using the Enterprise Library.

In the next articles we will explain the implementation of the AutoResolver.

Using the sample

The sample that we present with this article (download it from our resource center www.u2u.info) has been built with Beta2 of Visual Studio .NET 2005. However, the sources can also be used in VS2003 since we don’t use any specific features of .NET 2.0.

References

TODO: look up references, for example the Architecture patterns book contains a nice explanation of pessimistic and optimistic locking.

http://msdn.microsoft.com/msdnmag/issues/04/09/DataPoints/

About the author

<<Insert Peter Himschoot.GIF>> Peter Himschoot is an architect and trainer for U2U, specializing in .NET development, Visual Studio Team System and BizTalk. He also is a Microsoft Regional Director (http://www.microsoft.com/belux/nl/msdn/community/regionaldir.mspx ).

You can reach him at peter@u2u.net.

<<Insert U2U.GIF>>U2U Training and Consultancy Services is a Microsoft .NET competence center located in Belgium, to learn more please visit www.u2u.be .