Narcis Miclaus

8 min di lettura

SQL Server, dalle fondamenta Lezione 19 / 40

Livelli di isolamento smitizzati

READ COMMITTED, SNAPSHOT, SERIALIZABLE, e le quattro anomalie che prevengono. Perché il default di SQL Server può morderti, e perché RCSI è l'impostazione che la maggior parte degli shop EU dovrebbe attivare.

Nella lezione scorsa abbiamo coperto cosa sono le transazioni. Oggi copriamo la I in ACID: l’isolamento. Cosa succede quando due transazioni stanno avvenendo contemporaneamente e toccano qualcuno degli stessi dati?

La risposta breve: dipende da quale livello di isolamento sta usando il tuo database. E il livello di isolamento di default di SQL Server (READ COMMITTED) non fa quello che la maggior parte degli sviluppatori nuovi si aspetta. C’è un default migliore (READ COMMITTED SNAPSHOT) che viene fornito disattivato. Attivarlo è una delle singole decisioni a più alto impatto che un DBA possa prendere per un workload OLTP moderno.

Le quattro anomalie classiche

Prima di arrivare ai livelli, vediamo cosa può andare storto in SQL concorrente.

Lettura sporca (dirty read)

La transazione A scrive una riga. Prima che A committi, la transazione B legge quella riga. A fa rollback. B ora ha dei dati che ufficialmente non sono mai esistiti.

Scenario Runehold: il magazzino inizia a decrementare lo stock. La dashboard ops legge lo stock in quel momento. Il decremento fallisce e fa rollback. La dashboard riporta il livello di stock “nuovo”, che non è mai stato reale.

Lettura non ripetibile

La transazione A legge una riga. La transazione B aggiorna quella stessa riga e committa. La transazione A rilegge la riga, vede dati diversi.

Scenario Runehold: l’agente del customer service apre un record cliente, inizia a leggere i dettagli. Un’altra sessione processa l’aggiornamento dei dati bancari del cliente. L’agente rilegge, vede dati diversi a metà conversazione.

Lettura fantasma

La transazione A esegue una query. La transazione B inserisce una nuova riga che corrisponde alla query di A. A rilancia la query, vede una nuova riga (“fantasma”) che prima non c’era.

Scenario Runehold: finance esegue un report di “tutti gli ordini piazzati oggi.” Da qualche parte durante la generazione del report, atterra un nuovo ordine. Finance rilancia una sub-query, i totali non corrispondono alla prima query.

Lost update

La transazione A legge X, calcola X’, e sta per scrivere X’. La transazione B legge X, calcola X”, scrive X”. A scrive X’, sovrascrivendo il cambiamento di B. L’update di B è perso.

Scenario Runehold: due agenti del customer service aprono lo stesso profilo cliente. Entrambi modificano. Entrambi salvano. I cambiamenti di uno svaniscono.

I cinque livelli di isolamento

SQL Server offre cinque livelli di isolamento con nome. Ognuno previene alcune o tutte le anomalie, al costo di concorrenza o memoria.

READ UNCOMMITTED (anche NOLOCK)

Legge dati non committati. Permette le letture sporche. Per lo più da non usare mai nel codice nuovo.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- o equivalentemente, per query:
SELECT * FROM Sales.Orders WITH (NOLOCK);

Vedrai WITH (NOLOCK) ovunque nel codice SQL Server vecchio. La gente pensa che renda le query più veloci. Lo fa — al costo di leggere potenzialmente:

  • Dati che stanno per essere rolled back (sbagliato).
  • Righe che si stanno spostando per via di page split (contate due volte, o saltate).
  • Dati inconsistenti a metà transazione (due righe di un set correlato sono in stati diversi).

Per query di monitoraggio dove dati approssimativi vanno bene, NOLOCK è accettabile. Per qualsiasi altra cosa, è un bug pronto a esplodere.

READ COMMITTED

Il default di SQL Server. Previene le letture sporche. Permette letture non ripetibili e fantasmi.

Sotto READ COMMITTED, i lettori si bloccano sugli scrittori e gli scrittori si bloccano sui lettori. Una SELECT aspetta se un’altra sessione sta aggiornando la riga; un update aspetta se un’altra sessione sta leggendo.

È la fonte del classico problema di performance “la mia dashboard è lenta perché finance sta facendo il fine mese”. Le letture sono bloccate dalle scritture e viceversa.

READ COMMITTED SNAPSHOT ISOLATION (RCSI)

È l’impostazione che vuoi per la maggior parte dei workload OLTP. È READ COMMITTED, ma i lettori non bloccano gli scrittori e gli scrittori non bloccano i lettori — invece, i lettori ottengono uno snapshot consistente point-in-time dei dati al momento in cui la loro istruzione è iniziata.

Si attiva a livello di database:

ALTER DATABASE Runehold SET READ_COMMITTED_SNAPSHOT ON;

Una volta abilitato, ogni query READ COMMITTED (che è il default) diventa una snapshot read. Nessun cambio di comportamento nel tuo codice; miglioramento massivo in concorrenza. È la singola migliore impostazione che la maggior parte degli shop di e-commerce EU attiva e dimentica.

Costo: SQL Server usa tempdb per memorizzare versioni precedenti delle righe per gli snapshot in volo. tempdb cresce. Pianificalo (lezione 34).

SNAPSHOT ISOLATION

Un passo oltre RCSI: l’intera transazione vede uno snapshot consistente, non solo ogni istruzione. Previene letture sporche, letture non ripetibili e fantasmi. Permette aggiornamenti concorrenti che non confliggono.

ALTER DATABASE Runehold SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;
    SELECT ...;
    SELECT ...;      -- entrambe le SELECT vedono lo stesso snapshot al BEGIN TRAN
COMMIT;

Utile per i report che devono essere internamente consistenti: “al momento in cui ho iniziato, qual era il fatturato totale e il conteggio clienti?”

Tranello: se due transazioni SNAPSHOT provano a modificare la stessa riga, la seconda fallisce con un errore di update conflict. La tua app deve gestirlo (di solito: retry).

REPEATABLE READ e SERIALIZABLE

Livelli di isolamento più vecchi basati su lock che prevengono più anomalie al costo di tenere i lock più a lungo.

  • REPEATABLE READ: previene letture sporche e non ripetibili. Permette i fantasmi.
  • SERIALIZABLE: previene tutte e quattro le anomalie. Acquisisce range lock, blocca pesantemente.

Raramente usati nel codice nuovo. Se hai bisogno di garanzie più forti, SNAPSHOT è di solito la scelta migliore — dà sicurezza simile con meno bloccaggio.

Quando usare cosa

Regole pratiche veloci:

  • Default per app OLTP: attiva RCSI. Lascia le query al default READ COMMITTED. Fatto.
  • Report che devono essere internamente consistenti: SNAPSHOT ISOLATION.
  • Aggiornamenti di inventario, scritture finanziarie: locking esplicito con SERIALIZABLE o lock a livello applicativo (più su questo nella lezione 20).
  • Query diagnostiche ad-hoc che non devono essere perfettamente accurate: NOLOCK è una scorciatoia accettabile.

Il database di prod di Runehold ha RCSI attivo. La procedura “month-end closing” di finance gira sotto SNAPSHOT in modo da vedere una vista stabile. Gli aggiornamenti di inventario caldi usano concorrenza ottimistica (colonna RowVersion). Tutto il resto usa il default.

Il pattern RowVersion

Molti conflitti di update possono essere gestiti a livello applicativo con la concorrenza ottimistica tramite una colonna ROWVERSION (l’abbiamo menzionata nella lezione 5).

CREATE TABLE Sales.Customer (
    CustomerId INT PRIMARY KEY,
    Name       NVARCHAR(200),
    ...,
    Version    ROWVERSION        -- si auto-aggiorna a ogni cambiamento
);

L’applicazione legge la riga, inclusa Version. Quando aggiorna, include la Version che ha visto:

UPDATE Sales.Customer
SET Name = @name, Email = @email
WHERE CustomerId = @id AND Version = @versionItSawAtRead;

IF @@ROWCOUNT = 0
    -- Qualcun altro ha cambiato la riga tra read e update.
    -- Solleva un errore di conflitto; l'app riprova con dati freschi.
    THROW 50003, 'Optimistic concurrency conflict', 1;

Nessun lock tenuto, nessuna attesa. I conflitti vengono rilevati al commit e gestiti esplicitamente. È così che la maggior parte delle web app moderne gestisce “due utenti hanno modificato lo stesso record.”

Deadlock: una rapida anteprima

Se due transazioni hanno bisogno degli stessi lock in ordine opposto, possono andare in deadlock. SQL Server rileva i deadlock ogni 5 secondi e uccide la più economica delle due con errore 1205.

Transazione 1:                    Transazione 2:
  BEGIN TRAN;                       BEGIN TRAN;
  UPDATE A;                         UPDATE B;
  ... vuole B ...                   ... vuole A ...
  [si blocca, aspetta 2]            [si blocca, aspetta 1]
  [UCCISA dal deadlock detector]    [COMMITS]

La lezione completa su lock, bloccaggio e deadlock è la prossima (lezione 20). Versione corta: acquisisci sempre i lock in ordine consistente, mantieni le transazioni corte, e costruisci la logica di retry deadlock nell’applicazione (riprova la transazione uccisa 2-3 volte prima di arrenderti).

Leggere il tuo livello di isolamento corrente

SELECT CASE transaction_isolation_level
           WHEN 0 THEN 'Unspecified'
           WHEN 1 THEN 'ReadUncommitted'
           WHEN 2 THEN 'ReadCommitted'
           WHEN 3 THEN 'RepeatableRead'
           WHEN 4 THEN 'Serializable'
           WHEN 5 THEN 'Snapshot'
       END AS isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Utile durante il debug. Ogni client (SSMS, app, ORM) può impostare un default diverso.

Esegui questo sulla tua macchina (servono due sessioni)

-- Apri due finestre di query; le chiameremo Sessione A e Sessione B.

-- In entrambe: USE Runehold;

-- ========== CON READ COMMITTED (default, senza RCSI) ==========

-- Sessione A:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'UPDATED IN A' WHERE CustomerId = 1;
-- Non committare ancora.

-- Sessione B:
SELECT Name FROM Sales.Customer WHERE CustomerId = 1;
-- Si blocca, perché A sta tenendo un lock.

-- Sessione A:
COMMIT;   -- ora B si sblocca e vede il nuovo valore

-- ========== CON RCSI (raccomandato) ==========
-- Attivalo (fuori da qualsiasi transazione; ti serve accesso esclusivo brevemente)
-- ALTER DATABASE Runehold SET READ_COMMITTED_SNAPSHOT ON;

-- Ora rilancia lo stesso scenario:
-- Sessione A:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'UPDATED IN A' WHERE CustomerId = 1;

-- Sessione B:
SELECT Name FROM Sales.Customer WHERE CustomerId = 1;
-- Restituisce il valore PRE-UPDATE istantaneamente. Nessun bloccaggio.

-- Sessione A:
ROLLBACK;
-- Nessun danno fatto.

-- ========== Isolamento SNAPSHOT per report stabili ==========
-- ALTER DATABASE Runehold SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
    SELECT COUNT(*) AS initial_count FROM Sales.Orders;
    -- Immagina che qualcuno inserisca 1000 ordini qui.
    WAITFOR DELAY '00:00:02';
    SELECT COUNT(*) AS same_count FROM Sales.Orders;
COMMIT;
-- Entrambe le SELECT restituiscono lo stesso conteggio: lo snapshot al BEGIN TRAN.

RCSI è la singola attivazione più impattante. Esegui la demo prima/dopo; guarda B bloccarsi e poi non bloccarsi. Quel momento è quando la maggior parte dei DBA decide di abilitarlo per sempre.

Prossima lezione: lock, bloccaggio e deadlock — il triangolo quotidiano di qualsiasi sistema OLTP affollato. Guarderemo le DMV per vedere cosa è bloccato, come trovare chi sta bloccando chi, e come leggere un deadlock graph senza piangere.

Cerca