There are four popular strategies to dealing with optimistic concurrency:
- Do Nothing.
- Check for changes to all fields during update.
- Check for changes to modified fields during update.
- Check for changes to timestamp ( rowversion ) during update.
All of these strategies have to deal with the shaping of the Update T-SQL Command sent to the database during the updating of the data. The examples below are not very detailed on purpose and assume a basic understanding of ADO.NET. Below shows the strategies from a view point of 30,000 ft high.
Optimistic Concurrency on Update Strategy #1 - Do Nothing
The simplest strategy for dealing with concurrency issues during the updating of data is to do nothing.
The update command will not check for any changes in the data, only specify the primary key of the record to be changed. If someone else changed the data, those changes will more than likely be overwritten:Update Product
Set
Name = @Name Where
ID = @ID
One would hope that this means either 1) the application is a single-user application, or 2) the chance of multi-user update collisions is very unlikely and the repercussions of overwriting data is negligible.
Optimistic Concurrency on Update Strategy #2 - Check All Fields
With this strategy, the update command will check that all fields in the row ( usually minus BLOB fields ) are equal to their original values when peforming the update to assure no changes have been made to the original record. A check of the return value of the ExecuteNonQuery Command will tell you if the update actually took place. The return value of the ExecuteNonQuery Command is typically the number of rows affected by the query.Update Product
Set
Name = @Name,
Where
ID = @ID
AND
Name = @OriginalName
AND
Price = @OriginalPrice
This is essentially what CommandBuilder creates when using DataSets and is a strategy that doesn't want to see any changes to the data.
Optimistic Concurrency on Update Strategy #3 - Check Only Changed Fields
Rather than checking all fields in the row to make sure they match their original value, this strategy checks only those fields that are being updated in the command.Update Product
Set
Name = @Name
Where
ID = @ID
AND
Name = @OriginalName
This strategy only cares that it is not overwriting any data and could care less that other fields in the record may have been changed. This could create an interesting combination of data in the row.
Optimistic Concurrency on Update Strategy #4 - Implement Timestamp
SQL Server has a timestamp ( alias rowversion ) field that is modified everytime a change is made to a record that contains such a field. Therefore, if you add such a field to a table you only have to verify the timestamp record contains the same original value to be assured none of the fields have been changed in the record.Update Product
Set
Name = @Name
Where
ID = @ID
AND
TimestampID = @TimestampID
This is the same as Strategy #2 above without the need for checking all fields.
