Most applications are
written nowadays using optimistic concurrency. Locks aren’t placed on a piece of
data for the duration of someone viewing that data in their application. Instead
what you do is when someone wants to save the data you check to see whether
someone else as changed it since the time you read the data. If someone has you
generally don’t want to save the data because you are likely to overwrite the
change that had been made. The check normally consists of checking the value of
each column against the values you got back when you read the data or comparing
the value of a rowversion against what you read (a rowversion column gets
changed every time a row is changed, that means you can use this column to
detect a change rather than checking all the columns). Imagine buying a
What has this got to do
with the MERGE statement. Well optimistic concurrency is very easy to do when
you are dealing with a single row of data as you only have to do one check. If
you identify the row has changed you can return success or failure. However with
a MERGE statement what happens if you are updating 10 records and 4 have already
been updated. In this case you have a number of options one is to update those
that haven’t changed and return to the client those that have changed. This can
be done very nicely using a trick with MERGE and the OUTPUT clause .
Assuming you have a
rowversion (timestamp) column on your table (you can use the same approach if
you don’t, in which case you will have to compare all the columns ) the
following is an example MERGE statement with a TVP (Table Valued Parameter). We
are going to save a set of OrderLines
The classic MERGE
statement is as follows
MERGE
Sales.SalesOrderDetail
OrderDetail
USING
@ChangedOrderDetail
Chng
ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]
AND Chng.[SalesOrderID] = OrderDetail.[SalesOrderID]
WHEN
MATCHED THEN
UPDATE SET
[SalesOrderID]
= Chng.SalesOrderID
,[CarrierTrackingNumber] =
Chng.CarrierTrackingNumber
,.....
WHEN
NOT MATCHED
THEN
INSERT ([SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID]
,[UnitPrice],[UnitPriceDiscount],[rowguid],[ModifiedDate])
VALUES
(Chng.SalesOrderID,Chng.CarrierTrackingNumber,Chng.OrderQty,Chng.ProductID,Chng.SpecialOfferID
,Chng.UnitPrice,Chng.UnitPriceDiscount,Chng.rowguid,Chng.ModifiedDate);
To add optimistic
checking you can check at the start within a serialised transaction or check at
the end to see if the number of rows affected is the same as the number passed
in i.e.
SET
TRANSACTION ISOLATION SERIALIZABLE
BEGIN
TRANSACTION
IF
NOT EXISTS (SELECT
1
FROM Sales.SalesOrderDetail OrderDetail
JOIN @ChangedOrderDetail Chng
ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]
AND Chng.[SalesOrderID] = OrderDetail.[SalesOrderID]
WHERE OrderDetail.Rowversion <> Chng.Rowversion)
BEGIN
MERGE Sales.SalesOrderDetail OrderDetail
USING @ChangedOrderDetail Chng
ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]
.......
END
ELSE
RAISERROR(‘data has
changed since it was read’....)
COMMIT
TRANSACTION
Or
BEGIN
TRANSACTION
MERGE Sales.SalesOrderDetail OrderDetail
USING @ChangedOrderDetail Chng
ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]
.......
IF
@@ROWCOUNT<> (SELECT Count(1) FROM
@ChangedOrderDetail)
BEGIN
RAISERROR(‘data has
changed since it was read’....)
ROLLBACK TRANSACTION
END
ELSE
COMMIT TRANSACTION
The final way is to do a
partial update and report the records that have changed . The OUTPUT clause only
contains records that have been inserted, updated or deleted. So to get the
records out you need to UPDATE them, however you can UPDATE the columns to their
existing values (i.e. not change anything ) and that way you can get these rows
in the OUTPUT clause. You can then use a nice feature of the MERGE OUTPUT clause
and compare the deleted rowversion value with that in the source data. If they
are different it means the record wasn’t changed.
MERGE
Sales.SalesOrderDetail
OrderDetail
USING
@ChangedOrderDetail
Chng
ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]
AND Chng.[SalesOrderID] = OrderDetail.[SalesOrderID]
WHEN
MATCHED THEN
UPDATE SET
[SalesOrderID]
= CASE
WHEN Chng.Rowversion = OrderDetail.Rowversion
THEN Chng.SalesOrderID
ELSE OrderDetail.SalesOrderID
END
,[CarrierTrackingNumber] =
CASE WHEN
Chng.Rowversion =
OrderDetail.Rowversion
THEN Chng.CarrierTrackingNumber
ELSE OrderDetail.CarrierTrackingNumber
END
, .....
OUTPUT
CASE WHEN
Chng.Rowversion =
deleted.Rowversion THEN 1
ELSE 0 END
Changed
, Chng.SalesOrderDetailID
Note: For simplicity I
have removed most of the columns from the update statement. You would need to
include these in your application.
You can further extend
this by using another new feature of SQL Server 2008, Composable DML. This
allows you to consume the output from one statement into another statement. With
SQL Server 2008 you are limited to only being able to consume the data in an
INSERT statement. There are also restrictions on the structure of the table you
are inserting into. Refer to BOL for exact details.
Using composable DML you
can store only those records that failed update into a table variable and then
return those records.
DECLARE
@ChangedOrderDetail SalesOrderDetail_Type
DECLARE
@SalesOrderID int
DECLARE
@t TABLE (Changed int, ID int)
INSERT
INTO @t
SELECT
Changed,
SalesOrderDetailID
FROM
(MERGE
Sales.SalesOrderDetail
OrderDetail
USING @ChangedOrderDetail Chng
ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]
AND Chng.[SalesOrderID] = OrderDetail.[SalesOrderID]
WHEN MATCHED THEN
UPDATE SET [SalesOrderID]
= CASE
WHEN Chng.Rowversion = OrderDetail.Rowversion
THEN Chng.SalesOrderID
ELSE OrderDetail.SalesOrderID
END
,[CarrierTrackingNumber] =
CASE WHEN
Chng.Rowversion =
OrderDetail.Rowversion
THEN Chng.CarrierTrackingNumber
ELSE OrderDetail.CarrierTrackingNumber
END
OUTPUT
CASE WHEN
Chng.Rowversion =
deleted.Rowversion THEN 1
ELSE 0 END
Changed
, Chng.SalesOrderDetailID)
da
WHERE
CHanged = 0
SELECT
*
FROM
@t