Archiv pro štítek: deadlock

Jak na UPSERT

Často potřebujeme podle určitého klíče zaktualizovat hodnotu. A pokud klíč neexistuje, tak ho s hodnotou vložit do tabulky. Tedy UPDATE nebo INSERT…UPSERT. A často nás na první dobrou napadne úplně špatné řešení.

CREATE TABLE dbo.tabulka(
	id INT IDENTITY(1,1) NOT NULL CONSTRAINT pk_tabulka PRIMARY KEY CLUSTERED, 
	dalsiKlic VARCHAR(50) NOT NULL CONSTRAINT uq_tabulka_dalsiKlic UNIQUE, 
	data VARCHAR(50) NULL
)
GO

CREATE OR ALTER PROCEDURE dbo.p_velky_spatny_UPSERT
	@dalsiKlic VARCHAR(50)
	,@data VARCHAR(50)
AS
BEGIN
	IF EXISTS(
		SELECT * 
		FROM  dbo.tabulka t
		WHERE t.dalsiKlic = @dalsiKlic
	)
		UPDATE dbo.tabulka
		SET data = @data
		WHERE dalsiKlic = @dalsiKlic
	ELSE
		INSERT INTO dbo.tabulka
		(
			dalsiKlic,
			data
		)
		VALUES
		(   @dalsiKlic,
			@data
			)
END
GO

Tedy najdi záznam a pokud jsi našel, tak ho najdi znova a zaktualizuj. No a pokud jsi ho nenašel, tak ho založ. Problém je, že pokud záznam existuje, tak ho hledám dvakrát a plýtvám tak zdroji. Druhý problém je, že ta operace není atomická a v READ COMMITED isolation levelu se může stát, že dvě transakce najednou ověří, že záznam s daným klíčem neexistuje a obě se ho pak pokusí založit. V lepším případě pak skončíme s chybou Violation of UNIQUE KEY constraint…, v horším založíme duplicitu (pokud nám chybí constraint). Zabalit to do transakce nic neřeší.

Zabalit to do transakce a zvýšit isolation level na serializable řešením je:

CREATE OR ALTER PROCEDURE dbo.p_velky_spatny_serializable_UPSERT
	@dalsiKlic VARCHAR(50)
	,@data VARCHAR(50)
AS
BEGIN
	SET TRAN ISOLATION LEVEL SERIALIZABLE
	BEGIN TRAN
	IF EXISTS(
		SELECT * 
		FROM  dbo.tabulka t
		WHERE t.dalsiKlic = @dalsiKlic
	)
		UPDATE dbo.tabulka
		SET data = @data
		WHERE dalsiKlic = @dalsiKlic
	ELSE
		INSERT INTO dbo.tabulka
		(
			dalsiKlic,
			data
		)
		VALUES
		(   	@dalsiKlic,
			@data
		)
	COMMIT
END
GO

Ale tím vyměníme problém se zakládáním duplicit, za problém s deadlocky. U neexistující položky provede select v existu zamčení RangeS-S zámkem, čímž si pojistí, aby daná hodnota nebyla vložena v jiné transakci. Problém je, že RangeS-S je kompatiblní s RangeS-S. Tedy, pokud se dvě transakce sejdou a obě provedou EXISTS podmínku, tak si obě můžou hodit na stejný rozsah záznamů RangeS-S zámek. Následně při insertu se pokusí použít X zámek, čímž dojde k deadlocku.

Jak je to správně?
Správně to je v transakci, v serializable a bez zbytečného selectu.

CREATE OR ALTER PROCEDURE dbo.p_dobry_UPSERT
	@dalsiKlic VARCHAR(50)
	,@data VARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	UPDATE dbo.tabulka WITH (SERIALIZABLE)
	SET data = @data
	WHERE dalsiKlic = @dalsiKlic

	IF @@ROWCOUNT = 0
		INSERT INTO dbo.tabulka
		(
			dalsiKlic,
			data
		)
		VALUES
		(   	@dalsiKlic,
		    	@data
		)
	COMMIT
END
GO

Pokud záznam existuje, tak se provede update. Pokud neexistuje, tak po updatu zůstane na unikátním indexu RangeS-U zámek, který zabezpečí, že si jiný proces neuvalí stejný zámek a zároveň, že jiný proces nevloží hodnotu klíče, o které jsme se právě přesvědčili, že neexistuje. Tedy žádné deadlocky a žádné Violation of UNIQUE KEY constraint.

UPDATE WITH (UPDLOCK)

Ve zděděném kódu občas potkávám UPDATE tabulka WITH (UPDLOCK) SET… a nikdo mi k tomu nechtěl říct jestli to je k něčemu dobré. Teorie praví, že pro vyhledání updatovaného záznamu se použije U zámek a updatovaný záznam pak dostane X zámek, který se drží do konce transakce. Hint WITH (UPDLOCK) by měl zaručit držení U zámku do konce transakce. Pojďme si to zkusit v praxi.

Zkoušet to budeme na 3 tabulkách. Za prvé na haldě bez primárního klíče. Za druhé na haldě s neklastrovaným primárním klíčem. A za třetí na tabulce s klastrovaným primáním klíčem, kterou ještě okořeníme druhým indexovaným klíčem.

CREATE TABLE dbo.halda(
	id INT NOT NULL, 
	data VARCHAR(500) NULL
)

CREATE TABLE dbo.haldaPK(
	id INT NOT NULL CONSTRAINT pk_haldaPK PRIMARY KEY NONCLUSTERED, 
	data VARCHAR(500) NULL
)

CREATE TABLE dbo.tabulka(
	id INT NOT NULL CONSTRAINT pk_tabulka PRIMARY KEY CLUSTERED, 
	dalsiKlic VARCHAR(50) NOT NULL CONSTRAINT uq_tabulka_dalsiKlic UNIQUE, 
	data VARCHAR(50) NULL
)
GO

Naplníme nějakými testovacími daty

INSERT INTO dbo.halda(id, data)
VALUES(1,'a'),(2,'b'),(3,'c')

INSERT INTO dbo.haldaPK(id, data)
VALUES(1,'a'),(2,'b'),(3,'c')

INSERT INTO dbo.tabulka(id,dalsiKlic,data)
VALUES(1,'a','dataA'),(2,'b','dataB'),(3,'c','dataC')
GO

A jdeme zkoušet updaty. Nejdříve na haldě.

BEGIN TRAN
UPDATE dbo.halda WITH (UPDLOCK) SET data = 'x' WHERE id = 3
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1845581613  0      RID  1:40441:2                        X        GRANT
61     32     1845581613  0      PAG  1:40441                          IX       GRANT
61     32     1845581613  0      TAB                                   IX       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
*/

Hmm, tak na první pokus žádný U zámek.

Zkusíme haldu s primárním klíčem.

BEGIN TRAN
UPDATE dbo.haldaPK WITH (UPDLOCK) SET data = 'x' WHERE id = 3
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1861581670  2      KEY  (98ec012aa510)                   U        GRANT
61     32     1861581670  0      RID  1:8089:2                         X        GRANT
61     32     1861581670  0      TAB                                   IX       GRANT
61     32     1861581670  0      PAG  1:8089                           IX       GRANT
61     32     1861581670  2      PAG  1:16177                          IU       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
*/

Tady už se něco událo. Vidíme X zámek na záznamu na haldě a U zámek na záznamu indexu primárního klíče.

Teď zkusme update tabulky s klastrovaným primárním klíčem, kde budeme vyhledávat právě podle toho primárního klíče.

BEGIN TRAN
UPDATE dbo.tabulka WITH (UPDLOCK) SET data = 'x' WHERE id = 3
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1909581841  1      KEY  (98ec012aa510)                   X        GRANT
61     32     1909581841  0      TAB                                   IX       GRANT
61     32     1909581841  1      PAG  1:24265                          IX       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
*/

A tentokrát zase žádný U zámek.

Naposledy zkusíme hledat v klastrované tabulce podle jiného klíče než primárního.

BEGIN TRAN
UPDATE dbo.tabulka WITH (UPDLOCK) SET data = 'x' WHERE dalsiKlic = 'c'
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1909581841  1      KEY  (98ec012aa510)                   X        GRANT
61     32     1909581841  0      TAB                                   IX       GRANT
61     32     1909581841  2      KEY  (f037bcc414ff)                   U        GRANT
61     32     1909581841  1      PAG  1:24265                          IX       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
61     32     1909581841  2      PAG  1:32353                          IU       GRANT
*/

Zafungovalo to. Updatovaný záznam v klastrovaném indexu je zamčený X zámkem a záznam v unikátním indexu je zamčený U zámkem.

Závěr
Závěrem zatím budiž to, že při použití WITH (UPDLOCK) se U zámek drží do konce transakce na záznamu indexu, podle kterého se vyhledávalo, pokud je tento rozdílný od podkladové struktury, která drží data (halda, klastrovaný index).

Využití
Hodně jsem se snažil najít nějaký pěkný příklad, k čemu je to dobré. Ale našel jsem jen jeden dost pochybný. Pokud mám nějaký kód, který v jedné transakci dvakrát updatuje stejný řádek a navíc je tento kód vykonáván paralelně ve dvou vláknech nad stejným záznamem. V takovém případě může dojít k deadlocku, kterému se dá zabránit právě tím UPDATE WITH (UPDLOCK).

Příkladem budiž:

BEGIN TRAN
UPDATE dbo.tabulka SET data = 'x' WHERE dalsiKlic = 'c'

--nejaka dalsi prace
WAITFOR DELAY '0:0:10'


UPDATE dbo.tabulka SET data = 'x' WHERE dalsiKlic = 'c'
COMMIT

Tohle když spustím ve dvou oknech SSMS, tak mi to v jednom okně upadne na deadlock.

Mechanismus je takový, že se provede první update v prvním okně a na záznamu v klastrovaném indexu je pak držen X zámek. První update ve druhém okně pak provede jen vyhledání záznamu k updatu v unikátním indexu, který si označí U zámkem. Následně se pokusí označit U zámkem i záznam v klastrovaném indexu, což nebude kompatibilní s již uvaleným X zámkem, takže zůstane čekat na jeho uvolnění. Transakce v prvním okně mezitím dojde k druhému updatu. Pokusí se vyhledat záznam pro update v unikátním klíči a uvalit na něj U zámek, což nebude kompatibilní s již uvaleným U zámkem z druhé transakce. Dostaneme se tedy do situace, kdy první transakce čeká na druhou a druhá na první. Deadlock. SQL server jednu vybere a sestřelí.

Předejít tomu jde přidáním WITH (UPDLOCK) na první update.

BEGIN TRAN
UPDATE dbo.tabulka WITH (UPDLOCK) SET data = 'x' WHERE dalsiKlic = 'c'

--nejaka dalsi prace
WAITFOR DELAY '0:0:10'


UPDATE dbo.tabulka SET data = 'x' WHERE dalsiKlic = 'c'
COMMIT

V takovém případě zůstane po prvním updatu v prvním okně držen X zámek na záznamu v klastrovaném indexu a U zámek na záznamu v unikátním indexu. Transakci v druhém okně se už nepovede přidat U zámek na záznam v unikátním indexu a zůstane tedy čekat. Zároveň už nebude nijak omezovat transakci z prvního okna v úspěšném dokončení.

MocPeknyDeadlock

Kde najít info k proběhlému deadlocku

Kde najít info k proběhlému deadlocku? Od SQL 2012 v by default zapnuté extended event session system_health. Možná je to už od 2008, ale tam to má jiný formát a neznám nikoho, kdo by to tam chtěl hledat :) V sytem_health se loguje více věcí než deadlocky, má omezenou velikost a funguje tak, že nově logované události přepisují ty nejstarší. Na vytíženém serveru tam nemusí být historie ani za celý den.

Lepší je si založit vlastní session jen na deadlocky. Třeba takto:

CREATE EVENT SESSION [deadlocks] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'deadlocks.xel',max_file_size=(20),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

A teď fotoseríál jak z Bravíčka, jak se k těm informacím doklikat.

V Object Exploreru je to Management/Extended Events/Sessions/system_health a poklikat event_file
Deadlock_system_health

Já pak dávám Grouping, abych ten zmatek trochu zpřehlednil
Deadlock_system_health2

Podle názvu události
Deadlock_system_health3

Pak už je k nalezení deadlock, pokud je nějaký zalogovaný. Z deadlock grafu je vidět akorát o kterou jde databázi a o které tabulky se tam soupeřilo.
Deadlock_system_health4

Všechny informace jsou pak v XML, které dostanem na panelu Details po poklikání na XML.
Deadlock_system_health5

V XML jsou pak stacky volání a konkrétní dotazy, které se deadlocku zúčastnily a taky zdroje o které tam šlo. Zdroje tam jsou uvedené v podobě waitresource. Pokud je potřeba zjistit o který konkrétní záznam v tabulce šlo, tak tady Kendra Little popisuje jak ten waitresource rozkódovat.

Retry logika na ošetření deadlocku

Nejjednodušším řešením deadlocku je spustit znova transakci, která se stala jeho obětí. Transakce, která se stala obětí deadlocku je neplatná. Nedá se nad ní udělat commit, ani rollback po savepoint. Jedinnou možností je rollback celé transakce. Z toho plyne, že retry logiku můžu nasadit jen v místě, kde mám kontrolu nad zahájením transakce. Celou transakci pak zabalím do cyklu WHILE a v něm se ji v případě deadlocku pokusím několikrát zopakovat. Pokud se mi to nepodaří ani na několikátý pokus, pak chybu vypropaguju ven.

DECLARE @pocetPokusu INT = 1;
WHILE (1=1)
BEGIN
	BEGIN TRY
		BEGIN TRAN

		RAISERROR('pokousim se vykonat transakci',1,1) WITH NOWAIT;

		EXECUTE('
		CREATE TYPE dbo.instantiDeadlock AS TABLE(
			Id INT NOT NULL
		)
		')

		EXEC('
		declare @t dbo.instantiDeadlock;')

		COMMIT;
		BREAK;
	END TRY
	BEGIN CATCH
		ROLLBACK;

		IF ERROR_NUMBER() = 1205 AND @pocetPokusu < =  3--jsem obeti deadlocku a jeste jsem si nedal 3 pokusy
		BEGIN
			RAISERROR('jsem obeti deadlocku',1,1) WITH NOWAIT;
			SET @pocetPokusu += 1;
		END
		ELSE
			THROW;
	END CATCH
END

IF EXISTS (SELECT 1 FROM sys.table_types tt WHERE tt.name = 'instantiDeadlock')
	DROP TYPE instantiDeadlock


pokousim se vykonat transakci
Msg 50000, Level 1, State 1
jsem obeti deadlocku
Msg 50000, Level 1, State 1
pokousim se vykonat transakci
Msg 50000, Level 1, State 1
jsem obeti deadlocku
Msg 50000, Level 1, State 1
pokousim se vykonat transakci
Msg 50000, Level 1, State 1
jsem obeti deadlocku
Msg 50000, Level 1, State 1
pokousim se vykonat transakci
Msg 50000, Level 1, State 1
Msg 1205, Level 13, State 55, Line 6
Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

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.

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.