Zárak SQL Server 2018-ban, zárak típusai, zárak fokozódása
Az SQL Server optimalizálás nagyon fontos jellemzői kapcsolódnak a blokkoló rendszerhez. Ennek az alrendszernek az SQL Serverben történő végrehajtása indokolt panaszokat okoz a szakemberek számára, és sok gyakorlati feladathoz kézi lezárás szükséges.
Az SQL Server öt fő zárolási szintje van:
q blokkolja az adatbázis szintjét (DB). Az ilyen zárak automatikusan fel vannak helyezve minden olyan adatbázisra, amelyhez a felhasználó csatlakozik. Alapvetően úgy vannak megtervezve, hogy megakadályozzák bizonyos műveletek végrehajtását, például az adatok törlését, olyan adatbázisokkal, amelyekhez a felhasználók kapcsolódnak;
q az objektum szintjének blokkolása (pl. TAB). Az ilyen zárak egy asztalon vagy indexen helyezhetők el mind normál lekérdezések végrehajtása során, mind pedig ezekkel az objektumokkal végzett szolgáltatási műveletek végrehajtása során;
q Mélységszint-lezárás (EXT). Az ilyen zárolások ritkán láthatók az SQL Server Management Studio alkalmazásban, vagy egy sp_lock tárolt eljárás eredményei. Csak olyan szolgáltatási műveletekhez használják, mint új táblák létrehozása, adatbázis fájlok méretének növelése stb.
q oldalszintű lezárás (PAG). Az ilyen zárolásokat gyakran az SQL Server használja. Ha használják őket, a 8 KB teljes oldala blokkolva van, minden bejegyzést tartalmaz. Ez a típusú zár mindkét adatlapra és indexoldalra használható;
q írási szint / billentyűzár (RID / KEY). Az ilyen zárak egymásra vannak helyezve egymástól. A RID típusú zárolások a fürtindex nélküli (heap) nélküli táblák rekordjain helyezkednek el, és a KEY zárolásokat olyan táblákban írják fel, amelyekhez fürtözött indexet biztosítanak.
A zárak típusonként is különböznek (általános, kizárólagos, a várakozások blokkolása stb.), De csak a szintjük fontos az optimalizálás szempontjából.
Alapértelmezés szerint az SQL Server automatikusan kezeli a zárat. A legtöbb művelet esetében az SQL Server először csak írási szintű zárolásokat próbál használni. Ha az adatok olvasására vagy módosítására vonatkozó kérelem számos rekordra vonatkozik, akkor az erőforrások megtakarításához az SQL Server dönthet magasabb szintű zárolások használatáról. Ezt a növekedést úgy hívják, hogy a zárak fokozódnak.
Megjegyezzük a zárak fokozódásával kapcsolatos technikai kérdéseket.
A legtöbb esetben az SQL Server kezdetben megpróbál írni írási vagy oldalszintű zárolásokat. A használni kívánt zárolások szintje - rekordok vagy oldalak meghatározása a lekérdezés végrehajtása előtt történik. A zárolások kiterjesztésének döntése Az SQL Server két helyzetben van:
q ha a kérés több mint 5000 zárolást próbál fel a rekord vagy az oldal szintjén egyetlen objektumra (táblára vagy indexre). A 5000 zárolás értéke a dokumentációból származik, de a gyakorlatban az SQL Server néha továbbra is írási szintű vagy oldalszintű zárolásokat és több százezer ilyen zárat használ. Ebben az esetben az SQL Server soha nem növekszik az írási szintről az oldal szintjére, de azonnal megpróbálja lezárni az asztalra;
q amikor a zárolásokkal foglalkozó fő memória területén lévő hely végződik. Minden egyes zárhoz az SQL Server 96 bájtot oszt ki. Az alapértelmezés szerint az SQL Serverrel való együttműködéshez használt memóriaterület mérete (amikor a Lock szerver paraméter értéke 0) dinamikusan van beállítva. Miután a terület nagysága eléri a 40% -a az összes használt memória mennyiségét a folyamat SQL Server (arra korlátozódik, hogy az operációs rendszer vagy a paraméter MAX SERVER MEMORY), az SQL Server automatikusan megpróbál egy lock eszkaláció. Ha a RAM-terület mérete elérte a memóriakapacitás 60% -át, a maximális rendelkezésre álló SQL Server. az új zárolások létrehozása nem történik meg, és az ügyfél 1204-es hibaüzenetet kap "nem tudja lekötni a zárolási erőforrást".
Ha az SQL Server nem tudta felemelni a zárolási szintet (például az asztal szintjén már van egy zárolás egy másik tranzakcióban), az újratárgyalásokat minden 1250 új zárolásnál rögzítik a rekord vagy az oldal szintjén.
A zárkiemelések figyelemmel kísérése a Profiler segítségével végezhető el, az eseményzárás: Eskkaláció kiválasztásával.
Külsőleg az eszkaláció mechanizmusa nagyon logikusnak tűnik, de a gyakorlatban problémák merülnek fel vele:
q Amikor a táblázatban nagyszámú rekorddal végrehajtandó műveleteket hajt végre, az SQL Server elsőként írási szintű zárolásokat próbál meg használni. Ennek eredményeképpen jelentős mennyiségű rendszererőforrás fordul elő az ilyen zárak telepítéséhez és későbbi eltávolításához. Ez elkerülhető, ha azonnal elvégzi a szükséges zárolási szintet (PAG vagy TAB) a művelet végrehajtásához;
q A második, fontosabb probléma az, hogy az SQL Server alkalmazza a zárak fokozódását, beleértve azokat a nagy teljesítményű szervereket is, amelyekkel egyidejűleg nagy számú felhasználó dolgozik. Egy tipikus helyzet így néz ki: van egy nagy táblája az adatbázisban, amellyel a felhasználók folyamatosan dolgoznak (hívjuk a fő táblának). A zárak fokozódása miatt a felhasználókat egyszerre blokkoló bejegyzések száma automatikusan megnő, ami azt eredményezi, hogy más felhasználók nem tudnak hozzáférni hozzájuk. Így, ha eléri az adott táblázatban dolgozó bizonyos számú felhasználókat, ez nagymértékben akadályozza. Különösen kellemetlen, hogy azok a feljegyzések, amelyekkel a felhasználók tényleg nem működnek, blokkolva vannak: csak egy oldalt találnak más, jelenleg megnyitott rekordokkal.
A problémák megoldásához a helyzettől függően különböző módszereket használhat:
q Az első olyan funkció, amely lehetővé teszi, hogy megszabaduljon a zárolásoktól, amikor egyáltalán kér adatokat olvasni - használja a megfelelő tranzakcióelkülönítési szintet. Mûveletek rögzítésekor a zárolások nem kerülnek bevezetésre a szintek használatakor:
· READ FIGYELEM. Amikor a READ_COMMINTED_SNAPSHOT adatbázis-paraméter BE értékre van állítva;
Az ilyen elkülönítési szintek tökéletesen felhasználhatók a statisztikai információk lekérdezéséhez, amikor az információ abszolút pontossága a termelékenység érdekében feláldozható. A pénzügyi jelentések esetében ez a módszer nem feltétlenül megfelelő;
• Egy másik radikális megoldás az adatbázis áthelyezése az R EAD-ONLY állapotba. Ebben az esetben a hozzáféréshez nem használnak zárolásokat, amelyek nagy teljesítményt nyernek a teljesítményben, és növelhetik azoknak a felhasználóknak a számát, akik egyszerre képesek az asztalon dolgozni. De nyilvánvaló okokból ez a megoldás nem mindig működik;
q Ha először növelni szeretné a zárolási szintet, használhatja a NOLOCK tanácsokat. Evezővilla. PAGLOCK és TABLOCK a lekérdezésekben. E megközelítéstől eltekintve - az alkalmazáskód megváltoztatása nem mindig lehetséges;
Egy nem clusteres indexhez;
· Fürtözött index esetén (ugyanazok a beállítások egyidejűleg alkalmazhatók az asztalra);
· Olyan táblázathoz, amelyen nincs fürtözött index (az adatok a "halomban" vannak). Ez a lehetőség nem dokumentált, de használható.
A parancs ALLOW_PAGE_LOCKS paraméterének KI értékének használatával megszabadulhat az oldalszintű zárolásoktól, ami az egyidejű felhasználói hozzáférés problémáinak fő oka.
q Ha a probléma a várakozásaink miatt nem elegendő hely a zárolási terület számára, akkor megpróbálhatja kézzel beállítani a terület méretét. Ehhez a Lock kiszolgáló konfigurációs paraméterét használhatja. és megpróbálja növelni az SQL Server számára rendelkezésre álló RAM mennyiségét is. növelve a fizikai RAM-ot a kiszolgálón vagy a MAX SERVER MEMORY paraméter használatával;
Egy másik radikális lehetőség a nyomkövető zászlók használata. A zászló 1211 egyszerűen letiltja a zárak bármilyen fokozódását. Ezzel a paraméterrel nagyon óvatosnak kell lenni, mert drámaian csökkentheti a kiszolgáló teljesítményét és növelheti a RAM fogyasztását. Sőt, a kimerültség RAM allokált a zár, akkor kap egy hiba zászló 1204. A második - 1224. Úgy letiltja zár fokozódása által termelt méter (5000 zár az alsó szinten), de a kimerültség hely a memóriában terület alapján visszavont zár, az eszkaláció még mindig előfordul.
Ha az 1224 és 1211 jelzőket egyszerre állítja be, a 1224 jelző elsőbbséget élvez.