tabel SQL Server sugestii sunt un tip special de explicită în comandă, care este utilizat pentru a suprascrie comportamentul prestabilit al SQL Server query optimizer în T-SQL de execuție interogare Acest lucru este realizat prin aplicarea o anumită metodă de blocare, un anumit index sau interogare operațiune de prelucrare, astfel index de căutare sau de masă de scanare, pentru a fi utilizate de către SQL Server query optimizer pentru a construi planul de execuție interogare., Sugestiile tabelului pot fi adăugate la clauza FROM a interogării T-SQL, afectând tabelul sau vizualizarea la care se face referire numai în clauza FROM.
una dintre cele mai utilizate indicii de tabel în instrucțiunile SELECT T-SQL este cu (nolock) indiciu. Nivelul implicit de izolare a tranzacției în SQL Server este nivelul de izolare READ COMMITTED, în care recuperarea datelor în schimbare va fi blocată până când aceste modificări sunt comise., Cu (nolock) indiciu tabel este folosit pentru a trece peste nivelul implicit de izolare tranzacție din tabel sau tabelele din vizualizarea într-o interogare specifică, permițând utilizatorului pentru a prelua datele fără a fi afectate de încuietori, pe datele solicitate, din cauza unui alt proces care se schimbă. În acest fel, interogarea va consuma mai puțină memorie în menținerea blocărilor împotriva acestor date. În plus, nu se va produce niciun impas împotriva interogărilor, care solicită aceleași date din tabelul respectiv, permițând un nivel mai ridicat de concurență datorită unei amprente mai mici., Cu alte cuvinte, indiciul cu (NOLOCK) preia rândurile fără a aștepta celelalte interogări, care citesc sau modifică aceleași date, pentru a termina procesarea. Acest lucru este similar cu nivelul de izolare a tranzacțiilor citite, care permite interogării să vadă modificările de date înainte de a comite tranzacția care o schimbă. Nivelul de izolare a tranzacției poate fi setat global la nivelul conexiunii folosind comanda SET TRANSACTION ISOLATION LEVEL T-SQL, așa cum se va vedea mai târziu în acest articol.,deși indiciul tabelului nolock, similar cu toate celelalte indicii de tabel, poate fi utilizat fără a utiliza cuvântul cheie WITH, Microsoft a anunțat că omiterea cuvântului cheie WITH este o caracteristică învechită și va fi eliminată din viitoarele versiuni Microsoft SQL Server. Acestea fiind spuse, este mai bine să includeți cuvântul cheie cu atunci când specificați sugestiile tabelului. Un beneficiu al utilizării cuvântului cheie cu este că puteți specifica mai multe indicii de tabel folosind cuvântul cheie cu același tabel.
în general, utilizarea sugestiilor de masă explicite frecvent este considerată o practică proastă pe care ar trebui să o evitați în general., Pentru NOLOCK masă indiciu concret, citire date nevalidate care ar putea fi revenit după ce au citit aceasta poate duce la o citire Murdar, care pot apărea atunci când citirea datelor, care este de a fi modificate sau șterse în timpul neangajate datele citite, astfel încât datele pe care le citesc ar putea fi diferite, sau nici măcar nu au existat.
indiciul tabelului WITH (NOLOCK) duce, de asemenea, la citiri Nerepetabile; această citire apare atunci când este necesar să citiți aceleași date de mai multe ori și datele se schimbă în timpul acestor lecturi. În acest caz, veți citi mai multe versiuni ale aceluiași rând.,
Phantom citește poate fi, de asemenea, un rezultat al utilizării cu(nolock) indiciu tabel, în care veți obține mai multe înregistrări atunci când tranzacția care introduce noi înregistrări este rulat înapoi, sau mai puține înregistrări atunci când tranzacția care șterge datele existente este rulat înapoi. O altă problemă care poate apărea atunci când alte tranzacții mută datele pe care nu le-ați citit încă într-o locație pe care ați scanat-o deja sau ați adăugat pagini noi în locația pe care ați scanat-o deja. În acest caz, veți pierde aceste înregistrări și nu le veți vedea în rezultatul returnat., Dacă o altă tranzacție mută datele pe care le-ați scanat deja într-o locație nouă pe care nu ați citit-o încă, veți citi datele de două ori. De asemenea, deoarece datele solicitate ar putea fi mutate sau șterse în timpul procesului de citire, eroarea de mai jos ar putea fi confruntată:
Msg 601, nivelul 12, Starea 1
nu a putut continua scanarea cu NOLOCK din cauza mișcării datelor.
cu (nolock) indiciu tabel este o idee bună atunci când sistemul utilizează tranzacții explicite puternic, care blochează citirea datelor foarte frecvent., Cu (nolock) indiciu tabel este utilizat atunci când se lucrează cu sisteme care acceptă date de sincronizare, cum ar fi sistemele de raportare.,
Pentru a înțelege modul de utilizare a CU (NOLOCK) tabelul indiciu practic, să ne crea un nou tabel folosind CREA TABELUL T-SQL de mai jos:
După crearea tabelului, vom umple cu 100K rânduri pentru scopuri de testare, folosind ApexSQL Genera, SQL test generator de date, așa cum se arată în imaginea de mai jos:
Odată ce masa este gata, vom simula un scenariu de blocare, în care o actualizare tranzacția va fi executat într-o tranzacție care va începe și nu a comis sau laminate înapoi.,
Cu tabelul de date a blocat de tranzacție, vom rula un alt SELECT, în sesiunea SQL număr de 54 de ani, care preia datele din LockTestDemo masă, folosind comanda SELECT de mai jos:
|
1
|
SELECTAȚI * DE la LockTestDemo
|
Veți vedea că anterior SELECT va lua o lungă perioadă de timp, fără recuperarea înregistrărilor.,ocking că SELECTAȚI interogare folosind sp_who2 comanda cu numărul de sesiune pentru a SELECTA și ACTUALIZAREA declarațiilor:
|
1
2
3
|
sp_who2 53
GO
sp_who2 54
|
rezultatul va arăta că, deschis anterior tranzacției nu este de a efectua orice acțiune, după ACTUALIZAREA declarație executat cu succes., Dar, datorită faptului că tranzacția nu este comisă sau derulată înapoi încă, încă blochează alte interogări care încearcă să obțină date din acel tabel. Și SELECTAȚI declarație care se execută sub sesiune 54 este blocat de o tranzacție care se execută în cadrul sesiunii de 53 de ani, așa cum se arată în rezultatul de mai jos:
anterior SELECT va ține de așteptare pentru ca tranzacția să fie ucis, angajat sau laminate înapoi, în scopul de a obține solicitat rânduri de masă., COMMIT sau ROLLBACK comanda sub aceeași sesiune a tranzacției, dacă este cazul, după cum se arată mai jos:
Odată ce blocarea este eliberat, veți vedea că a solicitat rânduri vor fi preluate de la SELECT direct așa cum se arată în rezultatele de mai jos:
soluția anterioară nu este întotdeauna de preferat sau aplicabile, de exemplu, atunci când tranzacția, care ne blochează interogări este critică și nu este ușor să fie ucis sau laminate înapoi, sau atunci când nu aveți control asupra altor tranzacții în baza de date., În acest caz, indiciul de masă cu (NOLOCK) este util aici, dacă puteți tolera riscul de citire murdară sau inconsecvență a datelor. Așa cum am menționat anterior, indiciul tabelului cu (NOLOCK) vă permite să citiți datele care au fost modificate, dar care nu au fost încă angajate în baza de date., Dacă executați aceeași instrucțiune SELECT fără a ucide, comite sau rulare înapoi ACTUALIZAREA tranzacție, dar de data aceasta adăugând CU (NOLOCK) tabelul aluzie la numele tabelului în SELECT cum se arată mai jos:
|
1
|
SELECTAȚI * DE la LockTestDemo CU (NOLOCK)
|
Apoi verificarea SELECT statutul folosind sp_who2 comanda., Veți vedea că interogarea se execută, fără a aștepta pentru ACTUALIZAREA tranzacția să fie finalizată cu succes și eliberați butonul de blocare de pe masă, așa cum se arată în imaginea de mai jos:
CU (NOLOCK) tabelul indiciu funcționează la fel ca READUNCOMMITTED masă indiciu, permițându-ne pentru a prelua date care este schimbat, dar nu le-a comis.,iv>
|
1
|
SELECT * FROM LockTestDemo CU (READUNCOMMITTED)
|
Preluarea datelor solicitate în mod direct, fără a aștepta pentru a ACTUALIZA declarația pentru eliberarea de blocare a efectuat pe masă, întorcându-se același rezultat ca și prezentate în setul de rezultate de mai jos:
Luați în considerare faptul că, CU (NOLOCK) și READUNCOMMITTED masă indicii pot fi utilizate numai cu declarațiile SELECT.,
Această interogare va prelua, de asemenea, aceleași date în mod direct, fără a utiliza orice masă indiciu și fără de așteptare pentru ACTUALIZAREA declarației pentru eliberarea de blocare a efectuat pe masă, așa cum se arată în setul de rezultate de mai jos:
De la rezultatele anterioare, ați putea crede că aceasta este soluția perfectă pentru astfel de scenarii, în cazul în care veți obține datele solicitate mai rapid, fără a aștepta pentru alte operațiuni să fi comis, de a lua riscul de a avea date exacte., Dar interogarea SELECT care utilizează indiciul tabelului WITH (NOLOCK) va afecta negativ alte procese de pe serverul SQL? Pentru a obține răspunsul, să verificăm mai întâi ce tip de încuietori se va acorda indiciul de masă cu (NOLOCK) în timpul executării acestuia.,=”26bd69ce5a”>
|
1
|
sp_lock 54
|
Veți vedea din rezultatul că interogare care utilizează CU (NOLOCK) tabelul indiciu va fi acordat S și Sch-S tipuri de blocare, așa cum se arată în rezultatul de mai jos:
De la rezultatul anterior, veți vedea că CU (NOLOCK) tabelul indiciu va fi acordat acces partajat (E) de blocare la nivel de baze de date., La acces partajat (E) de blocare este folosit pentru citirea funcționare, care să permită tranzacții simultane pentru a citi date sub pesimist concurrency control, prevenind alte tranzacții la modificarea blocat resursă partajată (E) blochează exista pe acea resursă, până când blocarea este eliberat de îndată ce operația de citire completeaza.
al doilea tip de blocare care este acordat interogării folosind indiciul tabelului WITH (NOLOCK) este blocarea stabilității schemei (Sch-s)., Această blocare nu va împiedica nicio altă tranzacție să acceseze resursele, cu excepția operațiunilor DDL simultane și a operațiunilor DML concurente care achiziționează blocări de modificare a schemei (Sch-M) din același tabel, care vor fi blocate în timp ce interogarea se execută. Acest lucru are sens, deoarece nu este nevoie să începeți să citiți date din tabel, atunci o altă tranzacție schimbă structura tabelului în timpul procesului de recuperare a datelor., SQL Server Motor de baze de Date utilizează schema modificare (Sch-M) blochează în timp ce prelucrarea datelor definition language (DDL) comenzi, cum ar fi adăugarea o nouă coloană, aruncând o coloană existente, cădere sau reconstruirea indici, pentru a preveni accesul concurent la masă, până la blocare este eliberat.
interogarea mea NOLOCK se blochează!
aceasta înseamnă că denumirea NOLOCK nu este întotdeauna 100% exactă. Utilizarea cu (NOLOCK) indiciu de masă, care deține stabilitate schemă (Sch_S) lock, poate bloca alte interogări care încearcă să achiziționeze o modificare schemă (Sch-M) lock pe acel tabel., Acesta este un aspect critic care ar trebui să ia în considerare dacă există o mulțime de utilizatori de executare a acestora SELECTAȚI interogări utilizând CU (NOLOCK) tabelul sugestie, vă împiedică de la a face orice modificări la schema de masă sau de întreținere pe tabelul de indici, fiind blocat de către schema de stabilitate (Sch_S) de blocare.,>
În același timp, vom rula interogare de mai jos, care scade un index pe aceeași masă și de a crea din nou, sub numărul de sesiune 58:
Apoi verificarea stării de ambele interogări utilizând sp_who2 comandă, veți vedea la rezultatul că, SELECT, care este utilizarea CU (NOLOCK) tabelul indiciu și sesiune de alergare număr de 53 de ani, este blocarea DROP/create INDEX proces care rulează sub numărul de sesiuni de 58 de ani, așa cum se arată în mod clar mai jos:
Dacă vom verifica încuietori, care sunt efectuate de către fiecare interogare, folosind sys.,dm_tran_locks sistem de obiect ca și în interogarea de mai jos:
|
1
2
3
|
SELECT *
DIN sys.dm_tran_locks
UNDE resource_type = „OBIECT”
|
Veți vedea că, scăderea/create INDEX proces care rulează sub numărul de sesiuni de 58 de ani este în așteptare pentru a dobândi schema modificare (Sch-M) tip de blocare., Acest lucru se întâmplă datorită faptului că, schema de modificare (Sch-M) de blocare nu pot fi dobândite în timp ce schema de stabilitate (Sch_S) de blocare, care este deja acordate SELECT care rulează sub numărul de sesiuni de 53 de ani, există deja așa cum se arată în imaginea de mai jos:
Mea NOLOCK interogare este blocat!
invers, deoarece cu (NOLOCK) indiciu tabel dobândește stabilitate schemă (Sch-S) tip de blocare, instrucțiunea SELECT care utilizează cu (NOLOCK) indiciu tabel va fi blocat în cazul în care o modificare schemă este efectuată pe acel tabel.,
|
1
2
3
|
sp_who2 53
GO
sp_who2 54
|
Veți vedea că SELECTAȚI declarație care rulează sub sesiune 54 este blocat de către ALTER TABLE declarație care rulează sub sesiune de 54 de ani, așa cum se arată mai jos:
Apoi verificarea încuietori, care sunt efectuate de către fiecare interogare, folosind sys.,dm_tran_locks system object as in the query below:
|
1
2
3
4
|
SELECT *
FROM sys.,că, SELECT, care este utilizarea CU (NOLOCK) tabelul indiciu și rulează sub numărul de sesiuni de 54 de ani, va fi de așteptare pentru a dobândi schema de stabilitate (Sch_S) de blocare, datorită faptului că schema de stabilitate (Sch-S) de blocare nu pot fi dobândite în timp ce schema de modificare (Sch_M) de blocare, care este deja acordate în instrucțiunea ALTER rulează sub numărul de sesiuni de 53 de ani, există deja așa cum se arată în imaginea de mai jos:
Vă puteți imagina situația, atunci când sunt de planificare număr foarte mare de rapoarte pe timp de noapte, care sunt utilizați CU (NOLOCK) tabelul indiciu doar pentru a fi în siguranță., În același timp, există locuri de muncă de întreținere care sunt, de asemenea, programate să reconstruiască indici puternic fragmentați pe aceeași masă!există o serie de bune practici și sugestii pe care le puteți urma, pentru a evita problemele cu care vă puteți confrunta atunci când utilizați cu (nolock) indiciu de masă. Astfel de sugestii includ:
Ahmad Yaseen este un Microsoft de Date de Mare inginer cu cunoștințe profunde și experiență în SQL BI, SQL Server de baze de Date administrative și Dezvoltarea domeniilor de activitate.,
este expert Microsoft Certified Solution în managementul și analiza datelor, Microsoft Certified Solution Associate în administrarea și dezvoltarea bazelor de date SQL, Azure Developer Associate și Microsoft Certified Trainer. De asemenea, el contribuie cu sfaturile sale SQL în multe bloguri., Vezi toate posturile de Ahmad Yaseen Ultimele posturi de Ahmad Yaseen (vezi toate)
|
Lasă un răspuns