Blocări, blocaje, deadlock-uri: triunghiul zilnic

În momentul în care Runehold a depășit zece comenzi concurente pe secundă, dashboard-ul de customer support a început să dea timeout aleatoriu. Dezvoltatorul care a construit dashboard-ul jura că query-ul e rapid. Inginerul de ops care rula job-ul de procesare a comenzilor jura că nu e vina job-ului. Aveau amândoi dreptate. Problema reală era că dashboard-ul ținea o blocare SELECT pe tabela de clienți, job-ul de comenzi ținea o blocare UPDATE, și pentru trei secunde din fiecare minut se băteau pe aceleași rânduri.

Asta e blocarea, blocajul și diferența subtilă dintre ele. Lecția 19 a vorbit despre niveluri de izolare (politica); astăzi vorbim despre blocări (implementarea) și realitatea zilnică a rulării unui sistem OLTP la scară.

Ce e o blocare

O blocare e un marker pe care SQL Server îl pune pe o resursă din baza de date — un rând, o pagină, un indice, o tabelă — spunând „o folosesc, trebuie să aștepți (sau să te muți)”.

Blocările sunt obținute automat de SQL Server în timpul operării normale. Niciodată nu faci LOCK TABLE direct în SQL Server (ei bine, există hint-uri, dar rar). Citești un rând, SQL Server ia o blocare shared pe el. Actualizezi un rând, SQL Server ia o blocare exclusive. Sunt eliberate la commit-ul sau rollback-ul tranzacției.

Tipurile pe care le vei vedea:

• S (Shared) — pentru citiri. Mai multe sesiuni pot ține blocări shared pe același rând în același timp.
• X (Exclusive) — pentru scrieri. Doar o sesiune o poate ține, blochează tot restul.
• U (Update) — un „cititor care plănuiește să facă upgrade la exclusive”. Previne deadlock-urile unde două sesiuni citesc apoi încearcă să facă upgrade.
• IS, IX, IU (Intent) — „am o blocare S/X/U pe ceva sub acest obiect”. Blocările intent sunt pe tabelă atunci când rândurile individuale sunt blocate.
• Sch-S, Sch-M (Schema) — schema stability (citire) și schema modification (DDL). Drop-ul unei coloane ia Sch-M; orice alt query ia Sch-S.
• Key-range — specifică SERIALIZABLE; blochează intervale de chei pentru a preveni fantomele.

Nu e nevoie să le memorezi pe toate. Shared și Exclusive sunt 90% din ce vei vedea.

Compatibilitate

Două blocări pe aceeași resursă coexistă doar dacă sunt compatibile:

Ținută → / Cerută ↓	S	U	X
S	OK	OK	Așteaptă
U	OK	Așteaptă	Așteaptă
X	Așteaptă	Așteaptă	Așteaptă

Deci: mulți cititori (S) coexistă. Un cititor (S) + scriitor (X) nu — cititorul așteaptă. Doi scriitori (X) nu coexistă; al doilea scriitor așteaptă.

Sub RCSI (lecția 19), cititorii nu iau deloc blocări S — citesc dintr-un snapshot. Scriitorii încă iau X. Concurența urcă mult.

Blocaj vs deadlock

• Blocaj (blocking) e tranzitoriu. Sesiunea B așteaptă ca sesiunea A să elibereze o blocare. B e inactivă până când A face commit.
• Deadlock e permanent fără intervenție. A așteaptă blocarea lui B, B așteaptă blocarea lui A. Niciuna nu poate avansa.

SQL Server detectează deadlock-urile automat (la fiecare ~5 secunde) și ucide pe cea mai ieftină dintre cele două cu eroarea 1205. Blocajul nu e niciodată rezolvat automat — dacă A nu face niciodată commit, B așteaptă pentru totdeauna (sau până la query timeout).

Cine blochează pe cine — query-urile clasice

Old school: sp_who2

EXEC sp_who2;

Arată toate sesiunile, cu o coloană BlkBy. Dacă o sesiune are un număr acolo, aceea e sesiunea care o blochează. Lizibil pe un server liniștit, ilizibil pe unul aglomerat.

Modern: DMV-uri

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;

Câte un rând per sesiune activă de utilizator. blocking_session_id îți spune cine e blocat. current_statement îți spune ce încercau să facă. Ăsta e cel mai util query pe care îl rulează un DBA pe un server aglomerat.

Pentru o unealtă și mai frumoasă, lecția 39 va introduce sp_WhoIsActive — cadoul lui Adam Machanic pentru umanitate.

Pe ce așteaptă?

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;

Tipurile de wait LCK_M_* înseamnă „așteaptă pe o blocare”. PAGEIOLATCH_* înseamnă „așteaptă o pagină de pe disc”. SOS_SCHEDULER_YIELD înseamnă „concurență pe CPU”. Fiecare tip de wait spune o poveste.

Cum citești un graf de deadlock

Când un deadlock are loc, SQL Server emite un „graf de deadlock” sub formă de XML. E urât, dar informativ.

Activează capturarea cu o sesiune Extended Events (modern) sau Trace Flag 1222 (vechi). Sesiunea implicită system_health capturează deadlock-urile automat:

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;

Click pe celula XML în SSMS și primești o vizualizare: două procese, două resurse, săgeți care arată cine voia ce. Procesul marcat ca „victim” a fost cel ucis cu eroarea 1205.

Tipic, deadlock-urile au cauze de tipul:

• Ordine de blocare inconsistentă. Sesiunea A blochează tabela X apoi Y; sesiunea B blochează Y apoi X. Deadlock clasic.
• Lookups pe indici. Key lookups pot acumula blocări pe tabela de bază și pe indicele non-clustered în ordini diferite pentru query-uri diferite.
• Operațiuni pe foreign keys. Update-ul unei tabele copil poate intra în deadlock cu update-ul tabelei părinte dacă ambele tranzacții ating ambele.
• Update-uri largi + select-uri mici. Un update uriaș care atinge multe rânduri intră în deadlock cu un select mic ce ia unul dintre ele la mijlocul update-ului.

Pattern-ul de retry

Deadlock-urile se întâmplă. Aplicația ta de producție are nevoie să facă retry tranzacția ucisă:

// Pseudocod — pattern similar în orice limbaj
for (int attempt = 0; attempt < 3; attempt++) {
    try {
        using (var tx = conn.BeginTransaction()) {
            // ... lucru ...
            tx.Commit();
            break;
        }
    }
    catch (SqlException ex) when (ex.Number == 1205 && attempt < 2) {
        Thread.Sleep(50 * (1 << attempt));   // exponential backoff
        continue;
    }
}

Trei încercări, exponential backoff, apoi propagi eroarea. Nouăzeci-și-ceva la sută din deadlock-uri sunt rezolvate de un singur retry pentru că, până faci retry, cealaltă tranzacție a făcut commit și concurența a dispărut.

Escaladarea blocărilor: „renunță la rânduri” al SQL Server

Când SQL Server ține mii de blocări de rând pe aceeași tabelă într-o singură tranzacție, poate decide că „a ține 5.000 de blocări de rând e mai costisitor decât o blocare de tabelă” și escaladează la o singură blocare la nivel de tabelă. Pragul e ~5.000 de blocări implicit.

Consecințe:

• Tranzacția ta blochează acum pe toți ceilalți pe întreaga tabelă.
• Cititorii nu pot citi (sub izolare bazată pe blocări).
• Întregul server se simte lent.

Prevenire:

• Lotează update-urile mari. Am acoperit asta în lecția 17. UPDATE TOP (1000) într-o buclă nu trece niciodată peste prag.
• Ia în considerare partiționarea. O tabelă partiționată poate escalada la blocări la nivel de partiție în loc de tabelă.
• RCSI. Cititorii nu blochează pe scrieri, deci escaladarea e mai puțin dureroasă.

Greșeli frecvente de blocare

WITH (NOLOCK) ca leac universal

Un dezvoltator vede blocaje, găsește WITH (NOLOCK) pe Stack Overflow, îl presară pe fiecare SELECT. Acum citirile sunt rapide. Apoi cineva citește rânduri duplicate sau rânduri fantomă în timpul unui page split. Sau rapoartele arată numere greșite la reconciliere. WITH (NOLOCK) nu e o feature de performanță. E un compromis de corectitudine.

Mai bine: activează RCSI.

Ținerea unei tranzacții peste o interacțiune cu utilizatorul

Antipattern-ul de manual din lecția 18. Niciodată nu deschide o tranzacție, aștepta input de la utilizator, face commit. Utilizatorul pleacă după cafea. Toți ceilalți utilizatori așteaptă.

Auto-blocaj

Mai puțin frecvent, dar amuzant. Un plan de query paralel poate ține blocări incompatibile între propriile thread-uri și se poate auto-bloca. De obicei, semnul unui plan de execuție prea paralelizat și se rezolvă prin îmbunătățirea indexării.

Ignorarea log-ului de tranzacții

O tranzacție gigant nu doar ține blocări — umple log-ul de tranzacții. Dacă log-ul se umple, fiecare scriere pe baza de date se oprește. Vom acoperi dimensionarea fișierului de log în lecția 30.

Rulează asta pe mașina ta (ai nevoie de două sesiuni)

USE Runehold;
GO

-- ========== Sesiunea A ==========
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'A has the lock' WHERE CustomerId = 1;
-- Nu face commit.

-- ========== Sesiunea B (fereastră diferită) ==========
-- Acest SELECT se blochează până când A face commit (dacă RCSI nu e pornit)
SELECT Name FROM Sales.Customer WHERE CustomerId = 1;

-- Între timp, dintr-o a treia sesiune, observă blocajul:
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;

-- Curățenie
-- Sesiunea A:
ROLLBACK;

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

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

-- Sesiunea A:
UPDATE Sales.Customer SET Name = N'A2' WHERE CustomerId = 2;
-- se blochează pe B

-- Sesiunea B:
UPDATE Sales.Customer SET Name = N'B1' WHERE CustomerId = 1;
-- deadlock-ul are loc, o sesiune moare cu eroarea 1205

-- Oricare a murit:
-- 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.

Modulul 3 e încheiat. Între lecțiile 17-20 înțelegi acum DML cap-coadă: cum scrii date, cum funcționează tranzacțiile, ce face izolarea, cum se comportă blocările și cum supraviețuiești cu eleganță deadlock-urilor.

Următorul: indici. Lecția 21 (deja scrisă — indici clustered și non-clustered) pune fundația; lecțiile 22-24 acoperă indicii acoperitori, indicii filtrați, columnstore și adevărul despre fragmentare.