13. díl - MS-SQL - Transakce

MS-SQL krok za krokem MS-SQL - Transakce

Transakce

Před příkazy jako jsou DELETE, nebo i UPDATE je lepší uměle vyvolat transakci. Poté provést příkaz a když bude výsledek dotazu špatně, můžete ho zrušit. Je tu ta možnost zkontrolovat výsledek dotazu, což se mnohdy velmi hodí.

Vytvoření transakce

BEGIN TRANSACTION transactionName;

Je jasné že název který zde uvedete, později využijete k potvrzení, či stornu. Teď již můžete klidně experimentovat a vše bude bezpečné.

Zrušení transakce

Pokud se někde seknete a budete potřebovat všechny provedené změny zrušit použijete příkaz ROLLBACK.

ROLLBACK TRANSACTION transactionName;

Potvrzení transakce

Pokud vše provedete správně, můžete s klidným svědomím všechny změny potvrdit. Potvrzení se provádí příkazem COMMIT.

COMMIT TRANSACTION transactionName;

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

Procedura pro bezpečné úpravy

Zde se prováděné úpravy vloží do bloku try. Pokud jeden update selže, příkaz commit se neprovede a přejde se do bloku catch, kde se následně provede pří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

Povolení přístupu(dirty reads)

Pokud má někdo zapnutou transakci, blokuje ostatní uživatele. Pokud se ale přesto potřebuji dostat k datům, tak můžu před příkazem select nastavit READ MODE a čtení dat se provede:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Lost update problem

Máme dvě transakce tran1 a tran2. Obě se provádějí 10 sekund. V první transakci, odečteme od počtu článků Číslo 2. Ve druhé transakci přičteme 1. Počáteční stav je 12. (Když se odečtou 2 články a 1 přičte bude výsledek 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 byla hotová jako první, ale když skončila, tak tran1 již probíhala a byla uzamknutá, takže to přičtení 1 k celkovému počtu článku se nepropsalo. Výsledek je tedy 10 a ne 11, jak bychom čekali.

Řešení
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Po tomto nastavení se nebudou moci 2 transakci najednou spustit. V tomto případě je to chtěný stav. Resource = DEADLOCK.

PHANTOM PROBLEM

V průběhu zpracování T2 zavede T1 do databáze nový údaj (větu), proto T2 pro dva totožné dotazy poskytne dvě různé odpovědi.

Krok T1 T2
1.   select sum (stavUctu) from ucty
2. insert into ucty values(stavUctu, 1000)  
3.   select sum (stavUctu) from ucty
Řešení
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Isolation level vs Snapshot

Isolation level - uzamyká konkrétní transakci. Nepovolí přístup z druhé transakce. Snapshot - Se nastavuje celé databázi. Vytvoří kopijí před transakcí a s tou můžeme pracovat s jiné transakce. Při aupdatu, nebo deletu se k tabulkám nedostaneme, budou uzamknuté jako při isolation levelu. Snapshot - nastavení

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

S tím že hlavní transakce by měla mít nastaveno LEVEL SERIALIZABLE

Stupně ochrany

  • READ UNCOMMITTED - 1° browse - pro read-only transakce
  • READ COMMITTED - stabilita kursoru (vylepšený 2 °)
  • REPEATABLE READ - 3 ° bez ochrany fantom ů
  • SERIALIZABLE - 3 ° v četn ě ochany fantom ů
Podrobná tabulka
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        

Seznam otevřených transakcí

Někdy se hodí vypsat všechny transakce, které běží. To se dá udělat následujícím dotazem:

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

 

  Aktivity (2)

Článek pro vás napsal Milan Gallas
Avatar
Autor se věnuje programování, hardwaru a počítačovým sítím.

Jak se ti líbí článek?
Celkem (1 hlasů) :
4444 4


 


Miniatura
Předchozí článek
MS-SQL - Trigger (DML)
Miniatura
Všechny články v sekci
MS-SQL databáze krok za krokem
Miniatura
Následující článek
MS-SQL - Uložené procedury

 

 

Komentáře

Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zatím nikdo nevložil komentář - buď první!