IF OBJECT_ID('test_timestamp') IS NOT NULL
BEGIN
DROP TABLE test_timestamp
END
GO
CREATE TABLE test_timestamp
(
id INT PRIMARY KEY IDENTITY(1,1),
tname NVARCHAR(20),
[rowversion] TIMESTAMP
)
GO
INSERT INTO test_timestamp (tname)
SELECT 't1' UNION
SELECT 't2' UNION
SELECT 't3' UNION
SELECT 't4'
SELECT * FROM test_timestamp tt
UPDATE test_timestamp
SET tname = 't1_update'
WHERE id=1
--1. 仅仅获取已更新的行
-- 但之前的最大的那个 rowversion 要另外记录
SELECT * FROM test_timestamp tt WHERE tt.rowversion>0x00000000000007DC
--2. 在两人同时修改同一条数据时,避免 先取出/后保存 覆盖前面的记录
DECLARE @rowversion TIMESTAMP
SELECT @rowversion = rowversion FROM test_timestamp tt WHERE tt.id=1;
--过了 5 秒后再修改, 在这 5 秒之内如果有其它update语句修改过此行则此修改无效了.
waitfor delay '00:00:05'
update test_timestamp set tname='t1_u01' where id=1 AND rowversion=@rowversion
SELECT * FROM test_timestamp tt WHERE tt.id=1