Archiv autora: Jakub Rychlý

Deadlock, zámky a indexy

Nasimulujume deadlock.
Nejdříve si vytvoříme tabulku s clusterovaným indexem.

CREATE TABLE tabule(
  id INT CONSTRAINT pk_tabule PRIMARY KEY IDENTITY(1,1), 
  a INT
)

Dále v jednom okně management studia spustíme pár insertů pro naplnění tabulky.

BEGIN TRAN
INSERT INTO tabule(a) VALUES(3)
INSERT INTO tabule(a) VALUES(2)
INSERT INTO tabule(a) VALUES(1)

Transakci nepotvrdíme a proto záznamy zůstanou exkluzivně zamčené.

Ve druhém okně uděláme další insert.

BEGIN TRAN
INSERT INTO tabule(a) VALUES(4)

Zase transakci nepotvrdíme a záznam bude tudíž exkluzivně zamčený.

V prvním okně se třemi inserty ve stále běžící transakci se pokusíme smazat záznamy vyhovující podmínce.

DELETE FROM tabule WHERE a = 3

Protože na sloupci „a“ nemáme index, server se pokusí přečíst všechny záznamy (clustered index scan) a na každém záznamu se pokusí udělat UPDLOCK. Příkaz nedoběhne, protože bude čekat na uvolnění exkluzivního zámku na jednom vloženém záznamu z druhého okna.

Pokud se i ve druhém okně pokusíme mazat, pak dojde k deadlocku, protože v této situaci už ani jeden proces nemůže zámky uvolnit. Čekají na sebe navzájem = deadlock.

BEGIN TRAN
DELETE FROM tabule WHERE a = 4

Tyto problémy je možné vyřešit použitím vhodného indexu. Pokud vytvoříme index nad sloupcem „a“, pak při mazání záznamů s podmínkou proti sloupci „a“ server použije procházení indexu (index seek). Podaří se nám tedy smazat nezamčené záznamy, zamčené záznamy samozřejmě smazat nelze (vložené záznamy budou zamčené exkluzivně i na indexu a při mazání se nepovede vytvořit UPDLOCK zámek).

V obou transakcích provedeme ROLLBACK a vytvoříme index. Pak se pokusíme vkládat a mazat záznamy dle předchozího scénáře. Obojí se povede.

CREATE INDEX ix_tabule_a ON tabule(a)

Vhodným indexem tedy můžeme řešit problémy se zamykáním a deadlocky.

Zamknutí záznamu tak, aby nešel přečíst

Mějme situaci, kdy chceme uzamknout záznam tak, aby ho jiná transakce nemohla přečíst. Používáme ISOLATION LEVEL READ COMMITTED a kvůli propustnosti nechceme zamykat více záznamů než je nutné. Napadne nás použít hinty ROWLOCK a XLOCK, ale ejhle, ono to nefunguje.

Uvedu příklad. V jednom okně management studia vytvořím tabulku, naplním ji daty, započnu transakci, přečtu jeden záznam tabulky, který uzamknu, aby ho nemohl číst nikdo další. Dále spustím sp_lock a ověřím, že záznam je opravdu exkluzivně zamčený.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE tabulka(id INT CONSTRAINT PK_tabulka PRIMARY KEY IDENTITY(1,1), hodnota INT)
INSERT INTO tabulka VALUES(1)
INSERT INTO tabulka VALUES(2)
INSERT INTO tabulka VALUES(3)

BEGIN TRAN
SELECT *, %%LOCKRES%% AS Resource FROM tabulka WITH (ROWLOCK, XLOCK) WHERE id = 3
EXEC sp_lock

Potom ve druhém okně management studia přečtu z tabulky všechna data, očekávajíc, že to neprojde.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM tabulka

Ale ono to prošlo. Server totiž u ISOLATION LEVEL READ COMMITTED na zámky řádků moc nehledí. Všimne si, že záznam nebyl v jiné transakci změněn (je tedy COMMITTED) a klidně ho přečte. Pokud můžeme ovlivnit druhý SELECT, pak je řešením použít u něj hint HOLDLOCK. Pokud můžeme ovlivnit pouze první transakci, pak můžeme použít trik s UPDATE a vnutit tak serveru, že řádek se změnil a nemůže ho z jiné transakce číst.

UPDATE tabulka WITH (ROWLOCK) SET hodnota = hodnota + 0 WHERE id = 3

Pozor, SET hodnota = hodnota nestačí.

Další možností je, použít zamykání na úrovní stránky.

BEGIN TRAN
SELECT *, %%LOCKRES%% AS Resource FROM tabulka WITH (PAGLOCK, XLOCK) WHERE id = 3
EXEC sp_lock

Problém popsán taky v článku The madness of “exclusive” row locks.

Konverzní deadlock

Nasimulujeme si konverzní deadlock.

Nejdříve založíme tabulku, se kterou budeme následně pracovat.

CREATE TABLE tabule(a INT, b INT)
INSERT INTO tabule(a,b) VALUES(1,1)
INSERT INTO tabule(a,b) VALUES(2,2)
INSERT INTO tabule(a,b) VALUES(3,3)

Poté si v prvním okně management studia spustíme následující dotaz. (Řekněme, že hint HOLDLOCK používáme proto, že nechceme, aby nám jiný proces změnil čtená data pod rukama.)

BEGIN TRANSACTION
SELECT * FROM tabule WITH (HOLDLOCK)

A ten samý dotaz si spustíme i ve druhém okně management studia.

Oba dotazy doběhnou a vrátí data z tabulky. Protože jsme ale neukončili transakci a použili jsme HOLDLOCK budou oběma procesy sdíleně zamčené data tabulky.

Teď v obou oknech spustíme následující update.

UPDATE tabule SET b = 5 WHERE a= 1

V jednom okně pak dostaneme zprávu, že vznikl deadlock a proces byl vybrán jako oběť:

Msg 1205, Level 13, State 56, Line 1
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Ve druhém okně update projde.

Dostali jsme se do situace, kdy dva procesy měly sdíleně uzamknutý jeden zdroj. Následně oba procesy chtěly nad tímto zdrojem použít update lock. Žádný z procesů nemohl použít update zámek, protože zdroj byl sdíleně zamknutý druhým procesem. Neřešitelná situace, deadlock. Server tedy vybral jednu transakci jako oběť a zrušil ji, čímž uvolnil její sdílený zámek. Druhá transakce pak mohla udělat update.

Jak předejít konverznímu deadlocku

Pokud víme, že data čtená v transakci budeme v této transakci i modifikovat, pak můžeme konverznímu deadlocku předejít pomocí hintu UPDLOCK.

Pokud spustíme v prvním okně tento dotaz

BEGIN TRANSACTION
SELECT * FROM tabule WITH (UPDLOCK)

pak nám doběhne a vrátí data. Ve druhém okně nám ale stejný dotaz zůstane čekat, dokud neukončíme transakci v prvním okně. Druhý proces tedy bude už od začátku čekat a nebude mít možnost uvalit na data žádné zámky. K deadlocku tedy již nemůže dojít.

Query hints a zamykání

HOLDLOCK
Použité zámky jsou drženy až do konce transakce. Obdobné jako SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, akorát jen pro jednu tabulku.
UPDLOCK
Nutí server použít při čtení update lock namísto share lock. Můžeme použít pro eliminaci konverzních deadlocků.
TABLOCK
Nutí server použít share lock na celou tabulku i přesto, že by se jinak zamykala pouze stránka. To je užitečné, pokud víme, že by se zámky nakonec eskalovaly na celou tabulku. Pokud použijeme TABLOCK při mazání z haldy, umožníme SQL serveru uvolnit stránky hned, jakmile jsou smazány.
PAGLOCK
Nutí server použít sdílený zámek na stránku, tam kde by jinak použil sdílený zámek celé tabulky.
TABLOCKX
Exkluzivní zámek tabulky držený do konce transakce. (TABLOCK a XLOCK)
ROWLOCK
Použije se sdílený zámek řádků tam, kde by se jinak použil zámek stránky nebo tabulky.
READUNCOMMITTED, REPEATABLEREAD, SERIALIZABLE
Na tabulku se použije stejný mechanizmus zamykání, jako by byl zapnutý ISOLATION LEVEL se stejným názvem.
READCOMMITTED
Pokud je READ_COMMITTED_SNAPSHOT OFF, pak server používá sdílené zámky a uvolňuje je hned jak je to možné.

Pokud je READ_COMMITTED_SNAPSHOT ON, pak server nepoužívá zámky, ale verzování řádků.
READCOMMITTEDLOCK
Server používá sdílené zámky i pokud je READ_COMMITTED_SNAPSHOT ON.
NOLOCK
Umožnuje nepotvrzené, špinavé čtení. Zámky se nehlídají, je možné přečíst data, která jsou exkluzivně zamčená. Odpovídá READUNCOMMITED.
READPAST
Přeskakuje zamčené řádky. Aplikuje se pouze při READ COMMITTED isolation level a přeskakuje pouze zámky na úrovni řádků.
XLOCK
Použije exkluzivní zámky. Je možné kombinovat s PAGLOCK nebo TABLOCK.

Zámky

Zkratka Druh zámku Popis
S Shared Umožňuje ostatním procesům číst, ale neumožňuje měnit zamčené zdroje.
Tyto zámky jsou používány automaticky, když server čte data. Mohou být použíté na tabulku, stránku, klíč indexu, nebo řádek. Více procesů může použít sdílený zámek na stejný zdroj. Na zdroj zamčený sdíleným zámkem nelze použít exkluzivní zámek. Běžně jsou zámky uvolňovány jakmile jsou data přečtena. Toto můžeme ovlivnit hintem, nebo nastavením úrovně izolace.
X Exclusive Brání ostatním měnit i číst zamčená data. Sql server tento zámek používá při INSERT, UPDATE, DELETE operacích. Data jsou zamčená po celou dobu transakce, tedy než se provede COMMIT, nebo ROLLBACK. Na takto zamčená data nelze použít žádný jiný zámek. Ostatní procesy, tak nemají k datům přístup. Toto je možné změnit pomocí hintů.
U Update Brání ostatním získat update nebo exclusive zámek. Získání shared zámku a čtení je umožněno. SQL server používá tento zámek, když vyhledává data pro modifikaci. Použitím hintů můžeme tento zámek vynutit a předejít tak konvezním deadlockům.
IS Intent shared Označuje, že komponenta zdroje je zamčena shared zámkem. Může zamykat tabulku nebo stránku.
IU Intend update Označuje, že komponenta zdroje je zamčena update zámkem. Může zamykat tabulku nebo stránku.
IX Intent exclusive Označuje, že komponenta zdroje je zamčena exclusive zámkem. Může zamykat tabulku nebo stránku.
SIX Shared with intent exclusive Označuje, že zdroj zamčený shared zámkem obsahuje komponentu (stránku nebo řádek) zamčenou exclusive zámkem.
SIU Shared with intent update Označuje, že zdoj zamčený shared zámkem obsahuje komponentu (stránku nebo řádek) zamčenou update zámkem.
UIX Update with intent exclusive Označuje, že zdoj zamčený update zámkem obsahuje komponentu (stránku nebo řádek) zamčenou update zámkem.
Sch-S Schema stability Označuje, že tabulka je kompilována.
Sch-M Schema modification Označuje, že se mění struktura tabulky.
BU Bulk update Používá se při bulk operacích a zamyká celou tabulku.