- دوشنبه 22 خرداد 1396
- 16:09
من از timestamp برای به روز رسانی ها استفاده می کنم. سعی می کنم با یه مثال برات توضیح بدم.
تراکنش زیر رو در نظر بگیر
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 (
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**.
تو این مرحله ستون timestamp رو اضافه کردیم. حالا چه کاری باید انجام بدیم؟
سناریو به این شکل هست دو کاربر یک سطر را پشت یکدیگر ویرایش می کنیم. اینبار از طریق دستورات sql چک می کنیم که اگه کاربر با timestamp یکسان تغییرات رو انجام داد، ویرایش با موفقیت انجام می شه و اینبار timestamp به 0x00000000000007D4 تغییر می کنه.
حالا کاربر دوم میاد یک ویرایشی رو انجام می ده. اینبار تراکنش با شکست مواجه می شه. به این خاطر که مقدار 0x00000000000007D1 در زمان آغاز بوده است اما اکنون 0x00000000000007D4 این مقدار را گرفته. بنابراین این تراکنش به اصطلاح rollback می شود.
کوئری بانک اطلاعاتیش هم بصورت زیر هست.
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
نتیجه برای کاربر اول
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
نتیجه برای کاربر دوم
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