Каково практическое использование столбца timestamp в sql-сервере с примером?

Ответ 1

Я использовал тип данных TIMESTAMP (ROWVERSION, SQL2005 +), чтобы избежать проблемы с потерянным обновлением:

Утраченная проблема с обновлением: вторая транзакция записывает второе значение элемент данных (datum) поверх первого значения, записанного первым одновременная транзакция, и первое значение теряется для других транзакции, выполняемые одновременно, которые по своему приоритету нуждаются в прочитайте первое значение. Операции, которые прочитали неправильное значение закончите с неправильными результатами.

Пример: lost update:

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**.

Пример. Как предотвратить lost update:

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 script для How to prevent the lost update (предупреждение: вам нужно использовать два окна SSMS/два сеанса)

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   [email protected];

    -- 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     [email protected]
            WHERE   [email protected]
            AND     [email protected]; -- it checks the timestamp (current timestamp versus original timestamp)
            DECLARE @rowcount [email protected]@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   [email protected];  
    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   [email protected];

    -- 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     [email protected]
            WHERE   [email protected]
            AND     [email protected]; -- it checks the timestamp (current timestamp versus original timestamp)
            DECLARE @rowcount [email protected]@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   [email protected];  
    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('Lost update or row deleted.', 16, 1);

Ответ 2

Приведем пример таблицы заказов на продажу, чтобы проиллюстрировать, что делает временная метка.

create table saleorder (ordernumber int, amount int, timestamp);
insert into saleorder (ordernumber, amount) values (1, 100), (2, 100), (3, 200);
select * from saleorder

Обратите внимание на данные в столбце timestamp. Документация timestamp (SQL Server 2005) говорит: Это (т.е. временная метка) отслеживает относительное время в базе данных, а не фактическое время, которое может быть связано с часами.. Каждый раз, когда строка с столбцом временной метки модифицируется или вставлена, значение временной метки добавленной базы данных вставляется в столбец временной метки.

Посмотрите, как выглядят данные:

ordernumber amount  timestamp
1           100     0x00000000000007D1
2           100     0x00000000000007D2
3           200     0x00000000000007D3

Хорошо. Первый заказ был добавлен первым, а заказ 3 был введен последним. Что произойдет, если мы будем обновлять количество заказа 1?

update saleorder set amount = 200 where ordernumber = 1
select * from saleorder

Ah, обратите внимание, что временная метка порядка 1 теперь равна 0x7D4 (Decimal 2004). По отношению к другим строкам мы знаем, что порядок 1 был обновлен совсем недавно. Но, что более важно, значение timestamp возникает, когда происходят параллельные записи.

ordernumber amount  timestamp
1           200     0x00000000000007D4
2           100     0x00000000000007D2
3           200     0x00000000000007D3

Предположим, что Джон и Мэри оба продаются по заказу 3, используя веб-приложение, разработанное, скажем, в .NET. Джон подтягивает заказ и вносит изменения. Джон еще не сохранил данные. Мэри тянет тот же порядок и меняет его. Сначала Джон спасает. Мэри пытается сохранить данные. Приложение .NET может сначала посмотреть, не потеряна ли временная метка Мэри по-прежнему, что база данных имеет порядок 3.

Если метка времени, которую Мэри потянула с порядком 3, теперь отличается (потому что Джон сохранил данные и временную метку автоматически изменил), приложение .NET может предупредить Мэри и попросить ее обновить запись на своем экране, чтобы увидеть последнее изменение (или вероятно, выделите изменение на экране).

Подумайте о временной отметке в виде строки. Интересно, что последние выпуски SQL Server используют тип данных rowversion, который является синонимом типа данных timestamp. Документация rowversion (SQL Server 2012) содержит несколько интересных примеров.

Ответ 3

Я использовал столбец timestamp для отслеживания изменений данных, в частности данных, которые необходимо синхронизировать с одним или несколькими мобильными приложениями. Вы можете использовать столбец timestamp, чтобы просто вернуть строки, которые были изменены с момента времени (путем предоставления предыдущей отметки времени).