Problémy konzistence databáze

Ztracený update
Dva procesy přepisují stejná data na novou hodnotu. Hodnota z prvního procesu je ztracena a přepsána hodnotou z druhého procesu.

Špinavé čtení
Proces čte data, která upravuje jiná transakce, která ještě nebyla potvrzená COMMITem. Pokud druhý proces provede ROLLBACK, pak první proces přečetl chybná data.

Neopakovatelné čtení
Čtení je neopakovatelné, pokud proces v rámci transakce čte dvakrát stejná data a pokaždé přečte jinou hodnotu. To se stane pokud hodnotu mezitím změnil jiný proces.

Pro příklad předpokládejme, že máme tabulku se stavem zboží a v ní jeden kus zboží s id_zbozi = 5. Dvě souběžné transakce zkontrolují jestli je zboží dostatek a pokud ano, tak zboží vydají a sníží jeho stav na skladě.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @prodano_kusu INT = 1
DECLARE @id_zbozi INT = 5

BEGIN TRAN
--zboží je dostatek, pokračujeme dál
IF (
	(
		SELECT pocet 
		FROM stav_skladu 
		WHERE id_zbozi = @id_zbozi
	) >= @prodano_kusu)
BEGIN

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @prodano_kusu INT = 1
DECLARE @id_zbozi INT = 5

BEGIN TRAN
--zboží je dostatek, pokračujeme dál
IF (
	(
		SELECT pocet 
		FROM stav_skladu 
		WHERE id_zbozi = @id_zbozi
	) >= @prodano_kusu)
BEGIN

	--odeslani zbozi
	SELECT 'zbozi vydano'
	
	--update stavu skladu
	UPDATE stav_skladu 
	SET pocet = pocet - @prodano_kusu 
	WHERE id_zbozi = @id_zbozi

	--snížili jsme počet na 0
END
COMMIT

	--odeslani zbozi
	SELECT 'zbozi vydano'
	
	--update stavu skladu
	--zde dojde k neopakovatelnému čtení
	--počet se změnil od vyhodnoceni podmínky
	UPDATE stav_skladu 
	SET pocet = pocet - @prodano_kusu 
	WHERE id_zbozi = @id_zbozi

	--snížili jsme počet na -1
END
COMMIT

Fantom
Fantom se objeví, pokud v rámci jedné transakce proběhne dvakrát stejný dotaz a pokaždé vrátí jiný počet záznamů.

Dvojité čtení
Jedná se o opakovaně přečtený záznam, který během čtení změnil svou polohu v indexu. Záznam je například přečten na začátku indexu a než čtení doběhne do konce indexu, tak jiná transakce tento záznam modifikuje a přesune jej tím na konec indexu. První čtoucí transakce pak záznam přečte ještě jednou až se dostane ke konci indexu.

Úrovně izolace (Isolation Level)

SQL server 2008 podporuje 5 úrovní izolace.

Read Uncommitted
Mohou se objevit všechny chyby, kromě ztracených updatů. Může se objevit problém, že se server rozhodne číst datové stránky postupně. Pak pokud je před koncem čtení vložen řádek do již přečtené stránky, tak se nedostane do výsledků. Pokud dojde k rozpadu stránky, může se stát, že je některý řádek přečten dvakrát.

Read Committed
Default isolation level. Existují dvě varianty, optimistická a pesimistická, v závislosti na nastavení READ_COMMITTED_SNAPSHOT (Default OFF). Zajišťuje, že aplikace nemůže přečíst nepotvrzená data. Při čtení se používají sdílené zámky, které jsou uvolňovány, jak nejdříve je to možné. Sdílený zámek umožňuje ostatním transakcím číst, úpravy ale musí počkat. READ_COMMITTED_SNAPSHOT také zaručuje, že aplikace nikdy nepřečte nepotvrzená data. Pokud je modifikovaný řádek, pak server vytvoří verzi řádku s předchozími potvrzenými hodnotami. Modifikovaná data jsou uzamčená, ale ostatní procesy mohou číst předchozí potvrzené hodnoty.

Repeatable Read
Zajišťuje větší bezpečnost. Dotaz provedený v transakci dvakrát, dostane pokaždé stejný výsledek. Sdílené zámky jsou drženy po celou dobu transakce a ne jen po dobu nezbytně nutnou ke čtení. Tím je zaručeno, že ostatní procesy nemohou čtená data po dobu transakce změnit. Samozřejmě snižuje možnost souběhu a degraduje výkon.

Snapshot
Druhá optimistická úroveň izolace. Stejně jako READ_COMMITTED_SNAPSHOT umožňuje číst starší verzi potvrzených dat.

Serializable
Odstraňuje výskyt fantomů. Jedná se o nejbezpečnější režim, protože odstraňuje všechny problémy jmenované na začátku. Server musí zamykat nejen čtená data, ale i data, která by mohl jiný proces vložit do čteného rozsahu řádků. Používají se key-range zámky nad indexem sloupce. Pokud čtený sloupec nemá index, zamyká se celá tabulka. Zámek se drží po celou dobu transakce.

Srovnání Read Committed Snapshot Isolation (RCSI) a Snapshot Isolation (SI)
RCSI se zapne pouze na úrovni databáze. SI se musí zapnout na úrovni databáze (ALLOW_SNAPSHOT_ISOLATION) a pak v každém připojení použít SET TRANSACTION ISLOTION LEVEL SNAPSHOT.
RCSI může v rámci jedné transakce stejným dotazem přečíst jednou starší verzi zamčených dat a podruhé (po COMMIT ve druhé transakci) přečíst novou verzi dat. Vyskytuje se zde tedy anomálie neopakovatelného čtení. U SI přečteme v transakci pokaždé stejnou hodnotu, přestože ji mohla mezitím druhá transakce změnit a potvrdit.
U SI nemůže dojít ke ztraceným updatům. Pokud by k tomu mělo dojít, SQL server vyhodí chybu se zprávou 3960, na kterou by měl programátor reagovat znovuspuštěním transakce.