Time Stamp (Row Version) Implementation.

Problem defanition

We are in to a application where we have more than 100 clients talking to a server same time. How we are going to address the concurrency at DB level? There are many concurrency control techniques like locks, Timestamps, or Tickets. Here I would like to explain Time Stamps as a concurrency control technique that we have used in our WPF application.

Timestamp (Row Version)

The timestamp column of a table can be used to determine whether any value in the table row has changed since the last time the table was read. This article describes a way to use the timestamp column of a table for optimistic concurrency control in Microsoft SQL Server 2005.And in future they are going to change the name to ‘Row Version’.

The important thing to know about timestamps is that they are automatically updated every time a row containing a timestamp column is inserted or updated. Values in timestamp columns are not datetime data, but binary(8) varbinary(8) data. The timestamp datatype has no relation to the system time, it is simply a monotonically increasing counter whose values will always be unique within a database (making it basically a unique random number.)

You can add a timestamp column to a table to help maintain the integrity of the database when multiple users are updating rows at the same time. You may also want to know how many rows and which rows were updated without re-querying the table.

CREATE TABLE EMPLOYE (EMP_NUMBER int PRIMARY KEY,   EMP_NAME VARCHAR(100), timestamp)

GO

INSERT INTO EMPLOYE (EMP_NUMBER, EMP_NAME) VALUES (1, 'Jijo')

GO

INSERT INTO EMPLOYE (EMP_NUMBER, EMP_NAME) VALUES (2, 'Venginikkadan')

GO

Now when we are going ahead to update the table we can implement the logic something like this.It is a usual upate statement:

BEGIN TRY

  UPDATE EMPLOYE SET EMP_NAME = 'Jijo venginikkadan' WHERE myKey = 1

IF @@RowCount=0

   RAISERROR('Concurrent update error. Updated aborted.', 16, 2)

END TRY

Remember that the values in a timestamp column are automatically maintained by the database -- you don't have to do anything. In fact, you couldn't update a timestamp column directly if you tried! When you insert a record, a timestamp is automatically assigned for that record. When a record with a timestamp is updated, the timestamp changes. We said earlier that the value of the timestamp will be unique. This means that if you know the value of the timestamp before the update, you can easily be able to tell if the record has been updated by another user between the time you retrieved the information and the time you are trying to save your changes. In fact, SQL Server has a special error message it sends when this type of interim update has happened, so you can specifically trap this occurance!Timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. If you do not specify a column name, the SQL Server Database Engine generates the timestamp column name; however, the rowversion synonym does not follow this behavior. When you use rowversion, you must specify a column name, for example:

CREATE TABLE EMPLOYE (EMP_NUMBER int PRIMARY KEY,   EMP_NAME VARCHAR(100), VerCol rowversion)

Now let us give the real time example to explain how we have implimnted the timestamp logic as well:

Assume you are developing a program in C# to update the name of the Employee . There will be 100 users who can perform this application function. How can you be sure that while staff 1 typing in the change, staff 2 is not changing the same row?

Here is the logic:
1. Read the name from EMPLOYE table including the timestamp. You display the info to the user for update and save the timestamp.
2. Certain amount of time later, like 2 minutes, the user presses the submit button after changes were typed in.
3. Open a transaction with Begin Transaction
4. Read the timestamp of the name and address row
5. Now compare the current timestamp to the saved timestamp.
6. If the timestamps are same, you update the row and commit the transaction
7. If timestamps are different, you roll back the transaction and notify the user about the fact that the data was changed by someone else. You can let the user decide what to do or follow the appropriate company business rule for data entry conflict resolution.
Sounds good right? Now go ahead and revisit the concurrency control techniques that you already implemented in your application.