`Ghid Complet ACID: Niveluri de Izolare, Locking și Prevenirea Deadlock-urilor` în MySQL

Înțelegerea profundă a tranzacțiilor ACID în motorul InnoDB: cele 4 niveluri de izolare SQL, mecanismul de blocare (Record, Gap, Next-Key Locks) și tehnici dovedite de prevenire și depanare a deadlock-urilor.

Sintaxă

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; ... COMMIT;

Pentru orice sistem critic din domeniul e-commerce, banking sau procesare de comenzi, consistența datelor este non-negociabilă. Motorul implicit InnoDB din MySQL oferă suport complet pentru proprietățile ACID (Atomicity, Consistency, Isolation, Durability). Cu toate acestea, concurența ridicată între conexiuni paralele poate duce la fenomene nedorite și blocaje critice (deadlocks).

Anatomia unei tranzacții sigure în MySQL

-- 1. Începem o tranzacție explicită
START TRANSACTION;

-- 2. Blocăm rândul contului sursă pentru citire și actualizare exclusivă
SELECT sold FROM conturi WHERE id = 101 FOR UPDATE;

-- 3. Verificăm în codul aplicației dacă sold >= 500, apoi debităm contul
UPDATE conturi SET sold = sold - 500 WHERE id = 101;

-- 4. Credităm contul destinație
UPDATE conturi SET sold = sold + 500 WHERE id = 202;

-- 5. Salvăm ambele modificări atomic
COMMIT;

-- În caz de excepție neașteptată în aplicație:
-- ROLLBACK; -- Anulează toate modificările din tranzacție

Cele 4 Niveluri de Izolare a Tranzacțiilor

Standardul SQL definește 4 niveluri de izolare. MySQL permite setarea acestora la nivel global, de sesiune sau per tranzacție:

Nivel de Izolare	Dirty Read (Citire date necomise)	Non-repeatable Read (Date modificate)	Phantom Read (Rânduri nou inserate)	Descriere & Caz de Utilizare
`READ UNCOMMITTED`	Posibil	Posibil	Posibil	Nivel periculos; o conexiune citește modificări provizorii făcute de alta. Rar folosit.
`READ COMMITTED`	Prevenit	Posibil	Posibil	Fiecare instrucțiune `SELECT` citește un snapshot proaspăt comis. Nivel recomandat pentru sisteme OLTP mari cu locking redus.
`REPEATABLE READ` (Default InnoDB)	Prevenit	Prevenit	Prevenit în InnoDB*	Prima citire dintr-o tranzacție creează un snapshot MVCC stabil. InnoDB folosește Next-Key Locks pentru a preveni și Phantom Reads!
`SERIALIZABLE`	Prevenit	Prevenit	Prevenit	Transformă automat toate instrucțiunile `SELECT` simple în `SELECT ... FOR SHARE`. Garantează consistență absolută cu cost maxim de concurență.

Tipurile de Blocaje (Locks) în InnoDB

Pentru a garanta izolarea fără a bloca întreg tabelul, InnoDB blochează datele la nivel de rând și spațiu de index:

Record Lock: Blochează exact înregistrarea din indexul respectiv (ex: WHERE id = 10 FOR UPDATE).
Gap Lock: Blochează spațiul („golul”) dintre două intrări consecutive din index sau spațiul dinaintea primului/după ultimul rând. Previne alte conexiuni să insereze rânduri noi în acel interval.
Next-Key Lock: O combinație între un Record Lock pe rând și un Gap Lock pe spațiul din fața rândului. Este mecanismul prin care nivelul REPEATABLE READ blochează inserarea de „fantome” (phantom reads).

Cum apare un Deadlock și cum îl analizăm

Un Deadlock apare atunci când două tranzacții concurente dețin fiecare câte un blocaj pe o resursă și încearcă să obțină un blocaj exclusiv pe resursa deținută de cealaltă conexiune, creând un ciclu de așteptare infinit:

Scenariu clasic de Deadlock

-- Conexiunea A:                                -- Conexiunea B:
START TRANSACTION;                              START TRANSACTION;
UPDATE conturi SET sold = sold - 10 WHERE id=1; -- Lock obținut pe id=1
                                                UPDATE conturi SET sold = sold - 20 WHERE id=2; -- Lock obținut pe id=2
UPDATE conturi SET sold = sold + 10 WHERE id=2; 
-- Așteaptă după Conexiunea B!
                                                UPDATE conturi SET sold = sold + 20 WHERE id=1;
                                                -- MySQL detectează ciclul: ERROR 1213 (40001): 
                                                -- Deadlock found when trying to get lock; try restarting transaction

Pentru a analiza ultimul deadlock survenit pe server, rulați comanda:

SHOW ENGINE INNODB STATUS\G

Căutați secțiunea ------------------------\nLATEST DETECTED DEADLOCK\n------------------------. Acolo veți găsi exact identificatorii conexiunilor, interogările SQL care au intrat în coliziune, indexul pe care s-a cerut blocajul și tranzacția pe care InnoDB a ales să o anuleze automat (rollback pentru tranzacția cu cel mai mic număr de modificări efectuate).

Reguli de Aur pentru Prevenirea Deadlock-urilor

Ordinea deterministică: Întotdeauna actualizați tabelele și rândurile în aceeași ordine în toate tranzacțiile din aplicație (de exemplu, sortați array-ul de ID-uri crescător înainte de a emite instrucțiunile UPDATE).
Tranzacții compacte: Păstrați tranzacțiile cât mai scurte posibil. Nu efectuați apeluri HTTP către servicii externe sau procesare masivă de imagini/fișiere în interiorul unei tranzacții deschise.
Evitați Gap Locks inutile: Dacă logica de business o permite, comutarea la SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; dezactivează Gap Locks pentru operațiuni curente, reducând drastic riscul de blocaj.
Gestionarea retry-ului în cod: Niciun sistem de mare volum nu poate elimina complet posibilitatea unui deadlock. Orice cod care gestionează tranzacții trebuie să prindă eroarea SQL 1213 și să reîncerce automat tranzacția de până la 3 ori folosind un mecanism de tip exponential backoff.