Dataset optimistic concurrency

Question

What would I need to do in my dataset C# client for handling optimistic concurrency?

This article does not go into many details except from use a timestamp

mikalai mikalai · Accepted Answer · 2011-11-07T21:00:15

This article does not go into muy details except from use a timestamp

That's not really correct, the article just mentions timestamps (the use case in the first half or the article), and alternatively provides more details on the second optimistic lock implementation -

Another technique for testing for an optimistic concurrency violation is to verify that all the original column values in a row still match those found in the database

The magic is performed by the following statement:

UPDATE Table1 Set Col1 = @NewCol1Value,
          Set Col2 = @NewCol2Value,
          Set Col3 = @NewCol3Value
WHERE Col1 = @OldCol1Value AND
    Col2 = @OldCol2Value AND
    Col3 = @OldCol3Value

So you'd just listen to RowUpdated event, and if RecordsAffected is zero then something bad happened.

Timestamp-based implementation is pretty obvious as well. You'll have a datetime along with your dataset:

class OptLockDataSet { 
    DataSet _data;
    DateTime LastUpdate {
        return _data.Tables["ChangeTracker"][0][0];//just for example :)
    }
}

For updating you'll have two ways - explicit, when you check timestamps even before attempting to save anything:

if(GetLastUpdateFromDB(_data) > LastUpdate ) {
    //This means that last update was changed because someone else
    //modified the data.
    //Show error to user and reload data from db.
}

Or implicit, like the second way described in the article - try update using timestamp as a condition:

update data set @col1 = @val1 where last_update = @last_update

and if zero rows are updated then you'll know about concurrency exception and report correspondingly.

Dataset optimistic concurrency

3 Answers