Acquisizione di modifiche utilizzando le funzioni di rilevamento delle modifiche
In questo argomento vengono descritte le funzioni di rilevamento delle modifiche disponibili in SQL Server 2008 e viene illustrato il modo in cui utilizzarle per ottenere le modifiche apportate a un database e le relative informazioni.
Informazioni sulle funzioni di rilevamento delle modifiche
Per ottenere le modifiche apportate a un database e le relative informazioni, nelle applicazioni è possibile utilizzare le funzioni seguenti:
CHANGETABLE(CHANGES …)
Questa funzione per i set di righe viene utilizzata per eseguire query relative alle informazioni sulle modifiche. La funzione esegue una query sui dati archiviati nelle tabelle per il rilevamento delle modifiche interne e restituisce un set di risultati che contiene le chiavi primarie delle righe modificate, insieme con altre informazioni sulle modifiche, ad esempio l'operazione, le colonne aggiornate e la versione della riga.CHANGETABLE(CHANGES ...) accetta l'ultima versione sincronizzata come argomento. La semantica dell'ultima versione sincronizzata è la seguente:
Il client chiamante ha ottenuto le modifiche e conosce tutte le modifiche fino all'ultima versione sincronizzata compresa.
Tutte le modifiche eseguite dopo l'ultima versione sincronizzata verranno pertanto restituite in CHANGETABLE(CHANGES…).
Nella figura seguente viene illustrato il modo in cui CHANGETABLE(CHANGES ...) viene utilizzata per ottenere modifiche.
.gif "Esempio di output della query per il rilevamento delle modifiche")
Funzione CHANGE_TRACKING_CURRENT_VERSION()
Questa funzione viene utilizzata per ottenere la versione corrente che verrà utilizzata alla successiva esecuzione di query sulle modifiche. Tale versione è quella relativa all'ultima transazione di cui è stato eseguito il commit.Funzione CHANGE_TRACKING_MIN_VALID_VERSION()
Questa funzione viene utilizzata per ottenere la versione minima valida che un client può avere per ottenere risultati validi da CHANGETABLE (). Il client deve controllare l'ultima versione sincronizzata rispetto al valore restituito dalla funzione. Se l'ultima versione sincronizzata è inferiore rispetto a quella restituita dalla funzione, il client non sarà in grado di ottenere risultati validi da CHANGETABLE() e sarà necessario reinizializzare i dati.
Acquisizione dei dati iniziali
Prima che un'applicazione sia in grado di ottenere modifiche per la prima volta, è necessario che invii una query per ottenere i dati iniziali e la versione sincronizzata. È necessario che l'applicazione ottenga i dati appropriati direttamente dalla tabella, quindi utilizzi CHANGE_TRACKING_CURRENT_VERSION() per ottenere la versione iniziale. Tale versione verrà passata a CHANGETABLE(CHANGES ...) la prima volta che le modifiche vengono ottenute.
Nell'esempio seguente viene illustrato come ottenere la versione sincronizzata e il set di dati iniziali.
-- 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
Utilizzo delle funzioni di rilevamento delle modifiche per ottenere le modifiche
Per ottenere le righe modificate in una tabella e le relative informazioni, utilizzare CHANGETABLE(CHANGES ...). La query seguente, ad esempio, consente di ottenere le modifiche per la tabella SalesLT.Product.
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
Poiché in un client viene in genere richiesto di ottenere i dati più recenti relativi a una riga anziché le sole chiavi primarie per la riga stessa, verrà creato un join tra i risultati di CHANGETABLE(CHANGES ...) e i dati presenti nella tabella utente. Nella query seguente, ad esempio, viene creato un join con la tabella SalesLT.Product per ottenere i valori per le colonne Name e ListPrice. Si noti l'utilizzo di OUTER JOIN, necessario per garantire che le informazioni sulle modifiche vengano restituite per le righe eliminate dalla tabella utente.
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
Per ottenere la versione da utilizzare nella successiva enumerazione delle modifiche, utilizzare CHANGE_TRACKING_CURRENT_VERSION(), come illustrato nell'esempio seguente.
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
Quando in un'applicazione vengono ottenute modifiche, è necessario utilizzare sia CHANGETABLE(CHANGES…) che CHANGE_TRACKING_CURRENT_VERSION(), come illustrato nell'esempio seguente.
-- 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
Numeri di versione
Un database in cui è abilitato il rilevamento delle modifiche dispone di un contatore di versione che aumenta in base alle modifiche apportate alle tabelle in cui il rilevamento è attivato. A ciascuna riga modificata è associato un numero di versione. Quando a un'applicazione viene inviata una richiesta per eseguire una query relativa alle modifiche, viene chiamata una funzione che specifica un numero di versione. Tale funzione restituisce informazioni su tutte le modifiche apportate a partire da tale versione. Per alcuni aspetti, la versione del rilevamento delle modifiche è concettualmente analoga al tipo di dati rowversion.
Convalida dell'ultima versione sincronizzata
Le informazioni sulle modifiche vengono mantenute per un periodo di tempo limitato, la cui lunghezza viene controllata dal parametro CHANGE_RETENTION che può essere specificato nell'istruzione ALTER DATABASE.
È necessario tenere presente che il periodo di tempo specificato per CHANGE_RETENTION determina la frequenza con cui per tutte le applicazioni è necessario richiedere le modifiche apportate al database. Se a un'applicazione è associato un valore relativo a last_synchronization_version meno recente rispetto a quello della versione sincronizzata valida minima per una tabella, tale applicazione non può eseguire un'enumerazione delle modifiche valida poiché alcune informazioni sulle modifiche potrebbero essere state eliminate. Affinché in un'applicazione possano essere ottenute le modifiche mediante CHANGETABLE(CHANGES …), è necessario che venga convalidato il valore relativo a last_synchronization_version di cui è previsto il passaggio a CHANGETABLE(CHANGES …). Se il valore di last_synchronization_version non è valido, è necessario che l'applicazione reinizializzi tutti i dati.
Nell'esempio seguente viene illustrato come verificare la validità del valore di last_synchronization_version per ogni tabella.
-- 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
Come illustrato nell'esempio seguente, la validità del valore di last_synchronization_version può essere verificata in relazione a tutte le tabelle del database.
-- 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
Utilizzo del rilevamento a livello di colonna
Il rilevamento a livello di colonna consente di ottenere i dati relativi alle sole colonne modificate anziché all'intera riga. Si consideri ad esempio uno scenario in cui in una tabella sono presenti una o più colonne di notevoli dimensioni, ma modificate raramente, e altre colonne che subiscono modifiche frequenti. Senza il rilevamento a livello di colonna, un'applicazione è in grado di rilevare solo che una riga è stata modificata e che è necessario sincronizzare tutti i dati, inclusi quelli presenti nelle colonne di grandi dimensioni. Il rilevamento a livello di colonna consente tuttavia di stabilire se i dati presenti nelle colonne di grandi dimensioni sono stati modificati ed eseguire la sincronizzazione solo in quest'ultimo caso.
Le informazioni sul rilevamento a livello di colonna vengono visualizzate nella colonna SYS_CHANGE_COLUMNS restituita dalla funzione CHANGETABLE(CHANGES …).
Il rilevamento a livello di colonna può essere utilizzato in modo che per una colonna cui non sono state apportate modifiche venga restituito il valore NULL. Se la colonna può essere impostata su NULL, è necessario che venga restituita una colonna separata per indicare se la colonna è stata modificata.
Nell'esempio seguente se la colonna CT_ThumbnailPhoto non ha subito modifiche, verrà restituito il valore NULL. A tale colonna potrebbe inoltre essere associato il valore NULL poiché è stata impostata su NULL. È possibile utilizzare la colonna CT_ThumbNailPhoto_Changed per determinare se la colonna ha subito modifiche.
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'
Acquisizione di risultati coerenti e corretti
Per ottenere i dati modificati relativi a una tabella, è necessario effettuare più operazioni. È necessario inoltre tenere presente che potrebbero venire restituiti risultati incoerenti o non corretti se non vengono considerati e gestiti problemi specifici.
Per ottenere le modifiche apportate a una tabella Sales e a una tabella SalesOrders, in un'applicazione è necessario ad esempio effettuare le operazioni seguenti:
Convalidare l'ultima versione sincronizzata utilizzando CHANGE_TRACKING_MIN_VALID_VERSION().
Ottenere la versione che può essere utilizzata per ottenere le modifiche la volta successiva utilizzando CHANGE_TRACKING_CURRENT_VERSION().
Ottenere le modifiche per la tabella Sales utilizzando CHANGETABLE(CHANGES …).
Ottenere le modifiche per la tabella SalesOrders utilizzando CHANGETABLE(CHANGES …).
Nel database sono in esecuzione due processi che possono influenzare i risultati restituiti dalle operazioni precedenti:
Il processo di pulizia viene eseguito in background e rimuove le informazioni sul rilevamento delle modifiche precedenti rispetto al periodo di memorizzazione specificato.
Il processo di pulizia è un processo eseguito separatamente in background che utilizza il periodo di memorizzazione specificato quando si configura il rilevamento delle modifiche per il database. Quando il processo di pulizia viene eseguito nel periodo di tempo che intercorre tra la convalida dell'ultima versione sincronizzata e la chiamata di funzione CHANGETABLE(CHANGES ...), può verificarsi un problema poiché l'ultima versione sincronizzata valida potrebbe non esserlo più nel momento in cui vengono ottenute le modifiche. È possibile pertanto che vengano restituiti risultati non corretti.
Operazioni DML in corso nelle tabelle Sales e SalesOrders, ad esempio le operazioni seguenti:
Esecuzione di modifiche alle tabelle dopo che è stata ottenuta la versione per la volta successiva mediante CHANGE_TRACKING_CURRENT_VERSION(). È pertanto possibile che vengano restituite più modifiche del previsto.
Esecuzione del commit di una transazione nel periodo di tempo che intercorre tra la chiamata di funzione per ottenere le modifiche apportate alla tabella Sales e quella per ottenere le modifiche apportate alla tabella SalesOrders. Ai risultati relativi alla tabella SalesOrder potrebbe pertanto essere associato un valore di chiave esterna che non esiste nella tabella Sales.
Per risolvere i problemi elencati in precedenza, è consigliabile utilizzare l'isolamento dello snapshot che consente di garantire la coerenza delle informazioni sulle modifiche e di evitare situazioni di race condition correlate all'attività di pulizia eseguita in background. Se non si utilizzano transazioni snapshot, lo sviluppo di un'applicazione che utilizza il rilevamento delle modifiche potrebbe risultare notevolmente più impegnativo.
Utilizzo dell'isolamento dello snapshot
Il rilevamento delle modifiche è stato progettato per funzionare in modo ottimale con l'isolamento dello snapshot. È necessario che l'isolamento dello snapshot sia abilitato nel database. Tutti i passaggi necessari per ottenere le modifiche devono essere contenuti in una transazione snapshot in modo che tutte le modifiche apportate ai dati durante l'acquisizione delle modifiche stesse non siano visibili alle query eseguite nella transazione snapshot.
Per ottenere dati all'interno di una transazione snapshot, effettuare le operazioni seguenti:
Impostare il livello di isolamento della transazione sullo snapshot e avviare una transazione.
Convalidare l'ultima versione sincronizzata utilizzando CHANGE_TRACKING_MIN_VALID_VERSION().
Ottenere la versione da utilizzare successivamente tramite CHANGE_TRACKING_CURRENT_VERSION().
Ottenere le modifiche per la tabella Sales utilizzando CHANGETABLE(CHANGES …).
Ottenere le modifiche per la tabella SalesOrders utilizzando CHANGETABLE(CHANGES …).
Eseguire il commit della transazione.
Poiché tutti i passaggi necessari per ottenere le modifiche sono contenuti in una transazione snapshot, tenere in considerazione quanto segue:
Se il processo di pulizia viene eseguito dopo la convalida dell'ultima versione sincronizzata, i risultati restituiti da CHANGETABLE(CHANGES …) saranno ancora validi poiché le operazioni di eliminazione eseguite dal processo di pulizia non saranno visibili all'interno della transazione.
Qualsiasi modifica apportata alla tabella Sales o SalesOrders dopo che è stata ottenuta la versione sincronizzata successiva non sarà visibile e le chiamate a CHANGETABLE(CHANGES …) non restituiranno mai modifiche con una versione successiva a quella restituita da CHANGE_TRACKING_CURRENT_VERSION(). Verrà inoltre mantenuta la coerenza tra le tabelle Sales e SalesOrders poiché le transazioni di cui era stato eseguito il commit nel periodo di tempo che intercorre tra le chiamate a CHANGETABLE(CHANGES …) non saranno visibili.
Nell'esempio seguente viene illustrato il modo in cui l'isolamento dello snapshot viene abilitato per un database.
-- The database must be configured to enable snapshot isolation.
ALTER DATABASE AdventureWorksLT
SET ALLOW_SNAPSHOT_ISOLATION ON;
Una transazione snapshot viene utilizzata nel modo seguente:
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
Per ulteriori informazioni sulle transazioni snapshot, vedere Utilizzo di livelli di isolamento basati sul controllo delle versioni delle righe.
Alternative all'utilizzo dell'isolamento dello snapshot
Oltre all'isolamento dello snapshot, è possibile utilizzare metodi alternativi che tuttavia richiedono l'esecuzione di un numero maggiore di operazioni per garantire che tutti i requisiti relativi all'applicazione siano soddisfatti. Per garantire che il valore di last_synchronization_version sia valido e che i dati non vengano rimossi dal processo di pulizia prima dell'acquisizione delle modifiche, effettuare le seguenti operazioni:
Verificare il valore di last_synchronization_version dopo le chiamate a CHANGETABLE().
Verificare il valore di last_synchronization_version come parte di ciascuna query per ottenere modifiche tramite CHANGETABLE().
È possibile che vengano eseguite modifiche dopo che è stata ottenuta la versione sincronizzata per l'enumerazione successiva. Questa situazione può essere gestita in due modi diversi. L'approccio utilizzato dipende dall'applicazione e dalla modalità di gestione degli effetti collaterali associati a ciascun approccio:
Ignorare le modifiche con versione superiore rispetto alla nuova versione sincronizzata.
In base a questo approccio, verrà ignorata ogni riga nuova o aggiornata creata o aggiornata prima delle nuove versioni sincronizzate, ma aggiornata in seguito. Se è presente una nuova riga, potrebbe verificarsi un problema di integrità referenziale se in un'altra tabella era stata creata una riga che faceva riferimento alla riga ignorata. Se è presente una riga aggiornata, tale riga verrà ignorata e non verrà sincronizzata fino alla volta successiva.
Includere tutte le modifiche, anche quelle con versione superiore rispetto alla nuova versione sincronizzata.
Le righe con versione superiore alla nuova versione sincronizzata verranno ottenute anche alla sincronizzazione successiva. Questa situazione deve essere prevista e gestita dall'applicazione.
Oltre ai due approcci precedenti, è possibile definirne altri che utilizzino entrambe le opzioni, in base all'operazione. Potrebbe ad esempio essere necessario utilizzare un'applicazione per cui è preferibile ignorare le modifiche più recenti rispetto alla versione sincronizzata successiva in cui la riga è stata creata o eliminata, ma per cui gli aggiornamenti non vengono ignorati.
[!NOTA]
Poiché la scelta dell'approccio più efficiente per l'applicazione quando si utilizza il rilevamento delle modifiche o qualsiasi meccanismo di rilevamento personalizzato richiede l'esecuzione di un'analisi approfondita, l'utilizzo dell'isolamento dello snapshot è notevolmente più semplice.