Using a Timestamp for Optimistic Concurrency

for RuBoard

If you are using a data source (table) on a DBMS such as Microsoft SQL Server 2000 that allows for a timestamp column to be defined in a table, the coding for optimistic concurrency can be made much simpler. You must remember, though, that the code you write for DBMS-specific nuances (like that of timestamp) must only be used with that DBMS.

The timestamp column, if present in a table, will be updated automatically each time the data row is changed. This provides a single table column that can be used for comparison in the UPDATE statement rather than having to compare all the data columns . However, you must first have a timestamp column in the table that you are using.

Listing 14.2 is the Customers table ( renamed CustomersTS for TimeStamp) with a timestamp column (as supported by MS SQL Server 2000).

Listing 14.2 The Customer Table with a Timestamp Column (File 14OptConTS.sql)

 CREATE TABLE [dbo].[CustomersTS] (     [CustomerID] [nchar] (5) NOT NULL ,     [CompanyName] [nvarchar] (40) NOT NULL ,     [ContactName] [nvarchar] (30) NULL ,     [ContactTitle] [nvarchar] (30) NULL ,     [Address] [nvarchar] (60) NULL ,     [City] [nvarchar] (15) NULL ,     [Region] [nvarchar] (15) NULL ,     [PostalCode] [nvarchar] (10) NULL ,     [Country] [nvarchar] (15) NULL ,     [Phone] [nvarchar] (24) NULL ,     [Fax] [nvarchar] (24) NULL ,     timestamp ) 

Taking advantage of the timestamp column is very similar to what we did before except that we only need to save this one column for comparison: the timestamp column (other than the column(s) that are needed to locate the row itself ”such as CustomerID).

This time, we will read the rows and the timestamp column.

 Dim CustomerAdapter As SqlDataAdapter = New SqlDataAdapter         ("SELECT CustomerID, ContactName, timestamp " &          "FROM CustomersTS ORDER BY CustomerID", nwindConn) 

We then must save the original timestamp values for use in our comparison later.

 OldParms = CustomerAdapter.UpdateCommand.Parameters.Add         ("@oldTimestamp", SqlDbType.Varbinary, 8, "timestamp")   OldParms.SourceVersion = DataRowVersion.Original 

And then we will only need to supply this additional one column in the UPDATE statement.

 CustomerAdapter.UpdateCommand = New SqlCommand         ("UPDATE CustomersTS (CustomerID, ContactName, timestamp) " &          "VALUES(@CustomerID, @ContactName, null) " & _ "WHERE CustomerID = @oldCustomerID AND timestamp = @oldTimestamp", nwindConn) 

Again, if any other user has updated the data row before your update is executed, the timestamp value would have changed and your update will fail (optimistic concurrency violation).

There is also another comparison syntax for issuing an update in Microsoft SQL Server (up through SQL Server 2000) when using timestamp. It is called the TSEQUAL update syntax. When you use this syntax, it yields a true or false result when the TSEQUAL portion is executed. It is as follows :

 CustomerAdapter.UpdateCommand = New SqlCommand         ("UPDATE CustomersTS (CustomerID, ContactName, timestamp) " &          "VALUES(@CustomerID, @ContactName, null) " & _          "WHERE CustomerID = @oldCustomerID AND TSEQUAL " &          "(timestamp, @oldTimestamp) ", nwindConn) 

This is basically the same thing as before, but just specified slightly differently. The data row is located by using the CustomerID comparison, and TSEQUAL yields true or false depending on the results of the timestamp comparison.

for RuBoard