17. diel - MS-SQL krok za krokom: Transakcie

V predchádzajúcom kvíze, Kvíz - Procedúry v MS-SQL, sme si overili nadobudnuté skúsenosti z predchádzajúcich lekcií.

V dnešnom MS-SQL tutoriáli sa bližšie pozrieme na transakcie.

Ako už vieme z predchádzajúcich tutoriálov, tak transakcia je súbor niekoľkých dotazov, ktoré databáza chápe ako jeden dotaz. Môžeme vďaka nim zaistiť, aby sa buď vykonali všetky dotazy v transakcii, alebo žiadny. Transakciu tiež možno do poslednej chvíle odvolať a vrátiť tak všetky zmeny vykonané v rámci danej transakcie.

Typy transakcií

MS-SQL server umožňuje používať tri typy transakcií:

• autocommit transakcie,
• implicitné transakcie,
• explicitné transakcie.

Autocommit transakcie

Ide o predvolené nastavenie, pri ktorom je každý T-SQL príkaz vyhodnotený ako transakcia, ktorá je potvrdená alebo odvolaná na základe úspechu daného príkazu. Úspešné príkazy sú potvrdené a neúspešné príkazy sú okamžite vrátené späť.

Implicitné transakcie

Pri takýchto transakciách je každý T-SQL príkaz vyhodnotený ako transakcia, avšak jej vykonanie alebo odvolanie musíme vždy manuálne vyvolať príkazom COMMIT TRANSACTION alebo ROLLBACK TRANSACTION.

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

SET IMPLICIT_TRANSACTIONS ON;

V nasledujúcich príkladoch budeme opäť využívať databázu Firma z predchádzajúcich lekcií. Ak už túto databázu a jej tabuľky nemáte, tak si jej aktuálnu verziu môžete stiahnuť pod článkom a naimportovať.

Keď teraz budeme chcieť napríklad aktualizovať počet pracovníkov v jednej z našich pobočiek, tak transakciu musíme potvrdiť príkazom COMMIT TRANSACTION:

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

COMMIT TRANSACTION;

Tabuľka Pobocky:

IdPobocky	Mesto	Nazov	PocetPracovnikov
1	Košice	ITnetwork	80
2	Bratislava	ITnetwork	100
3	Nitra	ITnetwork	200

Odvolanie príkazu:

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

ROLLBACK TRANSACTION;

Tabuľka Pobocky:

IdPobocky	Mesto	Nazov	PocetPracovnikov
1	Košice	ITnetwork	80
2	Bratislava	ITnetwork	100
3	Nitra	ITnetwork	200

Ako vidíme, zmena počtu pracovníkov sa do databázy neuložila.

Keď raz transakciu potvrdíme, tak ju už nemôžeme vrátiť späť. Príkaz ROLLBACK potom už nebude fungovať.

Explicitné transakcie

Ide o transakcie, pri ktorých presne definujeme, kedy majú začať a kedy skončiť. Môžeme tak mať viac príkazov v jednej transakcii, čo sa často využíva napríklad v uložených procedúrach spolu s blokom TRY-CATCH.

Explicitnú transakciu si ukážeme na procedúre aktualizujúcej počet pracovníkov v určitej pobočke. Pretože si zároveň vedieme štatistiku celkového počtu našich pracovníkov, tak v procedúre musíme aktualizovať aj tento údaj. Všetky potrebné príkazy obalíme do transakcie, ktorá zaistí odvolanie všetkých zmien v prípade, že by jeden z príkazov zlyhal:

CREATE PROCEDURE UpdatePocetPracovnikov
    @IdPobocky INT,
    @PocetPracovnikov INT
AS
BEGIN
    BEGIN TRANSACTION UpdateTransaction;

    BEGIN TRY
        UPDATE [StatistikaPobociek] SET [PocetPracovnikovCelkovo] = [PocetPracovnikovCelkovo] - [PocetPracovnikov]
        FROM [Pobocky]
        WHERE [IdPobocky] = @IdPobocky;

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

        UPDATE [StatistikaPobociek] SET [PocetPracovnikovCelkovo] = [PocetPracovnikovCelkovo] + @PocetPracovnikov;

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

Transakciu začneme príkazom BEGIN TRANSACTION, za ktorý môžeme napísať jej názov. Tento názov potom používame pri jej potvrdzovaní alebo odvolávaní.

Na tento účel už máme napísaný trigger AfterUpdatePobocky, preto je potrebné ho najprv odobrať príkazom DROP TRIGGER [AfterUpdatePobocky], aby všetko fungovalo správne.

Javy spojené s transakciami

Možnosť potvrdenia alebo vrátenia zmien v databáze vykonaných transakciami môže viesť k niektorým nežiaducim javom, a to hlavne v prípade, keď je naraz spustených viac transakcií, ktoré pracujú s rovnakými tabuľkami. Ide predovšetkým o javy:

• Nečisté čítanie (Dirty Reads) – Nastane, keď transakcia číta dáta, ktoré ešte neboli potvrdené. Predpokladajme napríklad, že transakcia 1 aktualizuje nejaký riadok a transakcia 2 tento riadok prečíta ešte predtým, než transakcia 1 potvrdí jeho aktualizáciu. Ak však transakcia 1 vráti zmenu späť (zavolá ROLLBACK namiesto COMMIT), transakcia 2 bude mať načítané dáta, ktoré nemajú existovať.
• Neopakovateľné čítanie (Nonrepeatable Reads) – Nastane, keď je v priebehu transakcie nejaký riadok načítaný dvakrát a hodnoty v riadku sa medzi čítaniami líšia. Predpokladajme napríklad, že transakcia 1 prečíta hodnoty riadku a transakcia 2 hneď nato tento riadok aktualizuje alebo odstráni a danú aktualizáciu alebo odstránenie potvrdí. Ak transakcia 1 znovu načíta riadok, tak načíta iné hodnoty alebo zistí, že riadok bol odstránený.
• Problém stratených aktualizácií – Nastane, keď dve alebo viac transakcií môžu čítať a aktualizovať rovnaké dáta.
• Fantóm – Riadok, ktorý zodpovedá kritériám vyhľadávania, ale nie je spočiatku vidieť. Predpokladajme napríklad, že transakcia 1 číta sadu riadkov, ktoré spĺňajú určité kritériá vyhľadávania. Transakcia 2 vygeneruje nový riadok (pomocou príkazu UPDATE alebo INSERT), ktorý zodpovedá kritériám vyhľadávania transakcie 1. Ak transakcia 1 znovu vykoná vyhľadávanie, získa inú sadu riadkov.

Riešením týchto javov je použitie rôznych úrovní izolácie transakcií.

Úrovne izolácie transakcií v MS-SQL databázach

Úrovne izolácie transakcií sa používajú na definovanie miery, do akej musí byť jedna transakcia izolovaná od zmien dát vykonaných inými súbežne bežiacimi transakciami. Rôzne úrovne izolácie transakcií od najnižších po najvyššie sú:

• Read Uncommitted,
• Read Committed,
• Repeatable Read,
• Serializable.

Aké vyššie spomenuté javy sa môžu ukázať pri akých úrovniach izolácie, ukazuje táto tabuľka:

Úroveň izolácie	Nečisté čítanie	Problém stratených aktualizácií	Neopakovateľné čítanie	Fantóm
Read Uncommitted	x	x	x	x
Read Committed		x	x	x
Repeatable Read				x
Serializable

Porovnanie úrovní izolácie transakcií

Nižšia úroveň izolácie zvyšuje schopnosť mnohých používateľov pristupovať k rovnakým dátam súčasne, avšak zároveň zvyšuje pravdepodobnosť výskytu nežiaducich javov, ktoré sme si uviedli. Vyššia úroveň izolácie znižuje možnosť výskytu týchto javov, ale vyžaduje viac systémových prostriedkov a zvyšuje pravdepodobnosť, že jedna transakcia zablokuje inú.

Nastavenie úrovne izolácie transakcií

Úroveň izolácie transakcií zmeníme pomocou príkazu SET TRANSACTION ISOLATION LEVEL s názvom požadovanej úrovne:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Aktuálnu úroveň izolácie zistíme týmto dotazom:

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 [UrovenIzolacieTransakcie]
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Používame tu pre nás novú konštrukciu CASE. Táto konštrukcia má viac foriem. Nami používaná forma postupne porovnáva hodnotu stĺpca tabuľky (alebo všeobecne nejakého výrazu) so skupinou možných hodnôt, aby určila výsledok. V našom prípade porovnávame hodnotu stĺpca transaction_isolation_level s hodnotami 0 až 4. Pri prvej zhode je vrátená hodnota uvedená za kľúčovým slovom THEN.

Potrebnú informáciu získavame zo systémového pohľadu sys.dm_exec_sessions, ktorý obsahuje informácie o každom aktívnom spojení (session) medzi klientskou aplikáciou a databázovým systémom. @@SPID je potom globálna premenná vracajúca identifikátor aktuálneho spojenia.

Po vykonaní dotazu zistíme, že predvolenou úrovňou izolácie je Read Committed:

UrovenIzolaci­eTransakcie
Read Committed

Stĺpec transaction_isolation_level obsahoval hodnotu 2, preto konštrukcia CASE vrátila reťazec Read Committed.

Zoznam otvorených transakcií

Niekedy sa hodí vypísať bežiace transakcie. To sa dá urobiť nasledujúcim dotazom:

SELECT
    [s_tst].[session_id] AS [IdRelacie],
    [s_es].[login_name] AS [PrihlasovacieMeno],
    DB_NAME (s_tdt.database_id) AS [Databaza],
    [s_tdt].[database_transaction_begin_time] AS [CasZaciatku],
    [s_tdt].[database_transaction_log_bytes_used] AS [BajtyLogu],
    [s_tdt].[database_transaction_log_bytes_reserved] AS [RezervovaneLogy],
    [s_est].text AS [PoslednySQLText],
    [s_eqp].[query_plan] AS [PoslednyPlan]
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 [CasZaciatku] ASC;

Tento zložitý dotaz nebudeme detailne opisovať, pretože slúži primárne ako nástroj na správu databáz. Pre naše účely je kľúčové vedieť, že vracia:

• identifikátor spojenia (IdRelacie),
• prihlasovacie meno (PrihlasovacieMeno),
• názov databázy (Databaza),
• čas štartu transakcie (CasZaciatku),
• koľko zdrojov sa spotrebúva na logovanie (BajtyLogu a RezervovaneLogy),
• naposledy vykonaný SQL kód (PoslednySQLText),
• použitý tzv. execution plan pre daný SQL kód (PoslednyPlan), teda návod, ktorý databázový stroj používa na určenie najefektívnejšieho spôsobu načítania a spracovania dát pre určitý dotaz.

Použijeme ho na rýchlu diagnostiku zablokovania, ktoré vzniká kvôli problémom s izoláciou transakcií, ktoré sme si opísali.

Mechanizmus transakcií teraz po našom laborovaní vrátime do pôvodného stavu príkazom:

IF @@TRANCOUNT > 0
    ROLLBACK;

SET IMPLICIT_TRANSACTIONS OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

V nasledujúcej lekcii, MS-SQL - Dátové typy podrobnejšie, sa pozrieme podrobnejšie na dátové typy v MS-SQL databáze.

 

Mal si s čímkoľvek problém? Stiahni si vzorovú aplikáciu nižšie a porovnaj ju so svojím projektom, chybu tak ľahko nájdeš.