Transakce a izolace transakcí v databázích
17.5.2009 Pavel Stěhule
Kdyby neexistovala MySQL, tak by transakce patrně nebyly tak populárním tématem. I když ne všechny databáze byly a jsou transakční, nikdo (do nástupu MySQL) netvrdil, že transakce jsou něco "navíc". MySQL přišlo s novou filozofií - databáze nemusí být až tak 100% spolehlivé, hlavně musí být rychlé. Tento přístup se pak uchytil zejména mezi web designery. Možná proto, že chyby v datech nejsou na první pohled vidět, zato rychlost, resp. pomalost webu je zřejmá. Před cca. deseti lety, v době nástupu MySQL (a PHP) byl internet zvláštní experimentální zónou. S občasným výpadkem některých serverů si nikdo zvlášť hlavu nelámal, stejně tak se zabezpečením, ergonomií, rychlostí. Technologie, které se v té době používaly byly neporovnatelné s těmi dnešními. Ať už jsou to staré verze PHP nebo o něco málo mladší verze ASP. Databáze se používaly pro uložení výsledků anket, uložení účtů a hesel (vesele s otevřenými hesly), pro ukládání logů. Ztráta dat, v té době, neznamenala větší komplikaci (u typických www aplikacích konce devadesátých let). Pragmatický přístup MySQL AB (a nejen MySQL AB - "co neumíme, neumíme, protože to správný programátor nepotřebuje" - a to ať už se jednalo o podporu transakcí, podporu referenční a doménové integrity, poddotazů, reálně nikoho neomezoval (datové schéma www aplikací bylo jednoduché, dat bylo relativně málo, stejně tak jako programátorů), a tak se diskuze o významu transakcí vedli hlavně v ideologické rovině. Tudíž naprosto bezvýsledné a nekonečné. Možní díky nim málo který sw, jako MySQL, vzbuzuje tolik emocí. MySQL má stovky oddaných stoupenců, a stejně tak stovky vášnivých odpůrců. Tyto diskuze, naštěstí utichly, díky InnoDB engine, který implementuje vše, co uživatelé "opravdových databází" vyžadují, a engine MyISAM, který neimplementuje nic navíc.
Diskuze ohledně transakcí se netýkali jen stoupenců a odpůrců MySQL. Jako dnes si vzpomínám rozhovor s jedním programátorem nad Microsoft SQL Serverem v roce cca 2001. Na otázku, zda-li používá transakce, s pousmáním odpověděl otázkou. "K čemu? Šance, že server spadne v okamžiku, kdy se něco děje je minimální." Další moje otázka byla ohledně izolace uživatelů, řešení kolizí. Ani touto otázkou jsem jej nedostal - servery jsou tak rychlé, že není šance, že by mohlo dojít ke kolizi. A to se nejednalo o nějakého amatéra, ale o Microsoftem certifikovaného inženýra.
V ANSI SQL se předpokládá, že databáze implementuje transakce, které jsou prostředkem zajištění integrity dat. Všeobecně se ví, že transakce jsou ochranou před softwarovou nebo hardwarovou chybou (častou chybou je např. nedostatek místa na disku). Ovšem datová integrita může být porušena i z důvodů souběžné činnosti více uživatelů nad jednou databází. Takže pod jakousi správu transakcí spadá i koordinace jednotlivých SQL příkazů (spuštěných různými uživateli) v databázi. Při psaní aplikací, kde si uživatelé mohou přepisovat data je nutná mírná obezřetnost.
SELECT pocet FROM pocty WHERE id = 10 INTO $promenna; UPDATE pocty SET pocet = $promenna + 1 WHERE id = 10;
V jednouživatelské aplikaci se jedná o bezpečný kód. Problém může nastat, pokud tento kód spustí současně dva a více uživatelů.
u1: SELECT => $promenna := 25; u2: SELECT => $promenna := 25; u1: UPDATE => pocet := 26; u2: UPDATE => pocet := 26;
V to chvíli došlo tzv. race condition (souběhu) http://cs.wikipedia.org/wiki/Race_condition. Správný výsledek je 27, kdežto hodnota uložená v databázi bude 26. Teoreticky, a v některých databázích i prakticky (PostgreSQL a Oracle mezi nimi není) nás před touto chybou ochrání správně nastavená (nejvyšší) úroveň izolace transakce (viz dále). V PostgreSQL je nutné upravit kód:
- vynutit si řádkový zámek
SELECT pocet FROM pocty WHERE id = 10 FOR UPDATE INTO $promenna; UPDATE pocty SET pocet = $promenna + 1 WHERE id = 10;
- použít relativní UPDATE
UPDATE pocty SET pocet = pocet + 1 WHERE id = 10;
Uznávám, že je to trochu zrada - mám transakce, mám izolace transakcí, a stejně musím řešit souběh. Souběh, race condition je zákeřná chyba - obtížně se simuluje, vyskytuje se minimálně, a to ještě v závislosti na zatížení databáze. Setkal jsem se s jedním eshopem, kde se při předvánoční nákupní horečce náhodně objevovalo a ztrácelo zboží ve skladu (v tom virtuálním nikoliv fyzickém). Jinak bylo všechno celý rok v pohodě. Vsadil bych se o pivko, že se jednalo o souběh.
Transakce
Pod transakcí si můžeme představit příkaz nebo skupinu příkazů, které převedou databázi (resp. data) z jednoho konzistentního stavu do druhého. Další definice říká, že transakce je skupina příkazů, která se navenek tváří jako jeden příkaz
Pokud budu provádět UPDATE jednoho sloupce velké tabulky, tak konzistentní stav je před zahájením příkazu a po dokončení příkazu. V mezi čase je obsah tabulky nekonzistentní - část je modifikovaná, část nikoliv. Pokud je příkaz spuštěn v rámci transakce, tak prostředky databáze je zajištěno, že uživatelé budou mít vždy přístup pouze ke konzistentním datům. Dalším dnes již klasickou ukázkou je převod částky z účtu na účet.
BEGIN; UPDATE ucty SET castka = castka - 100 WHERE ucet = 1234; UPDATE ucty SET castka = castka + 100 WHERE ucet = 4321; COMMIT;
Transakce zajišťují jednak bezpečnost - viz problém v případě výpadku po provedení prvního příkazu UPDATE (pozn. vypařila by se částka 100), a jednak konzistenci dat - data v okamžiku, kdy došlo k odečtu účtu, a ještě nedošlo k přičtení částky na druhý účet jsou nekonzistentní. Pravidlo pro konzistenci by mohlo vypadat následovně (pro operaci MOVE): celková částka na účtech je konstantní (pokud nedojde k přijetí nebo k odeslání určité částky jinam).
Kritéria ACID
Kritéria ACID jsou teoretické požadavky na chování systémů, na kterých vyžadujeme bezpečné zpracování dat. Ne všechny databáze musí být transakční. Kromě databází mohou být transakční i jiné systémy - např. zpracování front požadavků, atd.
- A - Atomicity - atomicita - příkazy v transakci se vždy provedou všechny nebo žádný.
- C - Consistency - konzistence - před a po dokončení transakce jsou data konzistentní.
- I - Isolation - izolovanost - transakce je izolovaná od okolí, operace uvnitř transakce jsou pro okolí neviditelné.
- D - Durability - trvalost - pokud byla transakce potvrzena, pak změny dat jsou trvalé a nemohou být ztraceny.
Díky transakcím lze zjednodušit aplikační logiku více uživatelských aplikací. Transakce také zvyšují odolnost databáze vůči poškození. Na druhou stranu, existuje škála úloh, kde jsou transakce zbytečné (v jedno uživatelských aplikacích) - generování reportů, párování, kde by transakce představovaly zbytečnou zátěž. Proto netransakční databáze mají smysl, a proto existují - ať je to už MyISAM nebo stále populárnější memcache.
V SQL pro transakce existují tři základní příkazy: BEGIN - začátek transakce, ROLLBACK - odvolání transakce, a COMMIT - potvrzení transakce. Vlastní implementace je více-méně specifická pro každý databázový systém, a předurčuje chování databáze (a to ať celkově nebo v některých drobných nuancích - např. rozdíl InnoDB a PostgreSQL). V zásadě je možné rozdělit implementace do třech skupin:
- multigenerační architektura (Oracle, PostgreSQL, Firebird, InnoDB),
- používající zámky (DB2),
- kompromisní - obě předchozí architektury (Microsoft SQL Server).
Každá s těchto architektur má svoje pro a proti, a totéž platí i pro každou implementaci.
Možné situace při souběžné práci uživatelů, izolace transakcí
Ideálem izolace transakcí je navodit představu, že s databází pracuje pouze jeden uživatel. To samozřejmě není úplně možné, a aktivita ostatních uživatelů je občas vidět. Pokud budou s databází pracovat minimálně dva uživatelé, mohou nastat následující fenomény:
- Špinavé čtení (Dirty read) - příkaz SELECT načte nepotvrzená data druhé transakce - problém nastane v případě ROLLBACKu.
- Neopakovatelné čtení (Nonrepeatable Read) - dva po sobě jdoucí stejné příkazy SELECT mohou vrátit různé výsledky.
- Výskyt fantomů - při něm může mít uživatel pocit, že v databázi působí nadpřirozené síly viz:
BEGIN; UPDATE zaznamy SET deleted = true; SELECT count(*) FROM zaznamy WHERE NOT deleted; COMMIT;
Pokud count je větší než nula, tak máme fantomové řádky. Samozřejmě, zázraky se nedějí, i když moje dávná šéfová by to určitě sváděla na trpaslíky. Prostě další uživatel přidal a potvrdil nový záznam do tabulky zaznamy.
V ANSI SQL (SQL92) jsou pojmenovány a popsány výše uvedené možné situace a následně jsou definovány úrovně izolace transakcí, které předcházejí výše zmíněným situacím. Databázový systém nemusí implementovat všechny úrovně, stačí implementovat nejvyšší úroveň. Každá úroveň zastupuje všechny nižší úrovně. Tato definice je názorná, ale není úplná, co do popisu chování databázového systému. Jednotlivé implementace, které splňují standard, nemusí být 100% vzájemně kompatibilní. Typickou ukázkou je rozdíl v chování databází Oracle a DB2. Je to jen moje domněnka, ale snad nebude daleko od pravdy. V době psaní standardu se vůbec nepočítalo s multigenerační architekturou. Implicitně se počítalo se zamykáním - problém je v tom, že to ve standardu nikde není zmíněno. Důsledkem je sice platnost standardu i pro jinou databázovou architekturu (což dokazuje implementační nezávislost standardu), ale také reálná nekompatibilita stávajících databázových systémů (ačkoliv splňují standard).
Možné úrovně izolace transakcí jsou:
- READ UNCOMMITED - může nastat špinavé čtení, neopakovatelné čtení, a výskyt fantomů.
- READ COMMITTED - nemůže nastat špinavé čtení, může nastat neopakovatelné čtení, a výskyt fantomů.
- REPEATABLE READ - nemůže nastat špinavé čtení, neopakovatelné čtení, mohou se vyskytnout fantomy.
- SERIALIZABLE - nemůže nastat špinavé čtení, neopakovatelné čtení, a výskyt fantomů
Nejvyšší úrovní je úroveň serialisable. Tato úroveň nejlépe vzájemně izoluje uživatele databáze. Jak je z názvu patrné, transakce by se měly provádět za sebou, nikoliv souběžně. Triviální implementace této úrovně spočívá v omezení maximálního počtu přihlášených uživatelů na jednoho uživatele. Podobnou techniku používá např. SQLite. Výsledkem triviální implementace je totální destrukce výkonu databáze v případě přístupu více uživatelů. Vyjma embeded databází (což je např. SQLite) je proto tato technika nepoužitelná. I o něco sofistikovanější technika - provádění vždy pouze jedné write transakce je neefektivní a prakticky se nepoužívá. To, že je definována více než jedna úroveň izolace transakcí vychází ze skutečnosti, že úroveň SERIALIZABLE je "provozně" náročná - znamená intenzivní zamykání a ve většině případů není nutná. Nižší úroveň znamená méně zámků (v klasické ne MVCC architektuře). A je na programátorovi, aby vhodně zvolil bezpečnou a zároveň co nejúspornější úroveň pro jeho aplikaci.
PostgreSQL implementuje pouze READ COMMITED (READ UNCOMMITED jako READ COMMITTED) a SERIALIZABLE (REPEATABLE READ jako SERIALIZABLE). Hlavní rozdíly v chování databází jsou v úrovni SERIALIZABLE. Za vzorové chování se považuje implementace v DB2, naopak diskutabilní je implementace v Oracle a PostgreSQL ( http://en.wikipedia.org/wiki/Isolation_(database_systems) ). Vývojáři Firebirdu raději nejasnosti ohledně SERIALIZABLE neřešili a přišli s vlastní úrovní SNAPSHOT a SNAPSHOT TABLE STABILITY. Problém je, zda se upřednostní definice, která říká, že v SERIALIZABLE úrovni se nemá vyskytovat špinavé čtení, neopakovatelné čtení a fantómy, nebo sémantika, která mluví o řazení transakcí za sebou. Ta druhá varianta znamená zamykání celých oblastí tabulek (v lepším případě, v horším celých tabulek) - což může znamenat znatelné snížení výkonu. Naopak (v Oracle a PostgreSQL) při psaní aplikací, případně při migraci musí vývojáři dbát na explicitní zamykání flagem FOR UPDATE, případně zamykat celé tabulky (příkaz LOCK).
Standard specifikuje výchozí úroveň izolace transakcí a tou je úroveň SERIALIZABLE. Aplikační vývojář by tak měl úroveň ve své aplikaci spíše snižovat. Realita je přesně opačná. Plná implementace úrovně SERIALIZABLE je relativně náročná na zámky, takže výchozí úroveň je mnohem níže, a je na vývojářích vynucení vyšší úrovně izolace (výchozí READ COMMITTED je v MS SQL Server, PostgreSQL, Oracle, DB2). Ukázkovým příkladem je ověření rozvahy, kdy se aktiva musí rovnat pasivům.
BEGIN SELECT sum(castka) FROM aktiva; SELECT sum(castka) FROM pasiva; COMMIT;
Výše uvedený kód, v případě úrovně READ COMMITED, může vést k falešným poplachům - tj aktiva se nerovnají pasivům. Při souběhu by se totiž mohla projevit cizí potvrzená transakce v součtu pasiv, kdežto v součtu aktiv by se ještě neprojevila.
Správná verze obsahuje nastavení izolace transakce SERIALIZABLE:
BEGIN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT sum(castka) FROM aktiva; SELECT sum(castka) FROM pasiva; COMMIT;
V MVCC databázích úroveň SERIALIZABLE (Oracle, PostgreSQL), úroveň SNAPSHOT (Firebird), úroveň REPEATABLE READ(InnoDB) neznamená vyšší režii nebo pomalejší aplikace - využívá se vlastností architektury a není důvod, proč se "omezovat" nižší úrovní. Použití výchozího READ COMMITTED je více-méně úzus než nutnost.
Vrátím se k našemu prvnímu příkladu s aktualizací tabulky počty. Zde dochází k tzv. kolizi - k aktualizaci jednoho záznamu dvěma různými uživateli. Pokud v úrovni READ COMMITED nepoužijeme flag FOR UPDATE, dojde k souběhu (Race condition). V úrovni SERIALIZABLE je souběh ošetřen - pokus o editaci jednoho záznamu skončí výjimkou - serializable error "ERROR: could not serialize access due to concurrent update" - v podstatě se jedná o optimistické zamykání.
Pokud používáme úroveň SERIALIZABLE, musíme s touto výjimkou počítat - a v aplikaci rizikové operace zapouzdřit do cyklu (FOR UPDATE nepomůže):
loop
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT pocet FROM pocty WHERE id = 10 INTO $promenna;
$promenna := $promenna + 1;
UPDATE pocty SET pocet = $promenna WHERE id = 10;
if (no error)
break;
else
ROLLBACK;
end loop;
COMMIT;
V závislosti na četnosti kolizí je výhodnější první nebo druhá strategie - pokud je riziko kolizí velké, pak je výhodnější zamykání. Pokud ke kolizím dochází zřídka, pak je výhodnější optimistický přístup v úrovni SERIALIZABLE. V každém případě vývojář musí možnost kolize ošetřit!
Pozor i pesimistický přístup v úrovni READ COMMITTED (s FOR UPDATE) může skončit výjimkou - dead lockem. Pro představu - transakce X1 bude provádět převod z účtu A na účet B, a souběžná transakce X2 z účtu B na účet A. Což znamená, že v cyklu by měly být zapouzdřené operace i při používání úrovně READ COMMITTED a v fakticky úroveň READ COMMITTED nepřináší žádnou úsporu kódu.
V MVCC databázích platí, že zapisující proces neblokuje čtecí proces - writers don't block readers and readers don't block writers (neplatí pro úroveň SERIALIAZBLE. Pokud se ovšem dva zapisující procesy sejdou na jednom záznamu, pak druhý proces (který jako druhý provedl zápis) čeká na dokončení transakce prvního procesu. A to z toho důvodu, že není dopředu jasné, zda bude transakce prvního procesu potvrzena nebo odvolána.
Pozn. MVCC databáze úrovně READ COMMITTED a SERIALIZABLE implementují skrze tzv. snímky databáze, tzv. snapshoty. Tato technika sama o sobě nezabraňuje vytvoření fantomových řádků. Ty mohou vznikat, nejsou ovšem z ostatních transakcí viditelné. Tudíž ukázky fantomových řádků na úrovni READ COMMITTED pro MVCC databáze jsou jakoby nefunkční.
Explicitní zamykání - příkaz LOCK
V některých aplikacích se můžeme setkat s tzv. víceřádkovými podmínkami - (multi-row constraints). Např. součet všech objednávek od jednoho uživatele nesmí přesáhnout částku 10 000, nebo maximální měsíční počet objednávek může být 10. Teď vařím z vody, nikdy jsem se s ničím podobným nesetkal, ale dovedu si něco podobného představit. Tady klasický zámek nepomůže - nemůžete zamknout neexistující řádek. Řešením je uzamčení tabulky - nebo (v případě, že to databáze podporuje (pouze DB2), tzv. predikátový zámek (predicate lock). V Postgresu musí veškerou práci odřít vývojář a zamknout tabulku. V tomto případě stačí sdílený zámek - který umožní ostatním čtení tabulky, ale nedovolí zápis:
BEGIN
LOCK TABLE objednavky IN SHARE ROW EXCLUSIVE MODE;
SELECT count(*) FROM objednavky WHERE user_id = 1234 INTO $pocet;
if ($pocet < 10)
{
INSERT INTO objednavky (user_id, ...) VALUES(1234, ...);
COMMIT;
}
else
ROLLBACK;
Při jakémkoliv použití zámků je nutné se zabývat otázkou deadlocku. Na úrovni tabulek je řešení poměrně jednoduché - stačí vždy zamykat ve stejném pořadí. Stejné pravidlo samozřejmě platí i pro záznamy, ale, předpokládám, že by se dost obtížně implementovalo. Dnešní databáze deadlock dokáží identifikovat, a i řešit (výjimkou). Programátor by měl brát v úvahu potenciální deadlock, a aplikace by měla být připravená na tuto výjimku.
Rozdíly mezi InnoDB a PostgreSQL
InnoDB je představitelem multigenerační architektury (MVCC nebo MGA) s rollback segmentem (je to podobná implementace jako u Oracle). Rollback segment se používá jednak pro operaci ROLLBACK, druhak pro přístup ke starším verzím záznamů. Data v InnoDB se stěhují z datových souborů do rollback segmentu. Rollback segment v PostgreSQL úplně chybí. Verze záznamů zůstávají v jednom datovém souboru a nepřepisují se (dokud nejsou označeny jako explicitně mrtvé - viz. příkaz VACUUM). Výhodou InnoDB je rychlé čtení potvrzených neaktualizovaných záznamů, naopak nevýhodou pomalejší UPDATE a významně pomalejší ROLLBACK. V PostgreSQL je náročnější čtení (z disku se načítají jak potvrzené, tak nepotvrzené verze), rychlý COMMIT, ještě rychlejší ROLLBACK a a nevýhodou nutnost spouštět VACUUM. Obě databáze si relativně dobře poradí s dlouhými transakcemi - jakkoliv se doporučuje mít transakce rozumně dlouhé - u MySQL roste rollback segment, u Pg zas datové soubory.
Zásadní rozdíl mezi InnoDB a PostgreSQL je použití primárního klíče jako cluster indexu. Tj. data jsou v InnoDB fyzicky uspořádána podle primárního klíče. Tudíž dotazy na primární klíč jsou velice rychlé a efektivní, za cenu o něco pomalejšího INSERTu. UPDATE PK v InnoDB je náročná operace.
InnoDB nabízí všechny úrovně izolace transakcí - výchozí je REPEATABLE READ - ohledně chování odpovídá PostgreSQL implementaci SERIALIZABLE. Při úrovni SERIALIZABLE dochází k zamykání všech načtených řádků (normální SELECTy se automaticky převádějí na SELECT LOCK IN SHARE MODE) a i k jisté variantě predikátového zamykání (Next-row locking).
Rozdíly mezi Firebirdem a PostgreSQL
Z mého pohledu není engine Firebirdu je docela podobný engine PostgreSQL. Nejmarkantnější rozdíl je neexistence transakčního logu ve Firebirdu. Dalším rozdílem je ukládání tzv. delta verzí. Neukládají se kompletní verze záznamů (jako v PostgreSQL), ale pouze jejich rozdíly (ve Firebirdu). Další rozdíly mi přijdou více-méně kosmetické - ale na rovinu říkám, že do Firebirdu vidím jen okrajově. Firebird odstraňuje staré verze záznamů průběžně při přístupu k řádku. PostgreSQL ve verzi 8.3 odstraňuje staré verze záznamů při provádění příkazu UPDATE (tzv. HOT UPDATE), pokud nedošlo ke změně oindexovaného sloupce. Explicitně lze spustit tuto proceduru v obou databázích - příkaz SWEEP (Firebird), příkaz VACUUM (PostgreSQL).
Ohledně nabídky úrovní izolací transakcí používá Firebird vlastní terminologii - READ COMMITTED, SNAPSHOT, SNAPSHOT TABLE STABILITY. SNAPSHOT odpovídá REPEATABLE READu a SNAPSHOT TABLE STABILITY úrovni SERIALIZABLE. Pozor při úrovni SNAPSHOT TABLE STABILITY dochází k uzamčení pro zápis všech dotčených tabulek. Implicitní úrovní je SNAPSHOT.
Pak, samozřejmě, v samotných příkazech i funkcionalitě jsou celkem zásadní rozdíly. U Firebirdu je možné při nastavení úrovně izolace jednak nastavit způsob řešení konfliktu (WAIT, NOWAIT), dále pak možnost rovnou na začátku transakce uzamknout vybrané tabulky.
Rekapitulace
Po přečtení tohoto článku by si měl čtenář uvědomit:
- proč transakce - viz kritéria ACID - jinak řečeno, ať se děje, co se děje, data v databázi jsou konzistentní,
- co může nastat v případě souběžného přístupu uživatelů do databáze - špinavé čtení, neopakovatelné čtení, výskyt fantomů,
- jaké jsou úrovně izolace transakcí a co v které úrovni může nastat nebo nemůže,
- v některých případech a na některých db systémech je nutné explicitně zamykat, chce-li programátor zabránit chybě souběhu.
Závěr
V tomto článku jsem se snažil ukázat smysl a použití transakcí v databázi. Nejedná se o detailní přehled tématu - spíš jen o lehké nasměrování. Setkávám se se dvěma kategoriemi vývojářů - jedni tvrdí, že o transakcích nemusí nic vědět, neboť jejich databáze je nepodporuje. Druzí naopak, že transakce nemusí řešit, neboť vše řeší za ně databáze. Pravda je jiná, a to taková, že bez znalostí transakcí nelze napsat 100% spolehlivou více uživatelskou aplikaci, a to ať s podporou transakcí v databázi nebo bez podpory transakcí. Zvlášť při vývoji zatížených www aplikací je znalost transakcí nutná - málokteré jiné aplikace vygenerují tolik souběžných přístupů do databáze, a málo kde jinde je tak pravděpodobná chyba souběhu.
Další odkazy: