SQL Server offre diversi livelli di isolamento per gestire il modo in cui le transazioni interagiscono tra loro. Questi livelli di isolamento includono:
- Read Uncommitted: Questo è il livello di isolamento più permissivo in SQL Server in quanto consente alle transazioni di leggere i dati modificati da altre transazioni che non sono ancora state completate (committed), portando a ciò che è noto come “dirty reads” (“letture sporche”).
- Read Committed: Questo è il livello di isolamento predefinito in SQL Server. Le transazioni non possono leggere i dati che sono stati modificati da altre transazioni che non sono ancora state completate (committed).
- Repeatable Read: Questo è un livello di isolamento maggiore che, oltre a garantire le condizioni del livello Read Committed, garantisce che tutti i dati già letti durante una transazione non possono essere modificati da altre transazioni, appunto per permettere letture ripetute e uguali nella stessa transazione.
- Serializable: Questo è il livello più alto di isolamento, in cui le transazioni sono completamente isolate le une dalle altre. 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 in corso.
- Snapshot: Il livello di isolamento Snapshot utilizza il controllo delle versioni delle righe per fornire coerenza di lettura a livello di transazione. 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.
Un livello di isolamento inferiore (quindi più permissivo) aumenta la capacità di accedere ai dati contemporaneamente da parte di più utenti, ma aumenta anche il numero di effetti di concorrenza, (es. dirty reads). 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.
Ora, concentriamoci su un particolare livello di isolamento: Read Committed Snapshot Isolation (RCSI).
Read Committed Snapshot Isolation (RCSI)
Ora apriamo una nuova sessione (spid70) ed eseguiamo questo comando di UPDATE. Come vedete volutamente non è stato dato il COMMIT TRANSACTION, lasciando aperta la transazione.
In caso di livello di isolamento standard Read Committed se proviamo a ripetere il comando di SELECT sulla tabella, il nostro comando rimarrà in attesa fintanto che non verrà eseguito il comando COMMIT TRANSACTION per chiudere la transazione di UPDATE aperto nella sessione spid 68.
Nello specifico il comando di SELECT in attesa presenterà questo tipo di lock (LCK_M_S). La sessione 68 rimane in attesa della chiusura (commit) della sessione 70 prima di permettere la lettura del dato in tabella.
Ora proviamo ad attivare RCSI sul database [RCSI_TEST] e vediamo il comportamento delle stesse operazioni di prima
E proviamo a lanciare il SELECT per vedere il contenuto della tabella:
Il comando questa volta risponde subito fornendoci ancora il dato precedente all’UPDATE, in quanto, su livello di isolamento RCSI, viene utilizzato il versioning e fornito l’ultimo dato consistente.
Eseguiamo il COMMIT del comando UPDATE sulla sessione spid 70 e rieseguiamo il SELECT di cui sopra e otteniamo il valore del dato post UPDATE:
Vantaggi di RCSI
- Migliore concorrenza: RCSI può ridurre significativamente i blocchi e le attese, permettendo a più transazioni di eseguire in parallelo.
- Consistenza delle letture: Con RCSI, ogni transazione vede una “istantanea” coerente dei dati per tutta la durata della transazione.
Svantaggi di RCSI
- Overhead di storage: RCSI utilizza il versioning delle righe, che può aumentare l’overhead di storage poiché SQL Server deve mantenere le versioni multiple delle righe. Per gestire tale meccanismo di versioning viene utilizzato in modo intensivo il database di sistema tempdb, di conseguenza è importante un’ottima configurazione di tale database e un monitoraggio dei nuovi spazi di occupazione disco dove risiede il tempdb.
- Possibili conflitti di aggiornamento: Se due transazioni leggono la stessa riga e poi tentano di aggiornarla, la seconda transazione riceverà un errore di conflitto di aggiornamento. Si verifica un errore di conflitto di aggiornamento, noto anche come errore di concorrenza ottimistica. Questo perché RCSI non blocca le letture, ma controlla i conflitti al momento della scrittura. Ecco un esempio di come potrebbe apparire un errore di conflitto di aggiornamento:
Msg 3960, Level 16, State 2, Line 1
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.rcsi_tabble' directly or indirectly in database 'RCSI_TEST' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
Conclusioni
RCSI può essere un potente strumento per migliorare le prestazioni delle applicazioni, riducendo i blocchi e le attese. Tuttavia, come con qualsiasi caratteristica, è importante capire come funziona e quali sono i possibili trade-off. L’applicazione che utilizzerà il database deve essere in grado di gestire RCSI. Solitamente se l’applicazione è in grado di supportare un database Oracle o PostgreSQL (che utilizzano un approccio ottimistico al locking) tipicamente è pronta anche per gestire RCSI. Testare attentamente le prestazioni e i comportamenti in un ambiente di test prima di abilitare RCSI in produzione è sempre una buona pratica.
di Matteo Dal Bianco, pubblicato il 28 febbraio 2024
