|
|
Postat pe 20.07.2016 12:37:53
|
|
|
Prezentare generală a încuietorilor
1. De ce să introduci lacătele
Când mai mulți utilizatori efectuează operații simultane asupra bazei de date în același timp, apar următoarele inconsistențe de date:
Actualizări lipsă
Doi utilizatori, A și B, citesc aceleași date și le modifică, iar rezultatul modificării unuia dintre utilizatori distruge rezultatul celeilalte modificări, cum ar fi sistemul de rezervare a biletelor
Lectură murdară
Utilizatorul A modifică datele, apoi utilizatorul B citește datele, dar utilizatorul A anulează modificarea datelor dintr-un motiv oarecare, iar datele revin la valoarea lor originală
Nu citi în mod repetat
Utilizatorul A citește datele, apoi utilizatorul B citește datele și le modifică
Principala metodă de control al concurenței este blocarea, care presupune interzicerea utilizatorilor să efectueze anumite operații pentru o perioadă de timp pentru a evita inconsistențele datelor
2. Clasificarea încuietorilor
Există două categorii de încuietori:
1 . Din perspectiva sistemului de baze de date: acesta este împărțit în blocaje exclusive (adică blocaje exclusive), blocaje partajate și blocaje de actualizare
MS - SQL Server folosește următoarele tipare de blocare a resurselor.
Descrierea modului de blocare
Share(s) este folosit pentru operații care nu modifică sau actualizează datele (operații doar pentru citire), cum ar fi instrucțiunile SELECT.
Actualizarea (U) este folosită în resurse actualizabile. Previne formele comune de blocaje atunci când mai multe sesiuni sunt citite, blocate și, posibil, o actualizare a resurselor care poate apărea.
Exclusive (X) este folosit pentru operațiuni de modificare a datelor, cum ar fi INSERT, UPDATE sau DELETE. Asigurați-vă că nu se efectuează mai multe actualizări simultan pe aceeași resursă.
Încuietorile de intenție sunt folosite pentru a stabili o ierarhie a încuietorilor. Tipurile de blocaje de intenție sunt: Intenție Partajată (IS), Exclusivă pentru Intenție (IX) și Exclusivă pentru Intenție (ȘASE).
Blocajele de schemă sunt folosite atunci când se efectuează operații care depind de schema tabelului. Tipurile de blocaje de schemă sunt: modificarea schemei (Sch -M) și stabilitatea schemei (Sch -S).
Actualizările în masă (BU-uri) sunt folosite atunci când volume mari de date sunt copiate într-un tabel și este specificat un indiciu TABLOCK.
Încuietori comune
Un blocaj partajat permite tranzacțiilor concurente să citească (SELECT) o resursă. Când există un blocaj partajat (S) pe o resursă, nicio altă tranzacție nu poate modifica datele. Eliberați blocajul partajat (S) al resursei imediat ce datele au fost citite, cu excepția cazului în care nivelul de izolare al tranzacției este setat la repetabil sau mai mare, sau blocajul partajat (S) este păstrat cu un indiciu de blocare pe durata de viață a tranzacției.
Actualizarea blocajului
Actualizarea (U) blocările previn blocajele în forma lor obișnuită. Un model tipic de actualizare constă într-o tranzacție care citește o înregistrare, obține un blocaj partajat (S) pentru o resursă (pagină sau rând) și apoi modifică un rând, ceea ce necesită ca blocajul să fie convertit într-un blocaj exclusiv (X). Dacă două tranzacții dobândesc un blocaj în mod partajat pe o resursă și încearcă să actualizeze datele în același timp, o tranzacție încearcă să convertească blocajul într-un blocaj exclusiv (X). Tranziția de la modul partajat la blocarea exclusivă trebuie să aștepte o perioadă deoarece blocarea exclusivă a unei tranzacții nu este compatibilă cu blocarea modului partajat a altei tranzacții; Are loc o așteptare la încuietoare. A doua tranzacție încearcă să obțină un blocaj exclusiv (X) pentru o actualizare. Un deadlock apare deoarece ambele tranzacții sunt convertite în blocaje exclusive (X), iar fiecare tranzacție așteaptă ca cealaltă tranzacție să elibereze blocajul în modul partajat.
Pentru a evita această posibilă problemă de blocaj, folosește o încuietoare actualizată (U). Doar o singură tranzacție la un moment dat poate primi un blocaj actualizat (U) pentru o resursă. Dacă tranzacția modifică resursa, blocajul update (U) este convertit într-un blocaj exclusiv (X). În caz contrar, lacătul este convertit într-un lacăt partajat.
Încuietori exclusive
Blocările exclusive (X) împiedică accesul la resurse ale tranzacțiilor concurente. Alte tranzacții nu pot citi sau modifica datele blocate de blocarea exclusivă (X).
Blocaj de intenție
Un blocaj de intenție indică faptul că SQL Server trebuie să achiziționeze un blocaj partajat (S) sau un blocaj exclusiv (X) asupra unor resurse subiacente din ierarhie. De exemplu, un blocaj de intenție de partajare plasat la nivelul tabelului indică faptul că tranzacția intenționează să plaseze un blocaj share(S) pe o pagină sau un rând din tabel. Setarea unui blocaj de intenție la nivelul tabelului împiedică o altă tranzacție să dobândească ulterior un blocaj exclusiv (X) pe tabelul care conține acea pagină. Blocările de intenție pot îmbunătăți performanța deoarece SQL Server verifică blocarea intenției doar la nivelul tabelului pentru a determina dacă o tranzacție poate achiziționa în siguranță un blocaj pe acel tabel. În loc să verifice blocajele fiecărui rând sau pagină din tabel pentru a determina dacă o tranzacție poate bloca întregul tabel.
Blocajele de intenție includ Partajarea Intenției (IS), Exclusivitatea Intenției (IX) și Partajarea Exclusivă a Intenției (SIX).
Descrierea modului de blocare
Partajarea intenției (IS) indică faptul că intenția tranzacției este unea, nu toate, resursele subiacente din ierarhia de citire, prin plasarea de blocaje S pe fiecare resursă.
Intent Exclusive (IX) indică faptul că intenția tranzacției este de a modifica unele, dar nu toate, resursele subiacente din ierarhie prin plasarea unui X-lock pe fiecare resursă. IX este un superset al IS.
Partajarea exclusivă cu intenție (SIX) indică faptul că intenția tranzacției este să citească toate resursele subiacente din ierarhie și să modifice unele, dar nu toate, resursele subiacente prin plasarea de blocaje IX pe fiecare resursă. Permite blocări IS concurente pe resurse de nivel superior. De exemplu, blocarea SIX a unui tabel plasează o blocare SIX pe tabel (permițând blocări IS concurente) și o blocare IX pe pagina modificată în prezent (o blocare X pe rândul modificat). Deși fiecare resursă poate avea doar un singur blocaj SIX pentru o perioadă de timp pentru a preveni actualizarea altor tranzacții, alte tranzacții pot citi resursele subiacente din ierarhie prin achiziționarea blocajelor IS la nivel de tabel.
Blocare exclusivă: Doar programul care efectuează operația de blocare are voie să o folosească, iar orice altă operație nu va fi acceptată. Când rulezi o comandă de actualizare a datelor, SQL Server folosește automat un blocaj exclusiv. Când există alte lacăte pe un obiect, nu poți adăuga un blocaj exclusiv la acesta.
Blocaj partajat: Resursa blocată de blocajul partajat poate fi citită de alți utilizatori, dar alți utilizatori nu o pot modifica.
Blocare de actualizare: Când SQL Server este gata să actualizeze datele, acesta blochează mai întâi obiectul de date astfel încât datele să nu poată fi modificate, dar pot fi citite. Când SQL Server determină că dorește să actualizeze datele, va înlocui automat blocajul de actualizare cu un blocaj exclusiv și nu poate adăuga un blocaj de actualizare atunci când există alte blocaje pe obiect.
2 . Din perspectiva programatorului: este împărțită în blocare optimistă și blocare pesimistă.
Optimism Lock: Se bazează în totalitate pe baza de date pentru a gestiona funcționarea lacătului.
Încuietori pesimiste: Programatorii gestionează gestionarea lacătelor pe date sau obiecte în sine.
MS - SQLSERVER folosește blocaje pentru a implementa un control pesimist al concurenței între mai mulți utilizatori care efectuează modificări în baza de date în același timp
3. Dimensiunea particulelor lacătului
Granularitatea blocajului este dimensiunea țintei blocate, granularitatea mică de blocare este o concurență mare, dar overhead este mare, iar granularitatea mare de blocare este o concurență mică, însă overhead este mic
SQL Server suportă granularitatea blocajelor pentru rânduri, pagini, chei, intervale de chei, indexuri, tabele sau baze de date
Descrierea resursei
Identificator de rând RID. Obișnuia să blocheze un rând într-un tabel individual.
Încuietoare a rândului de chei în index. Folosit pentru a proteja gama de chei în tranzacțiile serializabile.
8 kilobytes (KB) de pagini de date sau pagini index.
Disc extins Un set de opt pagini de date adiacente sau pagini index.
Tabel: Tabelul complet, incluzând toate datele și indexurile.
Baza de date a bazei de date.
4. Durata perioadei de blocare
Durata în care o încuietoare este durata necesară pentru a proteja resursa la nivelul solicitat.
Timpul de reținere al blocării partajate folosite pentru a proteja operațiunile de citire depinde de nivelul de izolare al tranzacției. Cu nivelul implicit de izolare a tranzacțiilor READ COMMITTED, blocarea partajată este controlată doar pe durata paginii de citire. Într-o scanare, lacătul nu este eliberat până când nu este obținut pe pagina următoare din scanare. Dacă specifici un prompt HOLDLOCK sau setezi nivelul de izolare al tranzacției pe REPEATABLE READ sau SERIALIZABLE, blocajul nu este eliberat până la finalul tranzacției.
În funcție de opțiunea de concurență setată pentru cursor, cursorul poate obține un blocaj de derulare în modul partajat pentru a proteja extragerea. Când este necesar un blocaj de derulare, acesta nu este eliberat până la următorul moment în care cursorul este extras sau închis, oricare dintre acestea apare prima. Totuși, dacă specifici un HOLDLOCK, blocajul de derulare nu este eliberat decât la finalul tranzacției.
Blocajul exclusiv folosit pentru a proteja actualizarea nu va fi eliberat până la finalul tranzacției.
Dacă o conexiune încearcă să obțină un blocaj care intră în conflict cu un blocat controlat de o altă conexiune, conexiunea care încearcă să obțină blocajul va fi blocată până când:
Blocajul conflictual este eliberat și conexiunea dobândește blocajul cerut.
Expirarea conexiunii a expirat. Nu există un interval de timeout implicit, dar unele aplicații setează intervale de timeout pentru a preveni așteptarea nedefinită
Cinci personalizări ale lacătelor în SQL Server
1 Gestionarea blocajelor și stabilirea priorităților blocajelor
Blocajul este așteptarea nesfârșită cauzată de mai mulți utilizatori care aplică pentru blocări diferite, deoarece solicitantul are o parte din dreptul de blocare și așteaptă blocarea parțială deținută de alți utilizatori
Poți folosi DEADLOCK_PRIORITY SET pentru a controla modul în care reacționează sesiunea în cazul unei condiții de blocaj. Dacă ambele procese blochează datele, iar fiecare proces nu poate elibera propriul blocaj până când celălalt nu își eliberează propriul blocaj, apare o situație de blocaj.
2 Gestionează timeout-urile și setează duratele timeout-urilor de blocare.
@@LOCK_TIMEOUT returnează setarea curentă de timeout pentru blocarea sesiunii curente în milisecunde
Setarea LOCK_TIMEOUT SET permite aplicației să seteze timpul maxim de așteptare al instrucțiunii pentru a bloca resursa. Când timpul de așteptare al instrucțiunii este mai mare decât setarea LOCK_TIMEOUT, sistemul anulează automat instrucțiunea de blocare și returnează aplicației un mesaj de eroare 1222 indicând că perioada de timeout pentru cererea de blocare a fost depășită
exemplu
În exemplul următor, perioada de timeout a blocajului este setată la 1.800 milisecunde.
SETUL LOCK_TIMEOUT1800
3) Setarea nivelului de izolare a tranzacțiilor.
4) Folosește indicii de blocare la nivel de tabel pentru instrucțiunile SELECT, INSERT, UPDATE și DELETE.
5) Configurarea granularității de blocare a indexului
Poți folosi sp_indexoption proceduri stocate de sistem pentru a seta granularitatea blocajului pentru indexare
6. Vizualizarea informațiilor despre lacăt
1 Efectuați EXEC SP_LOCK raportați informații despre încuietoare
2 Apasă Ctrl + 2 în analizorul de interogări pentru a vedea informațiile despre blocat
7. Precauții pentru utilizare
Cum să eviți blocajele
1. Când se folosesc tranzacții, încercați să scurtați procesul logic de procesare a tranzacțiilor și să trimiteți sau să anulați tranzacțiile din timp.
2 Setează parametrul de timeout pentru blocaj la un interval rezonabil, cum ar fi: 3 minute - 10 minute; După acest timp, operațiunea va fi abandonată automat pentru a evita blocarea procesului;
3. Optimizarea programului, verificarea și evitarea fenomenului blocajului;
4. Testează cu atenție toate scripturile și SP-urile înainte de versiunea exactă.
5 Toate SP-urile trebuie să aibă gestionare a erorilor (prin @error)
6 Nu modificați nivelul implicit al tranzacțiilor SQL SERVER. Blocarea forțată nu este recomandată
Rezolvă problema Cum să blochezi o bază de date cu tabele de rânduri
8. Câteva întrebări despre încuietori
1 Cum să blochezi un rând de masă
SETEAZĂ NIVELUL TRANSACTIONISOLATION READUNCOMMITTED
SELECT *FROM table ROWLOCKWHERE id = 1
2 Blochează un tabel în baza de date
SELECT *DE la tabelul CU( HOLDLOCK )
Declarație de blocare:
sybase:
Tabel de actualizare col1 = col1 unde 1= 0 ;
MSSQL:
selectați col1din tabelul (tablockx)unde 1= 0 ;
oracle:
TABELĂ DE BLOCARE ÎN MOD EXCLUSIV ;
După ce blocarea este blocată, nimeni altcineva nu o poate folosi până când utilizatorul blocat nu o deblochează, iar aceasta este deblocată prin commit sau rollback
Câteva exemple te ajută să-ți adâncești impresia
Tabel de aranjare1(A,B,C)
A B C
a1 b1 c1
A2 B2 C2
A3 B3 C3
1) Blocare exclusivă
Creează două conexiuni noi
Executați următoarea instrucțiune în prima conexiune
Încep tranziția
Tabelul actualizării1
set A= ' aa '
unde B= ' b2 '
așteaptă întârziere' 00:00:30' --așteaptă 30 de secunde
commit tran
Executați următoarea instrucțiune în a doua conexiune
Încep tranziția
selectează *din tabel1
unde B= ' b2 '
commit tran
Dacă cele două instrucțiuni de mai sus sunt executate simultan, interogarea select trebuie să aștepte executarea actualizării, adică să aștepte 30 de secunde
2) Încuietoare partajată
Executați următoarea instrucțiune în prima conexiune
Încep tranziția
selectează *din tabelul 1 blocaj de rezervă - Blocajul de rezervă este adăugat artificial la încuietoare
unde B= ' b2 '
așteaptă întârziere' 00:00:30' --așteaptă 30 de secunde
commit tran
Executați următoarea instrucțiune în a doua conexiune
Încep tranziția
selectați A,C din tabelul 1
unde B= ' b2 '
Tabelul actualizării1
set A= ' aa '
unde B= ' b2 '
commit tran
Dacă cele două instrucțiuni de mai sus sunt executate simultan, interogarea select din a doua conexiune poate fi executată
Actualizarea trebuie să aștepte prima tranzacție pentru a elibera blocajul partajat și să îl convertească într-un blocaj exclusiv înainte de a putea fi executat, adică să aștepte 30 de secunde
3) Blocaj
Tabel2(D,E) adăugat
D E
d1 e1
d2 e2
Executați următoarea instrucțiune în prima conexiune
Încep tranziția
Tabelul actualizării1
set A= ' aa '
unde B= ' b2 '
așteaptă întârziere' 00:00:30'
Tabelul de actualizare2
set D= ' d5 '
unde E= ' e1 '
commit tran
Executați următoarea instrucțiune în a doua conexiune
Încep tranziția
Tabelul de actualizare2
set D= ' d5 '
unde E= ' e1 '
așteaptă întârziere' 00:00:10'
Tabelul actualizării1
set A= ' aa '
unde B= ' b2 '
commit tran
În același timp, sistemul detectează blocajul și întrerupe procesul
Pentru a adăuga:
Indicii de blocare la nivel de tabel suportate de SQL Server 2000
HOLDLOCK menține blocajul partajat până la finalizarea întregii tranzacții și ar trebui să fie eliberat imediat ce obiectul blocat nu mai este necesar, egal cu nivelul de izolare al tranzacțiilor SERIALIZABILE
Instrucțiunea NOLOCK este executată fără a emite un blocaj partajat, permițând citiri murdare, care este egal cu nivelul de izolare al tranzacțiilor READ UNCOMMITTED
PAGLOCK folosește multiple blocări de pagină unde se folosește un blocaj de tabel
READPAST permite serverului sql să sară peste orice linii blocate și să execute tranzacții, iar pentru nivelurile de izolare a tranzacțiilor READ UNCOMMITTED, să sară doar blocările RID, nu și blocările de pagină, zonă și tabel
ROWLOCK impune utilizarea rowlock-urilor
TABLOCKX impune utilizarea unui blocaj exclusiv la nivel de tabel, care împiedică orice altă tranzacție să folosească tabelul în timpul tranzacției
UPLOCK forțează utilizarea actualizărilor atunci când citești un tabel fără blocare partajată
Blocarea aplicației:
Un blocaj de aplicație este un blocaj generat de codul clientului, nu un blocaj generat de SQL Server în sine
Două procese pentru gestionarea blocărilor aplicațiilor
sp_getapplock Resurse pentru aplicații blocate
sp_releaseapplock Deblochează resursele aplicației
Notă: Diferența dintre blocarea unui tabel într-o bază de date
SELECT *FROM tabelul WITH( HOLDLOCK ) Alte tranzacții pot citi tabelul, dar nu pot actualiza și șterge
SELECT *FROM tabelul WITH(TABLOCKX) Alte tranzacții nu pot citi, actualiza și șterge tabelul
|
Precedent:Nu exista niciun punct final care să asculte la http://localhost:111/xxx.svc acel c...Următor:Blocări SQL NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
