How to use the timestamp column of a table for optimistic concurrency control in SQL Server

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 .

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.

For example, assume that you create a table that is named MyTest. You populate some data in the table by running the following Transact-SQL statements.

CREATE TABLE MyTest (myKey int PRIMARY KEY, myValue int, TS timestamp)
GO
INSERT INTO MyTest (myKey, myValue) VALUES (1, 0)
GO
INSERT INTO MyTest (mykey, myValue) VALUES (2, 0)
GO

You can then use the following sample Transact-SQL statements to implement optimistic concurrency control on the MyTest table during the update.

DECLARE @t TABLE (myKey int)
UPDATE MyTest SET myValue = 2
  OUTPUT inserted.myKey into @t(myKey)
WHERE myKey = 1 and TS = TSValue
IF (SELECT COUNT(*) FROM @t) = 0
  BEGIN
    RAISERROR ('error changing row with myKey = %d',
               16, -- Severity.
               1, -- State.
               1) -- myKey that was changed
  END

Note TSValue is the timestamp column value for the row that indicates the last time that you read the row. This value must be replaced by the actual timestamp value. An example of the actual timestamp value is 0x00000000000007D3.

You can also put the sample Transact-SQL statements into a transaction. By querying the @t variable in the scope of the transaction, you can retrieve the updated myKey column of the table without re-querying the MyTest table.

For more information about the timestamp column type, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/ms182776.aspx

http://support.microsoft.com/kb/917040