V běžném provozu se stává, že k jedné informaci uložené v databázi přistupuje několik klientů současně. V případě že všichni klienti chtějí informaci pouze číst, nenastává žádný problém. Potíže ovšem nastávají v případě, kdy jeden klient chce data číst a druhý je zároveň měnit. SQL Server proto implementuje zamykání řádků (Row Lock), stránek (Page Lock) či rovnou celých tabulek (Table Lock).
Databázových zámků existuje několik typů a o celé jejich řízení se stará Lock Manager. V jednoduchosti si celý princip zámku představme tak, že na začátku transakce jsou data zamčena a při ukončení transakce provedením COMMIT nebo ROLLBACK jsou všechny zámky odstraněny.
Lock Granularity
SQL server se snaží uzamykat data tak, aby zbytečně nesnižoval souběžný přístup více klientů k datům a zároveň aby počet zámků nezatěžoval server jako takový. Každý zámek a jeho správa odebírá SQL serveru část dostupných zdrojů jako je CPU, paměť atp. Čím více zámků je v systému aktivních, tím více je potřeba zdrojů na jejich režii. Jak jsem již předeslal, data mohou být uzamčena na úrovni řádku, stránky nebo tabulky. Přičemž platí, že největší možnost souběžného přístupu k datům je při úrovni zámku na řádku a nejmenší v případě zamčení celé tabulky.
Nejprve si představme strukturu tabulky, kterou v další části článku použijeme k demonstraci zámků. V databázi existuje tabulka Location3. Nad tabulkou existuje primární klíč PK_Location3_LocationID. Tabulka obsahuje dva indexy. První clusterovaný index náleží primárnímu klíči a je nazván PK_Location3_LocationID, druhý index IX_Name_ModifiedDate zahrnuje dva sloupce, jak je patrné z názvu. Postupně budeme souběžně spouštět dvě transakce a zkoumat, co se nad tabulkou děje. Obě transakce jsou spouštěny ze samostatných oken v SQL Management Studiu. Na konci každé transakce provádím rollback, abych si nezměnil data. První transakce má před rollbackem čekání 20 vteřin.
Následující tabulky ilustrují jak vypadají řádky uložené v databázi. Z tabulky Location3 nás budou zajímat pouze tyto tři řádky, jež budeme měnit.
| LocationID | Name | CostRate | Availability | ModifiedDate |
|---|---|---|---|---|
| 1 | A1 | 2 | 100 | 21.09.16 18:24 |
| 2 | A2 | 2 | 100 | 21.09.16 18:25 |
| 22000 | B2000 | 2 | 100 | 21.09.16 18:35 |
Dále se nám bude hodit znalost čísel stránek, jež obsahují uložené řádky pro oba existující indexy. Pro PK platí, že řádek A1 a A2 nalezneme uložené ve stránce číslo 36760 a budeme-li chtít vyhledat řádek B2000 podle druhého indexu IX, musíme mít přístup ke stránce číslo 54763.
| Name | PageID |
|---|---|
| A1 | 36760 |
| A2 | 36760 |
| B2000 | 37290 |
| Name | PageID |
|---|---|
| A1 | 54665 |
| A2 | 54670 |
| B2000 | 54763 |
Row Lock
První transakce bude provádět změnu data v řádku kde Name = ‚A1‘
BEGIN TRANSACTION T1 UPDATE Production.Location3 WITH (ROWLOCK) SET ModifiedDate = GETDATE() WHERE Name = 'A1' WAITFOR DELAY '00:00:20:00'; ROLLBACK TRANSACTION T1
Druhá transakce se zároveň pokusí provést změnu data v řádku kde Name = ‚A2‘
BEGIN TRANSACTION T2 UPDATE Production.Location3 WITH (ROWLOCK) SET ModifiedDate = GETDATE() WHERE Name = 'A2' ROLLBACK TRANSACTION T2
Výsledkem je téměř okamžité provedení druhé transakce a po 20 vteřinách dokončení první transakce. Podívejme se blíže na zámky, které se v testu objevily:
Transakce T1 v souladu s nastavením (ROWLOCK) aktivovala zámky nad řádkem resp. indexem, které se promítly do exkluzivního zámku (X). Na obrázku můžete vidět zámky na řádku 2 – 4. Povšimněte si prosím řádku 6 a 7. Zde jsou uvedena čísla stránek, kde jsou uložená vlastní data tabulky.
Obdobně vypadá i výstup pro transakci T2:
Jelikož se obě transakce ve změně obsahu tabulky nikde nepřekrývaly (různé hashe u zámku X) a díky vysoké granularitě zámku, mohly obě změny proběhnout zároveň.
Page Lock
Ve druhém příkladu změníme způsob zamykání na Page Lock. Stačí nahradit ROWLOCK za PAGLOCK. To znamená, že exkluzivní zámek nyní bude nad stránkou (PAGE). Z výše uvedených obrázků je patrné, že obě transakce ke svému provedení potřebují změnit stránku číslo 36760, kde jsou uloženy oba řádky tabulky, které chceme měnit. Mělo by tedy dojít k pozastavení druhé transakce T2 do té doby, dokud neskončí první transakce T1.
Podívejme se na zámky, které se nám objevily nad tabulkou:
Na obrázku vidíme, že exkluzivní zámky jsou nad stránkami. Tedy druhá transakce musí čekat na uvolnění požadované stránky první transakcí.
Změnou způsobu zamykání dat jsme zmenšili granularitu na úroveň stránek a tedy místo zamčení jednoho řádku tabulky jsme docílili zamčení všech řádků tabulky, které jsou uloženy ve stejné stránce. Pokračujme však dále v našem zkoušení. Pokud změníme druhou transakci tím, že budeme měnit datum řádku, který je uložen v jiné stránce, měla by transakce proběhnout téměř okamžitě jako při zámku nad řádkem.
První transakci tedy necháme nezměněnou a druhou změníme následovně:
BEGIN TRANSACTION T2 UPDATE Production.Location3 WITH (ROWLOCK) SET ModifiedDate = GETDATE() WHERE Name = 'B2000' ROLLBACK TRANSACTION T2
Dle našeho předpokladu, druhá transakce nyní proběhla bez čekání na ukončení první transakce. Na obrázku můžeme zkontrolovat požadované zámky. Srovnáme-li čísla zamčených stránek obou transakcí, zjistíme, že jsou rozdílná.
Table Lock
V našem posledním příkladě snížíme ještě více granularitu zámků a v obou transakcích zamkneme pro jistotu celou tabulku. Vezmeme skripty z prvního příkladu a zaměníme ROWLOCK za TABLOCK.
Díky zámku nad celou tabulkou bude druhá transakce vždy čekat dokud neskončí první transakce. Požadavek obou transakcí na zámky jsou shodné a můžete je vidět na následujícím obrázku:
Lock Hierarchy
V předchozích příkladech jste si mohli povšimnout předávání informace o zámku z jedné úrovně na druhou. Vyšší úroveň je tedy informována o tom, že někde níže probíhá transakce.
Při zámku nad řádkem (Row Lock), je zamčen řádek exkluzivním zámkem (X), který zakazuje přístup k danému řádku. Stránka která obsahuje zamčený řádek dostane příznak IX. Tento typ zámku umožňuje i nadále přístup ke stránce. Stejně tak i celá tabulka bude mít na sobě zámek IX.
V příštím díle se budeme podrobněji věnovat jednotlivým typům zámků.