A zárolás elengedhetetlen a sikeres SQL Server tranzakciók feldolgozásához, és arra szolgál, hogy az SQL Server zökkenőmentesen működhessen többfelhasználós környezetben. A zárolás az a mód, ahogyan az SQL Server kezeli a tranzakciók párhuzamosságát. A zárolások lényegében memórián belüli struktúrák, amelyek tulajdonosokkal, típusokkal és a védendő erőforrás hash-jával rendelkeznek. A zárolás mint memórián belüli struktúra mérete 96 bájt.
Az SQL Server zárolásának jobb megértéséhez fontos megérteni, hogy a zárolás célja az adatbázisban lévő adatok integritásának biztosítása, mivel minden SQL Server tranzakciót arra kényszerít, hogy átmenjen az ACID teszten.
Az ACID teszt 4 követelményből áll, amelyet minden tranzakciónak sikeresen teljesítenie kell:
- Atomicity – megköveteli, hogy egy tranzakciónak, amely két vagy több különálló információrészletet érint, az összes részt vagy egyiket sem kell lekötnie
- Consistency – megköveteli, hogy egy tranzakciónak érvényes állapotot kell létrehoznia az új adatokból, vagy vissza kell tekintenie az összes adatot a tranzakció végrehajtása előtti állapotba
- Isolation – megköveteli, hogy egy tranzakció, amely még fut, és még nem kötött le minden adatot, elszigeteltnek kell maradnia az összes többi tranzakciótól
- Tartósság – megköveteli, hogy az elkötelezett adatokat olyan módszerrel kell tárolni, amely az összes adatot helyes állapotban és a felhasználó számára elérhető állapotban tartja, még hiba esetén is
Az SQL Server zárolása az izolációs követelmény lényeges része, és a tranzakció által érintett objektumok zárolására szolgál. Amíg az objektumok zárolva vannak, az SQL Server megakadályozza, hogy más tranzakciók bármilyen módosítást hajtsanak végre a bevezetett zárolás által érintett objektumokban tárolt adatokon. Amint a zárolás feloldásra kerül a módosítások rögzítésével vagy a változások kezdeti állapotba történő visszaállításával, a többi tranzakció számára lehetővé válik a szükséges adatmódosítások elvégzése.
Az SQL Server nyelvére lefordítva ez azt jelenti, hogy amikor egy tranzakció zárolást rendel el egy objektumra, az összes többi tranzakció, amelynek szüksége van az adott objektumhoz való hozzáférésre, kénytelen lesz várni a zárolás feloldásáig, és ez a várakozás a megfelelő várakozási típussal lesz regisztrálva
Az SQL Server zárolások a zárolási módokon és a zárolási granularitáson keresztül adhatók meg
Zárolási módok
A zárolási mód figyelembe veszi a különböző zárolási típusokat, amelyeket egy zárolandó erőforrásra lehet alkalmazni:
- Kizárólagos (X)
- Megosztott (S)
- Frissítés (U)
- Szándék (I)
- Séma (Sch)
- Tömeges frissítés (BU)
Kizárólagos zár (X) – Ez a zár típus, ha ki van téve, biztosítja, hogy egy oldal vagy sor kizárólag az exkluzív zárat kiadó tranzakció számára lesz fenntartva mindaddig, amíg a tranzakció birtokolja a zárat.
A kizárólagos zárat a tranzakció akkor alkalmazza, amikor módosítani kívánja az oldal vagy a sor adatait, ami a DELETE, INSERT és UPDATE DML utasítások esetében történik. Kizárólagos zár csak akkor alkalmazható egy oldalra vagy sorra, ha a célponton már nincs más megosztott vagy kizárólagos zár. Ez gyakorlatilag azt jelenti, hogy egy oldalra vagy sorra csak egy kizárólagos zárat lehet alkalmazni, és ha egyszer már alkalmazzuk, a zárolt erőforrásokra nem lehet más zárat alkalmazni
Megosztott zár (S) – ez a zár típus, ha alkalmazzuk, egy oldalt vagy sort csak olvasásra tart fenn, ami azt jelenti, hogy semmilyen más tranzakció nem módosíthatja a zárolt rekordot, amíg a zár aktív. A megosztott zárolást azonban egyszerre több tranzakció is alkalmazhatja ugyanazon az oldalon vagy soron, és így több tranzakció is megoszthatja az adatolvasás képességét, mivel maga az olvasási folyamat semmilyen módon nem érinti az aktuális oldal vagy sor adatait. Ezenkívül a megosztott zár lehetővé teszi az írási műveleteket, de a DDL módosítások nem engedélyezettek
Frissítési zár (U) – ez a zár hasonló a kizárólagos zárhoz, de úgy tervezték, hogy bizonyos szempontból rugalmasabb legyen. Frissítési zárat olyan rekordra lehet alkalmazni, amely már rendelkezik megosztott zárral. Ebben az esetben a frissítési zár egy másik megosztott zárat rendel a célsorhoz. Amint a frissítési zárat birtokló tranzakció készen áll az adatok módosítására, a frissítési zár (U) kizárólagos zárrá (X) alakul át. Fontos megérteni, hogy a frissítési zár aszimmetrikus a megosztott zárak tekintetében. Míg a frissítési zárat rá lehet tenni egy olyan rekordra, amely rendelkezik megosztott zárral, addig a megosztott zárat nem lehet rátenni egy olyan rekordra, amely már rendelkezik frissítési zárral
Intent zárak (I) – ezt a zárat egy tranzakció arra használja, hogy tájékoztasson egy másik tranzakciót a zár megszerzésére irányuló szándékáról. Az ilyen zár célja az adatmódosítás megfelelő végrehajtásának biztosítása azáltal, hogy megakadályozza, hogy egy másik tranzakció zárat szerezzen a hierarchiában következő objektumra. A gyakorlatban, amikor egy tranzakció zárat akar szerezni egy sorra, szándékos zárat szerez egy táblán, amely egy magasabb hierarchiaobjektum. A szándékos zár megszerzésével a tranzakció nem engedi, hogy más tranzakciók kizárólagos zárat szerezzenek az adott táblán (különben egy másik tranzakció által alkalmazott kizárólagos zár törli a sorzárat).
Ez egy fontos zár típus a teljesítmény szempontjából, mivel az SQL Server adatbázis-motorja csak a táblaszinten vizsgálja a szándékos zárakat, hogy ellenőrizze, lehetséges-e a tranzakció számára, hogy biztonságos módon szerezzen zárat az adott táblában, és ezért a szándékos zár kiküszöböli annak szükségességét, hogy egy táblában minden egyes sor/oldal zárat ellenőrizzen, hogy megbizonyosodjon arról, hogy a tranzakció megszerezheti a teljes tábla zárját
Három hagyományos szándékos zár és három úgynevezett konverziós zár létezik:
Szokásos szándékos zárak:
Kizárólagos szándékú zár (IX) – a kizárólagos szándékú zár (IX) megszerzése azt jelzi az SQL Server számára, hogy a tranzakciónak szándékában áll módosítani néhány alacsonyabb hierarchiájú erőforrást azáltal, hogy egyenként kizárólagos (X) zárakat szerez az adott alacsonyabb hierarchiájú erőforrásokra
Megosztott szándékú zár (IS) – a megosztott szándékú zár (IS) megszerzése azt jelzi az SQL Server számára, hogy a tranzakciónak van hogy a tranzakciónak szándékában áll néhány alacsonyabb hierarchiájú erőforrást olvasni, a hierarchiában lejjebb elhelyezkedő erőforrásokon egyedileg megosztott zárak (S) megszerzésével
Intent update (IU) – amikor egy szándékos megosztott zár (IS) megszerzése azt jelzi az SQL Server számára, hogy a tranzakciónak szándékában áll néhány alacsonyabb hierarchiájú erőforrást olvasni, a hierarchiában lejjebb elhelyezkedő erőforrásokon egyedileg megosztott zárak (S) megszerzésével. A szándékos frissítési zár (IU) csak lapszinten szerezhető meg, és amint a frissítési művelet megtörténik, átalakul szándékos kizárólagos zárrá (IX)
Konverziós zárak:
Shared with intent exclusive (SIX) – ez a zár megszerzésekor azt jelzi, hogy a tranzakció az összes alacsonyabb hierarchiában lévő erőforrást olvasni kívánja, és így a hierarchiában alacsonyabb szinten lévő összes erőforráson megszerezni a megosztott zárat, és viszont ezek egy részét módosítani, de nem az összeset. Ennek során szándékosan kizárólagos (IX) zárat szerez az alacsonyabb hierarchiában lévő, módosítandó erőforrásokra. A gyakorlatban ez azt jelenti, hogy amint a tranzakció SIX zárat szerez a táblára, szándékos kizárólagos zárat (IX) szerez a módosított oldalakra és kizárólagos zárat (X) a módosított sorokra.
Egyszerre csak egy szándékkal megosztott kizárólagos zárat (SIX) lehet szerezni egy táblára, és ez blokkolja a többi tranzakciót a frissítések végrehajtásában, de nem akadályozza meg a többi tranzakciót abban, hogy olvassa az alacsonyabb hierarchia erőforrásait, ők megszerezhetik a szándékkal megosztott (IS) zárat a táblán
Megosztott szándékkal frissített (SIU) – ez egy kicsit specifikusabb zár, mivel a megosztott (S) és a szándékkal frissített (IU) zárak kombinációja. Tipikus példa erre a zárra, amikor egy tranzakció a PAGELOCK hint-tel és lekérdezéssel végrehajtott lekérdezést, majd a frissítési lekérdezést használja. Miután a tranzakció megszerzi a SIU zárat a táblán, a PAGELOCK hint-tel végrehajtott lekérdezés megszerzi a megosztott (S) zárat, míg a frissítési lekérdezés megszerzi a szándékos frissítési (IU) zárat
Frissítés szándékos exkluzivitással (UIX) – amikor a frissítési zár (U) és a szándékos exkluzív (IX) zárak egyszerre kerülnek megszerzésre a táblázat alacsonyabb hierarchiájú erőforrásain, a frissítés szándékos kizárólagos zárral a tábla szintjén ennek következményeként szerezhető meg
Sémazárak (Sch) – Az SQL Server adatbázis-motor a sémazárak két típusát ismeri: A séma módosítási zárat (Sch-M) és a séma stabilitási zárat (Sch-S)
- A séma módosítási zár (Sch-M) egy DDL utasítás végrehajtásakor kerül megszerzésre, és megakadályozza a zárolt objektum adataihoz való hozzáférést, mivel az objektum szerkezete megváltozik. Az SQL Server egyetlen sémamódosítási zár (Sch-M) zárolást engedélyez bármely zárolt objektumon. Egy tábla módosításához a tranzakciónak meg kell várnia, hogy Sch-M zárat szerezzen a célobjektumon. Miután megszerezte a sémamódosítási zárat (Sch-M), a tranzakció módosíthatja az objektumot, majd a módosítás befejezése után a zár felszabadul. A Sch-M zár tipikus példája az index újjáépítése, mivel az index újjáépítése táblamódosítási folyamat. Az index-újjáépítési azonosító kiadása után egy séma-módosítási zár (Sch-M) kerül megszerzésre az adott táblán, és csak az index-újjáépítési folyamat befejezése után kerül feloldásra (ONLINE opcióval való használat esetén az index-újjáépítés röviddel a folyamat végén megszerzi a Sch-M zárat)
- Egy sémastabilitási zár (Sch-S) megszerzésre kerül, miközben egy sémafüggő lekérdezés összeállítása és végrehajtása, valamint a végrehajtási terv generálása folyamatban van. Ez a bizonyos zár nem blokkolja más tranzakciók hozzáférését az objektumadatokhoz, és kompatibilis az összes zármóddal, kivéve a sémamódosítási zárat (Sch-M). Lényegében a sémastabilitási zárakat minden DML és select lekérdezés megszerzi, hogy biztosítsa a táblaszerkezet integritását (biztosítsa, hogy a tábla ne változzon a lekérdezések futása közben).
Bulk Update lockok (BU) – ezt a zárat a tömeges importálási műveletek használják, ha TABLOCK argumentummal/int-tel adják ki. Tömeges frissítési zár megszerzése esetén más folyamatok nem férhetnek hozzá egy táblához a tömeges betöltés végrehajtása során. A tömeges frissítés zárolása azonban nem akadályozza meg egy másik tömeges betöltés párhuzamos feldolgozását. Ne feledje azonban, hogy a TABLOCK használata egy fürtözött index táblán nem teszi lehetővé a párhuzamos tömeges importálást. További részletek erről az Irányelvek a tömeges importálás optimalizálásához
Zárhierarchia
Az SQL Server bevezette a zárhierarchiát, amelyet az adatok olvasásakor vagy módosításakor alkalmaz. A zárolási hierarchia a legmagasabb hierarchiaszinten lévő adatbázissal kezdődik, és a táblán és a lapon keresztül a legalacsonyabb szinten lévő sorig tart
Lényegében mindig van egy megosztott zárolás az adatbázis szintjén, amelyet mindig akkor alkalmaznak, amikor egy tranzakció kapcsolódik egy adatbázishoz. A megosztott zár az adatbázis szintjén azért van előírva, hogy megakadályozza az adatbázis eldobását vagy egy adatbázis biztonsági mentés visszaállítását a használt adatbázis felett. Például, amikor egy SELECT utasítást adunk ki valamilyen adat beolvasására, az adatbázis szintjén egy megosztott zár (S), a táblán és az oldal szintjén egy szándékolt megosztott zár (IS), magán a soron pedig egy megosztott zár (S) lesz érvényben
DML utasítás esetén (pl. insert, update, delete) az adatbázis szintjén egy megosztott zár (S), a táblán és az oldal szintjén egy szándékos kizárólagos zár (IX) vagy szándékos frissítési zár (IU), a soron pedig egy kizárólagos vagy frissítési zár (X vagy U) lesz
A zárak megszerzése mindig felülről lefelé történik, mivel így az SQL Server megakadályozza az úgynevezett Race állapot kialakulását.
Most, hogy a zárolási módokat és a zárolási hierarchiát elmagyaráztuk, részletezzük tovább a zárolási módokat, és azt, hogy ezek hogyan fordíthatók le a zárolási hierarchiára.
Nem minden zármód alkalmazható minden szinten.
Sorszinten a következő három zármód alkalmazható:
- Kizárólagos (X)
- Megosztott (S)
- Frissítés (U)
Az említett módok kompatibilitásának megértéséhez lásd az alábbi táblázatot:
| Exkluzív (X) | Megosztott (S) | Frissítés (U) | ||
| Exkluzív (X) | ✗ | ✗ | ✗ | |
| Megosztott (S) | ✗ | ✓ | ✓ | |
| Frissítés (U) | ✗ | ✓ | ✓ | ✗ |
✓ – Kompatibilis ✗ – Inkompatibilis
A táblázat szintjén, öt különböző típusú zár létezik:
- Kizárólagos (X)
- Megosztott (S)
- Szándékosan kizárólagos (IX)
- Szándékosan megosztott (IS)
- Megosztott szándékkal kizárólagos (SIX)
Ezen módok kompatibilitása az alábbi táblázatban látható
.
| (X) | (S) | (IX) | (IS) | (HAT) | ||
| (X) | ✗ | ✗ | ✗ | ✗ | ✗ | |
| (S) | ✗ | ✗ | ✓ | ✗ | ✓ | ✗ |
| (IX) | ✗ | ✗ | ✗ | ✓ | ✓ | ✗ |
| (IS) | ✗ | ✓ | ✓ | ✓ | ✓ | ✓ |
| (HAT) | ✗ | ✗ | ✗ | ✗ | ✓ | ✗ |
✓ – Kompatibilis ✗ – Inkompatibilis
A sémazár (Sch) egyben táblaszintű zár is, de nem adathoz kapcsolódó zárolás
A zárolástípusok közötti kompatibilitás jobb megértéséhez tekintse meg ezt a táblázatot:
Lock escalation
Annak érdekében, hogy megakadályozza, hogy a zárolás túl sok erőforrást használjon, az SQL Server bevezette a lock escalation funkciót.
Eszkaláció nélkül a zárolások jelentős mennyiségű memóriaforrást igényelhetnek. Vegyünk egy példát, ahol a 30 000 adatsorra, ahol minden sor 500 bájt méretű, a törlési művelet végrehajtásához zárolást kell elrendelni. Eszkaláció nélkül az adatbázisra egy megosztott zár (S), a táblára 1 szándékos kizárólagos zár (IX), az oldalakra 1 875 szándékos kizárólagos zár (IX) (8 KB-os oldal 16 500 bájtos sort tartalmaz, ami 1 875 oldalt tesz ki, amelyek 30 000 sort tartalmaznak) és magára a sorokra 30 000 kizárólagos zár (X) kerül. Mivel minden egyes zár 96 bájt méretű, 31 877 zár körülbelül 3 MB memóriát vesz igénybe egyetlen törlési művelethez. Nagyszámú művelet párhuzamos futtatása jelentős erőforrásokat igényelhet csak azért, hogy a zároláskezelő zökkenőmentesen végre tudja hajtani a műveletet
Az ilyen helyzetek megelőzésére az SQL Server zárolási eszkalációt használ. Ez azt jelenti, hogy olyan helyzetben, amikor egy szinten több mint 5000 zárat szereznek, az SQL Server ezeket a zárakat egyetlen táblaszintű zárra eszkalálja. Alapértelmezés szerint az SQL Server mindig közvetlenül a táblaszintre eszkalálódik, ami azt jelenti, hogy a lapszintre való eszkaláció soha nem történik meg. Ahelyett, hogy számos sor- és lapzárat szerezne, az SQL Server a táblaszintű kizárólagos zárra (X) eszkalál
Bár ez csökkenti az erőforrásigényt, a táblában lévő kizárólagos zárak (X) azt jelentik, hogy más tranzakció nem férhet hozzá a zárolt táblához, és minden, az adott táblához hozzáférni próbáló lekérdezés blokkolva lesz. Ezért ez csökkenti a rendszer többletköltségét, de növeli az egyidejűségi versengés valószínűségét
Az eszkaláció ellenőrzésének biztosítása érdekében az SQL Server 2008 R2-től kezdődően, az ALTER TABLE utasítás részeként bevezetésre került a LOCK_EXCALATION opció
Mindegyik opciót úgy definiálták, hogy a lock escalation folyamat felett meghatározott irányítást tegyen lehetővé:
Tábla – Ez az alapértelmezett beállítás minden újonnan létrehozott tábla esetében, mivel az SQL Server alapértelmezés szerint mindig táblaszintre hajtja végre a zár eszkalálását, ami a partícionált táblákra is vonatkozik
Auto – Ez a beállítás lehetővé teszi a zár eszkalálását partíciós szintre, ha egy tábla partícionált. Ha egyetlen partícióban 5000 zárat szereznek, a zár eszkalációja kizárólagos zárat (X) szerez a partícióra, míg a tábla szándékosan kizárólagos zárat (IX) szerez. Abban az esetben, ha a tábla nem particionált, a lock escalation a táblaszinten szerez zárat (a Table opcióval megegyezően).
Bár ez egy nagyon hasznos opciónak tűnik, nagyon óvatosan kell használni, mivel könnyen holtpontot okozhat. Abban a helyzetben, amikor két tranzakciónk van két partíción, ahol a kizárólagos zárat (X) megszereztük, és a tranzakciók megpróbálnak hozzáférni a másik tranzakció által használt partícióból származó dátumhoz, holtpont keletkezik
Tehát, nagyon fontos, hogy gondosan ellenőrizzük az adathozzáférési mintát, ha ez az opció engedélyezve van, amit nem könnyű elérni, és ezért ez az opció nem az SQL Server alapértelmezett beállításai között szerepel
Disable – Ez az opció teljesen letiltja a lock escalationt egy tábla számára. Ezt az opciót is óvatosan kell használni, nehogy az SQL Server zároláskezelője túlzott memóriahasználatra kényszerüljön
Amint látható, a zárolási eszkaláció kihívást jelenthet a DBA-k számára. Ha az alkalmazás tervezése egyszerre több mint 5000 sor törlését vagy frissítését igényli, a lock escalation és az ebből eredő hatások elkerülésére megoldás lehet az egyetlen tranzakció felosztása két vagy több tranzakcióra, ahol mindegyik kevesebb mint 5000 sort kezel, mivel így a lock escalation kikerülhető
Info az aktív SQL Server zárakról
Az SQL Server biztosítja a Dynamics Management View (DMV) sys.dm_tran_locks, amely információt ad vissza az éppen használatban lévő zárkezelő erőforrásokról, ami azt jelenti, hogy megjeleníti a tranzakciók által megszerzett összes “élő” zárat. További részletek erről a DMV-ről a sys.dm_tran_locks (Transact-SQL) cikkben találhatók.
A zár azonosítására használt legfontosabb oszlopok a resource_type, a request_mode és a resource_description. Ha szükséges, a hibaelhárítás során további információs információforrásként további oszlopok is bevonhatók
Íme a lekérdezés példája
A lekérdezésben a where záradék a kizárandó resource_type szűrőjeként szolgál. az eredmények közül az adatbázisban megszerzett, általában megosztott zárakat, mivel ezek mindig jelen vannak adatbázis szinten
Az itt bemutatott három oszlop rövid magyarázata:
resource_type – Megjeleníti az adatbázis-erőforrást, ahol a zárak megszerzése történik. Az oszlop a következő értékek egyikét jelenítheti meg: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID
request_mode – az erőforráson megszerzett zármódot jeleníti meg
resource_description – az erőforrás rövid leírását jeleníti meg, és nem minden zármód esetében van kitöltve. Leggyakrabban a sor, oldal, objektum, fájl azonosítóját tartalmazza az oszlop, stb
- Author
- Recent Posts
A katonai repülés híve és keményvonalas méretarányos repülőgépmodellező. Extrém sportok rajongója; ejtőernyős és bungee jump oktató. Egykor komoly, ma már csak szabadidős fotós
Nézd meg Nikola Dimitrijevic összes bejegyzését
- SQL Server trace flags guide; -1-től 840-ig – 2019. március 4.
- Hogyan kezeljük az SQL Server WRITELOG várakozási típusát – 2018. június 13.
- SQL Server teljesítményszámlálói (Batch Requests/sec vagy Transactions/sec): Mit kell figyelni és miért – 2018. június 5.