The problem
One of usually problems encountered during the usage of the multiuser databases is the lost update problem (described here and here). This means that after a user/transaction updates a row a second user/transaction tries to update the same row thus overwriting the first change. When the risk of getting this kind of problem is low one solution is the optimistic approach described here (see Understanding Lost Modifications > Optimistic approach). In such cases first transaction which updates some rows will succeed but the following transaction which tries to update the same rows raises an exception. At this point would be nice to show to the end user the values before second update (after first update) and after second update so he/she can see what values modified by the first user.
The solution
One solution is described within ninth chapter of Expert SQL Server 2008 Development and supposes returning an XML using AFTER UPDATE triggers. The XML is generated using inserted and deleted virtual tables.
To generate the same XML, this article uses another approach: starting with SQL Server 2005 the inserted and deleted tables are also available within OUTPUT clause. Thus, the OUTPUT clause is used to insert the updated rows into a table variable named @InsertedAndDeleted. Then an XML is generated using the rows previously inserted into @InsertedAndDeleted.
For this article I use following table:
GO
USE TestPreventLostUpdated;
GO
CREATE TABLE dbo.PaymentOrder
(
PaymentOrderID INT PRIMARY KEY,
PaymentDate DATETIME NOT NULL,
Amount NUMERIC(18,2) NOT NULL,
ModifyDate DATETIME NULL,
ModifyUser SYSNAME NULL,
RW ROWVERSION NOT NULL
);
GO
INSERT INTO dbo.PaymentOrder (PaymentOrderID, PaymentDate, Amount)
VALUES (1, '2013-09-14', 500);
INSERT INTO dbo.PaymentOrder (PaymentOrderID, PaymentDate, Amount)
VALUES (2, '2013-09-15', 1000);
GO
In SQL Server, one simple solution to detect changes is to use rowversion datatype. At this point, the content of this table is as it follows:
————– ———– —— ———- ———- —————–
1 2013-09-14 500.00 NULL NULL 0x7D1
2 2013-09-15 1000.0 NULL NULL 0x7D2
SQL Server automatically increments the RW value after each UPDATE. For example, if I change the value of Amount column from 1000.00 to 1111 for PaymentOrderID 2
UPDATE dbo.PaymentOrder
SET Amount = 1111
WHERE PaymentOrderID = 2;
SELECT *
FROM dbo.PaymentOrder;
ROLLBACK;
then the results will be:
————– ———– —— ———- ———- —————–
1 2013-09-14 500.00 NULL NULL 0x7D1
2 2013-09-15 1000.0 NULL NULL 0x7D3
As you can see, after this update of Amount column, SQL Server changed also the RW (rowversion) value from 0x7D2 to 0x7D3.
Our lost update scenario is as follows:
Now, if we want to show the old and the new values a simple solution is to insert the old and the values into a table variable using OUTPUT INTO clause and, then, using FOR XML we create the XML:
(
@PaymentOrderID INT,
@Amount NUMERIC(18,2),
@RW BINARY(8),
@OutputXml XML OUTPUT
)
AS
DECLARE @InsertedAndDeleted TABLE
(
NewPaymentOrderID INT NOT NULL,
NewPaymentDate DATETIME NOT NULL,
NewAmount NUMERIC(18,2) NOT NULL,
NewModifyDate DATETIME NULL,
NewModifyUser SYSNAME NULL,
NewRW BINARY(8) NOT NULL,
OldPaymentOrderID INT NOT NULL,
OldPaymentDate DATETIME NOT NULL,
OldAmount NUMERIC(18,2) NOT NULL,
OldModifyDate DATETIME NULL,
OldModifyUser SYSNAME NULL,
OldRW BINARY(8) NOT NULL
);
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.PaymentOrder
SET Amount = @Amount,
ModifyDate = GETDATE(),
ModifyUser = ORIGINAL_LOGIN()
OUTPUT inserted.*, deleted.* INTO @InsertedAndDeleted
WHERE PaymentOrderID = @PaymentOrderID;
IF EXISTS
(
SELECT *
FROM @InsertedAndDeleted id
WHERE id.OldRW <> @RW
)
RAISERROR('Lost update detected.', 16, 1);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrMsg NVARCHAR(2048), @ErrNum INT;
SET @ErrMsg = ERROR_MESSAGE();
SET @ErrNum = ERROR_NUMBER();
IF @ErrNum = 50000 AND @ErrMsg = 'Lost update detected.'
BEGIN
SET @OutputXml =
(
SELECT src.*
FROM @InsertedAndDeleted id
CROSS APPLY
(
SELECT 'Old' AS RowType, id.OldPaymentOrderID AS PaymentOrderID, id.OldAmount AS Amount, id.OldModifyUser AS ModifyUser
UNION ALL
SELECT 'New' AS RowType, id.NewPaymentOrderID AS PaymentOrderID, id.NewAmount AS Amount, id.NewModifyUser AS ModifyUser
) src
ORDER BY src.RowType
FOR XML AUTO, ELEMENTS XSINIL
)
END;
RAISERROR(@ErrMsg, 16, 1);
END CATCH
As you can see the UPDATE outputs the affected rows into a table variable and if a change is detected within RW column an exception (Lost update detected) is raised. The BEGIN CATCH … END CATCH block will intercept this exception and an XML is generated and sent back to client application.
Thus, after User #1 executes
@PaymentOrderID INT = 2,
@Amount NUMERIC(18,2) = 1000.00,
@RW BINARY(8) = 0x7D3,
@OutputXml XML;
– It changes the the amount: 1000 -> 1005
SET @Amount = 1005.00;
EXEC dbo.PaymentOrder_Update
@PaymentOrderID,
@Amount ,
@RW ,
@OutputXml OUTPUT
SELECT @OutputXml AS Result;
and the update succeeds the User #2 executes
@PaymentOrderID INT = 2,
@Amount NUMERIC(18,2) = 1000.00,
@RW BINARY(8) = 0x7D3,
@OutputXml XML;
– It tries to change the amount to 1050
SET @Amount = 1050.00;
EXEC dbo.PaymentOrder_Update
@PaymentOrderID,
@Amount ,
@RW ,
@OutputXml OUTPUT
SELECT @OutputXml AS Result;
and the results is an exception:
Lost update detected.
Result
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————–
<src xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RowType>New</RowType><PaymentOrderID>2</PaymentOrderID><Amount>1050.00</Amount><ModifyUser>N4FG80D9FGDFJGO\sqlservr.exe</ModifyUser></src>
<src xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RowType>Old</RowType><PaymentOrderID>2</PaymentOrderID><Amount>1005.00</Amount><ModifyUser>N4FG80D9FGDFJGO\sqlservr.exe</ModifyUser></src>
This solution can be easily changed to show also the original values for User #2 (example: Amount 1000.00).
Note: a simple update on a column with the same value will change also the rowversion for affected rows.
Leave a Reply