Č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.