Tech Tip: Concurrency Checking with the SQL Server Timestamp

Level 200 DevForce Express

   Level 200 
DevForce Express
Aug 8, 2006
DevForce applications can use the SQL Server timestamp data type for database concurrency control. The SQL Server timestamp column is especially appropriate in environments where data tables could be updated by both DevForce and non-DevForce applications. 
The SQL Server timestamp data type is a binary number maintained by the SQL Server engine. Don't confuse it with the SQL Server datetime or the SQL-92 standard timestamp. The SQL Server datetime conforms to the SQL-92 timestamp standard for representing real world dates and times. The SQL Server timestamp has nothing to do with time; it is actually a sequential counter used by SQL server to stamp updated records.

Here is what SQL Server Books Online says about it:

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. ... Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.
While the SQL timestamp is useless as a measure of time, it serves perfectly as an optimistic concurrency column in a table mapped to a DevForce business object. SQL Server (re)sets a timestamp column value whenever the record is saved and we don't have to lift a finger to make that happen. In fact, we couldn't if we wanted to because a timestamp column is read only.

Optimistic Concurrency

DevForce optimisitic concurrency checking depends upon our ability to detect if a single column of a table record has changed since we last fetched it.

When we attempt to update an existing record in a table with a timestamp concurrency column, we compare the original timestamp of our proposed record with the timestamp in the current database record. If they are the same, no one has changed the record since we fetched it; our update can be saved.

If the timestamps differ, we know that another user updated this record while we were changing our copy of it. There is a conflict between our changes and that other user's changes - a conflict we should detect and resolve. DevForce will reject our update attempt and raise a concurrency violation exception for our application to handle.

Using a Timestamp Property in a DevForce Application
We declare an entity's concurrency column in the DevForce Object Mapper (OM). There are four steps to making a timestamp column serve as the concurrency column.

Select the timestamp column's property name in the "Concurrency Column" combo box in the "Class" tab. 
Check the timestamp property's "Read Only" checkbox in the "Simple Properties" tab. 
Change its "Access Modifier" to "Protected". 
Change its "Source Access Type" to "Read Only". 
Steps #1 and #4 are essential. The other two steps are merely very good practice.

1. Set the Concurrency Column 
The timestamp column's property name is "TestTS" in this illustration.

2. Check the "Read Only" Box

Our application should never set a column designated as the concurrency column. In the case of a timestamp column, we couldn't save a changed value even if we wanted to. Therefore, the associated property should be configured to generate a "Read Only" property, a property with a get method but no set method. We configure it as a such by checking the "Read Only" checkbox.

3. Set the "Access Modifier" to "Protected"

We really don't want anyone to see this property either. A timestamp is not a true time value and displaying it would only confuse people. We should change the "Access Modifier" to "Protected" which will make it invisible outside the class. The DevForce persistence layer has no trouble finding it.

4. Set the "Source Access Type" to "ReadOnly"

We've said repeatedly that the SQL Server timestamp column is read only. Haven't we taken care of that already with steps #2 and #3?

Nope. Steps #2 and #3 govern the routine visibility of the timestamp in our client application. But our application could have changed the data column value by some means other than through its associated property.

Therefore, in the absence of contrary instructions, the DevForce persistence layer tries to set the timestamp column when it attempts a database update. We have to tell the persistence layer not to do that by setting the timestamp column property's "Source Access Type" to "Read Only" as shown here.

Catching Concurrency Violations

There are myriad reasons to be ready for a breakdown in the save process. We have just added another one to the list by engaging optimistic concurrency checking; the DevForce persistence layer will now check for concurrency violations and throw exceptions when it detects them.
Accordingly, all SaveChanges method calls should be encapsulated within a try-catch block that catches a PersistenceManagerSaveException. The following code sample shows a primitive catch block that displays the exception message when the save fails for any reason, including a concurrency violation.
C#:
SaveResult result; 
try {
      result = mPm.SaveChanges();
      MessageBox.Show("Changes saved");
} catch (PersistenceManagerSaveException pMException) {
      MessageBox.Show(pMException.Message); 
} catch (Exception ex) {
      MessageBox.Show(ex.Message); 
}
VB.NET: 
Dim result As SaveResult 
Try 
      result = mPm.SaveChanges() 
      MessageBox.Show("Changes saved")
Catch pMex As PersistenceManagerSaveException 
      MessageBox.Show(pMex.Message)
Catch ex As Exception
      MessageBox.Show(ex.Message)
End Try 
That�s all there is to it! Look at the �Handling Concurrency Conflicts� tutorial in the "Intermediate Topics" section of the DevForce tutorials collections to learn more about managing concurrency.

Edited by IdeaBlade - 06-Jun-2007 at 11:29am