Lekce 17 - MS-SQL krok za krokem: Transakce

V předchozím kvízu, Kvíz - Procedury v MS-SQL, jsme si ověřili nabyté zkušenosti z předchozích lekcí.

V dnešním MS-SQL tutoriálu se blíže podíváme na transakce.

Jak již víme z dřívějších tutoriálů, tak transakce je soubor několika dotazů, které databáze chápe jako jeden dotaz. Můžeme díky nim zajistit, aby se buď provedly všechny dotazy v transakci, nebo žádný. Transakci také lze do poslední chvíle odvolat a vrátit tak všechny změny provedené v rámci dané transakce.

Typy transakcí

MS-SQL server umožňuje používat tři typy transakcí:

• Autocommit transakce
• Implicitní transakce
• Explicitní transakce

Autocommit transakce

Jedná se o výchozí nastavení, při němž je každý T-SQL příkaz vyhodnocen jako transakce, která je potvrzena nebo odvolána na základě úspěchu daného příkazu. Úspěšné příkazy jsou potvrzeny a neúspěšné příkazy jsou okamžitě vráceny zpět.

Implicitní transakce

U takových transakcí je každý T-SQL příkaz vyhodnocen jako transakce, avšak její vykonání nebo odvolání musíme vždy definovat příkazem COMMIT TRANSACTION nebo ROLLBACK TRANSACTION.

Tento typ transakcí povolíme nastavením vlastnosti IMPLICIT_TRANSACTIONS na ON:

SET IMPLICIT_TRANSACTIONS ON;

V následujících příkladech budeme opět využívat databázi Firma z dřívějších lekcí. Jestliže již tuto databázi a její tabulky nemáte, tak si její aktuální verzi můžete stáhnout pod článkem a naimportovat.

Když teď budeme chtít například aktualizovat počet pracovníků v jedné z našich poboček, tak transakci musíme potvrdit příkazem COMMIT TRANSACTION:

UPDATE [Pobocky] SET [PocetPracovniku] = 80
WHERE [IdPobocky] = 1;

COMMIT TRANSACTION;

Tabulka Pobocky:

IdPobocky	Mesto	Nazev	PocetPracovniku
1	Ostrava	ITnetwork	80
2	Brno	ITnetwork	100
3	Praha	ITnetwork	200

Odvolání příkazu:

UPDATE [Pobocky] SET [PocetPracovniku] = 50
WHERE [IdPobocky] = 1;

ROLLBACK TRANSACTION;

Tabulka Pobocky:

IdPobocky	Mesto	Nazev	PocetPracovniku
1	Ostrava	ITnetwork	80
2	Brno	ITnetwork	100
3	Praha	ITnetwork	200

Jak vidíme, změna počtu pracovníků se do databáze neuložila.

Jakmile jednou transakci potvrdíme, tak ji již nemůžeme vrátit zpátky. Příkaz ROLLBACK poté už nebude fungovat.

Explicitní transakce

Jedná se o transakce, u kterých přesně definujeme, kdy mají začít a kdy skončit. Můžeme tak mít více příkazů v jedné transakci, čehož se často využívá například v uložených procedurách spolu s blokem TRY-CATCH.

Explicitní transakci si ukážeme na proceduře aktualizující počet pracovníků v určité pobočce. Protože si zároveň vedeme statistiku celkového počtu našich pracovníků, tak v proceduře musíme aktualizovat i tento údaj. Všechny potřebné příkazy obalíme do transakce, která zajistí odvolání všech změn v případě, že by jeden z příkazů selhal:

CREATE PROCEDURE UpdatePocetPracovniku
    @IdPobocky INT,
    @PocetPracovniku INT
AS
BEGIN
    BEGIN TRANSACTION UpdateTransaction;

    BEGIN TRY
        UPDATE [StatistikaPobocek] SET [PocetPracovnikuCelkem] = [PocetPracovnikuCelkem] - [PocetPracovniku]
        FROM [Pobocky]
        WHERE [IdPobocky] = @IdPobocky;

        UPDATE [Pobocky] SET [PocetPracovniku] = @PocetPracovniku
        WHERE [IdPobocky] = @IdPobocky;

        UPDATE [StatistikaPobocek] SET [PocetPracovnikuCelkem] = [PocetPracovnikuCelkem] + @PocetPracovniku;

        COMMIT TRANSACTION UpdateTransaction;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION UpdateTransaction;
    END CATCH;
END;

Transakci započneme příkazem BEGIN TRANSACTION, za kterým můžeme napsat její název. Tento název poté používáme při jejím potvrzování nebo odvolávání.

Pro tento účel již máme napsaný trigger AfterUpdatePobocky, proto je potřeba jej nejprve odebrat příkazem DROP TRIGGER [AfterUpdatePobocky], aby vše fungovalo správně.

Jevy spojené s transakcemi

Možnost potvrzení nebo vrácení změn v databázi provedených transakcemi může vést k některým nežádoucím jevům, a to hlavně v případě, kdy je najednou spuštěno více transakcí, které pracují se stejnými tabulkami. Jedná se především o jevy:

• Nečisté čtení (Dirty Reads) - nastane, když transakce čte data, která ještě nebyla potvrzena. Předpokládejme například, že transakce 1 aktualizuje nějaký řádek a transakce 2 tento řádek přečte, ještě než transakce 1 potvrdí jeho aktualizaci. Pokud však transakce 1 vrátí změnu zpět (zavolá ROLLBACK namísto COMMIT), transakce 2 bude mít načtená data, která nemají existovat.
• Neopakovatelné čtení (Nonrepeatable Reads) - nastane, když je v průběhu transakce nějaký řádek načten dvakrát a hodnoty v řádku se mezi čteními liší. Předpokládejme například, že transakce 1 přečte hodnoty řádku a transakce 2 hned nato tento řádek aktualizuje nebo odstraní a danou aktualizaci nebo odstranění potvrdí. Jestliže transakce 1 znovu načte řádek, tak načte jiné hodnoty nebo zjistí, že řádek byl odstraněn.
• Problém ztracených aktualizací - nastane, když dvě nebo více transakcí mohou číst a aktualizovat stejná data.
• Fantom - řádek, který odpovídá kritériím vyhledávání, ale není zpočátku vidět. Předpokládejme například, že transakce 1 čte sadu řádků, které splňují určitá kritéria vyhledávání. Transakce 2 vygeneruje nový řádek (pomocí příkazu UPDATE nebo INSERT), který odpovídá kritériím vyhledávání transakce 1. Pokud transakce 1 znovu provede vyhledávání, získá jinou sadu řádků.

Řešením těchto jevů je použití různých úrovní izolace transakcí.

Úrovně izolace transakcí v MS-SQL databázích

Úrovně izolace transakcí se používají k definování míry, do jaké musí být jedna transakce izolována od změn dat provedených jinými souběžně běžícími transakcemi. Různé úrovně izolace transakcí od těch nejnižších po nejvyšší jsou:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

Jaké výše zmíněné jevy se mohou ukázat u jakých úrovní izolace, ukazuje tato tabulka:

Úroveň izolace	Nečisté čtení	Problém ztracených aktualizací	Neopakovatelné čtení	Fantom
Read Uncommitted	x	x	x	x
Read Committed		x	x	x
Repeatable Read				x
Serializable

Srovnání úrovní izolace transakcí

Nižší úroveň izolace zvyšuje schopnost mnoha uživatelů přistupovat ke stejným datům současně, avšak zároveň zvyšuje pravděpodobnost výskytu nežádoucích jevů, které jsme si uvedli. Vyšší úroveň izolace snižuje možnost výskytu těchto jevů, ale vyžaduje více systémových prostředků a zvyšuje pravděpodobnost, že jedna transakce zablokuje jinou.

Nastavení úrovně izolace transakcí

Úroveň izolace transakcí změníme pomocí příkazu SET TRANSACTION ISOLATION LEVEL s názvem požadované úrovně:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Aktuální úroveň izolace zjistíme tímto dotazem:

SELECT CASE transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable Read'
    WHEN 4 THEN 'Serializable' END AS [TransactionIsolationLevel]
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Výchozí úrovní izolace je Read Committed:

TransactionIso­lationLevel
Read Committed

Seznam otevřených transakcí

Někdy se hodí vypsat běžící transakce. To se dá udělat následujícím dotazem:

SELECT
    [s_tst].[session_id] AS [SessionId],
    [s_es].[login_name] AS [LoginName],
    DB_NAME (s_tdt.database_id) AS [Database],
    [s_tdt].[database_transaction_begin_time] AS [BeginTime],
    [s_tdt].[database_transaction_log_bytes_used] AS [LogBytes],
    [s_tdt].[database_transaction_log_bytes_reserved] AS [LogRsvd],
    [s_est].text AS [LastSQLText],
    [s_eqp].[query_plan] AS [LastPlan]
FROM sys.dm_tran_database_transactions [s_tdt]
JOIN sys.dm_tran_session_transactions [s_tst]
    ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN sys.[dm_exec_sessions] [s_es]
    ON [s_es].[session_id] = [s_tst].[session_id]
JOIN sys.dm_exec_connections [s_ec]
    ON [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN sys.dm_exec_requests [s_er]
    ON [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY [BeginTime] ASC;

V příští lekci, MS-SQL - Datové typy podrobněji, se podíváme podrobněji na datové typy v MS-SQL databázi.

 

Měl jsi s čímkoli problém? Stáhni si vzorovou aplikaci níže a porovnej ji se svým projektem, chybu tak snadno najdeš.