ABONAMENTE VIDEO REDACȚIA
RO
EN
×
▼ LISTĂ EDIȚII ▼
Numărul 113
Abonament PDF

Cum ajungem la Deadlock

Alexandru Crăciun
Senior Database Engineer @ Cognizant Softvision
PROGRAMARE

Î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.

Proprietățile ACID

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.

Nivelele de izolare

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.

Problemele de concurență

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ță:

Tipologia nivelelor de izolare

Tranzacția Read Uncommitted

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).

Tranzacția Read Committed

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.

Tranzacția Repeatable Read

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.

Tranzacția Serializabilă

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ă.

Tranzacția Snapshot

Î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 și blocaj

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.

Read 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.

Write Locks

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.

Resurse ce pot fi locked

RID (Row Identifier/Identificator de rând) - un rând din cadrul unei tabele de tip heap;

Moduri Lock

Escalarea de tip Lock (Lock Escalation)

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ă.

Modificarea manuală a escalării de tip Lock

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 >1000

Puteț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.

Deadlocks

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.

Cum se alege victima?

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.

Informații despre Deadlocks în SQL Server

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:

Soluții comune pentru a evita fenomenele de tip deadlock

Acum că înțelegem ce este un deadlock, iată câteva scenarii care ne ajută să prevenim sau să reparăm acest deadlock:

Utilizați cod customizat pentru a rula din nou tranzacția aleasă drept victimă.

VIDEO: NUMĂRULUI 113

Sponsori

  • Accenture
  • Bosch
  • ntt data
  • Betfair
  • FlowTraders
  • MHP
  • Connatix
  • BoatyardX
  • metro.digital
  • Colors in projects