用VERSIONSTAMP处理并发操作

Timestamp (rowversion) Data Type

 

 

@@DBTS

为当前数据库返回当前 timestamp 数据类型的值。这一 timestamp 值保证在数据库中是唯一的。

语法

@@DBTS

返回类型

varbinary

注释

@@DBTS 返回当前数据库最后所使用的时间戳值。当带有 timestamp 列的一行被插入或更新时，会产生一个新的时间戳值。

示例

下面的示例从 pubs 数据库返回当前的 timestamp 值。

USE pubsSELECT @@DBTS

当我们要更新数据时：在获取数据源时，记录timestamp值，即执行select语句时，同时返回timestamp
数据列，当执行update和delete时，比较该列的timestamp值是否和之前取出的timestamp值相等，如果
相等则允许修改和根新操作，否则拒绝操作。

可以使用 select *，@@dbts from table 这样就可以把整个表且带timestamp列一起取出。
在写更新或者修改存储过程时，传入timestamp数据，在存储过程内部进行比较，即可实现。

 

 

 

By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP

November 17 , 2009

A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. Of course this means we should not use it as a primary key, because we can get many orphans quickly if updates are performed on other columns. As a row is modified in a table, the timestamp is updated with the current database timestamp value obtained from the @@DBTS function.

Timestamp is the wrong name, quite confusing in fact. It has nothing to do with time. Microsoft will rename it rowversion in the future. Rowversion is the synonym for timestamp in SQL Server 2005 and SQL Server 2008.

It is an 8 bytes unique binary key within the database.

Here is how it looks like: 0x0000000000000743. After an update to the row: 0x0000000000000745.

The rowversion(timestamp) starts changing as soon as the transaction begins. If the transaction is rolled back, it returns to the original value.

So how can we use it?

The main purpose is row versioning in multi user environment, in other words concurrency checking.
Assume you are a developer and developing a program in Visual Basic to update the name and address table of customers. There will be 100 staff member who can perform this application function. How can you be sure that while staff A typing in the change, staff X is not changing the same row?

Here is what you do:

1. Read the name and address 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. You open a transaction with Begin Transaction
4. You read the timestamp of the name and address row
5. You 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.

This is pretty common practice in multi user environment. The alternate would be to examine a datetime column, or the entire row which is more processing intensive.

The following example shows timestamp (rowversion in SQL Server 2008) in action:

-- SQL Server 2008 T-SQL Code

USE tempdb;

 

-- SQL create table for Concurrency Checking demo

CREATE TABLE Celebrity (

  CelebrityID INT    IDENTITY    PRIMARY KEY,

  FirstName   VARCHAR(25),

  LastName    VARCHAR(30),

  VERSIONSTAMP  ROWVERSION)

GO

 

-- SQL insert - populate table

INSERT Celebrity (FirstName, LastName)

VALUES

('Jessica', 'Simpson'),

('Nick', 'Carter'),

('Stevie', 'Brock'),

('Christina', 'Aguilera'),

('Frank','Sinatra'),

('Doris','Day'),

('Elvis', 'Presley')

GO

 

SELECT * FROM Celebrity

GO

 

/* Results

CelebrityID FirstName   LastName    VERSIONSTAMP

1           Jessica     Simpson     0x0000000000000876

2           Nick        Carter      0x0000000000000877

3           Stevie      Brock       0x0000000000000878

4           Christina   Aguilera    0x0000000000000879

5           Frank       Sinatra     0x000000000000087A

6           Doris       Day         0x000000000000087B

7           Elvis       Presley     0x000000000000087C

*/

 

-- SQL update demo: SOMEONE UPDATED RECORD since it was read

CREATE TABLE #Semaphore (ID int identity(1,1) primary key,

                          StartVersion bigint,

                          PK int)

DECLARE @MyKey int

INSERT INTO #Semaphore (StartVersion, PK)

SELECT  VERSIONSTAMP, 1

FROM Celebrity WHERE CelebrityID=1

SELECT @MyKey = SCOPE_IDENTITY()

 

-- SIMULATION: somebody else updating the same record

UPDATE Celebrity

SET    FirstName = 'Celine',

       LastName = 'Dion'

WHERE  CelebrityID = 1

 

-- We are attempting to update.

BEGIN TRANSACTION

IF (SELECT StartVersion

    FROM   #Semaphore

    WHERE  ID = @MyKey) = (SELECT VERSIONSTAMP

                           FROM   Celebrity

                           WHERE  CelebrityID = 1)

  BEGIN

    UPDATE Celebrity

    SET    FirstName = 'Lindsay',

           LastName = 'Lohan'

    WHERE  CelebrityID = 1

    

    COMMIT TRANSACTION

  END

ELSE

  BEGIN

    ROLLBACK TRANSACTION

    PRINT 'ROLLBACK - UPDATE CONFLICT'

    RAISERROR ('Celebrity update conflict.',10,0)

  END

DELETE #Semaphore WHERE ID = @MyKey

SELECT * FROM   Celebrity

GO

/* CelebrityID    FirstName   LastName    VERSIONSTAMP

1           Celine      Dion        0x000000000000087D

2           Nick        Carter      0x0000000000000877

3           Stevie      Brock       0x0000000000000878

4           Christina   Aguilera    0x0000000000000879

5           Frank       Sinatra     0x000000000000087A

6           Doris       Day         0x000000000000087B

7           Elvis       Presley     0x000000000000087C

*/

 

-- SQL UPDATE with NO CONFLICT

DECLARE @MyKey int

INSERT INTO #Semaphore (StartVersion, PK)

SELECT  VERSIONSTAMP, 1

FROM Celebrity WHERE CelebrityID=1

SELECT @MyKey = SCOPE_IDENTITY()

 

-- We are trying to update.

BEGIN TRANSACTION

IF (SELECT StartVersion

    FROM   #Semaphore

    WHERE  ID = @MyKey) = (SELECT VERSIONSTAMP

                           FROM   Celebrity

                           WHERE  CelebrityID = 1)

  BEGIN

    UPDATE Celebrity

    SET    FirstName = 'Lindsay',

           LastName = 'Lohan'

    WHERE  CelebrityID = 1

    

    COMMIT TRANSACTION

  END

ELSE

  BEGIN

    ROLLBACK TRANSACTION

    PRINT 'ROLLBACK - UPDATE CONFLICT'

    RAISERROR ('Celebrity update conflict.',10,0)

  END

DELETE #Semaphore WHERE ID = @MyKey

SELECT * FROM   Celebrity

GO

/*

CelebrityID FirstName   LastName    VERSIONSTAMP

1           Lindsay     Lohan       0x000000000000087E

2           Nick        Carter      0x0000000000000877

3           Stevie      Brock       0x0000000000000878

4           Christina   Aguilera    0x0000000000000879

5           Frank       Sinatra     0x000000000000087A

6           Doris       Day         0x000000000000087B

7           Elvis       Presley     0x000000000000087C

*/

-- Cleanup

DROP TABLE #Semaphore

DROP TABLE Celebrity

 

 

这篇写的比较好，通过rowversion控制并发操作。