Я столкнулся с типом данных timestamp в sql-сервере. Каково практическое использование столбца timestamp в sql-сервере с примером?
Каково практическое использование столбца 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, чтобы просто вернуть строки, которые были изменены с момента времени (путем предоставления предыдущей отметки времени).