How Change Tracking Handles Changes to a Database

Checking for Conflicts

In a two-way synchronization scenario, the client application must determine whether a row has not been updated since the application last obtained the changes.

The following example shows how to use the CHANGETABLE(VERSION …) function to check for conflicts in the most efficient way, without a separate query. In the example, CHANGETABLE(VERSION …) determines the SYS_CHANGE_VERSION for the row specified by @product id. CHANGETABLE(CHANGES …) can obtain the same information, but that would be less efficient. If the value of SYS_CHANGE_VERSION for the row is larger than the value of @last_sync_version, there is a conflict. If there is a conflict, the row will not be updated. The ISNULL() check is required because there might be no change information available for the row. No change information would exist if the row had not been updated since change tracking was enabled or since the change information was cleaned up.

-- Assumption: @last_sync_version has been validated.

UPDATE
    SalesLT.Product
SET
    ListPrice = @new_listprice
FROM
    SalesLT.Product AS P
WHERE
    ProductID = @product_id AND
    @last_sync_version >= ISNULL (
        SELECT CT.SYS_CHANGE_VERSION
        FROM CHANGETABLE(VERSION SalesLT.Product,
                        (ProductID), (P.ProductID)) AS CT),
        0)

The following code can check the updated row count and can identify more information about the conflict.

-- If the change cannot be made, find out more information.
IF (@@ROWCOUNT = 0)
BEGIN
    -- Obtain the complete change information for the row.
    SELECT
        CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_CREATION_VERSION,
        CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS
    FROM
        CHANGETABLE(CHANGES SalesLT.Product, @last_sync_version) AS CT
    WHERE
        CT.ProductID = @product_id;

    -- Check CT.SYS_CHANGE_VERSION to verify that it really was a conflict.
    -- Check CT.SYS_CHANGE_OPERATION to determine the type of conflict:
    -- update-update or update-delete.
    -- The row that is specified by @product_id might no longer exist 
    -- if it has been deleted.
END

Setting Context Information

By using the WITH CHANGE_TRACKING_CONTEXT clause, an application can store context information together with the change information. This information can then be obtained from the SYS_CHANGE_CONTEXT column that is returned by CHANGETABLE(CHANGES …).

Context information is typically used to identify the source of the changes. If the source of the change can be identified, that information can be used by a data store to avoid obtaining changes when it synchronizes again.

  -- Try to update the row and check for a conflict.
  WITH CHANGE_TRACKING_CONTEXT (@source_id)
  UPDATE
     SalesLT.Product
  SET
      ListPrice = @new_listprice
  FROM
      SalesLT.Product AS P
  WHERE
     ProductID = @product_id AND
     @last_sync_version >= ISNULL (
         (SELECT CT.SYS_CHANGE_VERSION FROM CHANGETABLE(VERSION SalesLT.Product,
         (ProductID), (P.ProductID)) AS CT),
         0)

Ensuring Consistent and Correct Results

An application must consider the cleanup process when it validates the value of @last_sync_version. This is because data could have been removed after CHANGE_TRACKING_MIN_VALID_VERSION() was called, but before the update was made.

-- Prerequisite is to ensure ALLOW_SNAPSHOT_ISOLATION is ON for the database.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
    -- Verify that last_sync_version is valid.
    IF (@last_sync_version <
CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(‘SalesLT.Product’)))
    BEGIN
       RAISERROR (N’Last_sync_version too old’, 16, -1);
    END
    ELSE
    BEGIN
        -- Try to update the row.
        -- Check @@ROWCOUNT and check for a conflict.
    END
COMMIT TRAN

See Also

Tasks

Concepts