13. diel - MS-SQL - Transakcia

V minulej lekcii, MS-SQL - Trigger (DML) , sme sa venovali triggerům.

V dnešnom MS-SQL tutoriálu si uvedieme transakcie.

Transakcie

Pred príkazy ako sú DELETE, alebo aj UPDATE je lepšie umelo vyvolať transakciu. Potom vykonať príkaz a keď bude výsledok dotazu zle, môžete ho zrušiť. Je tu tá možnosť skontrolovať výsledok dotazu, čo sa mnohokrát veľmi hodí.

Vytvorenie transakcie

BEGIN TRANSACTION transactionName;

Je jasné že názov ktorý tu uvediete, neskôr využijete na potvrdenie, či storne. Teraz už môžete pokojne experimentovať a všetko bude bezpečné.

Zrušenie transakcie

Ak sa niekde sekne a budete potrebovať všetky vykonané zmeny zrušiť použijete príkaz ROLLBACK.

ROLLBACK TRANSACTION transactionName;

Potvrdenie transakcie

Ak všetko urobíte správne, môžete s kľudným svedomím všetky zmeny potvrdiť. Potvrdenie sa vykonáva príkazom COMMIT.

COMMIT TRANSACTION transactionName;

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

Procedúra pre bezpečné úpravy

Tu sa vykonávané úpravy vloží do bloku try. Ak jeden update zlyhá, príkaz commit sa nevykoná a prejde sa do bloku catch, kde sa následne vykoná príkaz rollback.

CREATE PROCEDURE bezpecne_updaty
AS BEGIN
      BEGIN TRY
            BEGIN TRANSACTION
                  update....
                  update....
                  update....
            COMMIT TRANSACTION
      END TRY
      BEGIN CATCH
            ROLLBACK TRANSACTION
      END CATCH
END

Povolenie prístupu (dirty reads)

Pokiaľ má niekto zapnutú transakciu, blokuje ostatných užívateľov. Pokiaľ sa ale napriek tomu potrebujem dostať k dátam, tak môžem pred príkazom select nastaviť READ MODE a čítanie dát sa vykoná:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Lost update problem

Máme dve transakcie tran1 a tran2. Obe sa vykonávajú 10 sekúnd. V prvej transakcii, odpočítame od počtu článkov Číslo 2. V druhej transakciu prirátame 1. Počiatočná stav je 12. (Keď sa odpočítajú 2 články a 1 pripočíta bude výsledok 11).

begin transation tran1
  declare @count int;
  select @count = articleCount from User where id = 12
  set @count = @count - 2;
  update user set articleCount = @count where id = 12
commit transaction tran1

begin transation tran2
  declare @count int;
  select @count = articleCount from User where id = 12
  set @count = @count + 1;
  update user set articleCount = @count where id = 12
commit transaction tran2

Tran2 bola hotová ako prvý, ale keď skončila, tak tran1 už prebiehala a bola uzamknutá, takže to pripočítanie 1 k celkovému počtu článku sa nepropsalo. Výsledok je teda 10 a nie 11, ako by sme čakali.

Riešenie

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Po tomto nastavení sa nebudú môcť 2 transakciu naraz spustiť. V tomto prípade je to chcený stav. Resource = deadlock.

PHANTOM PROBLEM

V priebehu spracovania T2 zavedie T1 do databázy nový údaj (vetu), preto T2 pre dvoch totožné otázky poskytne dve rôzne odpovede.

krok	T1	T2
1.		select sum (stavUctu) from ucty
2.	insert into ucty values (stavUctu, 1000)
3.		select sum (stavUctu) from ucty

Riešenie

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Isolation level vs Snapshot

Isolation level - uzamyká konkrétnu transakciu. Nepovolí prístup z druhej transakcie. Snapshot - So nastavuje celej databáze. Vytvorí kópiu pred transakciou as tou môžeme pracovať s iné transakcie. Pri aupdatu, alebo deletu sa k tabuľkám nedostaneme sa uzamknuté ako pri isolation levelu. Snapshot - nastavenie

ALTER DATABASE data0003 SET ALLOW_SNAPSHOT_ISOLATION ON
....
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

S tým že hlavné transakcie by mala mať nastavené LEVEL Serializable

Stupňa ochrany

• READ uncommitted - 1 ° browse - pre read-only transakcie
• READ Committed - stabilita kurzoru (vylepšený 2 °)
• REPEATABLE READ - 3 ° bez ochrany fantóm ov
• Serializable - 3 ° v čítaní e ochany fantóm ov

Podrobná tabuľka

Isolation level	dirty Reads	lost Update	Nonrepeatable Reads	phantom Reads
read uncommitted	x	x	x	x
read Committed		x	x	x
Repeatable Read				x
snapshot
Seerializable

Zoznam otvorených transakcií

Niekedy sa hodí vypísať všetky transakcie, ktoré bežia. To sa dá urobiť nasledujúcim dotazom:

SELECT [s_tst].[session_id], [s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
 [s_tdt].[database_transaction_begin_time] AS [Begin Time],
 [s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
 [s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
 [s_est].text AS [Last T-SQL Text], [s_eqp].[query_plan] AS [Last Plan]
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 [Begin Time] ASC; GO

V ďalšej lekcii, MS-SQL krok za krokom: Klauzula HAVING , si predstavíme klauzulu HAVING, ukážeme si, na čo sa používa, aká je syntax a aké sú hlavné rozdiely medzi HAVING a WHERE.