Isolation levels on SSMS

data

21/6/2021

data progetto

autore

Giovanni Bertoglio

cliente

partnership

url

No items found.

Isolation levels on SQL Server Management Studio

“La palla è mia!”
“Non è vero, l’ho vista prima io!”
“Sì, ma io corro più veloce e l’ho presa per primo!”

Se potessimo parlare la lingua del database, probabilmente sentiremmo in continuazione discussioni come questa. Nel mondo ideale gli utenti accedono ai nostri dati in buon ordine, uno alla volta, e poi chiudono la porta quando escono, ma nel mondo reale può capitare che gli stessi dati vengano interrogati o addirittura modificati da persone differenti nel medesimo momento.

Cosa succede allora in questi casi? Chi ha la precedenza? Quello che ha acceduto per primo al dato? O quello che l’ha modificato per primo? E cosa viene visualizzato da una query che interroga un dato modificato da un altro? Si vede sempre lo stato più aggiornato?

L’intento di questo articolo è di fornire qualche risposta a simili domande.

Le transazioni

Per prima cosa dobbiamo prendere confidenza con un’entità fondamentale per il discorso che stiamo per affrontare, vale a dire la transazione. Con transazione si intende una serie di operazioni che vengono raggruppate tra loro: in questo modo, se anche solo una di esse va in errore, vengono annullate tutte quante così da lasciare i dati in uno stato consistente, cioè com’erano prima che cominciasse la prima delle operazioni della transazione.

Aiutiamoci subito con un esempio pratico: Emma deve effettuare un bonifico a Marta. L’operazione di trasferimento di denaro da un conto all’altro si articola in due semplici passaggi, cioè

prelevare la somma dal conto di Emma
versarla sul conto di Marta

Non serve un gigantesco sforzo di immaginazione per capire che queste due operazioni non possono essere disgiunte una dall’altra; o vanno a buon fine entrambe (e allora viene eseguita l’operazione di commit) o devono fallire entrambe (e allora viene eseguito un rollback, che riporta tutto a com’era prima). Supponiamo infatti che per un problema tecnico non si riesca a versare il denaro sul conto di Marta: se le due operazioni non fossero legate (quindi raggruppate in una transazione), potremmo avere la sgradevolissima situazione in cui, al verificarsi di un errore tra l’operazione a. e l’operazione b., i soldi non sarebbero né sul conto di Emma né sul conto di Marta perché prelevati da un conto ma non ancora versati sull’altro.

Le proprietà ACID

Perché una transazione si possa definire tale deve rispettare le proprietà ACID, ovvero

Atomicità
Consistenza
Isolamento
Durabilità

L’atomicità è la proprietà che abbiamo appena visto, ovvero l’impossibilità di un’esecuzione parziale delle operazioni raggruppate nella transazione. È nota anche come la regola del “o tutto o niente”.

Consistenza significa che una transazione deve lasciare i dati in uno stato coerente, quindi rispettando, per esempio, i vincoli di integrità.

L’isolamento consiste nel separare una transazione da quello che sta succedendo con le altre transazioni eseguite parallelamente, in modo che una transazione fallita non impatti sulle altre transazioni in esecuzione(e questo sarà il punto principale sviluppato in questo articolo).

Durabilità (o persistenza) significa non perdere i dati una volta che sono stati scritti, o meglio, non perderli dal momento in cui la base dati si impegna a scriverli.

Scegliere un isolation level

Un livello di isolamento inferiore (quindi più permissivo) aumentala capacità di accedere ai dati contemporaneamente da parte di più utenti, ma aumenta il numero di effetti di concorrenza, come la dirty read che vedremo in dettaglio più avanti. Al contrario, un livello di isolamento più elevato riduce i tipi di effetti di concorrenza che gli utenti potrebbero riscontrare, ma richiede più risorse di sistema e aumenta le possibilità che una transazione ne blocchi un'altra. Il livello di isolamento più alto infatti garantisce che una transazione recuperi esattamente gli stessi dati ogni volta che viene ripetuta un'operazione di lettura, ma lo fa eseguendo dei blocchi (lock)che potrebbero avere un impatto su altri utenti nei sistemi multiutente.

Effetti di concorrenza

In questa sezione vedremo i tre principali tipi di scenari che possono presentarsi in un livello di isolamento non elevato quando due transazioni sono in concorrenza tra loro.

Dirty read

Questa eventualità può verificarsi se la transazione 1 ha la possibilità di visualizzare un dato modificato dalla transazione 2 quando quest’ultima non è stata ancora confermata (ovvero non è stato eseguito un commit). Se qualcosa nella transazione 2 va storto, la transazione 1 avrà avuto accesso a un dato che in teoria non è mai esistito. Avendo la possibilità di guardaredati non ancora validati dal marchio di una commit, in una dirty read la lettura non viene fatta dal disco o dalla cache, ma direttamente dal transaction log.

Es. Supponiamo che il redattore di un giornale possa leggere l’articolo di un suo giornalista mentre questi lo sta scrivendo. Il giornalista aggiunge una frase, il redattore stampa l’articolo per guardarselo a casa, il giornalista ci ripensa e toglie la frase di prima. Il redattore avrà quindi stampato l’articolo con una frase che non verrà mai pubblicata.

Non-repeatable read

In questo caso la transazione 1 legge un dato, la transazione 2 lo modifica e viene chiusa, la transazione 1 (che è sempre rimasta aperta), riesegue la stessa query ottenendo un risultato differente rispetto a quello ottenuto in prima istanza.

Es. Riprendendo l’esempio di prima, il redattore (sempre con la capacità di vedere in tempo reale le modifiche ai pezzi che i giornalisti stanno scrivendo), deve stampare due copie di un articolo da distribuire ai suoi due assistenti. Il redattore stampa la prima copia, il giornalista aggiunge una frase, il redattore stampa la seconda copia che invece contiene la frase appena aggiunta. I due assistenti si troveranno così per le mani due versioni diverse dello stesso articolo.

Phantom read

Lo scenario qui è quasi un caso particolare della non-repeatable read, ovvero: la transazione 1 esegue una query che ritorna un set di dati, la transazione 2 inserisce dei nuovi record, la transazione 1 (che anche in questo caso non è mai stata chiusa) riesegue la query di prima trovando dei dati in più rispetto all’interrogazione precedente.

Es. Il redattore vuol fare avere ai suoi due assistenti una copia di tutti gli articoli pubblicati da uno dei suoi giornalisti. Il redattore stampa gli articoli per il primo assistente, nel mentre il giornalista in questione pubblica un nuovo articolo; quando il redattore stampala seconda copia del plico per l’altro assistente, questo conterrà un articolo in più. Di nuovo i due assistenti avranno in mano dei dati discordanti. Il caso è molto simile a prima ma la vera differenza sta nel fatto che mentre la non-repeatable read riguardava l’aggiornamento di un articolo (ovvero l’update di un record) ma entrambi gli assistenti avevano in mano lo stesso numero totale di articoli, la phantom read implica una differenza nel numero di articoli totali in possesso dei due assistenti (ovvero del numero totale di record).

In breve

Parlando di isolamento delle transazioni, questi tre casi sono rappresentati in una specie di ordine gerarchico, dal più sporco al meno sporco. Un livello di isolamento in cui si può verificare uno dei tre casi implica a cascata che si possano verificare anche quelli “meno sporchi”, per ilprincipio secondo cui se rapini una banca probabilmente non ti fai molti scrupoli a parcheggiare in divieto di sosta. Un livello di isolamento che consente dirty read consentirà pertanto anche phantom read, ma non vale il viceversa.

I livelli di isolamento

Non è affatto detto che vedere sempre un dato al massimo livello di aggiornamento sia un fatto negativo, ma ci sono dei casi in cui la priorità è avere un set di dati stabili, indipendentemente da quello che stanno facendo le transazioni concorrenti. Per questo motivo su SSMS abbiamo la possibilità di settare cinque diversi livelli di isolamento a seconda delle nostre esigenze, ciascuno dei quali consente o inibisce il verificarsi degli scenari descritti prima.

Dirty Read Non-repeatable Read Phantom Read Isolation level Read uncommitted Permesso Permesso Permesso Read committed Non permesso Permesso Permesso Repeatable read Non permesso Non permesso Permesso Serializable Non permesso Non permesso Non permesso Snapshot Non permesso Non permesso Non permesso

Su SSMS il comando per settare il livello di isolamento è il seguente

SET TRANSACTION ISOLATION LEVEL <TuoLivello>;

Read uncommitted

Come si può notare dalla tabella, questo livello di isolamento è il più permissivo di tutti, dato che consente addirittura la dirtyread, ovvero la lettura di dati modificati da un’altra transazione attualmente in corso. Guardiamo con un esempio quello che succederebbe con due transazioni in concorrenza tra loro.

All’inizio sul conto di Emma ci sono 3000 €. La banca apre prima transazione, la seconda transazione è aperta da Emma che vuol vedere lo stato del suo conto. A questo punto la banca preleva i soldi dal conto di Emma per trasferirli su un altro conto. Con il livello di isolamento Read Uncommitted Emma è in grado di sapere in tempo reale quello che succede, ragion per cui vedrebbe il suo conto scendere a 2000 € anche se l’operazione non è stata ancora confermata. Supponiamo che qualcosa nella transazione vada storto e l’operazione sia annullata. A questo punto Emma vedrebbe i soldi sul suo conto tornare magicamente a 3000 €; veder fluttuare il proprio saldo senza apparente motivo è un’eventualità che molto probabilmente non piacerebbe a nessun utente.

Read committed

Questo è il livello predefinito del Motore di database di SQL Server: in tale livello non è possibile per una transazione leggere i dati modificati da un’altra transazione ma non ancora committati, scongiurando in questo modo il pericolo di dirty read. La conseguenza di questa strategia è che l’operazione di lettura di un dato modificato da un’altra transazione rimarrà in attesa fino a quando la prima transazione non sarà stata committata.

Tuttavia a una transazione è consentito di visualizzare i dati precedentemente letti (non modificati) da un'altra transazione senza attendere il completamento della prima transazione. Questo, come vedremo, non ci salvaguarda dall’eventualità che possano ancora verificarsi dei casi di non-repeatable read o phantom read.

Qui siamo nello stesso caso di prima, ovvero la banca apre la transazione per prelevare i soldi dal conto di Emma, Emma apre la seconda transazione, ma dal momento in cui la prima transazione ha effettuato un’operazione di modifica sul suo conto, Emma non può più vedere quello che sta succedendo finché l’operazione concorrente non si conclude. In questo caso, come nel caso illustrato prima, per qualche motivo l’operazione fallisce, ma Emma non vedrà mai il suo conto in uno stato inconsistente. Il prezzo da pagare ovviamente consiste nel dover aspettare che l’operazione di modifica da parte della banca sia confermata da un commit o annullata da un rollback.

Se invece l’operazione va a buon fine abbiamo un caso lampante di non-repeatable read, dato che Emma vede il valore del proprio saldo cambiare all’interno della sua transazione dopo che la banca conferma l’operazione con un commit. È importante rendersi conto che questo è un caso diverso dalla dirty read del primo esempio, dato che alla fine della sua transazione, pur se diverso dall’inizio, Emma ha davanti agli occhi un dato reale e confermato.

Repeatable Read

Qui il motore di database di SQL Server mantiene i lock di lettura e scrittura sui dati selezionati fino alla fine della transazione. Pertanto basta che una transazione sia aperta in lettura su un dato per fare sì che questo dato non sia modificabile da altre transazioni, rendendo impossibile il verificarsi di non-repeatable read. I casi di phantom read sono però ancora possibili, come illustrato nell’esempio qui sotto: la stessa query infatti fornisce due set di dati con un numero diverso di record totali all’interno della stessa transazione.

La transazione 1, aperta dall’impiegato 1 della banca, richiede di visualizzare tutti i conti attualmente attivi, ottenendo come risultato che l’unica correntista è Emma (probabilmente non si tratta di una banca molto grande). Nello stesso momento però l’impiegato 2 apre un nuovo conto a nome di Marta e conferma con un commit. Se a questo punto l’impiegato 1 inoltra nuovamente la richiesta di vedere i conti attualmente attivi, leggerà un record in più rispetto a quanto letto a inizio transazione (phantom read).

Serializable

Questo è il livello più alto di isolamento, in cui le transazioni sono completamente isolate l'una dall'altra. Qui abbiamo che

Un dato non potrà essere letto se ci sono transazioni in corso che hanno modificato quel dato
Nessuna transazione potrà modificare un dato che sta venendo letto da una transazione in corso
Nessuna transazione potrà effettuare operazioni di insert che soddisfino le condizioni di select di un’altra transazione incorso

Il costo di un livello di isolamento così blindato consiste nell’avere l’impatto più elevato sulle performance rispetto a tutti gli altri livelli.

In questo esempio l’impiegato 1 (nella transazione 1)effettua esattamente come prima un’operazione di visualizzazione dei conti attivi. L’impiegato 2, che invece sta aprendo un nuovo conto a nome di Marta(nella transazione 2), deve aspettare che l’impiegato 1 chiuda la sua transazione per poter finalizzare la creazione del nuovo conto. A questo livello di isolamento vediamo che è sufficiente compiere una select per bloccare un’operazione di insert da parte di una transazione concorrente.

Snapshot

Il livello di isolamento snapshot utilizza il controllo delle versioni delle righe per fornire coerenza di lettura a livello di transazione. Se è impostato il livello snaphot, in caso di transazioni concorrenti verrà letta la versione consistente più recente da quando è iniziata la transazione, senza bloccare l’azione di lettura su un dato modificato da un’altra transazione (come invece avviene nel livello serializable). In comune con il livello serializable ha però il fatto di proteggerci dall’eventualità di dirty read, non-repeatable read e phantom read.

Qui Emma apre una transazione per sapere qual è il saldo del suo conto. Diversamente da prima la banca ha la possibilità di modificare tale saldo durante la transazione concorrente. Dopo l’aggiornamento da parte della banca (e conseguente commit), Emma continuerà a visualizzare la stessa cifra che vedeva all’inizio, prima che questa venisse modificata. La cifra aggiornata sarà disponibile alla lettura da parte di Emma solo dopo che anche lei avrà chiuso la sua transazione. L’inconveniente evidente del livello snapshot è che due utenti che stanno estraendo lo stesso dato nello stesso istante, potrebbero trovarsi di fronte a due valori differenti.

Conclusioni

Ma allora qual è il sistema migliore da adottare? La risposta è, come al solito: dipende.

Quello che dobbiamo tenere presente è sempre il bilancio costi / benefici, che si può esprimere essenzialmente in due modi dai nomi variopinti, ovvero la concorrenza pessimistica e la concorrenza ottimistica.

Concorrenza pessimistica

È la via più intransigente: nel momento in cui un utente compie un’operazione che porta ad un lock, gli altri utenti non possono compiere nessuna operazione che vada in conflitto con quel lock finché non viene rilasciato. Si chiama concorrenza pessimistica perché si basa sul principio che molti utenti potrebbero intervenire contemporaneamente sugli stessi dati, e quindi il costo di proteggere i dati con dei lock è inferiore rispetto ad effettuare il rollback delle transazioni in conflitto tra loro.

Concorrenza ottimistica

In questo caso gli utenti non bloccano i dati quando li leggono. Quando un utente aggiorna i dati, il sistema controlla se un altro utente ha modificato i dati dopo che sono stati letti. Se un altro utente ha aggiornato i dati, viene generato un errore. In genere, l'utente che riceve l'errore subisce il rollback della transazione e deve ricominciare. Questo tipo di concorrenza è chiamato ottimistico perché viene utilizzato principalmente in ambienti in cui vi è una bassa contesa per i dati e dove il costo del rollback occasionale di una transazione è inferiore al costo del blocco dei dati durante la lettura.