How-to Prevent Dirty Records to be updated

Question

Assuming a Product class

public class Product
{
    public String Name {get;set;}
    public Decimal Price {get;set;}
    public Int32 QuantityStock {get;set;}
}

Now assuming Two Clients who "Request" the same Product at the same time named "Product 1" with the following values

Name = "Product 1"

Price = 10

QuantityStock = 100

The first client increase the QuantityStock to 110

Then the second client decrease QuantityStock to 90

So I got a lack of 10 QuantityStock for this Product because the second client update quantityStock from original value "100" instead of the first client update "110" ...

Original Value = 100

First Client Update 100 => 110

Second Client Update 100 => 90

How can I prevent that behavior ?

Note : I'm in a 3-Tier Architecture with Repository Pattern and Entreprise Library.

Answer 1

I would go with number four from this list of options: http://davidhayden.com/blog/dave/archive/2005/10/05/2503.aspx

Check for changes to timestamp ( rowversion ) during update.

Answer 2

I'd use a timestamp on the record and confirm that the timestamp of the record remained untouched before I submitted the record to the database. If the timestamp has changed, i.e. client 1 has actioned their submission since client 2 read the data, then take appropriate action.

For the action you have indicated I'd be tempted to make it an adjustment rather than an absolute, so it's either adding or subtracting stock from the system. That way you aren't as concerned with the contention.

Answer 3

Take a look at this article:

SQL Server - Optimistic Concurrency Database Updating - Pessimistic Concurrency - High Performance ASP.NET Websites

And also here (at the end of the article):

Developing Next Generation Smart Clients using .NET 2.0 working with Existing .NET 1.1 SOA-based XML Web Services

Sample:

var reader = SqlHelper.ExecuteReader(connectionString,
    "UpdateProduct", product.Name, product.Price, product.StockQuantity);    
if (reader.RecordsAffected > 0)
{
    RefreshEntity(reader, product);
    result = reader.RecordsAffected;
}
else
{
    //must always close the connection
    reader.Close();

    // Concurrency exception
    DBConcurrencyException conflict = 
       new DBConcurrencyException("Concurrency exception");
    conflict.ModifiedRecord = product;

    AssessmentCollection dsrecord;
    // Get record from Datasource
    if (transactionManager != null)
        dsrecord = ProductRepository.Current.GetByName(
        this.transactionManager, product.Name);
    else
        dsrecord = ProductRepository.Current.GetByName(connectionString, 
            product.Name);
    if (dsrecord.Count > 0)
        conflict.DatasourceRecord = dsrecord[0];

    throw conflict;
}