DB Lock: Snapshot Isolation Level

Microsoft implementoval do SQL optimistické řízení přístupů pomocí Snapshot Isolation Level. Pojďme si říci, jak to vlastně funguje.

Jakmile je zapnuta volba Snapshot Isolation Level, začne se SQL Engine ke každému záznamu chovat odlišně od standardního chování. Při startu nové transakce T jí je přiřazeno sekvenční transakční číslo. SQL Engine v rámci transakce přečte řádek a zároveň obdrží číslo verze řádku, které je uloženo v tempdb a jehož sekvenční číslo je menší než sekvenční číslo právě probíhající transakce. Transakce T má tedy přístup k datům ve stavu, v jakém byly v době jejího začátku. Neuvidí například novější řádky, vytvořené jiným klientem nebo transakcí po začátku transakce T, protože mají vyšší transakční číslo. Naopak stále uvidí řádky, které byly smazány jiným klientem nebo transakcí po začátku transakce T. Na konci transakce T se opět načte číslo verze měněného řádku a porovná se s číslem verze na začátku transakce T. Jsou-li obě čísla stejná, řádek v průběhu transakce T nebyl změněn jiným klientem a změna může být uložena do databáze. Transakce T končí COMMITem. Jsou-li ovšem obě čísla rozdílná, znamená to, že jiná transakce řádek změnila. Provede se ROLLBACK a klientovi se zobrazí chybová zpráva. Nyní se můžeme blíže podívat na příklady.

Testovací tabulka je velmi jednoduchá a 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())

První transakce bude provádět čtení prvního řádku

--SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION T1

SELECT * FROM [dbo].[ConcurrencyExample] WHERE RowID = 1

WAITFOR DELAY '00:00:15'

COMMIT TRANSACTION T1

Druhá transakce bude provádět UPDATE prvního řádku.

BEGIN TRANSACTION T2

UPDATE [dbo].[ConcurrencyExample] SET Cislo = 100 WHERE RowID = 1

COMMIT TRANSACTION T2

Když si sputíte oba příklady, uvidíme, že T2 je blokována T1. Transakce T2 tedy musí počkat, až bude dokončena transakce T1.

Nyní si zkuste v okně pro první transakci T1 zakomentovat celý řádek SET TRANSACTION ISOLATION LEVEL REPEATABLE READ a povolit celý řádek SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

Opět spusťte obě transakce. Nyní byste měli dostat chybové hlášení informující, že Snapshot Isolation Level není pro databázi povolen.

Msg 3952, Level 16, State 1, Line 6
Snapshot isolation transaction failed accessing database 'TestDB' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

Zapnutí SNAPSHOT ISOLATION LEVEL

Budete-li chtít využívat Snapshot Isolation Level je potřeba nejprve pomocí volby ALLOW_SNAPSHOT_ISOLATION povolit používání snapshotu. Toto nastavení se mění na úrovni databáze.

ALTER DATABASE <jmeno_databaze> SET ALLOW_SNAPSHOT_ISOLATION ON

Povolte na databázi Snapshot Isolation a spusťte si obě transakce. Jen pro rekapitulaci okno s T1 má nyní nastavený Isolation Level na Snapshot a okno s T2 má nastavený Isolation Level na Read Committed. Opět si pusťte obě transakce.

Druhá transakce nyní proběhne ihned. Transakce T2 tedy není blokována transakcí T1. Po skončení T1 je na výstupu ve sloupci Cislo hodnota 1. Vzhledem k tomu, že T2 proběhla. Pokud si znovu spustíte T1, uvidíte ve sloupci Cislo hodnotu 100, která byla zapsána transakcí T2.

Při zapnutí volby Allow Snapshot Isolation se musí explicitně určit, zda má transakce běžet v módu Snapshot.

Zapnutí READ COMMITTED SNAPSHOT

Volba Read Committed Snapshot zapne verzování záznamů pro implicitní transakce.

USE [master]
GO
ALTER DATABASE <jmeno_databaze> SET READ_COMMITTED_SNAPSHOT
GO

Efekty souběžného přístupu

Nyní se podívejme, zda-li je možné předejít některým nežádoucím efektům, které jsme si popsali v článku DB Lock: Souběžný přístup k datům

Non-Repeatable Read

Otevřeme si dvě nová spojení a spustíme si nejprve první transakci

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION T1;

SELECT * FROM dbo.ConcurrencyExample

WAITFOR DELAY '00:00:05.000';

SELECT * FROM dbo.ConcurrencyExample

COMMIT TRANSACTION T1;

a po té druhou transakci

BEGIN TRANSACTION T2;

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

COMMIT TRANSACTION T2;

Druhá transakce proběhla okamžitě, protože nebyla blokována první transakcí.

Z výstupu první transakce si můžete všimnout, že oba dotazy vrátily stejnou sadu dat. Čtení bylo konzistentní.

Phantom Read

Podle návodu z předchozího článku, změníme druhou transakci a smažeme jeden řádek.

BEGIN TRANSACTION T2;

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

COMMIT TRANSACTION T2;

Spustíme obě transakce a zkontrolujeme výsledek.

Obě čtení byly opět konzistentní v rámci celé transakce.

Lost Update

Do tabulky si nejprve vložíme nový řádek

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

První transakce bude provádí následující kód

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

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 vypadá takto

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

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;

Nyní dochází k situaci, kdy první transakce trvá déle než transakce druhá. T1 si načte data z tabulky a provede výpočet. V momentě, kdy se data mají uložit do databáze, se provede kontrola, zda-li již nějaká jiná transakce neprovedla změnu stejných dat. Snapshot Isolation Level přesně v duchu optimistického řízení nepoužívá žádné zámky, proto data mohou být kdykoliv změněna jinou transakcí. Jakmile SQL Engine zjistí, že data byla změněna, provede se rollback transakce a zobrazí se chybové hlášení.

Msg 3960, Level 16, State 5, Line 16
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.ConcurrencyExample' directly or indirectly in database 'TestDB' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

V takovém případě nezbývá klientovi než akci zopakovat znovu. Znovu tedy spustíme transakci T1.

Double Read

Posledním případem, který Snapshot Isolation Level dokáže vyřešit, je dvojité čtení některých řádků v databázi.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION T1;

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

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION T2;

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

COMMIT TRANSACTION T2;

Spustíte-li si nejprve první a po té druhou transakci, uvidíte, že výsledný počet přečtených řádků je roven počtu řádků v databázi. Před dokončením první transakce se jedná o výsledek

Po zdárném ukončení první transakce a uložení změn do databáze je výsledek druhého spuštění T2 následující

Dirty Read

Jediným efektem, kterému nedokáže předejít ani Snapshot Isolation Level, je čtení změněných dat v neukončené transakci. Jednoduše buď chcete číst modifikovaná data v neukončené transakci, nebo nechcete.

Před aplikací optimistického přístupu k datům si vše raději vyzkoušejte na testovacím prostředí. Díky ukládání verzí v tempdb, se zvýší nároky na její výkon. Tempdb tak bude mít ještě větší vliv na celkovou výkonnost SQL Serveru než před změnou.