sql-server - sql server中时间戳列的实际用途是什么？

Question

我在sql server中遇到了时间戳数据类型。sql server中时间戳列的实际用途是什么？示例是什么？

Answer 1

我使用了 TIMESTAMP 数据类型(ROWVERSION，SQL2005+)来避免 lost update problem :

The lost update problem: A second transaction writes a second value of a data-item (datum) on top of a first value written by a first concurrent transaction, and the first value is lost to other transactions running concurrently which need, by their precedence, to read the first value. The transactions that have read the wrong value end with incorrect results.

示例:丢失更新:

t  : User 1 read payment order (PO) #1 (amount 1000)
t+1: User 2 read payment order (PO) #1 (amount 1000)

t+2: User 1 change the amount for PO #1 to 1005
t+3: User 2 change the amount for PO #1 to 1009 (change make by User 1 is lost because is overwritten by change make by User 2)
t+4: The amount is **1009**.

示例:如何防止丢失更新:

t  : User 1 read payment order (PO) #1 (amount 1000, timestamp 0x00000000000007D1)
t+1: User 2 read payment order (PO) #1 (amount 1000, timestamp 0x00000000000007D1)

t+2: User 1 change the amount for PO #1 to 1005 and it checks if row has the same `timestamp` (column `RW` in this case; 0x00000000000007D1). The check succeeds and the change is `COMMIT`ed. This will change, also, the timestamp (column 'RW'). The new timestamp is 0x00000000000007D4.
t+3: User 2 change the amount for PO #1 to 1009 and it checks if row has the same `timestamp` (column `RW` in this case; 0x00000000000007D4). The checks fails because the initial timestamp (@rw=0x00000000000007D1) is <> than current timestamp (column `RW`=0x00000000000007D4). An error is raised the catch block "intercepts" the error and this transaction is cancelled (`ROLLBACK`).
t+4: The amount {remains|is} **1005**.

示例:如何防止更新丢失的 T-SQL 脚本(警告:您必须使用两个 SSMS 窗口/两个 session )

CREATE DATABASE TestRowVersion;
GO
USE TestRowVersion;
GO

CREATE TABLE dbo.PaymentOrder(
    PaymentOrderID INT IDENTITY(1,1) PRIMARY KEY,
    PaymentOrderDate DATE NOT NULL,
    Amount NUMERIC(18,2) NOT NULL,
    CreateDate DATETIME NOT NULL DEFAULT (GETDATE()),
    UpdateDate DATETIME NULL,
    RW ROWVERSION NOT NULL -- R[ow] V[ersion]
);
GO

INSERT  dbo.PaymentOrder (PaymentOrderDate,Amount)
VALUES  ('2013-07-21',1000);
INSERT  dbo.PaymentOrder (PaymentOrderDate,Amount)
VALUES  ('2013-07-22',2000);
INSERT  dbo.PaymentOrder (PaymentOrderDate,Amount)
VALUES  ('2013-07-23',3000);
GO

SELECT * FROM dbo.PaymentOrder;
/*
PaymentOrderID PaymentOrderDate Amount  CreateDate              UpdateDate RW
-------------- ---------------- ------- ----------------------- ---------- ------------------
1              2013-07-21       1000.00 2013-07-21 09:35:38.750 NULL       0x00000000000007D1
2              2013-07-22       2000.00 2013-07-21 09:35:38.750 NULL       0x00000000000007D2
3              2013-07-23       3000.00 2013-07-21 09:35:38.750 NULL       0x00000000000007D3
*/
GO

-- User 1 (SQL Server Management Studio/SSMS window #1)
    -- [t] Client app, user 1: it loads first PO
    SET NOCOUNT ON;
    GO
    DECLARE @PaymentOrderID INT=1;  -- parameter

    SELECT  po.PaymentOrderID,
            po.PaymentOrderDate,
            po.Amount,
            po.RW 
    FROM    dbo.PaymentOrder po 
    WHERE   po.PaymentOrderID=@PaymentOrderID;

    -- Client app, user 1: during 15 seconds it edit the amount from 1000.00 to 1005.00
    WAITFOR DELAY '00:00:15';
    GO

    -- [t+2] Client app, user 1: it sends this change (new amount) from client app to database server 
    -- with the old row version value
    DECLARE @PaymentOrderID INT=1;              -- parameter
    DECLARE @rw BINARY(8)=0x00000000000007D1;   -- parameter
    DECLARE @NewAmount NUMERIC(18,2)=1005.00;   -- parameter

    BEGIN TRY
        BEGIN TRANSACTION
            UPDATE  dbo.PaymentOrder
            SET     Amount=@NewAmount
            WHERE   PaymentOrderID=@PaymentOrderID
            AND     RW=@rw; -- it checks the timestamp (current timestamp versus original timestamp)
            DECLARE @rowcount INT=@@ROWCOUNT; -- How many rows were affected by the last statement (UPDATE in this case) ?
            SELECT @rowcount AS [@@ROWCOUNT];
            IF @rowcount<>1
                RAISERROR('Lost update or row deleted.', 16, 1);
        COMMIT TRANSACTION
        PRINT 'UPDATE succeded';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT>0
            ROLLBACK;

        DECLARE @ErrMsg NVARCHAR(2002);
        SET @ErrMsg=ERROR_MESSAGE();
        RAISERROR(@ErrMsg,16,1);
    END CATCH;
    GO

    -- [t+4] Client app, user 1: it reloads first PO
    DECLARE @PaymentOrderID INT=1;  -- parameter

    SELECT  po.PaymentOrderID,
            po.PaymentOrderDate,
            po.Amount,
            po.RW 
    FROM    dbo.PaymentOrder po 
    WHERE   po.PaymentOrderID=@PaymentOrderID;  
    GO

-- User 2 (warning: run this script in another SQL Server Management Studio window: File > New Database Engine Query !; SSMS window #2)
    -- [t+1] Client app, user 1: it loads first PO
    SET NOCOUNT ON;
    GO
    DECLARE @PaymentOrderID INT=1;  -- parameter

    SELECT  po.PaymentOrderID,
            po.PaymentOrderDate,
            po.Amount,
            po.RW 
    FROM    dbo.PaymentOrder po 
    WHERE   po.PaymentOrderID=@PaymentOrderID;

    -- Client app, user 1: during 20 seconds it edit the amount from 1000.00 to 1005.00
    WAITFOR DELAY '00:00:20';
    GO

    -- [t+4] Client app, user 1: it sends this change (new amout) from client app to database server 
    -- with the old row version value
    DECLARE @PaymentOrderID INT=1;              -- parameter
    DECLARE @rw BINARY(8)=0x00000000000007D1;   -- parameter
    DECLARE @NewAmount NUMERIC(18,2)=1009.00;   -- parameter

    BEGIN TRY
        BEGIN TRANSACTION
            UPDATE  dbo.PaymentOrder
            SET     Amount=@NewAmount
            WHERE   PaymentOrderID=@PaymentOrderID
            AND     RW=@rw; -- it checks the timestamp (current timestamp versus original timestamp)
            DECLARE @rowcount INT=@@ROWCOUNT; -- How many rows were affected by the last statement (UPDATE in this case) ?
            SELECT @rowcount AS [@@ROWCOUNT];
            IF @rowcount<>1
                RAISERROR('Lost update or row deleted.', 16, 1);
        COMMIT TRANSACTION
        PRINT 'UPDATE succeded';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT>0
            ROLLBACK;

        DECLARE @ErrMsg NVARCHAR(2002);
        SET @ErrMsg=ERROR_MESSAGE();
        RAISERROR(@ErrMsg,16,1);
    END CATCH;
    GO

    -- [t+4] Client app, user 1: it reloads first PO
    DECLARE @PaymentOrderID INT=1;  -- parameter

    SELECT  po.PaymentOrderID,
            po.PaymentOrderDate,
            po.Amount,
            po.RW 
    FROM    dbo.PaymentOrder po 
    WHERE   po.PaymentOrderID=@PaymentOrderID;  
    GO

用户 1 的结果(金额 1000 -> 1005):

PaymentOrderID PaymentOrderDate Amount                                  RW
-------------- ---------------- --------------------------------------- ------------------
1              2013-07-21       1000.00                                 0x00000000000007D1

@@ROWCOUNT <- Timestamp check succeeds 
-----------
1

UPDATE succeded
PaymentOrderID PaymentOrderDate Amount                                  RW
-------------- ---------------- --------------------------------------- ------------------
1              2013-07-21       1005.00                                 0x00000000000007D4

用户 2 的结果(金额 1000 -> 1009):

PaymentOrderID PaymentOrderDate Amount                                  RW
-------------- ---------------- --------------------------------------- ------------------
1              2013-07-21       1000.00                                 0x00000000000007D1

@@ROWCOUNT <- Timestamp check fails 
-----------
0

Msg 50000, Level 16, State 1, Line 27
Lost update.
PaymentOrderID PaymentOrderDate Amount                                  RW
-------------- ---------------- --------------------------------------- ------------------
1              2013-07-21       1005.00                                 0x00000000000007D4

注意:将错误消息更改为 RAISERROR('丢失更新或行已删除。', 16, 1);