Transakce a zamykání Jiří Tomeš Administrace MS SQL Serveru (NDBI039)
O čem to dnes bude
Úvodní opakování základních pojmů
Jištění transakcí
Speciální konstrukce
Typy transakcí
Závěrečný souhrn, použité prameny a zdroje
Zámky a Izolace
Prostor pro dotazy
Trocha opakování Co je transakce :
Skupina (SQL) příkazů (operace s databází) převádějící data mezi jednotlivými konzistentními stavy
Navenek se tváří jako 1 příkaz
Pro databázový stroj je chápána jako nedělitelná jednotka
je potřeba, aby byla celá dokončena (commit) jinak nesmí být vůbec provedena (rollback)
Transakce vždy musí splňovat podmínky ACID
O podmínkách ACID
A – Atomicity („VŠE nebo NIC“) transakce musí být nedělitelná pokud nemůže databázový stroj operaci dokončit, veškeré již provedené změny musí být zrušeny
C – Consistency („Data se nesmí poškodit“)
I – Isolation („Vzájemně se nevidí“)
data musí být před započetím i po dokončení v konzistentním stavu změny prováděné v 1 transakci nejsou v jiné transakci viditelné
D – Durability (NE)Dokončené
databázi
změny transakce se (NE)projeví v
Co se děje uvnitř Po zahájení transakce se změny ukládají do interního transakčního logu Ten je zpočátku v operační paměti a při větších transakcích se odkládá na disk do souboru “transact.fil“ Až do commitu jsou tedy v databázi uložené hodnoty z doby před zahájením transakce Jak docílit toho, aby nedošlo ke ztrátě dat i při vypnutí serveru ?
Použijeme MECHANISMUS JIŠTĚNÍ !!!
Mechanismus jištění transakcí Při provádění transakcí je kritickým bodem jejich uzavírání (zaznamenávání změn do databáze) Mechanismus jištění přitom zajišťuje:
Konzistenci databáze (v případě výpadku počítače během uzavírání transakce)
Vypíná či zapíná správce databáze
Zapíná se pomocí vlastnosti serveru „SecureTransaction“
Zápis změn na disk
Většina OS má zabudovanou funkci, která odkládá zápis změn na disk (pro zrychlení běhu aplikace)
Má-li spolehlivě fungovat jištění transakcí, pak je nezbytné zapisovat při ukončení transakce všechny změny na disk
Zapíná se pomocí vlastnosti serveru „FlushOnCommit“
Ovlivnění rychlosti Jištění transakcí:
Vyžaduje cca dvojnásobný počet diskových operací při každé transakci Transakce nemají vliv na:
rychlost čtení, vyhledávání
Závisí na použitém počítači
Příklad (provedeno 1000 zápisů):
Pouze pro vyjádření závislosti času na způsobu práce
Typy transakcí SQL server rozlišuje následující typy transakcí:
Explicitní
Implicitní
chceme-li více operací v datech provést jako 1 celek
každý příkaz ~ vlastní transakce
Autocommit
defaultní nastavení
Explicitní transakce Začátek a konec transakce je explicitně zadán pomocí příkazů BEGIN TRANSACTION COMMIT (případně ROLLBACK)
Explicitní transakce mohou být vnořené, kdy v těle jedné transakce je vložena další
To umožňuje vytvářet uložené procedury pracující s vlastními transakcemi a volat je bez ohledu na to, zda volající proces má či nemá otevřenou transakci
Autocommit & TRANCOUNT V
takovém případě je u transakce příkaz vykonán ve vlastní samostatné transakci, která je po dokončení operace automaticky potvrzena. Odtud také název autocommit. Funkce @@TRANCOUNT: (INT) SELECT @@TRANCOUNT;
0 (žádná transakce není otevřená) číslo (úroveň zanoření) BEGIN (+1) COMMIT (-1) ROLLBACK (přiřadí 0)
Implicitní transakce
Práce s implicitními transakcemi : SET IMPLICIT_TRANSACTIONS ON;
V tomto módu každý příkaz v rámci daného připojení otevře transakci, pokud žádná není otevřená Pokud již transakce otevřená je, příkaz novou transakci neotvírá, ale je vykonán v rámci transakce již otevřené. Ukončení transakce musí být v tomto módu provedeno vždy explicitně voláním příkazu COMMIT, případně ROLLBACK. Transakce se tedy v tomto módu otevírá sama (implicitně), ukončit transakci je však třeba explicitně.
Zámky a izolace transakcí
Exklusivní (X) Číst
a zapisovat může pouze vlastník zámku Smí být přidělen nejvýše 1 transakci Trvá do konce transakce
Sdílené (S) Uzamkne
se daná entita pro zápis Číst může vlastník zámku (má jistotu, že nikdo nezapisuje) Může být přidělen více transakcím Trvá do načtení objektu nebo konce transakce SQL Server chce docílil co možná největší míry paralelního zpracování - používá různou granularitu zámků. Lze zamykat Řádky tabulek, celé datové stránky, tabulky, databáze
Případné problémy se zámky Kritické vlastnosti: Pokud
transakce dlouho a často zamyká exkluzivně řádek, nedá se číst Pokud mnoho transakcí zamyká řádek pro čtení, nedá se upravit Uzamknutí stránky nebo celé tabulky omezí přístup na data, která transakce nepoužívá Zámky vyžadují určitou režii
Izolace transakcí
Úroveň izolace transakcí Transaction Isolation Level udává do jaké míry se budou transakce vzájemně ovlivňovat
„jak
moc si budou vidět pod ruce“
Použitá úroveň izolace transakcí přitom ovlivňuje:
Zda se budou používat zámky a jakých typů Jak dlouho se mají držet zámky pro čtení Zda-li čtení dat modifikovaných jinou transakcí: Bude
blokováno, dokud se neuvolní explicitní zámky Použije se potvrzená verze dat Přečtou se modifikovaná dosud nepotvrzená data
Typy úrovně izolace transakcí
SQL server rozlišuje 5 typů: READ COMMITED (default) READ UNCOMMITED REPEATABLE READ SERIALIZABLE SNAPSHOT (od verze 2005)
Úroveň izolace lze pak nastavit příkazem SET : SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Princip snapshotů Jak to celé funguje:
Transakce T1 vidí data ve stavu počátku transakce (Start Timespan) Pokud data někdo mění, server udržuje pro transakci původní obraz Na
serveru může najednou existovat mnoho obrazů stejných dat
Transakce může měnit data Po provedení COMMIT se vytvoří COMMIT Timespan Ověří
se, že žádná jiná transakce T2 s COMMIT Timespan v intervalu [Start Timespan, COMMIT Timespan] transakce T1 neměnila data, která chce T2 zapsat. Pokud ano, je T2 zrušena
Explicitní vs. Implicitní „Izolace“
Explicitní transakce Stupeň
izolace se nastavuje pro každou transakci zvlášť Pomocí START_TRANSACTION Není-li zvolen stupeň izolace, zvolí-li SERIALIZABLE
Implicitní transakce Není
způsob jak nastavit stupeň izolace Použije se způsob u předchozí prováděné transakce Není-li možné aplikovat předchozí pravidlo, nastaví se READ COMMITED
START_TRANSACTION
Schéma příkazu : START_TRANSACTION [izolace][druh_izolace] Izolace ::= IZOLATION LEVEL READ
UNCOMITTED READ UNCOMITTED REPEATABLE READ SERIALIZABLE
druh_izolace ::= READ ONLY | READ WRITE
Úroveň izolace souhrnem Přehledová tabulka:
Dirty read:
Dirty write:
T1 modifikuje A T2 modifikuje A před tím než T1 projede COMMIT (ROLLBACK) ??? Stav A při ROLLBACK T1(T2)
T1 modifikuje A
T2 čte A před koncem T1
!!! ROLLBACK T1 => neexistující data pro T2
Fuzzy read & Phantom Fuzzy read:
T1 přečte A T2 změní nebo zruší A a projede COMMIT !!! T1 přečte data A, ty ale mají jinou hodnotu (nebo neexistují)
Phantom:
T1 přečte data s podmínkou <where> T2 přidá data odpovídající <where> a provede COMMIT !!! T1 znovu načte data s touto podmínkou,ale přitom dostane jiný výsledek
Phantom se vrací
Kde je (může být) problém ?
Select může vrátit nenulové číslo jiná transakce mezitím vložila produkt s DPH 22% jiná transakce smazala nějaký zmodifikovaný záznam
Princip deadlocku (uváznutí) :
Deadlock
Jde o situaci, kdy si 2 transakce (nebo více) vzájemně blokují zdroje a čekají na jejich uvolnění
Může nastat prakticky v každém systému, kde se více uživatelů dělí o zdroje
Řešení pak musí provést nějaká vnější autoria Datový
server
Server dokáže deadlock identifikovat Zruší
jednu (ev. i více) z množiny transakcí
Speciální konstrukce
Nečekat na uvolnění zámků nebo čekat jen nezbytnou dobu
Číst pouze z neuzamčených oblastí
select * from employee for update nowait; select * from employee for update wait 10;
select * from employee for update nowait;
Manuálně uzamknout tabulku
lock table table_name in {share | exclusive } mode [ wait [ numsecs ] | nowait ]
Závěrečné shrnutí
Složitost problematiky transakcí se projevuje při zvýšení počtu uživatelů (paralelních transakcí)
Nárůst problému je exponenciální vzhledem k počtu paralelních transakcí
Nutno vždy počítat s tím, že transakce bude z nějakého důvodu zrušena
Je vždycky dobré znát vlastnosti a přesné možnosti konkrétního serveru (verze)
Použité zdroje a prameny
Microsoft SQL Server 2008 Books Online
Článek z ROOT.CZ
http://msdn.microsoft.com/enus/library/windows/desktop/aa366402(v=vs.85).aspx
http://www.root.cz/clanky/transakce-a-izolace-transakci-vdatabazich/
602SQL Server
http://sql602.sourceforge.net/helpdir- cs/html/HE_SQL2_TRANSAKCE.htm
Prostor pro dotazy
Zeptejte se na to, co Vás zajímá
DÍKY za pozornost během prezentace
