Obtendo alterações usando as funções de controle de alterações

Este tópico descreve como usar as funções de controle de alterações no SQL Server 2008 e como usá-las para obter as alterações feitas em um banco de dados e as respectivas informações.

Sobre as funções de controle de alterações

Os aplicativos podem usar as funções a seguir para obter as alterações feitas em um banco de dados e as informações sobre as alterações:

• Função CHANGETABLE(CHANGES…)
  Esta função de conjunto de linhas é usada para consultar informações de alteração. A função consulta os dados armazenados nas tabelas internas de controle de alterações e retorna um conjunto de resultados que contém as chaves primárias de linhas, que foram alteradas, junto com outras informações de alterações como a operação, as colunas atualizadas e a versão da linha.

  A função CHANGETABLE(CHANGES…) considera uma última versão de sincronização como um argumento. A semântica da última versão de sincronização é a seguinte:

  • O cliente de chamada obteve e tem conhecimento de todas as alterações até, e inclusive, a última versão de sincronização.

  • A função CHANGETABLE(CHANGES…) retornará todas as alterações ocorridas depois da última versão de sincronização.

    A ilustração a seguir mostra como a função CHANGETABLE(CHANGES…) é usada para obter alterações.

    

• Função CHANGE_TRACKING_CURRENT_VERSION()
  É usada para obter a versão atual que será usada na próxima vez ao consultar alterações. Essa versão representa a versão da última transação confirmada.

• Função CHANGE_TRACKING_MIN_VALID_VERSION()
  Esta função é usada para obter a versão mínima válida que o cliente pode ter e também obter os resultados válidos de CHANGETABLE(). O cliente deverá comparar a última versão de sincronização com o valor retornado por essa função. Se a última versão de sincronização for menor que a versão retornada por essa função, o cliente não poderá obter os resultados válidos de CHANGETABLE() e deverá reinicializar os dados.

Obtendo dados iniciais

Antes que um aplicativo possa obter as alterações pela primeira vez, ele deverá enviar uma consulta para obter os dados iniciais e a versão de sincronização. O aplicativo deverá obter os dados adequados diretamente da tabela e usar a função CHANGE_TRACKING_CURRENT_VERSION() para obter a versão inicial. Essa versão passará para CHANGETABLE(CHANGES…) na primeira vez em que as alterações forem obtidas.

O exemplo a seguir mostra como obter a versão de sincronização inicial, bem como o conjunto de dados inicial.

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

Usando as funções de controle de alterações para obter alterações

Para obter as linhas alteradas de uma tabela e as informações sobre as alterações, use a função CHANGETABLE(CHANGES…). Por exemplo, a consulta seguinte obtém alterações para a SalesLT.Product tabela.

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

Geralmente, um cliente optará por obter os últimos dados de uma linha em vez de apenas as chaves primárias da linha. Além disso, um aplicativo juntaria os resultados de CHANGETABLE(CHANGES…) com os dados na tabela do usuário. Por exemplo, a consulta a seguir junta as linhas da tabela SalesLT.Product para obter os valores das colunas Name e ListPrice. Observe o uso da função OUTER JOIN. Ela é obrigatória para garantir que as informações de alteração sejam retornadas para essas linhas que foram excluídas da tabela do usuário.

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

Para obter a versão a ser usada na próxima enumeração de alteração, use CHANGE_TRACKING_CURRENT_VERSION(), como mostra o exemplo a seguir.

SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()

Quando um aplicativo obtiver alterações, ele deverá usar CHANGETABLE(CHANGES…) e CHANGE_TRACKING_CURRENT_VERSION(), como mostra o exemplo a seguir.

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

Números de versão

Um banco de dados com o controle de alterações habilitado tem um contador de versões que aumenta à medida que são realizadas mudanças nas tabelas controladas. Cada linha alterada tem um número de versão associado. Quando uma solicitação é enviada a um aplicativo para consultar as alterações, uma função é chamada para fornecer um número de versão. A função retorna informações sobre todas as alterações que foram feitas desde aquela versão. De alguma maneira, a versão de controle de alterações é semelhante ao conceito do tipo de dados de rowversion.

Validando a última versão sincronizada

As informações sobre as alterações são mantidas por um período limitado. A duração desse tempo e controlada pelo parâmetro CHANGE_RETENTION que pode ser especificado como parte de ALTER DATABASE.

Tenha em mente que o tempo especificado em CHANGE_RETENTION determina a freqüência com que todos os aplicativos solicitarão alterações do banco de dados. Se o valor de last_synchronization_version em um aplicativo for anterior ao valor da versão de sincronização mínima válida para uma tabela, esse aplicativo não poderá executar a enumeração de alteração válida. Isso ocorre porque algumas informações de alteração podem ser apagadas. Antes que um aplicativo possa obter as alterações usando a função CHANGETABLE(CHANGES…), ele deve validar o valor de last_synchronization_version para passá-lo para CHANGETABLE(CHANGES…). Se o valor de last_synchronization_version não for válido, esse aplicativo deverá reinicializar todos os dados.

O exemplo a seguir mostra como verificar a validade do valor de last_synchronization_version para cada tabela.

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

Como mostra o exemplo a seguir, a validade do valor de last_synchronization_version pode ser verificada em comparação a todas as tabelas no banco de dados.

    -- Check all tables with change tracking enabled
    IF EXISTS (
      SELECT COUNT(*) 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

Usando o rastreamento de coluna

O rastreamento de coluna permite que os aplicativos obtenham dados apenas das colunas que foram alteradas em vez de toda a linha. Por exemplo, considere o cenário no qual uma tabela tem uma ou mais colunas extensas, mas que raramente têm alterações, e outras colunas com alterações freqüentes. Com o rastreamento de coluna, um aplicativo pode determinar se apenas uma linha foi alterada e se seria preciso sincronizar todos os dados da coluna maior. Entretanto, usando o rastreamento de coluna, é possível que um aplicativo determine se os dados da coluna maior foram alterados e sincronize apenas os dados, caso tenham sido alterados.

As informações de rastreamento de coluna são exibidas na coluna SYS_CHANGE_COLUMNS que é retornada pela função CHANGETABLE(CHANGES…).

O rastreamento de coluna pode retornar NULL para uma coluna que não tenha sido alterada. Se a coluna puder ser alterada para NULL, outra coluna deverá ser retornada para indicar se a coluna foi alterada.

No exemplo a seguir, a coluna CT_ThumbnailPhoto será NULL se ela não tiver sido alterada. Esta coluna também deve ser NULL porque ela foi alterada para NULL - o aplicativo pode usar a coluna CT_ThumbNailPhoto_Changed para determinar se a coluna foi alterada.

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'

Obtendo resultados consistentes e corretos

Para obter os dados alterados de uma tabela é preciso seguir várias etapas. Tenha em mente que podem ocorrer resultados inconsistentes ou incorretos se determinados assuntos não forem considerados.

Por exemplo, para obter as alterações realizadas em uma tabela Sales e uma tabela SalesOrders, um aplicativo executaria as seguintes etapas:

1. Validar a última versão sincronizada usando CHANGE_TRACKING_MIN_VALID_VERSION().

2. Obter a versão que pode ser usada para obter a alteração na próxima vez usando CHANGE_TRACKING_CURRENT_VERSION().

3. Obtenha as alterações da tabela Sales usando CHANGETABLE(CHANGES…).

4. Obtenha as alterações da tabela SalesOrders usando CHANGETABLE(CHANGES…).

Dois processos estão ocorrendo no banco de dados que podem afetar os resultados retornados pelas etapas anteriores:

• O processo de limpeza é executado em segundo plano e remove as informações de controle de alterações anteriores ao período de retenção especificado.

  A limpeza é um processo interno e em segundo plano que usa um período de retenção especificado quando você configura o controle de alterações para o banco de dados. O problema é que o processo de limpeza pode ocorrer na hora em que a última versão de sincronização foi validada e quando a chamada para CHANGETABLE(CHANGES…) for feita. Uma última versão de sincronização que acabou de ser validada pode não ser mais válida no momento em que as alterações forem obtidas. Então, podem ser retornados resultados incorretos.

• Operações de DML contínuas ocorrem nas tabelas Sales e SalesOrders, como as operações a seguir:

  • As alterações podem ser feitas na tabela depois que a versão tiver sido obtida usando CHANGE_TRACKING_CURRENT_VERSION(). Talvez sejam retornadas mais alterações do que o esperado.

  • Uma transação pode ocorrer no período entre a chamada para obter as alterações da tabela Sales e a chamada para obter as alterações da tabela SalesOrders. Entre os resultados da tabela SalesOrder pode haver um valor de chave estrangeira que não exista na tabela Sales.

Para superar os desafios previamente listados, recomendamos usar o isolamento de instantâneo. Isso ajudará a garantir a consistência das informações de alterações e evitará situações de competição relacionadas à tarefa de limpeza em segundo plano. Se você optar por não usar as transações de instantâneo, o desenvolvendo um aplicativo que usa controle de alterações poderá requerer mais esforço.

Usando o isolamento de instantâneo

O controle de alterações foi projetado para funcionar bem com o isolamento de instantâneo. O isolamento de instantâneo deve estar habilitado para o banco de dados. Todas as etapas são obrigatórias para obter as alterações que devem estar incluídas em uma transação de instantâneo. Isso garantirá que todas as alterações realizadas nos dados durante a obtenção de alterações não estarão visíveis às consultas dentro da transação de instantâneo.

Para obter dados dentro de uma transação de instantâneo, execute as seguintes etapas:

1. Defina o nível de isolamento da transação para o instantâneo e inicie uma transação.

2. Valide a última versão de sincronização usando a função CHANGE_TRACKING_MIN_VALID_VERSION().

3. Obtenha a versão a ser usada na próxima vez usando CHANGE_TRACKING_CURRENT_VERSION().

4. Obtenha as alterações da tabela Sales usando CHANGETABLE(CHANGES…).

5. Obtenha as alterações da tabela SalesOrders usando CHANGETABLE(CHANGES…).

6. Confirme a transação.

A seguir alguns pontos para recordar as etapas da obtenção de alterações em uma transação de instantâneo:

• Se a limpeza ocorrer depois que a última versão de sincronização for validada, os resultados da função CHANGETABLE(CHANGES…) ainda serão válidos, pois as operações de exclusão desenvolvidas pela limpeza não serão visíveis dentro da transação.

• Todas as alterações realizadas nas tabelas Sales ou SalesOrders depois que a versão de sincronização tiver sido obtida não estarão visíveis e as chamadas para CHANGETABLE(CHANGES…) nunca retornarão alterações com uma versão posterior àquela retornada pela função CHANGE_TRACKING_CURRENT_VERSION(). A consistência entre as tabelas Sales e SalesOrders também será mantida porque as transações que foram confirmadas no período entre as chamadas para CHANGETABLE(CHANGES…) não estarão visíveis.

O exemplo a seguir mostra como o isolamento de instantâneo é habilitado para um banco de dados.

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

Uma transação de instantâneo é usada a seguinte forma:

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

Para obter mais informações sobre as transações de instantâneo, consulte Usando níveis de isolamento com base em controle de versão de linha.

Alternativas para usar o isolamento de instantâneo

Existem alternativas para o uso do isolamento de instantâneo, mas elas exigem mais trabalho para certificar-se de que todos os requisitos do aplicativos sejam atendidos. Para verificar se last_synchronization_version é válido e se os dados não foram removidos pelo processo de limpeza antes de as alterações serem obtidas, siga estas etapas:

1. Verifique last_synchronization_version depois das chamadas para CHANGETABLE().

2. Verifique last_synchronization_version como parte de cada consulta obter alterações usando CHANGETABLE().

As alterações podem ocorrer depois da versão de sincronização para a próxima enumeração for obtida. Existem duas maneiras para controlar essa situação: A opção que é usada depende do aplicativo e como ele pode controlar os efeitos colaterais de cada abordagem:

• Ignore as alterações cuja versão é maior que a nova versão de sincronização.

  O efeito colateral dessa abordagem é fazer com que uma nova linha ou uma linha atualizada seja ignorada caso ela tenha sido criada ou atualizada antes da nova versão de sincronização, exceto a atualização posterior. Se houver uma nova linha, poderá ocorrer um problema de integridade referencial caso tenha existido uma linha em outra tabela que tenha sido criada com referência à linha ignorada. Se houver uma linha existente atualizada, a linha será ignorada e não será sincronizada até o próximo período.

• Inclua todas as alterações, até mesmo aquelas que contêm uma versão maior que a nova versão de sincronização.

  As linhas com uma versão maior que a nova versão de sincronização serão obtidas na próxima sincronização. Isso deve ser esperado e controlado pelo aplicativo.

Além das duas opções anteriores, você pode criar uma abordagem que combine ambas as opções, dependendo da operação. Por exemplo, você poder desejar uma aplicação na qual seja melhor ignorar as alterações mais recentes do que a próxima versão de sincronização na qual a fila foi criada ou excluída, mas as atualizações não foram ignoradas.

Observação
Ao optar pela abordagem que funcionará com o aplicativo quando você estiver usando o controle de alterações (ou qualquer mecanismo de controle personalizado), será preciso considerar uma análise significativa. Portanto, é muito mais simples usar o isolamento de instantâneo.

Consulte também

Tarefas

Usando o controle de alterações

Conceitos

Como o controle de alterações controla as alterações em um banco de dados

Controle de alterações e restauração de dados