How Change Tracking Handles Changes to a Database
Some applications that use change tracking perform two-way synchronization with another data store. That is, changes that are made in the SQL Server database are updated in the other data store, and changes that are made in the other store are updated in the SQL Server database.
When an application updates the local database with changes from another data store, the application must perform the following operations:
Check for conflicts.
A conflict occurs when the same data is changed at the same time in both data stores. The application must be able to check for a conflict and obtain enough information to enable the conflict to be resolved.
Store application context information.
The application stores data that has the change tracking information. This information would be available together with other change tracking information when changes were obtained from the local database. A common example of this contextual information is an identifier for the data store that was the source of the change.
To perform the previous operations, a synchronization application can use the following functions:
CHANGETABLE(VERSION…)
When an application is making changes, it can use this function to check for conflicts. The function obtains the latest change tracking information for a specified row in a change tracked table. The change tracking information includes the version of the row that was last changed. This information enables an application to determine whether the row was changed after the last time that the application was synchronized.
WITH CHANGE_TRACKING_CONTEXT
An application can use this clause to store context data.
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.
Important
We recommend that you use snapshot isolation and make the changes within a snapshot transaction.
-- 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
Note
There is a possibility that the row being updated within the snapshot transaction could have been updated in another transaction after the snapshot transaction was started. In this case, a snapshot isolation update conflict will occur and lead to the transaction being terminated. If this happens, retry the update. This will then lead to a change tracking conflict being detected and no rows being changed.
See Also