• Articolo

Livelli di isolamento nel motore di database

Le transazioni specificano un livello di isolamento che definisce il grado di isolamento di una transazione dalle modifiche alle risorse o ai dati apportate da altre transazioni. I livelli di isolamento sono descritti in termini di effetti secondari consentiti sulla concorrenza, ad esempio letture dirty o fantasma.

I livelli di isolamento delle transazioni controllano gli elementi seguenti:

  • Se i blocchi vengono acquisiti alla lettura dei dati e quali tipi di blocchi vengono richiesti.

  • La durata dei blocchi di lettura.

  • Se un'operazione di lettura che fa riferimento a righe modificate da un'altra transazione:

    • Si blocca fino al rilascio del blocco esclusivo sulla riga.

    • Recupera la versione di cui è stato eseguito il commit della riga esistente al momento dell'avvio dell'istruzione o della transazione.

    • Legge la modifica dei dati di cui non è stato eseguito il commit.

La scelta di un livello di isolamento delle transazioni non ha effetto sui blocchi acquisiti per proteggere le modifiche dei dati. Una transazione ottiene sempre un blocco esclusivo su qualsiasi dato da essa modificato, che mantiene fino al suo completamento, indipendentemente dal livello di isolamento impostato per la transazione. Per le operazioni di lettura, i livelli di isolamento delle transazioni definiscono essenzialmente il livello di protezione dagli effetti delle modifiche apportate da altre transazioni.

Un livello di isolamento inferiore aumenta la possibilità per un maggior numero di utenti di accedere ai dati contemporaneamente, ma anche la quantità di effetti di concorrenza (ad esempio letture dirty o perdita di aggiornamenti) potenzialmente verificabili. Per contro, un livello di isolamento elevato riduce i tipi di effetti di concorrenza verificabili, ma richiede più risorse di sistema e aumenta le probabilità che una transazione venga bloccata da un'altra. La scelta del livello di isolamento corretto dipende dal giusto equilibrio tra requisiti relativi all'integrità dei dati per l'applicazione e overhead di ogni livello di isolamento. Il livello di isolamento più elevato, Serializable, garantisce che una transazione recuperi esattamente gli stessi dati a ogni ripetizione di un'operazione di lettura. Tuttavia questo avviene applicando un livello di blocco con probabilità effetti su altri utenti in sistemi multiutente. Il livello di isolamento minimo, Read uncommitted, è in grado di recuperare i dati modificati ma di cui non è stato eseguito il commit da altre transazioni. In tale livello possono verificarsi tutti gli effetti secondari della concorrenza, ma l'assenza di blocco in lettura e di controllo delle versioni riduce al minimo l'overhead.

Livelli di isolamento del motore di database

Lo standard ISO definisce i livelli di isolamento seguenti, tutti supportati da Motore di database di SQL Server:

  • Read Uncommitted, il livello di isolamento delle transazioni più basso sufficiente a impedire la lettura dei dati danneggiati fisicamente

  • Read Committed, livello predefinito di Motore di database

  • Repeatable Read

  • Serializable, il livello più alto corrispondente all'isolamento completo di una transazione dall'altra

Nota importanteImportante

È possibile che le operazioni e le transazioni DDL in tabelle replicate abbiano esito negativo quando è richiesto il livello di isolamento serializable. Ciò è dovuto al fatto che le query di replica utilizzano hint che possono essere incompatibili con il livello di isolamento serializable.

SQL Server supporta inoltre due livelli di isolamento delle transazioni che utilizzano il controllo delle versioni delle righe. il primo è una nuova implementazione dell'isolamento Read committed, l'altro è un nuovo livello di isolamento delle transazioni, Snapshot.

  • Quando l'opzione di database READ_COMMITTED_SNAPSHOT è impostata su ON, l'isolamento Read committed utilizza il controllo delle versioni delle righe per assicurare consistenza in lettura a livello di istruzioni. Le operazioni di lettura richiedono solo i blocchi di livello di tabella SCH-S e nessun blocco di pagina o di riga. Quando l'opzione di database READ_COMMITTED_SNAPSHOT è impostata su OFF, ovvero sull'impostazione predefinita, l'isolamento Read committed funziona in modo analogo a quanto avviene nelle versioni precedenti di SQL Server. Entrambe le implementazioni sono conformi alla definizione ANSI di isolamento di cui è stato eseguito il commit in lettura.

  • Il livello di isolamento dello snapshot utilizza il controllo delle versioni delle righe per assicurare consistenza in lettura a livello di transazioni. Le operazioni di lettura non acquisiscono blocchi di pagina o di riga, ma solo blocchi della tabella SCH-S. Quando viene eseguita la lettura delle righe modificate da un'altra transazione, esse recuperano la versione della riga esistente all'avvio della transazione. È possibile utilizzare l'isolamento snapshot in un database solo quando l'opzione di database ALLOW_SNAPSHOT_ISOLATION è impostata su ON. Per impostazione predefinita, l'opzione è impostata su OFF per i database utente.

Nota

SQL Server non supporta il controllo delle versioni dei metadati. Per questo motivo, esistono restrizioni sulle operazioni DDL che possono eseguite in una transazione esplicita che viene eseguita con l'isolamento dello snapshot. Le istruzioni DDL seguenti non sono permesse con l'isolamento dello snapshot dopo un'istruzione BEGIN TRANSACTION: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME o qualsiasi istruzione DDL di Common Language Runtime (CLR). Queste istruzioni sono permesse quando viene utilizzato l'isolamento dello snapshot all'interno di transazioni implicite. Per definizione, una transazione implicita è una sola istruzione che rende possibile l'applicazione della semantica dell'isolamento dello snapshot, anche con le istruzioni DDL. Le violazioni di questo principio possono provocare l'errore 3961: "Transazione di isolamento dello snapshot non riuscita nel database '%. * ls' perché l'oggetto a cui accede l'istruzione è stato modificato da un'istruzione DDL in un'altra transazione simultanea fin dall'inizio di questa transazione. Ciò non è consentito perché non viene controllata la versione dei metadati. Un aggiornamento simultaneo ai metadati può provocare un'inconsistenza se si utilizza anche l'isolamento dello snapshot."

Nella tabella seguente vengono illustrati gli effetti secondari della concorrenza attivati dai diversi livelli di isolamento.

Livello di isolamento

Lettura dirty

Nonrepeatable read

Lettura fantasma

Read uncommitted

Sì

Sì

Sì

Read committed

No

Sì

Sì

Repeatable read

No

No

Sì

Snapshot

No

No

No

Serializable

No

No

No

Per ulteriori informazioni sui tipi specifici di blocco o di controllo delle versioni delle righe controllati da ogni livello di isolamento delle transazioni, vedere SET TRANSACTION ISOLATION LEVEL (Transact-SQL).