skip to content
TheZal.dev

Coerenza dei Dati con SQL Server Locking e Isolation Levels

/ 4 min read

sql-server, database, concorrenza, coerenza, locking, isolation-levels

Introduzione

In ambienti multiutente, la gestione della concorrenza e della coerenza dei dati è fondamentale per garantire l’affidabilità di un database SQL Server. Due concetti chiave in questo contesto sono il locking e i livelli di isolamento. Il locking impedisce modifiche concorrenti non sicure, mentre i livelli di isolamento definiscono il grado di visibilità dei dati tra le transazioni. Comprendere questi meccanismi è cruciale per ottimizzare le performance e prevenire problemi come deadlock, blocchi e anomalie nei dati.

Locking in SQL Server

SQL Server utilizza un sistema di locking per gestire l’accesso concorrente ai dati. I blocchi possono essere applicati a diversi livelli di granularità, tra cui la riga, la pagina (8 KB) e l’intera tabella. Per garantire l’integrità dei dati, SQL Server implementa diversi tipi di blocco:

  • Shared (S): Permette letture concorrenti, ma impedisce modifiche fino a quando il blocco è rilasciato.
  • Exclusive (X): Impedisce ad altre transazioni di leggere o modificare la risorsa bloccata.
  • Update (U): Previene deadlock nelle operazioni di aggiornamento.
  • Intent (IS, IX, SIX): Segnala l’intenzione di acquisire blocchi a livello inferiore.
  • Schema (Sch-M, Sch-S): Utilizzato per operazioni di modifica dello schema.

Un aspetto critico della gestione dei blocchi è l’escalation dei blocchi. Quando il numero di blocchi granulari acquisiti da una transazione diventa troppo elevato, SQL Server converte questi blocchi in un blocco più ampio per ridurre il carico di gestione. Tuttavia, questo può avere impatti negativi sulle performance se blocca l’accesso a una grande quantità di dati.

Livelli di Isolamento delle Transazioni

I livelli di isolamento determinano come le transazioni interagiscono tra loro e quale visibilità hanno sui dati modificati da altre transazioni concorrenti.

Read Uncommitted

Consente la lettura di dati non confermati (dirty reads), massimizzando la concorrenza a scapito della coerenza.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM Spells;

Livelli di Isolamento in SQL Server e Gestione dei Deadlock

Read Committed

Il livello predefinito in SQL Server previene le dirty reads ma permette non-repeatable reads.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM Spells;

Repeatable Read

Garantisce che i dati letti all’interno di una transazione non possano essere modificati da altre transazioni concorrenti.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM Spells WHERE WizardID = 1;
COMMIT;

Serializable

Impedisce qualsiasi modifica o inserimento di nuovi record che possano alterare il risultato di una query.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM Spells WHERE WizardID = 1;
INSERT INTO Spells VALUES (1001, 'Expecto Patronum');
COMMIT;

Snapshot

Utilizza il row versioning, fornendo a ogni transazione un’istantanea dei dati al momento del suo avvio.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM Spells;
COMMIT;

Deadlock e Strategie di Prevenzione

I deadlock si verificano quando due o più transazioni restano bloccate in un ciclo di attesa reciproca, impedendo l’avanzamento di entrambe. SQL Server rileva automaticamente i deadlock e termina una delle transazioni coinvolte.

Esempio di deadlock:

BEGIN TRANSACTION;
UPDATE Spells SET Power = 10 WHERE ID = 1;
WAITFOR DELAY '00:00:05';
UPDATE Wizards SET Name = 'Harry Potter' WHERE ID = 1;
COMMIT;


BEGIN TRANSACTION;
UPDATE Wizards SET Name = 'Albus Dumbledore' WHERE ID = 1;
WAITFOR DELAY '00:00:05';
UPDATE Spells SET Power = 5 WHERE ID = 1;
COMMIT;

Strategie per evitare i deadlock

  • Accesso ordinato ai dati: Assicurarsi che tutte le transazioni accedano alle risorse nella stessa sequenza.
  • Transazioni più brevi: Ridurre il tempo di esecuzione delle transazioni.
  • Utilizzo di indici appropriati: Ottimizzare le query con indici per ridurre il numero di righe bloccate.
  • Monitoraggio dei deadlock: Usare SQL Profiler, Extended Events o DMV (sys.dm_tran_locks).

Best Practices per la Concorrenza

Per bilanciare prestazioni e coerenza, è fondamentale scegliere il livello di isolamento più basso possibile che soddisfi i requisiti dell’applicazione.

Ad esempio, in un sistema di reportistica, l’uso di Snapshot Isolation può migliorare le performance evitando blocchi di lettura:

ALTER DATABASE [HogwartsDB] SET READ_COMMITTED_SNAPSHOT ON;

Suddividere le transazioni in operazioni più piccole e rapide riduce il tempo di attesa su risorse condivise. Inoltre, l’ottimizzazione delle query attraverso indici ben strutturati e strategie di partizionamento può abbassare il rischio di blocchi estesi.

Infine, il monitoraggio attivo delle transazioni con strumenti come Extended Events e Query Store permette di identificare rapidamente colli di bottiglia e intervenire proattivamente.

Conclusione

Gestire correttamente locking e livelli di isolamento è essenziale per bilanciare concorrenza e coerenza in SQL Server. Una scelta oculata del livello di isolamento e un uso efficiente delle tecniche di locking possono migliorare significativamente le prestazioni delle applicazioni basate su database.