DB Lock: Souběžný přístup k datům

V minulých dílech jsme se dozvěděli, jak v principu fungují zámky. Existuje tabulka kompatibility mezi různými druhy zámků. Celkové nastavení pravidel pro aplikaci zámků a řízení souběžných transakcí je poměrně přísné. Existují dva pojmy, které se vztahují k tomuto tématu.

Pesimistické řízení (Pessimistic concurrency control)

Stejně jako pesimista, jenž vidí svět poněkud černobíle, tak i tento styl řízení předpokládá, že s největší pravděpodobností při souběžném přístupu k datům dojde ke konfliktu, který bude nutné složitě napravovat. Proto byl zaveden systém zámků tak, jak jsme si ho představili v minulých dílech. Transakce si při manipulaci s daty aplikuje příslušné zámky a uvolní je při svém skončení.

Optimistické řízení (Optimistic concurrency control)

Znamená víceméně otevřený přístup k datům pro kohokoliv. Pokud uživatel změní data, systém jednoduše zkontroluje, zda již nebyly změněny mezi tím, kdy si je přečetl z databáze, vlastní změnou a mezi pokusem o jejich opětovné uložení. V případě že data byla změněna druhým uživatelem, první uživatel obdrží chybovou zprávu a obvykle musí svoji změnu opakovat.

Souběžný přístup k datům pak znamená nutnost řešit nejen konflikty mezi transakcemi, ale také mít na paměti jistá specifika tohoto přístupu. V případě dvou transakcí, které pouze čtou data, nevzniká žádný problém. Obě transakce mohou přistupovat k datům a libovolně je číst. Obě dostanou vždy stejné hodnoty. Existují-li dvě transakce, které chtějí zapsat různá data do stejné buňky tabulky ve stejný čas, systém vyřeší konflikt tím, že zjednodušeně řečeno zápis není povolen. Zajímavější je situace, kdy jedna transakce čte data a druhá transakce zapisuje. Mohou tedy nastat jevy, které si popíšeme dále na příkladech.

Testovací tabulka je velmi jednoduchá. Vytvořit a naplnit si ji můžete v libovolné testovací databázi.

CREATE TABLE [dbo].[ConcurrencyExample](
[RowID] [bigint] IDENTITY(1,1) NOT NULL,
[Popis] [varchar](100) NOT NULL,
[Cislo] [decimal](18, 0) NOT NULL,
[Datum] [datetime] NOT NULL,
CONSTRAINT [PK_ConcurrencyExample_ROWID] PRIMARY KEY CLUSTERED
(
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_Popis_Cislo] ON [dbo].[ConcurrencyExample]
(
[Popis] ASC,
[Cislo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

INSERT INTO [dbo].[ConcurrencyExample] ([Popis],[Cislo],[Datum]) VALUES ('Cteni',1,GETDATE()), ('Cteni',2,GETDATE()), ('Cteni',3,GETDATE()), ('Smazat',4,GETDATE())

Non-Repeatable Read

Jsou-li stejná data v jedné transakci čtena vícekrát z databáze, nelze zabezpečit, že každé čtení v rámci jedné transakce vrátí stejné hodnoty. Je to způsobeno umisťováním Shared zámků v transakci pouze na dobu nezbytně nutnou k přečtení dat. Mějme transakci T1, která bude číst data a transakci T2, která bude data zapisovat.
Nejprve si spustíme první transakci

BEGIN TRANSACTION T1;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM dbo.ConcurrencyExample; 

WAITFOR DELAY '00:00:05.000'; 

SELECT * FROM dbo.ConcurrencyExample;

COMMIT TRANSACTION T1;

Zatímco první transakce je v běhu, spustíme si ve druhém okně druhou transakci, která provede UPDATE.

BEGIN TRANSACTION T2;

UPDATE dbo.ConcurrencyExample SET Cislo = 2 WHERE RowID = 1;

COMMIT TRANSACTION T2;

Výsledek T1 můžete vidět na obrázku.

Protiopatřením je zamknout čtená data po celou dobu trvání transakce. Tuto změnu provedete odkomentováním řádku s nastavením Isolation Level na Repeatable Read a zakomentováním řádku s Read Committed. Případně si ještě ve druhé transakci změňte hodnotu sloupce Cislo na 1 pro RowID = 1. Výsledkem je, že obě čtení v T1 zobrazí stejné hodnoty prvního řádku, protože změna prvního řádku v T2 proběhne až po ukončení T1.

Phantom Read

V minulém příkladu jsme mezi dvěma čteními dat z databáze provedli změnu již existujícího řádku. Zkusme nyní řádek přidat nebo smazat. První transakci si prosím upravte do původní podoby tak, aby byl nastaven Isolation Level na Read Committed. Druhou transakci změníme následovně:

BEGIN TRANSACTION T2;

INSERT INTO [dbo].[ConcurrencyExample] ([Popis],[Cislo],[Datum]) VALUES ('Vlozit',5,GETDATE());

COMMIT TRANSACTION T2;

Chceme-li vyzkoušet i smazání řádku, lze to provést změnou druhé transakce

BEGIN TRANSACTION T2;

DELETE FROM [dbo].[ConcurrencyExample] WHERE RowID = 4;

COMMIT TRANSACTION T2;

Jak je vidět z uvedených obrázků, zápis i mazání proběhne bez problémů a počet řádků v tabulce se změní. Stejně jako v předchozím příkladě lze tomuto chování předejít zamknutím celého rozsahu dat, který spadá do podmínky dotazu po celou dobu trvání transakce. Toto lze provést změnou nastavení Isolation Level z Read Committed na Serializable. Stačí tedy zakomentovat první řádek a povolit třetí řádek s nastavením pro Isolation Level. Zkuste si pak vložit v druhé transakci nový řádek.

Lost Update

Dalším efektem souběžného přístupu k datům je přepis jedné změny druhou. Je to stejné, jako když máte textový soubor, který si dva uživatelé zkopírují nezávisle na sobě, aniž by věděli jeden o druhém. První uživatel provede změny v dokumentu a nahraje jej zpět na původní místo. Druhý uživatel o hodinu později nahraje zpět svoji verzi. Veškeré změny, které provedl první uživatel jsou tedy přepsány a nenávratně ztraceny. Řešením tohoto konfliktu je řízení přístupu, kdy si první uživatel nejprve zamkne dokument a teprve po té se dostane k vlastnímu obsahu. Po editaci, jakmile dokument vrátí na původní místo, se provede odemknutí dokumentu a zpřístupnění pro druhého uživatele.
Předpokládejme, že máme řádek v tabulce, jehož hodnota je ve sloupci Cislo = 5. První transakce provede nejprve čtení hodnoty. Druhým krokem je provedení kalkulace (v našem případě jednoduchý součin), jež může trvat různě dlouho (simulace pomocí WAITFOR). Posledním krokem je uložení změny do databáze.

První transakce tedy simuluje provádění složitějšího výpočtu a tedy trvá déle než druhá transakce.

DECLARE @StaraHodnota AS DECIMAL;
DECLARE @NovaHodnota AS DECIMAL;

BEGIN TRANSACTION T1;

SELECT @StaraHodnota = Cislo FROM dbo.ConcurrencyExample WHERE RowID = 5;
--SELECT @StaraHodnota = Cislo FROM dbo.ConcurrencyExample WITH (XLOCK) WHERE RowID = 5;

SET @NovaHodnota = 2 * @StaraHodnota;

WAITFOR DELAY '00:00:05.000'; --Simulace vypocetne narocne kalkulace

UPDATE dbo.ConcurrencyExample SET Cislo = @NovaHodnota WHERE RowID = 5;
SELECT * FROM dbo.ConcurrencyExample WHERE RowID = 5;

COMMIT TRANSACTION T1;

Druhá transakce simuluje provedení jednoduchého výpočtu

DECLARE @StaraHodnota AS DECIMAL;
DECLARE @NovaHodnota AS DECIMAL;

BEGIN TRANSACTION T2;

SELECT @StaraHodnota = Cislo FROM dbo.ConcurrencyExample WHERE RowID = 5;
--SELECT @StaraHodnota = Cislo FROM dbo.ConcurrencyExample WITH (XLOCK) WHERE RowID = 5;

SET @NovaHodnota = 3 * @StaraHodnota;

UPDATE dbo.ConcurrencyExample SET Cislo = @NovaHodnota WHERE RowID = 5;
SELECT * FROM dbo.ConcurrencyExample WHERE RowID = 5;

COMMIT TRANSACTION T2;

V zásadě jsou obě transakce totožné. Můžeme je tedy chápat jako stejný kód, který je spuštěn dvěma různými klienty. V ideálním případě bychom tedy chtěli, aby byly aplikovány všechny změny. Transakce T1 by tedy měla spočítat výslednou hodnotu což je 2 * 5 = 10 a uložit ji do databáze. Druhá transakce T2 by pak měla vypočítat hodnotu 3 * 10 = 30 a uložit ji zpět do databáze. Této představě vyhovuje, když si zakomentujete WAITFOR a spustíte nejprve první a pak druhou transakci. V našem případě (s použitím WAITFOR) však po dokončení obou transakcí uvidíme v databázi uložený výsledek Cislo = 10.

Již dříve jsme si řekli, že zámek sloužící k uzamknutí čtených řádků je uvolněn ihned po té, co jsou načteny hodnoty. V našem příkladě je to tedy ve zlomku sekundy. Zatímco první transakce T1 provádí výpočet nové hodnoty, řádek na sobě nemá žádné zámky a je tedy dostupný i pro druhou transakci T2. Druhá transakce T2 provede také načtení hodnoty, což je stále hodnota 5, provede kalkulaci 5 * 3 = 15 a uloží ji do databáze jako první, protože její kalkulace trvá mnohem kratší dobu než první transakci. První transakce T1 mezitím dokončí kalkulaci 2 * 5 = 10 a uloží novou hodnotu do databáze.

Z předchozích příkladů bychom si mohli vypůjčit řešení a zamknout čtená data po celou dobu trvání transakce. Jenže opravdu by nám to pomohlo? Zkusme si navržené řešení rozebrat podrobněji. Transakce T1 si načte hodnotu 5 a provádí kalkulaci. Řádek je zamčený S zámkem, tudíž druhá transakce T2 jej může pohodlně číst. Tedy také si načte hodnotu 5. Vzhledem k tomu, že druhá transakce provede kalkulaci mnohem rychleji než první, chtěla by provést uložení nové hodnoty do databáze. Řádek je však zamčen T1, proto musí T2 čekat dokud řádek nebude uvolněn. T1 dokončí kalkulaci, provede uložení hodnoty 10 do databáze a řádek uvolní. T2 provede uložení nové hodnoty do databáze ihned po uvolnění řádku. Výsledná hodnota tedy bude 15. Což není stále to, co jsme chtěli.
Jak jsem již předeslal v úvodu, je potřeba si exkluzivně zamknout řádek při čtení, což lze provést pomocí tzv. TABLE HINT. Pokusně si můžete zakomentovat v každé transakci první SELECT a odkomentovat druhý.

Dirty Read

Veškeré změny v tabulce jsou zapsány až po provedení COMMIT na konci transakce. Jak jsme si uvedli již dříve, ve výchozím nastavení transakce se řádek tabulky zamkne exkluzivním zámkem a chce-li druhá transakce data přečíst, musí počkat na dokončení zápisu změn (viz. DB Lock: Typy zámků (Lock Modes) a jejich kompatibilita). SQL server nám dává možnost toto výchozí chování změnit a umožnit tak číst i právě modifikovaná a neuložená data. Stinnou stránkou tohoto přístupu je ovšem možnost, že přečtené řádky mohou obsahovat hodnoty, které ve skutečnosti v tabulce nejsou uloženy, což může při nesprávném použití způsobit problémy s integritou dat. Dirty read si ukážeme na následujícím příkladu.

První transakce bude měnit data v tabulce. Ve sloupci Cislo budeme chtít uložit druhou mocninu již uložené hodnoty, ale místo uložení celou transakci zrušíme.

BEGIN TRANSACTION T1;

UPDATE dbo.ConcurrencyExample SET Cislo = Cislo * Cislo; 
WAITFOR DELAY '00:00:05.000'; 

ROLLBACK TRANSACTION T1;

Druhá transakce bude číst data z tabulky

BEGIN TRANSACTION T2;

--Povoleni cteni neulozenych hodnot
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--Cteni aktualnich neulozenych hodnot
SELECT * FROM dbo.ConcurrencyExample;

WAITFOR DELAY '00:00:07.000';

--Cteni hodnot po ROLLBACK prvni transakce
SELECT * FROM dbo.ConcurrencyExample;

COMMIT TRANSACTION T2;

Výsledkem prvního dotazu jsou hodnoty, které nebyly nikdy uloženy do databáze! Není-li to nezbytně nutné, raději vůbec nepoužívejte čtení neuložených hodnot.

Double Read

Několikrát jsem již zmínil, že ve výchozím nastavení (Isolation Level Read Commited) jsou řádky při čtení zamknuty pouze po nezbytnou dobu. Jakmile jsou data přečtena, zámky jsou uvolněny. Tím vzniká možnost vícenásobného čtení jednoho řádku. Nejprve si ukážeme příklad a po té jej rozebereme podrobněji. Abychom si nastavili výchozí tabulku do původního stavu, klidně si ji můžete smazat a znovu vytvořit skriptem z úvodu tohoto článku.
První transakce provede změnu hodnot ve sloupci Popis.

BEGIN TRANSACTION T1;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE dbo.ConcurrencyExample SET Popis = 'Cteni2' WHERE Popis = 'Cteni' and Cislo = 3;

WAITFOR DELAY '00:00:05.000';

UPDATE dbo.ConcurrencyExample SET Popis = 'Cteni3' WHERE Popis = 'Cteni' and Cislo = 1;

COMMIT TRANSACTION T1;

Druhá transakce vypíše všechny řádky, jejichž hodnota ve sloupci Popis začíná Cteni. Pokud si spouštíte jednotlivé příklady po sobě, je potřeba se přesvědčit, že máte nastaven výchozí Isolation Level.

BEGIN TRANSACTION T2;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT Cislo, Popis FROM dbo.ConcurrencyExample WHERE Popis like 'Cteni%';

COMMIT TRANSACTION T2;

Dotaz vrátil čtyři řádky, přestože jsou v tabulce uloženy pouze tři řádky začínající slovem Cteni.

Jak je tedy možné, že dotaz přečetl první řádek dvakrát? Podívejme se nejprve na strukturu tabulky. V tabulce existuje index IX_Popis_Cislo, který zahrnuje sloupec Popis a Cislo. Dotaz v transakci T2 vypisuje sloupce Cislo, Popis a provádí filtrování na základě sloupce Popis. Celý výsledek je tedy pokrytý indexem. Index řadí data podle sloupce Popis a zároveň je modifikován transakcí T1.

Na začátku transakce T1 se zamkne pro UPDATE řádek Cislo = 3. Transakce T2 začne postupně číst řádky Cislo = 1, Cislo = 2. Při pokusu o čtení řádku Cislo = 3 zjistí, že je tam aplikován zámek a proto musí počkat na ukončení transakce T1. T1 v dalším pokračování provede změnu řádku Cislo = 1. Na řádku Cislo = 1 totiž není aplikován žádný zámek. Čtení tohoto řádku transakcí T2 již proběhlo a zámek byl uvolněn. Touto změnou se řádek Cislo = 1 přesune v indexu až na poslední místo. Jak jsem již zmínil, index je seřazen primárně podle sloupce Popis. Transakce T1 je tedy ukončena a uvolní zámky ze všech řádků. Transakce T2 může pokračovat ve čtení řádku Cislo = 3 a znovu přečte i řádek Cislo = 1, který byl přesunut z prvního místa na poslední místo v indexu první transakcí T1.

Jak jste si mohli všimnout, používali jsme v příkladech změnu nastavení Isolation Level. V příštím díle se tedy podíváme podrobněji právě na toto nastavení.