În acest articol veți deveni familiari cu nivelele de izolare din SQL Server, cu mecanismele de tip locking și blocking, cu conceptul deadlock, cu identificarea și prevenția fenomenelor deadlock în SQL Server. Un fenomen deadlock este o situație în care două sau mai multe procese așteaptă unul după celălalt să finalizeze operațiile din cauza unui locking, ceea ce duce la așteptare infinită și la faptul că niciunul din procese nu mai progresează.
Fenomenele deadlock sunt importante, deoarece, dacă se întâmplă des în cadrul unei baze de date tranzacționale, acest lucru poate duce la probleme serioase de performanță și la erori multiple.
Înainte de a aprofunda fenomenul deadlock și modul în care îl putem preveni, trebuie să înțelegem câteva concepte de bază legate de bazele de date relaționale: proprietățile ACID, nivelele de izolare, problemele de concurență, locking și blocking.
O tranzacție din baza de date (Database Transaction) este o unitate logică de procesare din cadrul DBMS care presupune una sau mai multe operațiuni de acces în baza de date. Pe scurt, tranzacțiile din bazele de date sunt evenimentele reale, concrete ale unei afaceri.
Toate tipurile de operațiuni de acces în baza de date care sunt cuprinse între declarațiile (statements) de început și final sunt considerate drept o singură tranzacție logică în DBMS. În timpul tranzacției, baza de date este inconsistentă. Doar după ce baza de date este comisă (committed), starea se modifică de la un nivel de consistență la altul.
Sumar: Tranzacție - unitate logică singulară de lucru din cadrul unei baze de date. Într-o RDBMS o tranzacție respectă proprietățile ACID.
A - Atomicitate: Tranzacțiile sunt totul sau nimic.
C - Consistență: Baza de date trebuie să fie consistentă înainte și după tranzacție.
I - Izolare: Tranzacțiile nu au efect unele asupra altora.
Nivelele de izolare definesc cât de mult trebuie să fie izolată tranzacția de modificările aduse asupra datelor de alte tranzacții din SQL Server. Nivelul de izolare va izola procesul și îl va proteja de alte tranzacții. Pentru acest lucru, este nevoie de locking.
Un nivel scăzut de izolare va genera nivele crescute de concurență și de utilizare partajată a datelor, dar poate avea drept efect nivele scăzute de consistență prin generarea de citiri corupte sau fantomă (dirty / phantom reads). SQL Server va folosi nivelele de izolare pentru a controla comportamentul de citire (read) a bazei de date/tranzacției.
Din moment ce multiple tranzacții ar putea rula simultan (în mod concurent) într-o bază de date, putem găsi tranzacții care citesc din/sau actualizează același obiect de tip dată (rând, tabelă, index). Se introduc astfel mai multe probleme la care nivelele de izolare reacționează. Nivelul de izolare a tranzacției definește cum și unde va expune baza de date celorlalte sesiuni modificările efectuate de orice tranzacție.
Pentru a selecta nivelul de izolare propice pentru fiecare tranzacție, este obligatoriu să existe o bună înțelegere a problemelor de concurență:
Dirty Reads (Citiri Corupte) - Datele se citesc în timp ce alt proces modifică datele, iar procesul original citește datele necomise.
Non-Repeatable Reads (Citiri Non-Repetabile) - Un proces citește aceleași date de mai multe ori în cadrul unei sesiuni. Între citirile de date, un alt proces poate schimba datele și, prin urmare, se returnează valori diferite.
Tipologia nivelelor de izolare
Citire Necomisă (Read Uncommited)
Citire Comisă (Read Commited)
Citire Repetabilă (Repeateable Read)
Serializare (Serializable)
Aceasta reprezintă cele mai scăzut nivel de izolare sau cel mai puțin restrictiv nivel de izolare. O tranzacție poate citi modificări care nu sunt încă comise de altă tranzacție care le efectuează. Permite citiri corupte (dirty reads).
Aceasta reprezintă nivelul standard (default) de izolare setat în SQL Server. Acesta nu permite citirea datelor din tranzacții necomise, deci nu permite citiri corupte (dirty reads). Poate avea drept efect citiri fantomă / citiri repetabile.
Acest nivel permite ca, dacă datele sunt recitite în cadrul unei tranzacții, acestea să nu se modifice. Nicio tranzacție nu poate modifica datele până când tranzacția care citește datele nu este finalizată. Consecința este că toate elementele shared lock din cadrul operațiunii de citire vor aștepta ca tranzacția să fie completă. Citirile corupte și non-repetabile vor fi eliminate, dar citirile fantomă pot să persiste.
Aceasta reprezintă nivelul de izolare cel mai restrictiv și cea mai bună metodă de a asigura respectarea proprietăților ACID. Un bloc citire/scriere va fi generat pentru toate datele afectate de tranzacție, ceea ce elimină citirile fantomă.
În cadrul acestui nivel de izolare, o tranzacție recunoaște doar datele care sunt comise înainte de începutul tranzacției. Acest lucru se realizează folosindu-se un snapshot al datelor în tempdb. Procesul folosește versiunea rândului (row version) unde o versiune separată a fiecărui rând modificat se păstrează în tempdb. Cost ridicat/utilizare ridicată în tempdb. Elimină citirile corupte, non-repetabile, fantomă dar și actualizările pierdute.
Locking este un mecanism folosit de SQL Server Database Engine pentru a sincroniza accesul mai multor utilizatori la aceleași date în același timp. Elementele lock sunt gestionate intern de Lock Manager. Pentru a minimiza costul lockingului, SQL Server aplică automat un lock calibrat cu taskurile, asupra resurselor. A aplica un lock la un nivel mai mic de granularitate, precum rândul, crește concurența, dar are o încărcătură mai mare, deoarece trebuie păstrate mai multe elemente lock. Durata cât se păstrează un lock depinde de nivelul de izolare.
Cele două tipuri de bază ale elementelor lock sunt: read locks și write locks.
Shared (Partajate) - Atâta timp cât se păstrează un shared lock, alte tranzacții pot citi, dar nu pot modifica datele care sunt locked. Elementul lock este ridicat după ce datele locked au fost citite cu condiția ca nivelul de izolare să se afle la sau mai sus de valoarea Repeatable Read.
Concurrent (Concurente) - În acest context, citim datele în cadrul unui nivel de izolare Read Uncommitted sau când specificăm un indiciu (hint) NOLOCK.
Update (Actualizare) - Elementele update lock sunt o combinație de elemente lock shared și exclusive. Când se execută o declarație update, SQL Server trebuie să găsească datele pe care dorește să le modifice prima oară. Pentru a evita un blocaj (deadlock) de conversie al elementelor lock, se folosește un update lock. Un singur update lock poate fi menținut la nivelul datelor la un moment dat, similar unui lock exclusiv. Elementul lock update în sine nu poate modifica datele subiacente, deci când datele sunt modificate, se realizează transferul către un lock exclusiv.
Exclusive (Exclusive) - Elementele exclusive lock sunt folosite pentru a bloca datele ce sunt modificate de o tranzacție, prevenindu-se astfel modificările altor tranzacții concurente. O tranzacție primește mereu un element exclusive lock pe orice date pe care le modifică și menține acel lock până la finalizarea tranzacției, indiferent de nivelul de izolare setat pentru acea tranzacție.
RID (Row Identifier/Identificator de rând) - un rând din cadrul unei tabele de tip heap;
Key (Cheie) - un lock la nivel de rând din cadrul unui index;
Page (Pagină) - o pagină de 8KB în cadrul unei baze de date;
Extent (Extensiune) - un grup adiacent de opt pagini;
HoBT - o tabelă de tip heap sau B-tree;
Table (Tabelă) - tabela integrală și indecșii;
File (Fișier) - un lock la nivel de fișier din baza de date;
Object (Obiect) - un lock pe orice obiect precum o procedură stocată, vedere; în fond, orice obiect din sys.all_objects;
Moduri Lock
Shared (S) - Utilizat pentru operațiuni read care nu trebuie să actualizeze/modifice datele;
Update (U) - Utilizat pentru resursele ce ar putea fi actualizate;
Exclusive (X) - Utilizat pentru operațiuni de tipul modificării de date;
Intent (I) - Utilizat pentru a stabili ierarhia lock (IS), (IX), (IU), (SIU), (UIX) și (SIX);
Schema (Sch) - Utilizat când este actualizată schema tabelei;
Bulk Update (BU) - Utilizat pentru a copia toate datele în mod bulk într-o tabelă;
SQL Server folosește lock escalation pentru a gestiona granularitatea la care se face locking. Lock escalation este gestionată intern, fiind o tehnică de optimizare care să controleze volumul de locks păstrate în Lock Manager din cadrul SQL Server.
Când se face locking la o granularitate mai scăzută, precum rândurile, concurența crește, accesul fiind restricționat la anumite rânduri, nu la toată tabela. Un lock este o structură in-memory de 96 bytes.
Granularitatea unui lock se compune din elemente lock DATABASE, TABELĂ, PAGINĂ și RÂND (ROW). O conexiune către SQL Server va crea un shared lock la nivel de bază de date, prevenind acțiuni precum "DROP DATABASE" atâta timp cât există o conexiune.
SQL Server va aplica granularitatea la nivel de elemente lock, de sus în jos: mai întâi va verifica dacă un lock la nivel de tabelă este necesar, apoi la nivel de pagină, iar apoi la nivel de rând. Un Intent (Shared or Exclusive) este generat la nivel de tabelă și pagină, iar un Shared sau Exclusive este generat la nivel de rând.
După ce SQL Server atinge 5000 de elemente lock la nivel de rând, se va escala la nivel de lock de tabelă.
Valorile standard (default) pot fi modificate, folosind indicii de interogare (query hints) precum ROWLOCK, PAGLOCK, TABLOCK
select * from tbl_test with (ROWLOCK)
where fld_id >1000Puteți modifica lock escalation pentru fiecare tabelă folosind sintaxa:
ALTER TABLE tbl_test
SET LOCK_ESCALATION = Aceasta nu este recomandată, deoarece este cel mai bine să se permită ca Database Engine să efectueze operațiuni de tipul escalate locks.
Un deadlock apare când două procese sunt blocate, deoarece fiecare proces păstrează o resursă de care are nevoie celălalt proces.
Exemplu: Kid_1 are un lock pe Phone_1 și solicită un lock pe Headphone_2. Kid_2 are un lock pe Headphone_2 și a solicitat un lock pe Phone_1. Aceștia așteaptă unul după celălalt.
SQL Server Engine decide care dintre cei doi este victima deadlockului, din moment ce niciun proces nu poate merge mai departe.
SQL Server Database Engine verifică dacă există deadlock la fiecare cinci secunde. Tranzacția care cade victimă este cea care are cea mai mică PRIORITATE DEADLOCK care se poate seta explicit la nivel de sesiune. Dacă ambele tranzacții au aceeași prioritate deadlock, atunci victima va fi tranzacția pentru care se face rollbackul cel mai ieftin. Cu alte cuvinte, tranzacția care a făcut cele mai puține schimbări în baza de date (cei mai puțini bytes scriși în logul bazei de date) va fi supusă unui rollback.
Activați trace flag 1222 : DBCC TRACEON(1222,-1) sau setați-l drept parametru de startup în SQL Server Configuration Manager. Informațiile sunt disponibile în SQL Server Error Log.
Folosind SQL Server Profiler / realizați un server side trace adăugând evenimentul: Locks: Deadlock Graph.
Folosind Extended Events: folosind evenimentul extins system_health sau creând unul și adăugând evenimente precum: lock_deadlock, lock_deadlock_chain or xml_deadlock_report.
Iată un exemplu de deadlock din SQL Server:
Acum că înțelegem ce este un deadlock, iată câteva scenarii care ne ajută să prevenim sau să reparăm acest deadlock:
Accesați obiectele în aceeași ordine logică. Să luăm exemplul de mai sus. În acest caz, soluția este ca Kid_1 și Kid_2 să primească ambii, în primul rând Phone și doar ulterior să solicite Headphone. În acest caz, deadlockul ar putea fi evitat, fiind redus la un simplu caz de locking unde 1 Kid îl așteaptă pe celălalt să termine, având ambele obiecte: Phone și Headphone. După ce primul Kid finalizează utilizarea lor, cealaltă entitate le va primi pe ambele.
Mențineți tranzacțiile pe cât de scurte posibil.
Modificați nivelul de izolare la valoarea SNAPSHOT ISOLATION.
Creați un covering index care să îndepărteze nevoia ca indexul clustered subiacent să fie modificat de operațiuni de tip select. Dacă deadlockul este cauzat de shared locks din cadrul unui select asupra unui subset redus de rânduri, atunci este posibil să mutați aceste noi elemente lock spre un nou index. Astfel, cele două interogări aflate în competiție nu trebuie să pună lock pe aceleași resurse.
Utilizați cod customizat pentru a rula din nou tranzacția aleasă drept victimă.