Blocarea este esențială pentru procesarea cu succes a tranzacțiilor SQL Server și este concepută pentru a permite SQL Server să funcționeze fără probleme într-un mediu cu mai mulți utilizatori. Blocarea este modul în care SQL Server gestionează concurența tranzacțiilor. În esență, încuietorile sunt structuri în memorie care au proprietari, tipuri și hash-ul resursei pe care trebuie să o protejeze. O blocare ca structură în memorie are o dimensiune de 96 de octeți.
Pentru a înțelege mai bine blocarea în SQL Server, este important să înțelegem că blocarea este concepută pentru a asigura integritatea datelor din baza de date, deoarece forțează fiecare tranzacție SQL Server să treacă testul ACID.
Testul ACID constă în 4 cerințe pe care fiecare tranzacție trebuie să le treacă cu succes:
- Atomicitate – impune ca o tranzacție care implică două sau mai multe părți discrete de informații să confirme toate părțile sau niciuna
- Consistență – impune ca o tranzacție să creeze o stare validă de date noi sau să readucă toate datele la starea existentă înainte ca tranzacția să fie executată
- Izolare – impune ca o tranzacție care este încă în desfășurare și nu a confirmat încă toate datele, trebuie să rămână izolată de toate celelalte tranzacții
- Durabilitate – impune ca datele angajate să fie stocate folosind o metodă care va păstra toate datele în stare corectă și disponibile pentru un utilizator, chiar și în cazul unui eșec
Blocarea SQL Server este partea esențială a cerinței de izolare și are rolul de a bloca obiectele afectate de o tranzacție. În timp ce obiectele sunt blocate, SQL Server va împiedica alte tranzacții să efectueze orice modificare a datelor stocate în obiectele afectate de blocarea impusă. Odată ce blocajul este eliberat prin confirmarea modificărilor sau prin revenirea modificărilor la starea inițială, altor tranzacții li se va permite să efectueze modificările de date necesare.
Tradus în limbajul SQL Server, aceasta înseamnă că, atunci când o tranzacție impune blocarea unui obiect, toate celelalte tranzacții care necesită accesul la acel obiect vor fi obligate să aștepte până la eliberarea blocării, iar această așteptare va fi înregistrată cu tipul de așteptare adecvat
Blocajele SQL Server pot fi specificate prin intermediul modurilor de blocare și al granularității de blocare
Moduri de blocare
Modul de blocare ia în considerare diferite tipuri de blocare care pot fi aplicate unei resurse care trebuie să fie blocată:
- Exclusiv (X)
- Partajat (S)
- Actualizare (U)
- Intenție (I)
- Schema (Sch)
- Actualizare în bloc (BU)
Blocare exclusivă (X) – Acest tip de blocare, atunci când este impus, va asigura că o pagină sau un rând va fi rezervat exclusiv pentru tranzacția care a impus blocarea exclusivă, atâta timp cât tranzacția deține blocarea.
Blocajul exclusiv va fi impus de către tranzacție atunci când aceasta dorește să modifice datele paginii sau ale rândului, ceea ce se întâmplă în cazul instrucțiunilor DML DELETE, INSERT și UPDATE. O blocare exclusivă poate fi impusă unei pagini sau unui rând numai dacă nu există o altă blocare partajată sau exclusivă impusă deja pe țintă. Practic, aceasta înseamnă că se poate impune o singură blocare exclusivă pentru o pagină sau un rând, iar odată impusă, nicio altă blocare nu mai poate fi impusă pe resursele blocate
Shared lock (S) – acest tip de blocare, atunci când este impus, va rezerva o pagină sau un rând pentru a fi disponibil numai pentru citire, ceea ce înseamnă că orice altă tranzacție nu va putea modifica înregistrarea blocată atâta timp cât blocarea este activă. Cu toate acestea, un blocaj partajat poate fi impus de mai multe tranzacții în același timp pentru aceeași pagină sau rând și, în acest fel, mai multe tranzacții pot împărți capacitatea de citire a datelor, deoarece procesul de citire în sine nu va afecta în niciun fel datele actuale ale paginii sau rândului. În plus, un blocaj partajat va permite operațiuni de scriere, dar nu va fi permisă nici o modificare DDL
Blocaj de actualizare (U) – acest blocaj este similar cu un blocaj exclusiv, dar este conceput pentru a fi mai flexibil într-un fel. O blocare de actualizare poate fi impusă pe o înregistrare care are deja o blocare partajată. În acest caz, blocajul de actualizare va impune un alt blocaj partajat pe rândul țintă. În momentul în care tranzacția care deține un blocaj de actualizare este pregătită să modifice datele, blocajul de actualizare (U) va fi transformat într-un blocaj exclusiv (X). Este important să se înțeleagă că blocarea actualizării este asimetrică în ceea ce privește blocările partajate. În timp ce blocajul de actualizare poate fi impus unei înregistrări care are un blocaj partajat, blocajul partajat nu poate fi impus unei înregistrări care are deja un blocaj de actualizare
Blocaje de intenție (I) – acest blocaj este un mijloc utilizat de o tranzacție pentru a informa o altă tranzacție despre intenția sa de a obține un blocaj. Scopul unui astfel de blocaj este de a asigura executarea corectă a modificării datelor, împiedicând o altă tranzacție să dobândească un blocaj asupra obiectului următor în ierarhie. În practică, atunci când o tranzacție dorește să obțină un blocaj pe un rând, aceasta va obține un blocaj de intenție pe un tabel, care este un obiect ierarhic superior. Prin dobândirea blocării intenționate, tranzacția nu va permite altor tranzacții să dobândească blocarea exclusivă a tabelului respectiv (în caz contrar, blocarea exclusivă impusă de o altă tranzacție ar anula blocarea rândului).
Acesta este un tip de blocare important din punct de vedere al performanței, deoarece motorul bazei de date SQL Server va inspecta blocările de intenție numai la nivelul tabelului pentru a verifica dacă este posibil ca o tranzacție să dobândească o blocare în mod sigur în acel tabel și, prin urmare, blocarea de intenție elimină necesitatea de a inspecta fiecare blocare de rând/pagină dintr-un tabel pentru a se asigura că tranzacția poate dobândi o blocare pe întregul tabel
Există trei blocări de intenție obișnuite și trei așa-numite blocări de conversie:
Încuietori de intenție obișnuite:
Intenție exclusivă (IX) – atunci când se achiziționează o blocare exclusivă de intenție (IX), aceasta indică SQL Server că tranzacția are intenția de a modifica unele dintre resursele ierarhice inferioare prin achiziționarea de blocaje exclusive (X) în mod individual pe acele resurse ierarhice inferioare
Intenție partajată (IS) – atunci când se achiziționează o blocare partajată de intenție (IS), aceasta indică SQL Server că tranzacția a intenția de a citi unele resurse ierarhice inferioare prin achiziționarea de încuietori partajate (S) în mod individual asupra acelor resurse inferioare în ierarhie
Intent update (IU) – atunci când se achiziționează un intent shared lock (IS), acesta indică SQL Server că tranzacția are intenția de a citi unele resurse ierarhice inferioare prin achiziționarea de încuietori partajate (S) în mod individual asupra acelor resurse inferioare în ierarhie. Blocajul cu intenție de actualizare (IU) poate fi dobândit numai la nivel de pagină și, de îndată ce are loc operațiunea de actualizare, se convertește în blocaj exclusiv de intenție (IX)
Blocaje de conversie:
Blocaj partajat cu intenție exclusivă (SIX) – atunci când este dobândit, acest blocaj indică faptul că tranzacția intenționează să citească toate resursele de la un nivel ierarhic inferior și, prin urmare, să dobândească blocajul partajat pe toate resursele care se află mai jos în ierarhie și, la rândul său, să modifice o parte dintre acestea, dar nu pe toate. În acest fel, va dobândi un blocaj exclusiv de intenție (IX) asupra acelor resurse din ierarhia inferioară care trebuie modificate. În practică, acest lucru înseamnă că, odată ce tranzacția dobândește un blocaj SIX asupra tabelului, aceasta va dobândi un blocaj exclusiv de intenție (IX) asupra paginilor modificate și un blocaj exclusiv (X) asupra rândurilor modificate.
Doar un singur blocaj partajat cu intenție de blocare exclusivă (SIX) poate fi achiziționat pe o tabelă la un moment dat și va bloca alte tranzacții să facă actualizări, dar nu va împiedica alte tranzacții să citească resursele ierarhice inferioare; acestea pot achiziționa blocajul partajat cu intenție (IS) pe tabel
Partajat cu intenție de actualizare (SIU) – acesta este un blocaj ceva mai specific, deoarece este o combinație între blocajul partajat (S) și blocajul cu intenție de actualizare (IU). Un exemplu tipic al acestui blocaj este atunci când o tranzacție utilizează o interogare executată cu indicația PAGELOCK și interogarea, apoi interogarea de actualizare. După ce tranzacția dobândește un blocaj SIU pe tabel, interogarea cu indicația PAGELOCK va dobândi blocajul partajat (S), în timp ce interogarea de actualizare va dobândi blocajul de actualizare cu intenție (IU)
Update with intent exclusive (UIX) – atunci când blocajul de actualizare (U) și blocajul de actualizare cu intenție exclusivă (IX) sunt dobândite simultan la resursele ierarhic inferioare din tabel, ca o consecință, la nivelul tabelului se va achiziționa un blocaj de actualizare cu intenție exclusivă
Schema locks (Sch) – Motorul bazei de date SQL Server recunoaște două tipuri de blocaje de schemă: Blocajul de modificare a schemei (Sch-M) și Blocajul de stabilitate a schemei (Sch-S)
- Un blocaj de modificare a schemei (Sch-M) va fi dobândit atunci când se execută o instrucțiune DDL și va împiedica accesul la datele obiectului blocat pe măsură ce structura obiectului este modificată. SQL Server permite o singură blocare de blocare a modificării schemei (Sch-M) pe orice obiect blocat. Pentru a modifica o tabelă, o tranzacție trebuie să aștepte să dobândească o blocare Sch-M pe obiectul țintă. Odată ce dobândește blocarea de modificare a schemei (Sch-M), tranzacția poate modifica obiectul, iar după ce modificarea este finalizată, blocarea va fi eliberată. Un exemplu tipic de blocare Sch-M este reconstrucția unui index, deoarece reconstrucția unui index este un proces de modificare a unei tabele. Odată ce ID-ul de reconstruire a indexului este emis, un blocaj de modificare a schemei (Sch-M) va fi achiziționat pe acel tabel și va fi eliberat numai după ce procesul de reconstruire a indexului este finalizat (atunci când este utilizat cu opțiunea ONLINE, reconstrucția indexului va achiziționa un blocaj Sch-M la scurt timp la sfârșitul procesului)
- Un blocaj de stabilitate a schemei (Sch-S) va fi achiziționat în timp ce o interogare dependentă de schemă este compilată și executată și planul de execuție este generat. Această blocare specială nu va bloca accesul altor tranzacții la datele obiectului și este compatibilă cu toate modurile de blocare, cu excepția blocării pentru modificarea schemei (Sch-M). În esență, blocajele de stabilitate a schemei vor fi achiziționate de fiecare interogare DML și de fiecare interogare de selectare pentru a asigura integritatea structurii tabelului (pentru a se asigura că tabelul nu se modifică în timp ce se execută interogări).
Bulk Update locks (BU) – această blocare este concepută pentru a fi utilizată de operațiunile de import în masă atunci când este emisă cu un argument/indicator TABLOCK. Atunci când se achiziționează un blocaj de actualizare în bloc, alte procese nu vor putea accesa un tabel în timpul execuției de încărcare în bloc. Cu toate acestea, un blocaj de actualizare în bloc nu va împiedica procesarea în paralel a unei alte încărcări în bloc. Țineți însă cont de faptul că utilizarea TABLOCK pe o tabelă de indexare grupată nu va permite efectuarea în paralel a importului în bloc. Mai multe detalii despre acest lucru sunt disponibile în Guidelines for Optimizing Bulk Import
Locking hierarchy
SQL Server a introdus ierarhia de blocare care se aplică atunci când se efectuează citirea sau modificarea datelor. Ierarhia de blocare începe cu baza de date la cel mai înalt nivel ierarhic și coboară prin tabel și pagină până la rândul de la cel mai mic nivel
În esență, există întotdeauna o blocare partajată la nivelul bazei de date care este impusă ori de câte ori o tranzacție este conectată la o bază de date. Blocajul partajat la nivelul bazei de date este impus pentru a preveni abandonarea bazei de date sau restaurarea unei copii de rezervă a bazei de date peste baza de date în uz. De exemplu, atunci când o instrucțiune SELECT este emisă pentru a citi niște date, se va impune un blocaj partajat (S) la nivelul bazei de date, un blocaj partajat de intenție (IS) va fi impus la nivel de tabel și la nivel de pagină și un blocaj partajat (S) pe rândul propriu-zis
În cazul unei instrucțiuni DML (de ex. insert, update, delete) se va impune un blocaj partajat (S) la nivelul bazei de date, un blocaj exclusiv de intenție (IX) sau un blocaj de intenție de actualizare (IU) la nivel de tabel și la nivel de pagină și un blocaj exclusiv sau de actualizare (X sau U) pe rând
Blocajele vor fi întotdeauna achiziționate de sus în jos, deoarece în acest fel SQL Server previne apariția așa-numitei condiții Race.
Acum că au fost explicate modurile de blocare și ierarhia de blocare, haideți să dezvoltăm în continuare modurile de blocare și modul în care acestea se traduc într-o ierarhie de blocare.
Nu toate modurile de blocare pot fi aplicate la toate nivelurile.
La nivel de rând, se pot aplica următoarele trei moduri de blocare:
- Exclusive (X)
- Shared (S)
- Update (U)
Pentru a înțelege compatibilitatea acestor moduri, vă rugăm să consultați următorul tabel:
| Exclusiv (X) | Partajat (S) | Actualizare (U) | |
| Exclusiv (X) | ✗ | ✗ | ✗ |
| Partajat (S) | ✗ | ✓ | ✓ |
| Actualizare (U) | ✗ | ✓ | ✗ |
✓ – Compatibil ✗ – Incompatibil
La nivel de tabel, există cinci tipuri diferite de blocaje:
- Exclusiv (X)
- Partajat (S)
- Exclusiv cu intenție (IX)
- Partajat cu intenție (IS)
- Partajat cu intenție exclusivă (SIX)
Compatibilitatea acestor moduri poate fi observată în tabelul de mai jos
.
| (X) | (S) | (IX) | (IS) | (SASE) | (ȘASE) | |
| . (X) | ✗ | ✗ | ✗ | ✗ | ✗ | |
| (S) | ✗ | ✓ | ✗ | ✓ | ✗ | |
| (IX) | ✗ | ✗ | ✗ | ✓ | ✓ | ✗ |
| (IS) | ✗ | ✓ | ✓ | ✓ | . ✓ | ✓ |
| (ȘASE) | ✗ | ✗ | ✗ | ✗ | ✓ | . ✗ |
✓ – Compatibil ✗ – Incompatibil
O blocare de schemă (Sch) este, de asemenea, o blocare la nivel de tabel, dar nu este un blocaj legat de date
Pentru a înțelege mai bine compatibilitatea dintre aceste tipuri de blocare, vă rugăm să consultați acest tabel:
Lock escalation
Pentru a preveni o situație în care blocarea utilizează prea multe resurse, SQL Server a introdus funcția de escaladare a blocării.
Fără escaladare, încuietorile ar putea necesita o cantitate semnificativă de resurse de memorie. Să luăm un exemplu în care ar trebui impusă o blocare pe cele 30.000 de rânduri de date, în care fiecare rând are o dimensiune de 500 de octeți, pentru a efectua operația de ștergere. Fără escaladare, se va impune un blocaj partajat (S) asupra bazei de date, 1 blocaj exclusiv de intenție (IX) asupra tabelului, 1 875 de blocaje exclusive de intenție (IX) asupra paginilor (o pagină de 8 KB conține 16 rânduri de 500 de octeți, ceea ce înseamnă că 1 875 de pagini conțin 30 000 de rânduri) și 30 000 de blocaje exclusive (X) asupra rândurilor în sine. Având în vedere că fiecare blocare are o dimensiune de 96 de octeți, 31.877 de blocări vor ocupa aproximativ 3 MB de memorie pentru o singură operațiune de ștergere. Rularea unui număr mare de operații în paralel ar putea necesita unele resurse semnificative doar pentru a se asigura că managerul de blocare poate efectua operația fără probleme
Pentru a preveni o astfel de situație, SQL Server utilizează escaladarea de blocare. Aceasta înseamnă că, în situația în care mai mult de 5.000 de blocaje sunt achiziționate la un singur nivel, SQL Server va escalada aceste blocaje la un singur blocaj la nivel de tabel. În mod implicit, SQL Server va escalada întotdeauna direct la nivel de tabel, ceea ce înseamnă că escaladarea la nivel de pagină nu are loc niciodată. În loc să dobândească numeroase blocaje de rânduri și pagini, SQL Server va escalada la blocajul exclusiv (X) la nivel de tabel
Deși acest lucru va reduce nevoia de resurse, blocajele exclusive (X) într-un tabel înseamnă că nicio altă tranzacție nu va putea accesa tabelul blocat și toate interogările care încearcă să acceseze acel tabel vor fi blocate. Prin urmare, acest lucru va reduce cheltuielile generale ale sistemului, dar va crește probabilitatea de contenție a concurenței
Pentru a oferi control asupra escaladării, începând cu SQL Server 2008 R2, opțiunea LOCK_EXCALATION este introdusă ca parte a instrucțiunii ALTER TABLE
Fiecare dintre aceste opțiuni este definită pentru a permite un control specific asupra procesului de escaladare a blocării:
Table – Aceasta este opțiunea implicită pentru orice tabel nou creat, deoarece în mod implicit SQL Server va executa întotdeauna escaladarea încuietorii la nivel de tabel, ceea ce include și tabelele partiționate
Auto – Această opțiune permite escaladarea încuietorii la un nivel de partiție atunci când un tabel este partiționat. Atunci când se achiziționează 5.000 de încuietori într-o singură partiție, escaladarea încuietorii va achiziționa o blocare exclusivă (X) pe acea partiție, în timp ce tabela va achiziționa o blocare exclusivă de intenție (IX). În cazul în care tabelul respectiv nu este partiționat, escaladarea încuietorilor va achiziționa încuietori la nivel de tabel (egal cu opțiunea Table).
Deși pare o opțiune foarte utilă, ea trebuie utilizată cu mare atenție, deoarece poate provoca cu ușurință un blocaj (deadlock). În situația în care avem două tranzacții pe două partiții în care este achiziționat blocajul exclusiv (X), iar tranzacțiile încearcă să acceseze data de pe partiția folosită de cealaltă tranzacție, se va întâlni un deadlock
Deci, este foarte important să se controleze cu atenție modelul de accesare a datelor, dacă această opțiune este activată, ceea ce nu este ușor de realizat și acesta este motivul pentru care această opțiune nu face parte din setările implicite în SQL Server
Disable – Această opțiune va dezactiva complet escaladarea blocării pentru o tabelă. Din nou, această opțiune trebuie utilizată cu atenție pentru a evita ca managerul de blocare SQL Server să fie forțat să utilizeze o cantitate excesivă de memorie
După cum se poate observa, escaladarea de blocare ar putea fi o provocare pentru DBA. În cazul în care proiectarea aplicației necesită ștergerea sau actualizarea a mai mult de 5.000 de rânduri deodată, o soluție pentru a evita escaladarea încuietorii și efectele rezultate este împărțirea tranzacției unice în două sau mai multe tranzacții în care fiecare va gestiona mai puțin de 5.000 de rânduri, deoarece în acest fel escaladarea încuietorii ar putea fi eludată
Obțineți informații despre încuietorile active din SQL Server
SQL Server oferă sistemul Dynamics Management View (DMV) sys.dm_tran_locks care returnează informații despre resursele de gestionare a încuietorilor care sunt utilizate în prezent, ceea ce înseamnă că va afișa toate încuietorile „active” achiziționate de tranzacții. Mai multe detalii despre această DMV pot fi găsite în articolul sys.dm_tran_locks (Transact-SQL).
Cele mai importante coloane utilizate pentru identificarea blocajului sunt resource_type, request_mode și resource_description. Dacă este necesar, mai multe coloane ca resursă suplimentară pentru informații pot fi incluse în timpul depanării
Iată un exemplu de interogare
Clauza where din această interogare este utilizată ca filtru asupra tipului de resursă care trebuie eliminat. din rezultate, cele care sunt, în general, încuietori partajate achiziționate pe baza de date, deoarece acestea sunt întotdeauna prezente la nivelul bazei de date
O scurtă explicație a celor trei coloane prezentate aici:
resource_type – Afișează o resursă de bază de date în care sunt achiziționate încuietorile. Coloana poate afișa una dintre următoarele valori: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID
request_mode – afișează modul de blocare care este achiziționat pe resursă
resource_description – afișează o scurtă descriere a resursei și nu este completată pentru toate modurile de blocare. Cel mai adesea, coloana conține id-ul rândului, paginii, obiectului, fișierului, etc
- Autor
- Recent Posts
Dezvotat de aviația militară și modelator de aeronave la scară redusă hard core. Fan al sporturilor extreme; parașutist și instructor de bungee jumping. Cândva serios, acum doar un fotograf de timp liber
Vezi toate postările lui Nikola Dimitrijevic
- SQL Server trace flags guide; de la -1 la 840 – 4 martie 2019
- Cum se gestionează tipul de așteptare SQL Server WRITELOG – 13 iunie 2018
- Contoare de performanță SQL Server (Batch Requests/sec sau Transactions/sec): ce să monitorizați și de ce – 5 iunie 2018