Foto de James Sutton pe Unsplash
Notă: problema descrisă mai jos se aplică tuturor SELECTAȚI interogări, nu doar cei împodobită cu NOLOCK indicii. Faptul că se aplică interogărilor NOLOCK a fost o surpriză uriașă pentru mine, de unde și titlul.
o mulțime de oameni nu le place NOLOCK (adică nivelul de izolare citit neangajat), deoarece poate returna date inexacte., Am vazut o multime de argumente avertizat dezvoltatorii de la preluarea neangajate citește pentru că de modul în care acestea pot returna date murdare, phantom citește, și irepetabilă citește .
am știut despre toate aceste probleme de mai sus, dar există o problemă despre care nu am auzit niciodată până de curând: NOLOCK poate bloca alte interogări de la rulare.
Urmăriți videoclipul din această săptămână pe YouTube
să facem un pas înapoi și să înțelegem de ce am folosit atât de des NOLOCK în trecut. O instanță destul de tipică atunci când folosesc NOLOCK este atunci când vreau să las o interogare să ruleze peste noapte pentru a returna un set mare de date., Sunt în regulă cu unele inconsecvențe în date (de la citirea murdară etc…). Preocuparea mea principală este că nu vreau ca interogarea de lungă durată să stea în calea altor procese. întotdeauna am crezut că NOLOCK a fost o soluție perfectă pentru acest scenariu, deoarece nu blochează niciodată datele pe care le citește – rezultatele ar putea să nu fie perfecte, dar cel puțin interogarea nu va afecta negativ niciun alt proces pe server.
aici înțelegerea mea despre NOLOCK a fost greșită: în timp ce NOLOCK nu va bloca datele la nivel de rând, va scoate o blocare a stabilității schemei.,
un sistem de blocare s tability (Sch-S) previne structura unui tabel de la schimbarea în timp ce interogarea se execută. Toate declarațiile selectate, inclusiv cele din nivelul de izolare read uncommitted/NOLOCK, scoate un sistem de blocare Sch-S. Acest lucru are sens, deoarece nu am dori să începem să citim date dintr-un tabel și apoi să modificăm structura coloanei la jumătatea recuperării datelor. cu toate acestea, acest lucru înseamnă, de asemenea, că ar putea exista unele operații care sunt blocate de o blocare Sch-S. De exemplu, orice comandă care solicită o blocare de modificare a schemei (Sch-M) este blocată în acest scenariu.,
ce comenzi solicită încuietori Sch-M?
lucruri cum ar fi un index reconstrui sau sp_recompile tabel. Acestea sunt tipurile de comenzi care rulează în lucrările mele de întreținere de noapte pe care am încercat să le evit să le rănesc folosind NOLOCK în primul rând!
pentru a reitera, obișnuiam să cred că utilizarea sugestiei NOLOCK a fost o modalitate excelentă de a preveni blocarea în timpul interogărilor de lungă durată., Cu toate acestea, se pare că-mi NOLOCK interogări au fost, de fapt, blocarea meu de noapte index locuri de munca (toate SELECTAȚI interogări bloc în acest exemplu, dar mi se pare NOLOCK a fi deosebit de înșelătoare), care apoi a provocat alte declarațiile SELECT pentru a obține blocat prea! să aruncăm o privire la acest lucru în acțiune. Aici am o interogare care creează o bază de date, tabel, și apoi rulează o interogare care rulează lung cu NOLOCK:
acum, în timp ce miliarde de rând citit are loc, putem verifica dacă interogarea a scos un sistem de blocare Sch-S uitandu-se la sys.,dm_tran_locks:
SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'
în Timp ce rulează, dacă vom încerca pentru a reconstrui un index, care a reconstrui este blocat (prezentată ca o AȘTEPTA):
USE GOALTER INDEX IX_Id ON dbo.Test REBUILD
indexul Nostru reconstrui interogare va rămâne blocat până miliarde de rând NOLOCK interogare de SELECTARE finisaje de funcționare (sau este ucis). Aceasta înseamnă că interogarea pe care intenționam să o fac complet discret îmi blochează acum activitatea de întreținere a indexului de noapte.,
chiar mai rău, orice alte interogări care încearcă să ruleze după interogarea reconstrui (sau orice alte comenzi care solicită o blocare Sch-M) vor fi blocate, de asemenea! Daca incerc sa rulez o simpla interogare COUNT (*):
USE GOSELECT COUNT(*) FROM dbo.Test
blocat! Aceasta înseamnă că nu numai că interogarea mea inițială NOLOCK determină așteptarea lucrărilor mele de întreținere a reconstrucției indexului, blocarea Sch-M plasată de lucrarea de întreținere a reconstrucției determină blocarea oricăror întrebări ulterioare de pe masa respectivă și să fie forțată să aștepte., Tocmai am deraiat oportunitatea slujbei mele de întreținere și interogările ulterioare cu o declarație de blocare NOLOCK! din păcate, aceasta este o problemă dificilă și nu există un remediu unic.
soluție #1: nu rulați interogări de lungă durată
aș putea evita rularea interogărilor lungi noaptea, când s-ar putea rula în lucrările mele de întreținere a indexului., Acest lucru ar împiedica pe cei indice de locuri de muncă de întreținere și interogări ulterioare de întârziere, dar aceasta înseamnă mea inițială de miliarde de rând selectați interogare ar trebui să ruleze mai devreme, un impact negativ performanta serverului timpul un potențial mai aglomerat moment al zilei.,
Soluție #2: Utilizarea WAIT_AT_LOW_PRIORITY
Începând din 2014, am putea face un index online reconstrui cu WAIT_AT_LOW_PRIORITY opțiunea set:
ALTER INDEX IX_Id ON dbo.Test REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES , ABORT_AFTER_WAIT = BLOCKERS)))
Această interogare oferă practic orice blocarea SELECTAȚI interogări care rulează în prezent de 1 minut pentru a termina de executare sau altcineva această interogare va ucide și apoi executa index rebuild. În mod alternativ, am fi putut seta și ABORT_AFTER_WAIT = SELF, iar interogarea reconstruită s-ar ucide, permițând nolock billion row SELECT să termine rularea și să nu împiedice rularea altor interogări.,
aceasta nu este o soluție excelentă, deoarece înseamnă că fie interogarea de lungă durată este ucisă, fie reconstruirea indexului este ucisă.
soluția #3: reconstruiți dacă nu Sch-S, reorganizați altfel
o soluție programatică poate fi scrisă care încearcă să reconstruiască indexul, dar se întoarce să se reorganizeze dacă știe că va trebui să aștepte o blocare Sch-M.
am creat placa cazanului de mai jos ca punct de plecare, dar cerul este limita cu ceea ce se poate face cu ea (de ex., creați o buclă de timp pentru a verifica blocarea la fiecare X secunde, creați un timeout pentru momentul în care scriptul ar trebui să înceteze să încerce să reconstruiască și să se reorganizeze, etc…)
Aceasta solutie este preferata mea pentru că:
- Ad-hoc lungă de funcționare interogări nu fi ucis (toate că timpul petrecut de prelucrare nu se pierd)
- Alte selectați interogări nu sunt blocate de către Sch-M de blocare încercare de a RECONSTRUI
- Indicele de întreținere apare încă, chiar dacă se termină prin a fi un REORGANIZA în loc de a o RECONSTRUI
