Narcis Miclaus

7 min de citit

SQL Server, de la zero Lecția 19 / 40

Nivelurile de izolare, demistificate

READ COMMITTED, SNAPSHOT, SERIALIZABLE și cele patru anomalii pe care le previn. De ce setarea implicită a SQL Server te poate mușca și de ce RCSI e setarea pe care majoritatea magazinelor din UE ar trebui să o pornească.

Lecția trecută am acoperit ce sunt tranzacțiile. Astăzi acoperim I-ul din ACID: izolarea. Ce se întâmplă când două tranzacții au loc în același timp și ating o parte dintre aceleași date?

Răspunsul scurt: depinde de nivelul de izolare la care rulează baza ta de date. Iar nivelul de izolare implicit al SQL Server (READ COMMITTED) nu face ce așteaptă majoritatea dezvoltatorilor noi. Există un default mai bun (READ COMMITTED SNAPSHOT) care e dezactivat implicit. Activarea lui e una dintre cele mai puternice decizii pe care le poate lua un DBA pentru un workload OLTP modern.

Cele patru anomalii clasice

Înainte de niveluri, hai să vedem ce poate merge prost în SQL concurent.

Citire murdară (dirty read)

Tranzacția A scrie un rând. Înainte ca A să facă commit, tranzacția B citește acel rând. A face rollback. B are acum date care, oficial, nu au existat vreodată.

Scenariu Runehold: warehouse-ul începe să decrementeze stocul. Dashboard-ul de ops citește stocul în acel moment. Decrementarea eșuează și face rollback. Dashboard-ul raportează nivelul „nou” de stoc, care n-a existat niciodată.

Citire neimplicită (non-repeatable read)

Tranzacția A citește un rând. Tranzacția B actualizează același rând și face commit. Tranzacția A re-citește rândul, vede date diferite.

Scenariu Runehold: agentul de customer service deschide un profil de client, începe să citească detaliile. Altă sesiune procesează update-ul datelor bancare ale clientului. Agentul re-citește, vede date diferite în mijlocul conversației.

Citire fantomă (phantom read)

Tranzacția A rulează un query. Tranzacția B inserează un rând nou care se potrivește cu query-ul A. A re-rulează query-ul, vede un rând nou („fantomă”) care nu era acolo înainte.

Scenariu Runehold: finance rulează un raport cu „toate comenzile plasate astăzi”. Undeva în generarea raportului, aterizează o comandă nouă. Finance re-rulează un sub-query, totalurile nu se potrivesc cu primul query.

Update pierdut (lost update)

Tranzacția A citește X, calculează X’ și e pe punctul să scrie X’. Tranzacția B citește X, calculează X”, scrie X”. A scrie X’, suprascriind modificarea lui B. Update-ul lui B e pierdut.

Scenariu Runehold: doi agenți de customer service deschid același profil de client. Amândoi editează. Amândoi salvează. Modificările unuia dispar.

Cele cinci niveluri de izolare

SQL Server oferă cinci niveluri de izolare cu nume. Fiecare previne unele sau toate anomaliile, în detrimentul concurenței sau memoriei.

READ UNCOMMITTED (sau NOLOCK)

Citește date necommit-uite. Permite citiri murdare. Practic niciodată în cod nou.

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

O să vezi WITH (NOLOCK) peste tot în codul SQL Server vechi. Lumea crede că face query-urile mai rapide. O face — cu prețul potențialei citiri:

  • Date care urmează să fie rolled back (greșit).
  • Rânduri care se mișcă din cauza page splits (numărate de două ori sau ratate).
  • Date inconsistente la mijlocul tranzacției (două rânduri ale unui set înrudit sunt în stări diferite).

Pentru query-uri de monitorizare unde datele aproximative sunt ok, NOLOCK e acceptabil. Pentru orice altceva, e un bug care așteaptă să se întâmple.

READ COMMITTED

Default-ul SQL Server. Previne citirile murdare. Permite citirile neimplicite și fantomele.

Sub READ COMMITTED, cititorii blochează scriitorii și scriitorii blochează cititorii. Un SELECT așteaptă dacă altă sesiune actualizează rândul; un update așteaptă dacă altă sesiune citește.

Asta e sursa clasicului „dashboard-ul meu e lent pentru că finance rulează închiderea de lună”. Citirile sunt blocate de scrieri și viceversa.

READ COMMITTED SNAPSHOT ISOLATION (RCSI)

Asta e setarea pe care o vrei pentru majoritatea workload-urilor OLTP. E READ COMMITTED, dar cititorii nu blochează scriitorii și scriitorii nu blochează cititorii — în schimb, cititorii primesc un snapshot consistent al datelor de la momentul în care a început instrucțiunea lor.

Activată la nivel de bază de date:

ALTER DATABASE Runehold SET READ_COMMITTED_SNAPSHOT ON;

Odată activată, fiecare query READ COMMITTED (care e default-ul) devine o citire de snapshot. Fără schimbare de comportament în codul tău; îmbunătățire masivă a concurenței. E cea mai bună setare pe care majoritatea magazinelor de e-commerce din UE o pornesc și o uită.

Cost: SQL Server folosește tempdb pentru a stoca versiuni anterioare ale rândurilor pentru snapshot-urile în zbor. tempdb crește. Planifică pentru asta (lecția 34).

SNAPSHOT ISOLATION

Un pas dincolo de RCSI: întreaga tranzacție vede un snapshot consistent, nu doar fiecare instrucțiune. Previne citirile murdare, citirile neimplicite și fantomele. Permite update-uri concurente care nu intră în conflict.

ALTER DATABASE Runehold SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;
    SELECT ...;
    SELECT ...;      -- ambele select-uri văd același snapshot de la BEGIN TRAN
COMMIT;

Util pentru rapoarte care trebuie să fie intern consistente: „de la momentul în care am început, care era venitul total și numărul de clienți?”

Capcană: dacă două tranzacții SNAPSHOT încearcă să modifice același rând, a doua eșuează cu o eroare de conflict de update. Aplicația ta trebuie să gestioneze asta (de obicei: retry).

REPEATABLE READ și SERIALIZABLE

Niveluri de izolare bazate pe blocări, mai vechi, care previn mai multe anomalii cu prețul ținerii blocărilor mai mult timp.

  • REPEATABLE READ: previne citirile murdare și citirile neimplicite. Permite fantomele.
  • SERIALIZABLE: previne toate cele patru anomalii. Acumulează blocări de range, blochează intens.

Rar folosite în cod nou. Dacă ai nevoie de garanții mai puternice, SNAPSHOT e de obicei alegerea mai bună — dă siguranță similară cu mai puține blocări.

Când să folosești ce

Reguli rapide:

  • Default pentru aplicații OLTP: pornește RCSI. Lasă query-urile la default-ul READ COMMITTED. Gata.
  • Rapoarte care trebuie să fie intern consistente: SNAPSHOT ISOLATION.
  • Update-uri de stoc, înregistrări financiare: blocare explicită cu SERIALIZABLE sau blocări la nivel de aplicație (mai multe în lecția 20).
  • Query-uri ad-hoc de diagnostic care nu trebuie să fie perfect exacte: NOLOCK e o scurtătură acceptabilă.

Baza de producție Runehold are RCSI activat. Procedura de „închidere de lună” pentru finance rulează sub SNAPSHOT pentru a vedea o vedere stabilă. Update-urile fierbinți de stoc folosesc concurență optimistă (coloana RowVersion). Restul folosește default-ul.

Pattern-ul RowVersion

Multe conflicte de update pot fi gestionate la nivelul aplicației cu concurență optimistă prin coloana ROWVERSION (am menționat-o în lecția 5).

CREATE TABLE Sales.Customer (
    CustomerId INT PRIMARY KEY,
    Name       NVARCHAR(200),
    ...,
    Version    ROWVERSION        -- se actualizează automat la fiecare modificare
);

Aplicația citește rândul, inclusiv Version. Când actualizează, include Version-ul pe care l-a văzut:

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

IF @@ROWCOUNT = 0
    -- Altcineva a schimbat rândul între citire și update.
    -- Aruncă o eroare de conflict; aplicația face retry cu date proaspete.
    THROW 50003, 'Optimistic concurrency conflict', 1;

Fără blocări ținute, fără așteptare. Conflictele sunt detectate la commit și gestionate explicit. Așa gestionează majoritatea aplicațiilor web moderne „doi utilizatori au editat aceeași înregistrare”.

Deadlock-uri: o avanpremieră scurtă

Dacă două tranzacții au nevoie de aceleași blocări în ordine inversă, pot intra în deadlock. SQL Server detectează deadlock-urile la fiecare 5 secunde și ucide pe cea mai ieftină dintre cele două cu eroarea 1205.

Tranzacția 1:                     Tranzacția 2:
  BEGIN TRAN;                       BEGIN TRAN;
  UPDATE A;                         UPDATE B;
  ... vrea B ...                    ... vrea A ...
  [se blochează, așteaptă pe 2]     [se blochează, așteaptă pe 1]
  [UCISĂ de detectorul de deadlock] [face COMMIT]

Lecția completă despre blocări, blocaje și deadlock-uri urmează (lecția 20). Versiunea scurtă: acumulează blocările întotdeauna într-o ordine consistentă, ține tranzacțiile scurte și construiește logică de retry pentru deadlock în aplicație (retry-uiește tranzacția ucisă de 2-3 ori înainte de a renunța).

Citirea nivelului tău curent de izolare

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;

Util la debug. Fiecare client (SSMS, aplicație, ORM) poate seta un default diferit.

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

-- Deschide două ferestre de query; le numim Sesiunea A și Sesiunea B.

-- În ambele: USE Runehold;

-- ========== CU READ COMMITTED (default, fără RCSI) ==========

-- Sesiunea A:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'UPDATED IN A' WHERE CustomerId = 1;
-- Nu face commit încă.

-- Sesiunea B:
SELECT Name FROM Sales.Customer WHERE CustomerId = 1;
-- Se blochează, pentru că A ține o blocare.

-- Sesiunea A:
COMMIT;   -- acum B se deblochează și vede valoarea nouă

-- ========== CU RCSI (recomandat) ==========
-- Pornește-l (în afara oricărei tranzacții; ai nevoie de acces exclusiv pentru o clipă)
-- ALTER DATABASE Runehold SET READ_COMMITTED_SNAPSHOT ON;

-- Acum re-rulează același scenariu:
-- Sesiunea A:
BEGIN TRAN;
UPDATE Sales.Customer SET Name = N'UPDATED IN A' WHERE CustomerId = 1;

-- Sesiunea B:
SELECT Name FROM Sales.Customer WHERE CustomerId = 1;
-- Întoarce instant valoarea DE DINAINTEA update-ului. Fără blocaj.

-- Sesiunea A:
ROLLBACK;
-- Niciun rău făcut.

-- ========== Izolare SNAPSHOT pentru rapoarte stabile ==========
-- ALTER DATABASE Runehold SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
    SELECT COUNT(*) AS initial_count FROM Sales.Orders;
    -- Imaginează că cineva inserează 1000 de comenzi aici.
    WAITFOR DELAY '00:00:02';
    SELECT COUNT(*) AS same_count FROM Sales.Orders;
COMMIT;
-- Ambele SELECT-uri întorc același număr: snapshot-ul de la BEGIN TRAN.

RCSI e cel mai impactant single switch. Rulează demo-ul înainte/după; privește B blocându-se, apoi nu. Acel moment e cel în care majoritatea DBA-ilor decid să-l activeze pentru totdeauna.

Următoarea lecție: blocări, blocaje și deadlock-uri — triunghiul zilnic al oricărui sistem OLTP aglomerat. Vom uita la DMV-uri pentru a vedea ce e blocat, cum să găsim cine blochează pe cine și cum să citim un graf de deadlock fără să plângi.

Caută