Lock, bloccaggio, deadlock: il triangolo quotidiano

Nel momento in cui Runehold ha superato i dieci ordini concorrenti al secondo, la dashboard del customer support ha iniziato ad andare in timeout in modo casuale. Lo sviluppatore che aveva costruito la dashboard giurava che la query era veloce. L’ingegnere ops che eseguiva il job di processamento ordini giurava che non era colpa del job. Avevano entrambi ragione. Il problema vero era che la dashboard teneva un lock di SELECT sulla tabella clienti, il job ordini teneva un lock di UPDATE, e per tre secondi al minuto si stavano contendendo le stesse righe.

È questo il bloccaggio, il blocking, e la sottile differenza tra di loro. La lezione 19 parlava dei livelli di isolamento (la policy); oggi parliamo dei lock (l’implementazione) e della realtà quotidiana di gestire un sistema OLTP in scala.

Cos’è un lock

Un lock è un marker che SQL Server mette su una risorsa del database — una riga, una pagina, un indice, una tabella — che dice “lo sto usando, devi aspettare (o spostarti).”

I lock vengono acquisiti automaticamente da SQL Server durante le normali operazioni. Non si fa mai LOCK TABLE direttamente in SQL Server (be’, ci sono hint, ma raramente). Leggi una riga, SQL Server prende un lock condiviso su di essa. Aggiorni una riga, SQL Server prende un lock esclusivo. Vengono rilasciati al commit o al rollback della transazione.

Tipi che vedrai:

• S (Shared) — per le letture. Più sessioni possono tenere lock condivisi sulla stessa riga contemporaneamente.
• X (Exclusive) — per le scritture. Solo una sessione può tenerlo, blocca tutto il resto.
• U (Update) — un “lettore che ha intenzione di promuoversi a esclusivo”. Previene i deadlock dove due sessioni leggono entrambe e poi entrambe provano a promuoversi.
• IS, IX, IU (Intent) — “Ho un lock S/X/U su qualcosa sotto questo oggetto.” Gli intent lock stanno sulla tabella quando le righe singole sono lockate.
• Sch-S, Sch-M (Schema) — schema stability (read) e schema modification (DDL). Droppare una colonna prende Sch-M; ogni altra query prende Sch-S.
• Key-range — specifico di SERIALIZABLE; lockka range di chiavi per prevenire i fantasmi.

Non devi memorizzarli tutti. Shared ed Exclusive sono il 90% di quello che vedrai.

Compatibilità

Due lock sulla stessa risorsa coesistono solo se sono compatibili:

Tenuto → / Richiesto ↓	S	U	X
S	OK	OK	Aspetta
U	OK	Aspetta	Aspetta
X	Aspetta	Aspetta	Aspetta

Quindi: molti lettori (S) coesistono. Un lettore (S) + scrittore (X) no — il lettore aspetta. Due scrittori (X) non coesistono; lo scrittore successivo aspetta.

Sotto RCSI (lezione 19), i lettori non prendono lock S — leggono da uno snapshot. Gli scrittori prendono comunque X. La concorrenza schizza in alto.

Bloccaggio vs deadlock

• Bloccaggio è transitorio. La sessione B sta aspettando che la sessione A rilasci un lock. B è inattiva finché A non committa.
• Deadlock è permanente senza intervento. A sta aspettando il lock di B, B sta aspettando il lock di A. Nessuna delle due può procedere.

SQL Server rileva i deadlock automaticamente (ogni ~5 secondi) e uccide la più economica delle due con errore 1205. Il bloccaggio non viene mai risolto automaticamente — se A non committa mai, B aspetta per sempre (o fino al timeout della query).

Trovare chi sta bloccando chi — le query classiche

Vecchia scuola: sp_who2

EXEC sp_who2;

Mostra tutte le sessioni, con una colonna BlkBy. Se una sessione ha un numero lì, è la sessione che la sta bloccando. Leggibile su un server tranquillo, illeggibile su uno affollato.

Moderna: DMV

SELECT
    s.session_id,
    s.login_name,
    s.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    SUBSTRING(t.text,
              r.statement_start_offset / 2 + 1,
              (CASE r.statement_end_offset
                   WHEN -1 THEN DATALENGTH(t.text)
                   ELSE r.statement_end_offset
               END - r.statement_start_offset) / 2 + 1) AS current_statement
FROM sys.dm_exec_sessions  AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
ORDER BY r.blocking_session_id DESC;

Una riga per sessione utente attiva. blocking_session_id ti dice chi è bloccato. current_statement ti dice cosa stavano cercando di fare. È la singola query più utile che un DBA esegue su un server ingolfato.

Per uno strumento ancora più carino, la lezione 39 introdurrà sp_WhoIsActive — il regalo di Adam Machanic all’umanità.

Cosa stanno aspettando?

SELECT wait_type, COUNT(*) AS sessions, SUM(wait_time) AS total_wait_ms
FROM sys.dm_os_waiting_tasks
GROUP BY wait_type
ORDER BY total_wait_ms DESC;

I wait type LCK_M_* significano “in attesa su un lock”. PAGEIOLATCH_* significa “in attesa di una pagina dal disco”. SOS_SCHEDULER_YIELD significa “contesa CPU”. Ogni wait type racconta una storia.

Leggere un deadlock graph

Quando scatta un deadlock, SQL Server emette un “deadlock graph” come XML. È brutto ma informativo.

Abilita la cattura con una sessione Extended Events (moderna) o il Trace Flag 1222 (vecchio). La sessione di default system_health cattura i deadlock automaticamente:

SELECT TOP (5)
    XEvent.value('(@timestamp)[1]', 'DATETIME2') AS ts,
    XEvent.query('.')                              AS deadlock_xml
FROM (
    SELECT CAST(target_data AS XML) AS td
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
) AS source
CROSS APPLY td.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS X(XEvent)
ORDER BY ts DESC;

Clicca la cella XML in SSMS e ottieni una visualizzazione: due processi, due risorse, frecce che mostrano chi voleva cosa. Il processo marcato come “victim” è quello ucciso con errore 1205.

Tipicamente trovi deadlock causati da:

• Ordine di lock inconsistente. La sessione A locka la tabella X poi Y; la sessione B locka Y poi X. Deadlock classico.
• Index lookup. I key lookup possono acquisire lock sulla tabella base e sull’indice non clustered in ordini diversi per query diverse.
• Operazioni su foreign key. Aggiornare una tabella figlia può andare in deadlock con l’aggiornamento del parent se entrambe le transazioni toccano entrambi.
• Update larghi + select piccole. Un update enorme che tocca molte righe va in deadlock con una select piccola che ne afferra una a metà update.

Il pattern di retry

I deadlock succedono. La tua app di produzione deve riprovare la transazione uccisa:

// Pseudocodice — pattern simile in qualsiasi linguaggio
for (int attempt = 0; attempt < 3; attempt++) {
    try {
        using (var tx = conn.BeginTransaction()) {
            // ... lavoro ...
            tx.Commit();
            break;
        }
    }
    catch (SqlException ex) when (ex.Number == 1205 && attempt < 2) {
        Thread.Sleep(50 * (1 << attempt));   // exponential backoff
        continue;
    }
}

Tre tentativi, exponential backoff, poi rilancia l’errore in superficie. Più del novanta percento dei deadlock si risolve con un singolo retry perché nel momento in cui riprovi, l’altra transazione si è committata e la contesa è andata.

Lock escalation: il “molla le righe” di SQL Server

Quando SQL Server sta tenendo migliaia di row lock sulla stessa tabella in una singola transazione, può decidere “tenere 5.000 row lock è più costoso di un singolo lock di tabella” e fare escalation a un singolo lock a livello tabella. La soglia di default è ~5.000 lock.

Conseguenze:

• La tua transazione ora blocca tutti gli altri sull’intera tabella.
• I lettori non possono leggere (sotto isolamento basato su lock).
• L’intero server sembra lento.

Prevenzione:

• Batcha gli update grandi. Lo abbiamo coperto nella lezione 17. UPDATE TOP (1000) in un loop non supera mai la soglia.
• Considera il partizionamento. Una tabella partizionata può fare escalation a livello partizione invece che a livello tabella.
• RCSI. I lettori non si bloccano sulle scritture, quindi l’escalation è meno dolorosa.

Errori comuni di locking

WITH (NOLOCK) come cura per tutto

Uno sviluppatore vede del bloccaggio, trova WITH (NOLOCK) su Stack Overflow, lo cosparge su ogni SELECT. Ora le letture sono veloci. Poi qualcuno legge righe duplicate o righe fantasma durante un page split. O i report mostrano numeri sbagliati alla riconciliazione. WITH (NOLOCK) non è una funzionalità di performance. È un compromesso sulla correttezza.

Meglio: abilita RCSI.

Tenere una transazione attraverso un’interazione utente

L’anti-pattern da manuale della lezione 18. Mai aprire una transazione, aspettare l’input dell’utente, committare. L’utente va per un caffè. Ogni altro utente aspetta.

Auto-bloccaggio

Non comune ma divertente. Un piano di query parallelo può tenere lock incompatibili tra i suoi stessi thread e bloccarsi da solo. Di solito è segno di un piano di esecuzione eccessivamente parallelo, e si risolve migliorando l’indicizzazione.

Ignorare il transaction log

Una transazione gigantesca non tiene solo i lock — riempie il transaction log. Se il log si riempie, ogni scrittura sul database si ferma. Tratteremo il sizing del file di log nella lezione 30.

Esegui questo sulla tua macchina (servono due sessioni)

USE Runehold;
GO

-- ========== Sessione A ==========
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'A has the lock' WHERE CustomerId = 1;
-- Non committare.

-- ========== Sessione B (finestra diversa) ==========
-- Questa SELECT si blocca finché A non committa (a meno che RCSI non sia attivo)
SELECT Name FROM Sales.Customer WHERE CustomerId = 1;

-- Nel frattempo, da una terza sessione, osserva il bloccaggio:
SELECT s.session_id, s.login_name, r.wait_type, r.blocking_session_id,
       DB_NAME(r.database_id) AS db_name
FROM sys.dm_exec_sessions  AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
WHERE s.is_user_process = 1;

-- Pulizia
-- Sessione A:
ROLLBACK;

-- ========== Demo deadlock ==========
-- Sessione A:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'A1' WHERE CustomerId = 1;

-- Sessione B:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'B2' WHERE CustomerId = 2;

-- Sessione A:
UPDATE Sales.Customer SET Name = N'A2' WHERE CustomerId = 2;
-- si blocca su B

-- Sessione B:
UPDATE Sales.Customer SET Name = N'B1' WHERE CustomerId = 1;
-- scatta il deadlock, una sessione muore con errore 1205

-- Quella che è morta:
-- Msg 1205, Level 13, State 51, Line ...
-- Transaction (Process ID N) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Il modulo 3 è finito. Tra le lezioni 17-20 ora capisci il DML da capo a piedi: come scrivere dati, come funzionano le transazioni, cosa fa l’isolamento, come si comportano i lock, e come sopravvivere ai deadlock con grazia.

Prossimo: indici. La lezione 21 (già scritta — indici clustered e non clustered) pone le fondamenta; le lezioni 22-24 coprono indici di copertura, indici filtrati, columnstore, e la verità sulla frammentazione.