使用更改跟踪 (SQL Server)

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Microsoft Fabric 中的 SQL 数据库

使用更改跟踪的应用程序必须能够获取跟踪的更改，将这些更改应用到其他数据存储区并更新源数据库。 本文介绍了如何执行这些任务，以及在发生故障转移并且必须从备份中还原数据库时，角色更改跟踪所起的作用。

通过使用更改跟踪函数获取更改

介绍如何使用更改跟踪功能来获取更改以及有关对数据库所做的更改的信息。

关于更改跟踪功能

应用程序可以使用以下函数来获取在数据库中所做的更改以及有关这些更改的信息：

• CHANGETABLE(CHANGES ...) 函数

  此行集函数用于查询更改信息。 该函数查询内部更改跟踪表中存储的数据。 该函数返回一个结果集，其中包含已更改行的主键以及其他更改信息，例如操作、已更新的列和该行的版本。

  CHANGETABLE(CHANGES ...) 采用上一个同步版本作为参数。 上次同步版本是使用 @last_synchronization_version 变量获得的。 上次同步版本的语义如下所示：

• 发起调用的客户端已获取这些更改，并已知晓截至并包括上次同步版本的所有更改。

• CHANGETABLE(CHANGES ...) 因此，将返回上次同步版本之后发生的所有更改。

下图显示了如何使用 CHANGETABLE(CHANGES ...) 获取更改。

在此示例中，客户端 A 上次在上午 9:30 同步，而客户端 B 上次在上午 10:30 同步。 分别于上午10:00和上午11:00，对数据进行了几处更改。 以下示例汇总了这些修订。

CHANGETABLE(CHANGES...) 输出 - 上午 11:30

客户端 A 上次在上午 9:30 同步。

客户端 B 上次在上午 10:30 同步。

• CHANGE_TRACKING_CURRENT_VERSION() 功能

  用于获取当前版本，以供下次查询更改时使用。 该版本对应于最近一次已提交事务的版本。

• CHANGE_TRACKING_MIN_VALID_VERSION() 函数

  用于获取客户端可以拥有的最低有效版本，并且仍然从中 CHANGETABLE()获取有效结果。 客户端应将上次同步版本与此函数返回的值进行对照检查。 如果上次同步版本小于此函数返回的版本，客户端将无法从 CHANGETABLE() 中获取有效结果，并且必须重新初始化。

获取初始数据

在应用程序第一次获取更改之前，应用程序必须发送查询以获取初始数据和同步版本。 应用程序必须直接从表中获取适当的数据，然后用于 CHANGE_TRACKING_CURRENT_VERSION() 获取初始版本。 此版本将在首次获取到更改时传递给 CHANGETABLE(CHANGES ...)。

下面的示例说明了如何获取初始同步版本和初始数据集。

declare @synchronization_version bigint;

-- Obtain the current synchronization version. This will be used next time that changes are obtained.
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

-- Obtain initial data set.
SELECT
    P.ProductID, P.Name, P.ListPrice
FROM
   SalesLT.Product AS P;

使用更改跟踪功能获取更改内容

若要获取表的更改行以及有关更改的信息，请使用 CHANGETABLE(CHANGES...)。 例如，以下查询获取 SalesLT.Product 表的更改。

declare @last_synchronization_version bigint;

SELECT
    CT.ProductID, CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT;

通常，客户端会希望获取某一行的最新数据，而不仅仅是该行的主键。 因此，应用程序会将结果 CHANGETABLE(CHANGES ...) 与用户表中的数据联接。 例如，下面的查询与 SalesLT.Product 表联接在一起以获取 Name 和 ListPrice 列的值。 请注意，本例中使用了 OUTER JOIN。 若要确保返回有关从用户表中删除的那些行的更改信息，则必须使用此运算符。

SELECT
    CT.ProductID, P.Name, P.ListPrice,
    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
    CT.SYS_CHANGE_CONTEXT
FROM
    SalesLT.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
    P.ProductID = CT.ProductID;

若要获取在下次更改枚举中使用的版本，请使用 CHANGE_TRACKING_CURRENT_VERSION()，如下面的示例所示。

SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

当应用程序获取更改时，它必须同时使用 CHANGETABLE(CHANGES…) 和 CHANGE_TRACKING_CURRENT_VERSION()，如下面的示例所示。

-- Obtain the current synchronization version. This will be used the next time CHANGETABLE(CHANGES...) is called.
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

-- Obtain incremental changes by using the synchronization version obtained the last time the data was synchronized.
SELECT
    CT.ProductID, P.Name, P.ListPrice,
    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
    CT.SYS_CHANGE_CONTEXT
FROM
    SalesLT.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
    P.ProductID = CT.ProductID;

版本号

启用了更改跟踪的数据库具有一个版本计数器；在对启用了更改跟踪的表进行更改时，该计数器会随之递增。 每个更改的行都有一个关联的版本号。 将请求发送到应用程序以查询更改时，将调用一个函数以提供版本号。 该函数返回在该版本之后所做的所有更改的相关信息。 从某种意义上讲，更改跟踪版本在概念上与 rowversion 数据类型类似。

验证上次同步的版本

有关更改的信息仅保留一段有限时间。 时长由 CHANGE_RETENTION 参数控制，该参数可作为 ALTER DATABASE 的一部分进行指定。

为 CHANGE_RETENTION 指定的时间决定了所有应用程序必须以多高的频率从数据库请求更改。 如果应用程序 last_synchronization_version 的值早于表的最低有效同步版本，则该应用程序无法执行有效的更改枚举。 这是因为，可能已清除了某些更改信息。 在应用程序使用 CHANGETABLE(CHANGES ...) 获取更改之前，应用程序必须验证它计划传递给 CHANGETABLE(CHANGES ...) 的 last_synchronization_version 值。 如果该值 last_synchronization_version 无效，则该应用程序必须重新初始化所有数据。

下面的示例说明了如何验证每个表的 last_synchronization_version 值的有效性。

-- Check individual table.
IF (@last_synchronization_version < CHANGE_TRACKING_MIN_VALID_VERSION(
                                   OBJECT_ID('SalesLT.Product')))
BEGIN
  -- Handle invalid version and do not enumerate changes.
  -- Client must be reinitialized.
END;

正如下面的示例所示，可以对照数据库中的所有表检查 last_synchronization_version 值的有效性。

-- Check all tables with change tracking enabled
IF EXISTS (
  SELECT 1 FROM sys.change_tracking_tables
  WHERE min_valid_version > @last_synchronization_version )
BEGIN
  -- Handle invalid version & do not enumerate changes
  -- Client must be reinitialized
END;

使用列跟踪功能

通过使用列跟踪，应用程序可以仅获取已更改的列数据，而不是获取整个行。 例如，请考虑以下情况：某个表包含一个或多个较大但很少更改的列，并且还包含其他经常更改的列。 如果未使用列跟踪，应用程序只能确定某一行已更改并且必须同步所有数据（包括大型列数据）。 但是，通过使用列跟踪，应用程序可以确定是否更改了大型列数据，并且仅同步已更改的数据。

列跟踪信息显示在 SYS_CHANGE_COLUMNS 函数返回的 CHANGETABLE(CHANGES ...) 列中。

可以使用列跟踪，以便对于未更改的列返回 NULL。 如果列可以更改为 NULL，则必须返回单独的列，以指示列是否已更改。

在下面的示例中，如果 CT_ThumbnailPhoto 列未更改，该列将为 NULL。 此列也可能是 NULL，因为它已被更改为 NULL。 应用程序可以使用 CT_ThumbNailPhoto_Changed 该列来确定列是否已更改。

DECLARE @PhotoColumnId int = COLUMNPROPERTY(
    OBJECT_ID('SalesLT.Product'),'ThumbNailPhoto', 'ColumnId');

SELECT
    CT.ProductID, P.Name, P.ListPrice, -- Always obtain values.
    CASE
           WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
                     @PhotoColumnId, CT.SYS_CHANGE_COLUMNS) = 1
            THEN ThumbNailPhoto
            ELSE NULL
      END AS CT_ThumbNailPhoto,
      CHANGE_TRACKING_IS_COLUMN_IN_MASK(
                     @PhotoColumnId, CT.SYS_CHANGE_COLUMNS) AS
                                   CT_ThumbNailPhoto_Changed,
     CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
     CT.SYS_CHANGE_CONTEXT
FROM
     SalesLT.Product AS P
INNER JOIN
     CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
     P.ProductID = CT.ProductID AND
     CT.SYS_CHANGE_OPERATION = 'U';

获取一致且正确的结果

若要获取更改的表数据，你需要执行多个步骤。 如果没有考虑到并处理某些问题，可能会返回不一致或错误的结果。

例如，要获取对 Sales 表和 SalesOrders 表所做的更改，应用程序应执行以下步骤：

1. 使用 CHANGE_TRACKING_MIN_VALID_VERSION().. 验证上次同步的版本。

2. 获取下次可使用 CHANGE_TRACKING_CURRENT_VERSION() 进行更改的版本。

3. 使用 CHANGETABLE(CHANGES ...) 获取 Sales 表的更改内容。

4. 通过使用 CHANGETABLE(CHANGES ...) 获取 SalesOrders 表的更改。

数据库中运行的两个进程可能会影响上述步骤返回的结果：

• 清除进程在后台运行，并删除早于指定保持期的更改跟踪信息。

  清除进程是一个单独的后台进程，它使用在为数据库配置更改跟踪时指定的保持期。 问题是清除进程可能会在验证上次同步版本之后以及调用 CHANGETABLE(CHANGES…) 之前运行。 在检索到这些更改时，之前有效的上一次同步版本可能已不再有效。 因此，可能会返回错误的结果。

• 正在 Sales 和 SalesOrders 表中执行 DML 操作，如下面的操作：

  • 在获取到下一个版本后，可以使用 CHANGE_TRACKING_CURRENT_VERSION() 对这些表进行更改。 因此，返回的更改可能超过预期数量。

  • 某个事务可能会在调用从 Sales 表获取更改和调用从 SalesOrders 表获取更改之间被提交。 因此，SalesOrder 表的结果可能包含 Sales 表中不存在的外键值。

若要克服前文列出的挑战，我们建议你使用快照隔离。 这将有助于确保更改信息的一致性，并避免与后台清理任务相关的竞争条件。 如果不使用快照事务，开发使用更改跟踪的应用程序可能需要付出多得多的工作量。

使用快照隔离

从设计上，更改跟踪可以很好地与快照隔离配合使用。 必须为数据库启用快照隔离。 获取更改所需的所有步骤必须包含在快照事务中。 这可确保快照事务中的查询看不见在获取更改时对数据所做的所有更改。

若要在快照事务中获取数据，请执行以下步骤：

1. 将事务隔离级别设置为快照，然后启动一个事务。

2. 使用 CHANGE_TRACKING_MIN_VALID_VERSION().. 验证上次同步版本。

3. 使用 CHANGE_TRACKING_CURRENT_VERSION() 获取下次要使用的版本。

4. 使用 CHANGETABLE(CHANGES ...) 获取 Sales 表的更改

5. 使用 CHANGETABLE(CHANGES ...) 获取 SalesOrders 表的更改

6. 提交事务。

由于获取更改所需的所有步骤都是在快照事务中执行的，因此，应注意以下事项：

• 如果在验证最后一个同步版本之后进行了清理，则 CHANGETABLE(CHANGES ...) 返回的结果仍然有效，因为清理执行的删除操作在事务中不可见。

• 获取到下一同步版本后，对 Sales 表或 SalesOrders 表所做的任何更改都将不可见，并且对 CHANGETABLE(CHANGES ...) 的调用永远不会返回版本晚于 CHANGE_TRACKING_CURRENT_VERSION() 所返回版本的更改。 Sales 表与 SalesOrders 表之间也将保持一致，因为在两次调用 CHANGETABLE(CHANGES ...) 之间提交的事务将不可见。

下面的示例说明了如何为数据库启用快照隔离。

-- The database must be configured to enable snapshot isolation.
ALTER DATABASE AdventureWorksLT
    SET ALLOW_SNAPSHOT_ISOLATION ON;

快照事务的使用方式如下：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
  -- Verify that version of the previous synchronization is valid.
  -- Obtain the version to use next time.
  -- Obtain changes.
COMMIT TRAN

有关快照事务的详细信息，请参阅SET TRANSACTION ISOLATION LEVEL（Transact-SQL）。

清理和快照隔离

在同一数据库上，或在同一实例中的两个不同数据库上，同时启用快照隔离和更改跟踪时，如果启用了快照隔离的数据库中存在未完成的事务，则可能导致清除过程在 sys.syscommittab 中保留过期行。 之所以会出现这种情况，是因为更改跟踪清理过程在执行清理时会考虑实例范围的低水位线（即安全清理版本）。 这样做是为了确保更改跟踪自动清理过程不会删除启用了快照隔离的数据库中的打开事务可能需要的任何行。 尽可能使读取已提交的快照隔离和快照隔离事务保持简短，以确保 sys.syscommittab 中的过期行得到及时清理。

快照隔离的替代方法

除了使用快照隔离之外，还有其他替代方案，但它们需要投入更多工作，以确保满足应用程序的所有要求。 为确保 last_synchronization_version 有效，并且在获取更改之前数据不会被清理过程删除，请执行以下步骤：

1. 在调用 CHANGETABLE() 后检查 last_synchronization_version。

2. 检查每个查询中的 last_synchronization_version，以使用 CHANGETABLE() 获取更改。

在获取下一次枚举的同步版本后，可能会发生更改。 可以使用两种方法来处理这种情况。 所使用的选项取决于应用程序及其处理每种方法的副作用的方式：

• 忽略版本高于新同步版本的更改。

  这种方法有一个副作用：如果某个新建或已更新的行是在新同步版本之前创建或更新的，但随后又被更新，那么该行仍会被跳过。 如果有一个新行，并且另一个表中创建的行引用跳过的行，则可能会出现引用完整性问题。 如果更新了某个现有行，将跳过该行，并且下次才会对其进行同步。

• 包括所有更改项，即使其版本高于新的同步版本。

  下次同步时，将重新获取版本高于新同步版本的行数据。 应用程序必须能够预料并处理这种情况。

除了上述两个选项外，还可以设计结合这两个选项的方法，具体取决于所执行的操作。 例如，你可能希望应用程序最好忽略晚于下次同步版本（在该版本中创建或删除了行）的更改，但不忽略更新。

更改跟踪如何处理对数据库的更改

某些使用更改跟踪的应用程序执行与另一个数据存储区的双向同步。 即，在一个 SQL Server 数据库中所做的更改将更新到另一个数据存储区中，而在该数据存储区中所做的更改将更新到该 SQL Server 数据库中。

当应用程序使用另一个数据存储区中的更改更新本地数据库时，应用程序必须执行以下操作：

• 检查冲突。

  如果在两个数据存储区中同时更改相同的数据，则会发生冲突。 应用程序必须能够检查冲突，并获取足够的信息以便能够解决冲突。

• 存储应用程序上下文信息。

  应用程序存储包含更改跟踪信息的数据。 如果更改是从本地数据库中获取的，则会将此信息与其他更改跟踪信息放在一起。 此上下文信息的一个常见示例是作为更改源的数据存储区的标识符。

若要执行上述操作，同步应用程序可使用下列函数：

• CHANGETABLE(VERSION...)

  当应用程序进行更改时，它可以使用该函数来检查冲突。 对于启用了更改跟踪的表，该函数可获取该表中指定行的最新更改跟踪信息。 更改跟踪信息包括上次更改的行的版本。 应用程序可以使用此信息来确定自上次应用程序同步后该行是否进行了更改。

• WITH CHANGE_TRACKING_CONTEXT

  应用程序可以使用此子句来存储上下文数据。

检查冲突

在双向同步场景中，客户端应用程序必须确定某一行自应用程序上次获取这些更改以来是否尚未被更新。

以下示例演示如何使用 CHANGETABLE(VERSION ...) 函数以最有效的方式检查冲突，而无需单独的查询。 在此示例中，CHANGETABLE(VERSION ...) 确定由 @product id 指定的行的 SYS_CHANGE_VERSION。 CHANGETABLE(CHANGES ...) 可以获取相同的信息，但效率较低。 如果该行的 SYS_CHANGE_VERSION 值大于 @last_sync_version 值，则说明有冲突。 如果有冲突，则不会更新该行。 ISNULL() 检查是必需的，因为该行可能没有可用的更改信息。 如果在启用更改跟踪或清理更改信息后没有更新该行，则不存在任何更改信息。

-- 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);

以下代码可以检查更新的行数以及找出有关冲突的更多信息。

-- 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

设置上下文信息

通过使用子 WITH CHANGE_TRACKING_CONTEXT 句，应用程序可以将上下文信息与更改信息一起存储。 然后，可以从 CHANGETABLE(CHANGES ...) 返回的 SYS_CHANGE_CONTEXT 列中获取这些信息。

上下文信息通常用于确定更改源。 如果可以确定更改源，数据存储区在重新同步时可使用该信息来避免获取更改。

-- 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);

确保一致且正确的结果

在验证 @last_sync_version 值时，应用程序必须考虑清除过程。 这是因为在调用 CHANGE_TRACKING_MIN_VALID_VERSION() 之后、完成更新之前，数据可能已被删除。

你应使用快照隔离，并在快照事务中进行更改。

-- 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;

更改跟踪和数据恢复

对于需要同步的应用程序，必须考虑启用了更改跟踪的数据库恢复到早期版本数据的情况。 这种情况可能会在以下情况下发生：数据库从备份还原后、故障转移到异步数据库镜像后，或者使用日志传送时发生故障时。 以下情况揭示了这一问题：

1. 表 T1 启用了更改跟踪，并且该表的最低有效版本为 50。

2. 客户端应用程序在版本 100 处同步数据，并获取有关版本 50 到 100 之间的所有更改的信息。

3. 在版本 100 之后又对表 T1 进行了其他更改。

4. 在版本 120 处出现故障，数据库管理员还原了数据库，但出现了数据丢失。 在还原操作之后，该表包含直至版本 70 的数据，最低同步版本仍为 50。

   也就是说，同步数据存储区具有主数据存储区中已不再存在的数据。

5. T1 已多次更新。 这使当前版本升至 130。

6. 客户端应用程序再次进行同步并提供上次同步版本号 100。 客户端会验证此版本号有效，因为 100 大于 50。

   客户端获取版本 100 到 130 之间的更改。 此时，客户端并不知道版本 70 到 100 之间的更改已经与以前不同。 客户端上的数据与服务器上的数据不同步。

如果将数据库恢复到版本 100 之后的某一版本，则同步不会出现问题。 客户端和服务器将在下一个同步间隔内正确同步数据。

更改跟踪不支持恢复丢失的数据。 但是，有两种选择可用于检测这些类型的同步问题：

• 在服务器上存储数据库版本 ID，每次恢复数据库或丢失数据时都更新此值。 每个客户端应用程序将存储该 ID，并且每个客户端在同步数据时必须验证此 ID。 如果发生数据丢失，则 ID 将不匹配，并且客户端将重新初始化。 这种方法有一个缺点，即如果数据丢失未越过上次的同步边界，则客户端可能会进行不必要的重新初始化。

• 当客户端查询更改时，会在服务器上为每个客户端记录上次同步的版本号。 如果数据有问题，则上次同步的版本号将不匹配。 这表明需要进行重新初始化。

相关内容

• 跟踪数据更改（SQL Server）
• 关于更改跟踪 (SQL Server)
• 管理更改跟踪 （SQL Server）
• 启用和禁用更改跟踪 （SQL Server）
• CHANGETABLE （Transact-SQL）
• CHANGE_TRACKING_MIN_VALID_VERSION（Transact-SQL）
• CHANGE_TRACKING_CURRENT_VERSION（Transact-SQL）
• WITH CHANGE_TRACKING_CONTEXT（Transact-SQL）