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

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ále sa bližšie pozrieme na transakcie.

Ako už vieme zo skorších tutoriálov, tak transakcia je súbor niekoľkých otázok, ktoré databáza chápe ako jeden dotaz. Môžeme vďaka nim zaistiť, aby sa buď vykonali všetky otázky 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 východiskové 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 definovať 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 Firm zo skorších 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 [Branches] SET [NumberOfEmployees] = 80
WHERE [BranchId] = 1;

COMMIT TRANSACTION;

Tabuľka Branches:

BranchId	City	Name	NumberOfEmployees
1	London	ICTdemy	80
2	Glasgow	ICTdemy	50
4	Leeds	ICTdemy	200

Odvolanie príkazu:

UPDATE [Branches] SET [NumberOfEmployees] = 50
WHERE [BranchId] = 1;

ROLLBACK TRANSACTION;

Tabuľka Branches:

BranchId	City	Name	NumberOfEmployees
1	London	ICTdemy	80
2	Glasgow	ICTdemy	50
4	Leeds	ICTdemy	200

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

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

Explicitné transakcie

Jedná sa 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 UpdateNumberOfEmployees
    @BranchId INT,
    @NumberOfEmployees INT
AS
BEGIN
    BEGIN TRANSACTION UpdateTransaction;

    BEGIN TRY
        UPDATE [BranchStatistics] SET [EmployeeTotalCount] = [EmployeeTotalCount] - [NumberOfEmployees]
        FROM [Branches]
        WHERE [BranchId] = @BranchId;

        UPDATE [Branches] SET [NumberOfEmployees] = @NumberOfEmployees
        WHERE [BranchId] = @BranchId;

        UPDATE [BranchStatistics] SET [EmployeeTotalCount] = [EmployeeTotalCount] + @NumberOfEmployees;

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

Transakciu začneme príkazom BEGIN TRANSACTION, za ktorým 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 AfterUpdateBranches, preto je potrebné ho najskôr odstrániť príkazom DROP TRIGGER [AfterUpdateBranches], 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 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 znova 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 tých najnižších po najvyššie sú:

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

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

Ú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

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 touto otázkou:

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;

Predvolenou úrovňou izolácie je Read Committed:

TransactionIso­lationLevel
Read Committed

Zoznam otvorených transakcií

Niekedy sa hodí vypísať bežiace transakcie. To sa dá urobiť nasledujúcou otázkou:

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 budú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š.