Scelta di livelli di isolamento basati sul controllo delle versioni delle righe
I livelli di isolamento basati sul controllo delle versioni delle righe migliorano le letture simultanee perché eliminano i blocchi per le operazioni di lettura. Con Microsoft SQL Server 2005, sono disponibili due livelli di isolamento delle transazioni che utilizzano il controllo delle versioni delle righe:
- Una nuova implementazione del livello di isolamento Read Committed che utilizza il controllo delle versioni delle righe quando l'opzione del database READ_COMMITTED_SNAPSHOT è impostata su ON.
- Il nuovo livello di isolamento dello snapshot, che è attivato quando l'opzione di database ALLOW_SNAPSHOT_ISOLATION è impostata su ON.
Per la maggior parte delle applicazioni, è consigliabile utilizzare l'isolamento Read Committed con il controllo delle versioni delle righe anziché l'isolamento dello snapshot e ciò per i motivi seguenti:
Utilizza una quantità di spazio di tempdb inferiore rispetto all'isolamento dello snapshot.
A differenza dell'isolamento dello snapshot, funziona con le transazioni distribuite.
Funziona con la maggior parte delle applicazioni, senza che sia necessaria alcuna modifica. Le applicazioni scritte utilizzando il livello di isolamento predefinito, Read Committed, possono essere ottimizzate in modo dinamico. Il comportamento dell'isolamento Read Committed, ovvero se utilizzare o meno il controllo delle versioni delle righe, è determinato dall'impostazione dell'opzione del database e tale impostazione può essere modificata senza influire sull'applicazione.
[!NOTA] Per le applicazioni che sono state progettate per l'utilizzo dei blocchi forniti dall'isolamento Read Committed, può risultare utile che gli sviluppatori modifichino l'applicazione in modo che sia in grado di utilizzare entrambe le modalità dell'isolamento Read Committed. In caso contrario, è importante notare che l'opzione del database READ_COMMITTED_SNAPSHOT rimane impostata su OFF.
L'isolamento dello snapshot può comportare conflitti di aggiornamento che non si verificano con l'isolamento Read Committed che utilizza il controllo delle versioni delle righe. Se una transazione eseguita con isolamento dello snapshot legge dati che vengono successivamente modificati da un'altra transazione, l'aggiornamento degli stessi dati da parte di una transazione snapshot provoca un conflitto di aggiornamento. La transazione verrà quindi terminata e ne verrà eseguito il rollback. Questo problema non si verifica con l'isolamento Read Committed che utilizza il controllo delle versioni delle righe.
Utilizzo dell'isolamento Read Committed con il controllo delle versioni delle righe
L'isolamento Read Committed con il controllo delle versioni delle righe assicura la consistenza in lettura a livello di istruzione. Mentre un'istruzione viene eseguita nell'ambito della transazione, viene creato un nuovo snapshot di dati che rimane consistente fino al termine dell'esecuzione dell'istruzione. Attivare l'isolamento Read Committed con il controllo delle versioni delle righe nei casi seguenti:
- Il blocco di lettura/scrittura si verifica nel momento in cui i vantaggi offerti dalla simultaneità compensano l'aumento dell'overhead determinato dalla creazione e dalla gestione delle versioni delle righe.
- In un'applicazione è necessario un livello di accuratezza assoluto per le aggregazioni con esecuzione prolungata o per le query con valori dei dati che devono essere consistenti nel momento in cui una query viene avviata.
Utilizzo dell'isolamento dello snapshot
L'isolamento dello snapshot assicura la consistenza in lettura a livello della transazione. All'avvio della transazione snapshot viene creato uno snapshot di dati che rimane consistente per la durata della transazione. Utilizzare l'isolamento dello snapshot nei casi seguenti:
- È consigliabile eseguire il controllo della concorrenza ottimistica.
- Le probabilità che sia necessario eseguire il rollback di una transazione a causa di un conflitto di aggiornamento sono basse.
- In un'applicazione è necessario generare report basati su query con esecuzione prolungata e con istruzioni multiple che devono essere caratterizzate dalla consistenza in un punto nel tempo. L'isolamento dello snapshot offre il vantaggio di poter eseguire letture ripetute (vedere Effetti della concorrenza) senza che sia necessario utilizzare blocchi condivisi. Lo snapshot del database offre una funzionalità simile, che tuttavia deve essere implementata manualmente. L'isolamento dello snapshot assicura automaticamente le informazioni più aggiornate del database per ogni transazione di isolamento dello snapshot.
Vantaggi dei livelli di isolamento basati sul controllo delle versioni delle righe
I livelli di isolamento che utilizzano il controllo delle versioni delle righe offrono i vantaggi seguenti:
- Le operazioni di lettura recuperano uno snapshot del database consistente.
- Le istruzioni SELECT non bloccano i dati durante un'operazione di lettura (i lettori non bloccano i writer e viceversa).
- Le istruzioni SELECT possono accedere all'ultimo valore della riga di cui è stato eseguito il commit, mentre le altre transazioni stanno eseguendo l'aggiornamento della riga senza venire bloccate.
- Il numero di deadlock viene ridotto.
- Il numero di blocchi necessari per una transazione viene ridotto e pertanto viene inoltre ridotto l'overhead del sistema necessario per la gestione dei blocchi.
- Si verifica un numero minore di escalation dei blocchi.
Costi dei livelli di isolamento basati sul controllo delle versioni delle righe
La scelta di utilizzare l'isolamento basato sul controllo della versione delle righe richiede che venga valutato il vantaggio determinato dalla riduzione al minimo dei blocchi e lo svantaggio determinato dall'aumento delle risorse necessarie per mantenere e gestire le versioni delle righe. L'utilizzo del controllo della versione delle righe per i livelli di isolamento dello snapshot e Read Committed comporta i costi seguenti:
È possibile che le prestazioni a livello di lettura siano inferiori se le versioni necessarie per le query diventano obsolete ed è necessario eseguire la scansione di sequenze di versioni lunghe.
Il controllo delle versioni delle righe comporta un maggiore utilizzo di risorse per la modifica dei dati perché le versioni delle righe vengono gestite in tempdb.
Se le opzioni del database READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION sono impostate su ON, le transazioni di aggiornamento e di eliminazione per un database specifico devono mantenere le versioni delle righe anche quando non sono presenti transazioni che utilizzano il livello di isolamento basato sul controllo della versione delle righe. La creazione di uno snapshot consistente di dati che utilizza il controllo della versione delle righe comporta l'utilizzo di risorse di sistema (CPU e memoria) e genera potenzialmente attività di I/O. Dato che le versioni dei record vengono archiviate in tempdb, maggiore è il numero di pagine di tempdb archiviabili in memoria per il controllo delle righe, migliori saranno le prestazioni e minore sarà il numero di I/O generati.
[!NOTA] L'inserimento di una riga in genere non comporta la creazione di una versione di riga. In alcuni casi, tuttavia, il comando INSERT genera una versione di riga. Ad esempio, se si inserisce una riga in una tabella con un indice univoco e la versione di riga eliminata in precedenza (record fantasma) non è stata troncata, il comando INSERT genererà una versione di riga.
In tempdb deve essere disponibile spazio sufficiente per l'archivio delle versioni. Nel caso di transazioni con esecuzione molto prolungata, tutte le versioni generate nel frattempo dalle transazioni di aggiornamento devono essere mantenute in tempdb. Se lo spazio in tempdb è insufficiente, le operazioni di aggiornamento non avranno esito negativo, ma è possibile che invece lo abbiano le operazioni di lettura che utilizzano il controllo della versione delle righe.
Le informazioni del controllo delle versioni delle righe comportano l'aggiunta di 14 byte alla riga del database.
È possibile che le prestazioni dell'aggiornamento siano inferiori a causa delle operazioni necessarie per la gestione delle versioni delle righe. Per i carichi di lavoro OLTP, ogni aggiornamento in genere comporta la modifica di un numero limitato di righe del database. In questi sistemi, è possibile che le prestazioni relative agli aggiornamenti di un database in cui le opzioni sono impostate su ON siano di poco inferiori a quelle di database nei quali entrambe le opzioni sono impostate su OFF. Il costo a livello di prestazioni per gli aggiornamenti con versione potrebbe essere più alto se durante le operazioni di aggiornamento vengono modificate grandi quantità di dati.
Per i lettori di dati esiste inoltre il costo aggiuntivo determinato dall'attraversamento dell'elenco di collegamenti delle versioni. Meno è recente lo snapshot e più lento sarà il processo necessario per accedervi in una transazione che prevede l'isolamento dello snapshot.
È possibile che per alcune transazioni di aggiornamento che utilizzano l'isolamento dello snapshot sia necessario eseguire il rollback a causa del rilevamento obbligatorio dei conflitti per le operazioni di aggiornamento. Le transazioni eseguite in modalità di isolamento Read Committed con il controllo della versione delle righe non generano conflitti di aggiornamento.
Per le transazioni che utilizzano il controllo delle versioni delle righe sono valide altre limitazioni. Per ulteriori informazioni, vedere Utilizzo di livelli di isolamento basati sul controllo delle versioni delle righe.
Sistemi che sfruttano i vantaggi dei livelli di isolamento basati sul controllo della versione delle righe
Tra gli scenari che sfruttano i livelli di isolamento basati sul controllo delle versioni delle righe sono inclusi i seguenti:
- Sistemi nei quali i report di sola lettura e le query ad hoc vengono eseguiti in parallelo con un'applicazione che esegue l'aggiornamento dei dati.
- Applicazioni che eseguono la migrazione a Microsoft Motore di database di SQL Server da altri sistemi di database relazionali che supportano livelli di isolamento simili.
- Sistemi nei quali la creazione di aggregazioni consistenti, ad esempio AVG, COUNT e SUM, o l'esecuzione di intersezioni degli indici o di join di indici richiede un livello di isolamento elevato, ad esempio Repeatable Read o Serializable).
- Sistemi con un numero elevato di deadlock determinati da contesa di lettura/scrittura.
Vedere anche
Concetti
Informazioni sui livelli di isolamento basati sul controllo delle versioni delle righe
Utilizzo di livelli di isolamento basati sul controllo delle versioni delle righe
Utilizzo delle risorse di controllo delle versioni delle righe
Attivazione dei livelli di isolamento basati sul controllo delle versioni delle righe
Livelli di isolamento nel motore di database