Zamykání je nezbytné pro úspěšné zpracování transakcí SQL Serveru a je navrženo tak, aby SQL Server mohl bez problémů pracovat ve víceuživatelském prostředí. Uzamykání je způsob, jakým SQL Server spravuje souběžnost transakcí. Zámky jsou v podstatě struktury v paměti, které mají vlastníky, typy a hash prostředku, který mají chránit. Zámek jako struktura v paměti má velikost 96 bajtů.
Pro lepší pochopení zamykání v SQL Serveru je důležité pochopit, že zamykání je určeno k zajištění integrity dat v databázi, protože nutí každou transakci SQL Serveru projít testem ACID.
Test ACID se skládá ze 4 požadavků, kterými musí každá transakce úspěšně projít:
- Atomicita – vyžaduje, aby transakce, která zahrnuje dvě nebo více diskrétních částí informací, odevzdala všechny části nebo žádnou
- Konzistence – vyžaduje, aby transakce vytvořila platný stav nových dat nebo vrátila všechna data do stavu, který existoval před provedením transakce
- Izolace – vyžaduje, aby transakce, která stále běží a ještě neodevzdala všechna data, musí zůstat izolována od všech ostatních transakcí
- Trvanlivost – vyžaduje, aby odevzdaná data byla uložena metodou, která zachová všechna data ve správném stavu a dostupná uživateli i v případě selhání
Podstatnou součástí požadavku izolace je zamykání SQL Serveru, které slouží k uzamčení objektů ovlivněných transakcí. Dokud jsou objekty uzamčeny, SQL Server zabrání ostatním transakcím provést jakoukoli změnu dat uložených v objektech, kterých se týká zavedený zámek. Jakmile je zámek uvolněn odevzdáním změn nebo vrácením změn do původního stavu, bude ostatním transakcím umožněno provádět požadované změny dat.
Přeloženo do jazyka SQL Serveru to znamená, že když transakce uloží zámek na objekt, všechny ostatní transakce, které vyžadují přístup k tomuto objektu, budou nuceny čekat, dokud nebude zámek uvolněn, a toto čekání bude registrováno s odpovídajícím typem čekání
Zámky SQL Serveru lze specifikovat pomocí režimů zámku a granularity zámku
Režimy zámku
Režim zámku zohledňuje různé typy zámků, které lze použít na prostředek, který má být uzamčen:
- Exkluzivní (X)
- Sdílený (S)
- Aktualizace (U)
- Záměr (I)
- Schéma (Sch)
- Hromadná aktualizace (BU)
Exkluzivní zámek (X) – Tento typ zámku, zajistí, že stránka nebo řádek budou rezervovány výhradně pro transakci, která exkluzivní zámek zavedla, a to po celou dobu, kdy transakce zámek drží.
Exkluzivní zámek zavede transakce, když chce změnit data stránky nebo řádku, což je v případě příkazů DML DELETE, INSERT a UPDATE. Exkluzivní zámek může být na stránku nebo řádek uvalen pouze v případě, že na cíli již není uvalen jiný sdílený nebo exkluzivní zámek. To prakticky znamená, že na stránku nebo řádek lze uložit pouze jeden exkluzivní zámek a po jeho uložení nelze na uzamčené prostředky uložit žádný další zámek
Sdílený zámek (S) – tento typ zámku po uložení vyhradí stránku nebo řádek tak, aby byly dostupné pouze pro čtení, což znamená, že jakákoli jiná transakce nebude moci modifikovat uzamčený záznam, dokud bude zámek aktivní. Sdílený zámek však může být nad stejnou stránkou nebo řádkem zaveden několika transakcemi současně, a tak může několik transakcí sdílet možnost čtení dat, protože samotný proces čtení nijak neovlivní aktuální data stránky nebo řádku. Kromě toho bude sdílený zámek umožňovat operace zápisu, ale nebudou povoleny žádné změny DDL
Zámek aktualizace (U) – tento zámek je podobný exkluzivnímu zámku, ale je navržen tak, aby byl svým způsobem flexibilnější. Aktualizační zámek lze zavést na záznam, který již má sdílený zámek. V takovém případě aktualizační zámek uloží další sdílený zámek na cílový řádek. Jakmile je transakce, která drží aktualizační zámek, připravena změnit data, změní se aktualizační zámek (U) na exkluzivní zámek (X). Je důležité si uvědomit, že aktualizační zámek je asymetrický, pokud jde o sdílené zámky. Zatímco aktualizační zámek lze vnutit záznamu, který má sdílený zámek, sdílený zámek nelze vnutit záznamu, který již má aktualizační zámek
Intenční zámek (I) – tento zámek je prostředek, který transakce používá k informování jiné transakce o svém záměru získat zámek. Účelem tohoto zámku je zajistit správné provedení modifikace dat tím, že zabrání jiné transakci získat zámek na dalším objektu v hierarchii. V praxi, když chce transakce získat zámek na řádek, získá zámek záměru na tabulku, která je objektem vyšší hierarchie. Získáním zámku záměru transakce nedovolí jiným transakcím získat exkluzivní zámek na této tabulce (jinak by exkluzivní zámek zavedený některou jinou transakcí zrušil zámek řádku).
Jedná se o důležitý typ zámku z hlediska výkonu, protože databázový stroj SQL Serveru kontroluje zámky záměru pouze na úrovni tabulky, aby ověřil, zda je možné, aby transakce získala zámek v této tabulce bezpečným způsobem, a proto zámek záměru eliminuje nutnost kontrolovat každý zámek řádku/stránky v tabulce, aby se ujistil, že transakce může získat zámek na celou tabulku
Existují tři běžné zámky záměru a tři tzv. konverzní zámky:
Pravidelné zámky záměru:
Exkluzivní zámek záměru (IX) – když je získán exkluzivní zámek záměru (IX), znamená to pro SQL Server, že transakce má v úmyslu modifikovat některé prostředky nižší hierarchie tím, že získá exkluzivní (X) zámky jednotlivě na těchto prostředcích nižší hierarchie
Sdílený zámek záměru (IS) – když je získán sdílený zámek záměru (IS), znamená to pro SQL Server, že transakce má záměr číst některé prostředky nižší hierarchie tím, že získá sdílené zámky (S) jednotlivě na těchto prostředcích níže v hierarchii
Záměr aktualizace (IU) – když je získán záměr sdíleného zámku (IS), znamená to pro SQL Server, že transakce má záměr číst některé prostředky nižší hierarchie tím, že získá sdílené zámky (S) jednotlivě na těchto prostředcích níže v hierarchii. Zámek záměru aktualizace (IU) lze získat pouze na úrovni stránky a jakmile proběhne operace aktualizace, převede se na zámek záměru exkluzivity (IX)
Konverze zámků:
Sdílený se záměrem exkluzivity (SIX) – po získání tento zámek indikuje, že transakce má v úmyslu číst všechny prostředky na nižší hierarchii a získat tak sdílený zámek na všech prostředcích, které jsou níže v hierarchii, a následně modifikovat část z nich, ale ne všechny. Přitom získá exkluzivní zámek záměru (IX) na ty prostředky nižší hierarchie, které mají být modifikovány. V praxi to znamená, že jakmile transakce získá zámek SIX na tabulce, získá zámek intent exclusive (IX) na modifikované stránky a zámek exclusive (X) na modifikované řádky.
Na tabulce může být současně získán pouze jeden sdílený se záměrným exkluzivním zámkem (SIX), který zablokuje ostatním transakcím provádění aktualizací, ale nezabrání ostatním transakcím číst nižší zdroje hierarchie, které mohou získat záměrný sdílený zámek (IS) na tabulce
Sdílený se záměrnou aktualizací (SIU) – jedná se o trochu specifičtější zámek, protože je kombinací zámků sdíleného (S) a záměrné aktualizace (IU). Typickým příkladem tohoto zámku je situace, kdy transakce používá dotaz provedený s nápovědou PAGELOCK a dotazem a následně aktualizačním dotazem. Poté, co transakce získá zámek SIU na tabulce, získá dotaz s hintem PAGELOCK sdílený zámek (S), zatímco aktualizační dotaz získá zámek intent update (IU)
Aktualizace s intent exclusive (UIX) – když jsou současně získány zámky update (U) a intent exclusive (IX) na zdrojích nižší hierarchie v tabulce, bude následkem toho na úrovni tabulky získán zámek aktualizace s exkluzivním záměrem
Zámky schématu (Sch) – databázový stroj SQL Serveru rozeznává dva typy zámků schématu: Zámek modifikace schématu (Sch-M) a zámek stability schématu (Sch-S)
- Zámek modifikace schématu (Sch-M) bude získán při provádění příkazu DDL a zabrání přístupu k datům uzamčeného objektu při změně jeho struktury. SQL Server umožňuje jeden zámek modifikace schématu (Sch-M) na libovolný uzamčený objekt. Aby mohla transakce změnit tabulku, musí počkat na získání zámku Sch-M na cílovém objektu. Jakmile transakce získá zámek modifikace schématu (Sch-M), může objekt modifikovat a po dokončení modifikace a uvolnění zámku. Typickým příkladem zámku Sch-M je obnova indexu, protože obnova indexu je procesem modifikace tabulky. Po vydání ID pro přestavbu indexu bude na dané tabulce získán zámek modifikace schématu (Sch-M), který bude uvolněn až po dokončení procesu přestavby indexu (při použití volby ONLINE získá přestavba indexu zámek Sch-M krátce na konci procesu)
- Zámek stability schématu (Sch-S) bude získán během sestavování a provádění dotazu závislého na schématu a generování plánu provádění. Tento konkrétní zámek nebude blokovat přístup ostatních transakcí k datům objektu a je kompatibilní se všemi režimy zámků s výjimkou zámku modifikace schématu (Sch-M). Zámky stability schématu budou v podstatě získávány každým dotazem DML a selectem, aby byla zajištěna integrita struktury tabulky (aby se zajistilo, že se tabulka během provádění dotazů nezmění).
Zámky hromadné aktualizace (BU) – tento zámek je určen pro operace hromadného importu, pokud je vydán s argumentem/nápovědou TABLOCK. Při získání zámku hromadné aktualizace nebudou mít ostatní procesy během provádění hromadného načítání přístup k tabulce. Zámek hromadné aktualizace však nebrání paralelnímu zpracování dalšího hromadného načítání. Mějte však na paměti, že použití zámku TABLOCK na tabulce se shlukovaným indexem neumožní paralelní hromadný import. Podrobnější informace o této problematice naleznete v Pokynech pro optimalizaci hromadného importu
Hierarchie zamykání
SQL Server zavedl hierarchii zamykání, která se uplatňuje při čtení nebo změně dat. Hierarchie zámků začíná databází na nejvyšší úrovni hierarchie a přes tabulku a stránku sestupuje k řádku na nejnižší úrovni
V podstatě vždy existuje sdílený zámek na úrovni databáze, který je zaveden vždy, když je k databázi připojena transakce. Sdílený zámek na úrovni databáze je zaveden proto, aby se zabránilo shození databáze nebo obnovení zálohy databáze nad používanou databází. Například při vydání příkazu SELECT pro čtení nějakých dat bude na úrovni databáze zaveden sdílený zámek (S), na úrovni tabulky a stránky bude zaveden sdílený zámek záměru (IS) a na samotném řádku bude zaveden sdílený zámek (S)
V případě příkazu DML (tj. vložení, aktualizace, odstranění) bude na úrovni databáze zaveden sdílený zámek (S), na úrovni tabulky a stránky bude zaveden záměrný exkluzivní zámek (IX) nebo záměrný aktualizační zámek (IU) a na úrovni řádku bude zaveden exkluzivní nebo aktualizační zámek (X nebo U)
Zámky budou vždy získávány shora dolů, protože tímto způsobem SQL Server zabraňuje vzniku tzv. podmínky Race.
Nyní, když jsme si vysvětlili režimy zámků a hierarchii zámků, se dále věnujme režimům zámků a jejich převodu na hierarchii zámků.
Ne všechny režimy zámků lze použít na všech úrovních.
Na úrovni řádků lze použít následující tři režimy zámků:
- Exkluzivní (X)
- Sdílený (S)
- Aktualizace (U)
Pro pochopení kompatibility těchto režimů se podívejte na následující tabulku:
| Exkluzivní (X) | Sdílený (S) | Aktualizace (U) | |
| Exkluzivní (X) | ✗ | ✗ | ✗ |
| Sdílený (S) | ✗ | ✓ | ✓ |
| Aktualizace (U) | ✗ | ✓ | ✗ |
✓ – Kompatibilní ✗ – Nekompatibilní
Na úrovni tabulky, existuje pět různých typů zámků:
- Exkluzivní (X)
- Sdílené (S)
- Záměrně exkluzivní (IX)
- Záměrně sdílené (IS)
- Sdílené se záměrnou exkluzivitou (SIX)
Kompatibilitu těchto režimů lze vidět v následující tabulce
.
| (X) | (S) | (IX) | (IS) | (SIX) | ||
| (X) | ✗ | ✗ | ✗ | ✗ | ✗ | |
| (S) | ✗ | ✓ | ✗ | ✓ | ✗ | |
| (IX) | ✗ | ✗ | ✗ | ✓ | ✓ | ✗ |
| (IS) | ✗ | ✓ | ✓ | ✓ | ✓ | ✓ |
| (ŠEST) | ✗ | ✗ | ✗ | ✓ | ✗ |
✓ – Kompatibilní ✗ – Nekompatibilní
Zámek schématu (Sch) je zároveň zámkem na úrovni tabulky, ale není to zámek související s daty
Pro lepší pochopení kompatibility mezi těmito typy zámků se podívejte na tuto tabulku:
Eskalace zámku
Aby se zabránilo situaci, kdy zamykání využívá příliš mnoho prostředků, zavedl SQL Server funkci eskalace zámku.
Bez eskalace by zámky mohly vyžadovat značné množství paměťových prostředků. Uveďme příklad, kdy je třeba zavést zámek na 30 000 řádků dat, kde každý řádek má velikost 500 bajtů, aby bylo možné provést operaci odstranění. Bez eskalace bude na databázi zaveden sdílený zámek (S), na tabulku 1 záměrný exkluzivní zámek (IX), na stránky 1 875 záměrných exkluzivních zámků (IX) (8KB stránka obsahuje 16 řádků o velikosti 500 bajtů, což je 1 875 stránek, které obsahují 30 000 řádků) a na samotné řádky 30 000 exkluzivních zámků (X). Protože každý zámek má velikost 96 bajtů, 31 877 zámků zabere pro jednu operaci mazání přibližně 3 MB paměti. Spuštění velkého počtu operací paralelně by mohlo vyžadovat značné prostředky jen proto, aby správce zamykání mohl operaci plynule provést
Aby se takové situaci zabránilo, používá SQL Server eskalaci zámků. To znamená, že v situaci, kdy je na jedné úrovni získáno více než 5 000 zámků, SQL Server tyto zámky eskaluje na jeden zámek na úrovni tabulky. Ve výchozím nastavení bude SQL Server vždy eskalovat přímo na úroveň tabulky, což znamená, že k eskalaci na úroveň stránky nikdy nedojde. Namísto získávání četných zámků řádků a stránek bude SQL Server eskalovat na exkluzivní zámek (X) na úrovni tabulky
Tím se sice sníží potřeba prostředků, ale exkluzivní zámky (X) v tabulce znamenají, že k uzamčené tabulce nebude moci přistupovat žádná jiná transakce a všechny dotazy, které se pokusí k této tabulce přistoupit, budou blokovány. Proto to sice sníží režii systému, ale zvýší pravděpodobnost souběžné kontaminace
Aby byla zajištěna kontrola nad eskalací, počínaje SQL Serverem 2008 R2, zavedena možnost LOCK_EXCALATION jako součást příkazu ALTER TABLE
Každá z těchto možností je definována tak, aby umožnila specifickou kontrolu nad procesem eskalace zámků:
Table – Tato možnost je výchozí pro každou nově vytvořenou tabulku, protože ve výchozím nastavení SQL Server vždy provede eskalaci zámku na úroveň tabulky, což zahrnuje i tabulky s oddíly
Auto – Tato možnost umožňuje eskalaci zámku na úroveň oddílu, pokud je tabulka rozdělena. Pokud je v jednom oddílu získáno 5 000 zámků, eskalace zámků získá exkluzivní zámek (X) na tomto oddílu, zatímco tabulka získá intent exkluzivní zámek (IX). V případě, že daná tabulka není rozdělena na oddíly, získá eskalace zámku zámek na úrovni tabulky (rovná se volbě Tabulka).
Ačkoli tato možnost vypadá jako velmi užitečná, je třeba ji používat velmi opatrně, protože může snadno způsobit deadlock. V situaci, kdy máme dvě transakce na dvou oddílech, kde je získán exkluzivní zámek (X), a transakce se pokusí přistoupit k datům z oddílu používaného jinou transakcí, dojde k deadlocku
Takže, je velmi důležité pečlivě kontrolovat vzor přístupu k datům, pokud je tato volba povolena, čehož není snadné dosáhnout, a proto tato volba není ve výchozím nastavení SQL Serveru
Zakázat – Tato volba zcela zakáže eskalaci zámku pro tabulku. Tuto volbu je opět nutné používat opatrně, aby správce zámků SQL Serveru nebyl nucen používat nadměrné množství paměti
Jak je vidět, eskalace zámků může být pro DBA problém. Pokud návrh aplikace vyžaduje mazání nebo aktualizaci více než 5 000 řádků najednou, je řešením, jak se vyhnout eskalaci zámků a z toho plynoucím důsledkům, rozdělení jediné transakce na dvě nebo více transakcí, z nichž každá bude zpracovávat méně než 5 000 řádků, protože tímto způsobem by se eskalaci zámků dalo vyhnout
Zjištění informací o aktivních zámcích SQL Serveru
SQL Server poskytuje sys.dm_tran_locks, který vrací informace o aktuálně používaných prostředcích správce zámků, což znamená, že zobrazí všechny „živé“ zámky získané transakcemi. Další podrobnosti o tomto DMV najdete v článku sys.dm_tran_locks (Transact-SQL).
Nejdůležitější sloupce používané pro identifikaci zámku jsou resource_type, request_mode a resource_description. V případě potřeby lze při řešení problémů zahrnout další sloupce jako dodatečné informační informace o zdroji
Zde je příklad dotazu
Klauzule where v tomto dotazu slouží jako filtr na resource_type k vyřazení. z výsledků ty, které jsou obecně sdílené zámky získané v databázi, protože ty jsou vždy přítomny na úrovni databáze
Stručné vysvětlení tří zde uvedených sloupců:
resource_type – Zobrazuje databázový prostředek, ve kterém se zámky získávají. Sloupec může zobrazovat jednu z následujících hodnot: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID
request_mode – zobrazuje režim zámku, který je na prostředku získáván
resource_description – zobrazuje stručný popis prostředku a není vyplněn pro všechny režimy zámku. Nejčastěji sloupec obsahuje id řádku, stránky, objektu, souboru, atd
- Autor
- Poslední příspěvky
Vyznavač vojenského letectví a zarytý modelář zmenšených letadel. Fanoušek extrémních sportů; parašutista a instruktor bungee jumpingu. Kdysi vážný, nyní jen volnočasový fotograf
Zobrazit všechny příspěvky od Nikola Dimitrijevic
- SQL Server trace flags guide; od -1 do 840 – 4. března 2019
- Jak zacházet s typem čekání SQL Server WRITELOG – 13. června 2018
- Čítače výkonu SQL Serveru (Dávkové požadavky/sec nebo Transakce/sec): Co sledovat a proč – 5. června 2018