Úvod do databázových systémů

Úvod do databázových systémů Jakub Lokoč

Literatura • POKORNÝ, J., HALAŠKA, J.: Databázové systémy, skripta FEL ČVUT 2003 • HALAŠKA, J., POKORNÝ, J.: Databázové systémy-cvika, skripta ČVUT 2002 • Ramakrishnan, Gehrke: DatabaseSystems Management, McGraw-Hill, 2003 • Další zdroje - web • http://nb.vse.cz/~palovska/uds/ • http://siret.ms.mff.cuni.cz/skopal/DBI025.htm Upozornění – informace v této prezentaci nejsou vyčerpávající !!! Databázové systémy, Jakub Lokoč

Relační algebra(RA) • Deklarativní dotazovací jazyk (neprocedurální) • Výsledkem je vždy konečná relace • Základní operace relační algebry (ZORA) • “<aiaj>” – přejmenování atributu (konflikty jmen, spojení tabulek, …) • “[a1, .., an]” – projekce záhlaví jen na vybrané atributy a1, .., an • “(logická podmínka)” - selekce řádků jejichž atributy splňují logickou podmínku • “R1xR2x …” - kartézský součin více tabulek • “R1–R2“ - rozdíl řádků tabulek se stejným záhlavím • “R1R2” - sjednocení řádků tabulek se stejným záhlavím • “R1R2” - průnik řádků tabulek se stejným záhlavím • Operace jsou uvedeny v pořadí jejich priorit • Jejich kombinací získáváme odvozené operace relační algebry (OORA) Databázové systémy, Jakub Lokoč

Relační algebra(RA) • Kartézský součin jako jediný rozšiřuje schéma nové tabulky • Jejich kombinací lze formulovat velice pestrou škálu dotazů • Při konfliktu jmen budeme pro jednodušší vyjádření pomocí ZORA přejmenovávat automaticky Databázové systémy, Jakub Lokoč

ZORA – [], (), , , – Dům Jaké jsou barvy všech domů? Dům[Barva] Jaké jsou barvy domů na Estonské? Dům(Adresa=‘Estonská’)[Barva] Jaké jsou barvy domů na Estonské a Vltavské? Dům(Adresa=‘Estonská’ v Adresa=‘Vltavská’)[Barva] Dům(Adresa=‘Estonská’)[Barva]  Dům(Adresa=‘Vltavská’)[Barva] Jaké barvy domů mají společné Estonská a Vltavská? Dům(Adresa=‘Estonská’)[Barva]Dům(Adresa=‘Vltavská’)[Barva] Jaké barvy domů na Estonské chybí? Dům[Barva] - Dům(Adresa=‘Estonská’)[Barva] Databázové systémy, Jakub Lokoč

ZORA – kartézský součin • Základní matematická operace nad množinami • Pozor na konflikty jmen (přejmenování, prefixy) • Většinou kombinovaný se selekcí a projekcí • Pomocí něj se odvozují všechny operace spojení • Značíme R1x R2 R1 R2 Databázové systémy, Jakub Lokoč

OORA – přirozené spojení • „Intuitivní“ propojení tabulek přes společné atributy • Nechť As = A1 (A2– A1) a Ap = A1 A2 • R1 x R2(R1.a = R2.a pro všechna a z Ap)[As] • Značíme R1 * R2 • Pokud je Ap prázdná, je výsledkem kartézský součin R1 R2 Databázové systémy, Jakub Lokoč

OORA – vnitřní spojení • Přirozené spojení spojuje přes predikát rovnosti společných atributů • Ten je ale jen speciálním případem libovolného predikátu, který určuje podmínku spojení • R1 x R2 (t1t2) [A1A2] • Značíme jako R1[t1t2]R2 R1 R2 R1[Číslo < R & Adresa=“Vltavská”]R2 Databázové systémy, Jakub Lokoč

OORA – levé vnitřní spojení • Ve spojení nás zajímají jen ty řádky z první tabulky, které se „spárovaly“ s nějakými řádky z druhé tabulky • Neboli (R1[t1t2]R2)[A1] • Značíme R1<t1t2]R2 • Pravé vnitřní spojení se definuje analogicky R1 R2 (R1[Číslo< R & Adresa=“Vltavská”]R2)[Adresa, Číslo, R1.Barva] Databázové systémy, Jakub Lokoč

OORA – vnější spojení • Rozšíření výsledku o řádky, které se „nespárovaly“ • Levé vnější spojení R1*LR2 = R1* R2R1 x (NULL, …) • Pravé vnější spojení R1*PR2 = R1* R2R2 x (NULL, …) • R1 a R2 jsou nespárované řádky, R1 = R1 – (R1* R2)[A1] • Plné vnější spojení R1*FR2 = R1*LR2 R1*PR2 • Existuje i zobecněná varianta spojení přes libovolný predikát R1 PVS R2 LVS Databázové systémy, Jakub Lokoč

OORA - dělení • Pro dotazy typu – „Které entity z R1 obsahují všechny entity z R2?“ • Máme tabulky R1(A1) aR2(A2) • R1[A1 – A2] – (R1[A1 – A2] x R2 – R1)[A1 – A2] • Značíme jakoR3 = R1R2 R1 R2 Adresy ulic, kde jsou všechny barvy z tabulky barev? Databázové systémy, Jakub Lokoč

Vyhodnocování dotazu RA • Operace jsou vyhodnocovány dle jejich priorit (popř. dle závorek) • Postup vyhodnocení lze zachytit tzv.stromem dotazu • Příklad Dům(Adresa=‘Estonská’)[Barva] Dům(Adresa=‘Vltavská’)[Barva] Sjednocení Projekce [Barva] Projekce [Barva] Selekce (Adresa=‘Estonská’) Selekce (Adresa=‘Vltavská’) Dům Dům Databázové systémy, Jakub Lokoč

Optimalizace dotazu v RA • Dotaz je možné často přepsat aniž by se změnil jeho výsledek – ekvivalentnívýraz • Přeskládání dotazu je v SŘBD úkolem optimalizátorů • Ty se snaží mimo jiné zajistit, aby se projekce a selekce prováděly co nejdříve • Dotaz lze také optimalizovat předpočítáním často používaných tabulek Databázové systémy, Jakub Lokoč

Relační úplnost • Né všechny operace jsou nezbytně nutné • Která se dá odebrat? • Dotazovací jazyk, kterým lze vyjádřit všechny konstrukce relační algebry se nazývá relačně úplný Databázové systémy, Jakub Lokoč

Relační kalkul • Využití predikátové logiky 1. řádu pro formulaci dotazů • Různé úrovně práce s daty • Doménový kalkul – atributy • N-ticový kalkul – celé řádky • Výsledkem dotazu je opět relace • Popisujeme CO chceme získat z DB Databázové systémy, Jakub Lokoč

Relační kalkul - příklady • Dům(Adresa, Číslo, Barva) • Čísla a barvy domů na Estonské • { x, y : Dům(‘Estonská’, x, y) } • { n[Číslo, Barva] : Dům(n) & n.Adresa = ‘Estonská’} • Co ale vrátí následující dotaz? • { x : Dům(‘Estonská’, 25, x) } • Může být výsledkem ‘abc’? ‘zlatá’? Databázové systémy, Jakub Lokoč

Relační kalkul – bezpečné výrazy • Předchozí příklad představuje výraz, který není bezpečný • Výraz je bezpečný, pokud všechny hodnoty výsledku jsou vytvořeny z n-tic v databázi • Relační kalkul omezený na bezpečné výrazy je ekvivalentní relační algebře Databázové systémy, Jakub Lokoč

Rekapitulace • Představili jsme si dva formální dotazovací jazyky • Relační algebru • Relační kalkul (doménový, n-ticový) • Pomocí nich lze konstruovat dotazy nad definovaným datovým schématem relační databáze • Prvky obou jazyků tvoří kostru jazyka SQL, který je rozšířen o další konstrukce (funkce, agregace, atp…) Databázové systémy, Jakub Lokoč

SQL – Group by • Podpora seskupovacích dotazů v SQL, výsledkem opět tabulka! • Nezajímají nás detaily, ale spíš souhrny s agregacemi • Lze zapsat i bez použití Group by (nevýhody?) • Příklad dat - detaily vs. souhrny Databázové systémy, Jakub Lokoč

SQL – seskupení bez Group by Select ‘Estonská’As Ulice, Min(Cena) From Dům Where Ulice=‘Estonská’  Select ‘Vltavská’As Ulice, Min(Cena) From Dům Where Ulice=‘Vltavská’  Select ‘Francouzská’As Ulice, Min(Cena) From Dům Where Ulice=‘Francouzská’ Musí se 3 x projít tabulka dům!!! Navíc ani dotaz není nejjednodušší… Databázové systémy, Jakub Lokoč

SQL – Group by • Select Ulice, Min(Cena) From Dům Group by Ulice • Pro každou hodnotu atributu Ulice jeden superřádek • V dotazu s Group by se mohou používat ze seskupované tabulky pouze • Atributy přes které se dělá Group by • Agregační funkce nad zbývajícímí atributy Špatně zapsaný dotaz: Select Cena From Dům Group by Ulice Bez funkce agragace by nebylo zřejmé, kterou cenu vybrat (pro každou ulici je ve výsledku jen jeden řádek)! Databázové systémy, Jakub Lokoč

SQL – Group by / Having • Having slouží pro filtrování výsledné tabulky superřádků • Podmínka nemůže obsahovat samostatné atributy ze seskupované tabulky, které nejsou uvedeny za Group by (ale jejich agregace ano!) Select Ulice, Min(Cena) From Dům Group by Ulice Having Min(Cena) <= 2000000 Databázové systémy, Jakub Lokoč

Vnořené dotazy • Volání Selectu v Selectu • Může vrátit jednu nebo i více hodnot • Dvě možnosti vnoření dotazu • V klauzuli Where + operace In, Exists, All, Any • V klauzuli From (popř. i join s jinou tabulkou) • Chování a efektivita vnořených dotazů • Opakující se mezivýsledky • Výskyty NULL v odpovědi • atribut IN (NULL, …, NULL) – nelze rozhodnout • EXISTS (NULL, …, NULL) – vrací TRUE Databázové systémy, Jakub Lokoč

Vnořené dotazy – IN a EXISTS Select* From Dům D1Where Cena IN (Select Cena From Dům D2WhereD2.Ulice = ‘Estonská’) Select* From Dům D1WhereEXISTS (Select Cena From Dům D2WhereD2.Ulice = ‘Vltavská’ & D2.Cena > D1.Cena) Databázové systémy, Jakub Lokoč

Vnořené dotazy – ALL a ANY Select* From Dům Where Cena < ALL (Select Cena From Dům Where Ulice = ‘Estonská’) Select* From Dům Where Cena < ANY (Select Cena From Dům Where Ulice = ‘Vltavská’) Databázové systémy, Jakub Lokoč

Transakce • Podobně jako u jiných oborů se jedná o sadu operací, u které předpokládáme nějaké garantované chování • Ty databázové charakterizují 4 vlastnosti • Atomicity – buď proběhne vše nebo nic • Consistency – zachování konzistentního stavu DB • Isolation – transakce běží nezávisle na sobě • Durability – po potvrzení se určitě zapíše do DB • Podporovány všemi známějšími SŘBD • Pořadí jejich spouštění a vykonávání zajišťuje v SŘBD tzv. rozvrhovač transakcí Databázové systémy, Jakub Lokoč

Transakce • Proč transakce? • Netriviální operace na které nestačí základní SQL operace • V průběhu vykonávání může být DB chvíli v nekonzistentním stavu • Běh v konkurenčním prostředí (více transakcí současně) • Příkladem může být třeba transakce pro převod peněz mezi účty Úhrada(částka, zÚčtu, naÚčet) SELECT zůstatek INTO z FROM Účet WHERE čÚčtu = zÚčtu If (z < částka) ZrušTransakci(„Nedostatek peněz na účtu!“) UPDATE Účet SET zůstatek = zůstatek – částka WHERE čÚčtu = zÚčtu UPDATE Účet SET zůstatek = zůstatek + částka WHERE čÚčtu = naÚčet Potvrdit transakci READ(zU) WRITE(zU) WRITE(naU) Databázové systémy, Jakub Lokoč

Transakce • Základní operace v transakci • READ(X) – načtení objektu X z DB • WRITE(X) – zapsání objektu X do DB (pozor na cache!) • COMMIT – potvrzení změn a ukončení transakce • ABORT (ROLLBACK) – stornování změn a ukončení transakce • Transakci lze chápat jako DB program • Operace s DB (Read / Write) + další operace (aritmetické, pomocné, atp…) • Problémy způsobují R/W operace nad stejnými daty, na ty se budeme zaměřovat • Transakce - posloupnost R/W operací zakončená COMMIT nebo ABORT • Např. Úhrada(částka, zU, naU) = { READ(zU), WRITE(zU), WRITE(naU), COMMIT} • Dále budeme operace transakce zapisovat do sloupce Databázové systémy, Jakub Lokoč

Transakce - vykonávání • Uvažujme prostředí ve kterém může být současně spuštěno více transakcí (zatím - každá končí příkazem COMMIT, statické DB) • Sériové vykonávání • Dokud není obsloužena aktuálně zpracovávaná transakce, ostatní čekají • Nemusí se řešit konflikty u společných DB objektů - konzistence • Během R/W operací je procesor nevytížen • Paralelní vykonávání • všechny transakce jsou průběžně obsluhovány (tj. jedna čte data z DB, druhá může provádět výpočty nad již dotaženými daty) • Jedna velká „neodstřelí“ server - odezva • Musí se řešit konflikty nad společnými DB objekty (Isolation) • Požadované chování SŘBD – paralelní vykonávání transakcí, kdy výsledný stav DB je stejný jako při sériovém vykonávání Databázové systémy, Jakub Lokoč

Transakce - rozvrh • Rozvrh je posloupnost operací které vykonávají průběžně běžící transakce, operace jsou libovolně prokládány • S = {T1.R(X), T1.W(Y), T2.R(Y), T3.R(X), T1.R(Z), …} • Nedá se naplánovat dopředu (rozvrhovač neví, které transakce dorazí ke zpracování, dokonce ani nezná dopředu jednotlivé kroky vykonávaných transakcí) • Tzn. rozvrh není vytvořen rozvrhovačem předem, rozvrh je jen historie provedených operací • Rozvrhovač může jen garantovat vlastnosti rozvrhu pomocí omezení, které klade na spouštěné transakce (např. pustí transakci jakmile má potřebné zámky) Databázové systémy, Jakub Lokoč

Transakce – rozvrh – příklad Pokud by rozvrhovač pouštěl transakce ke slovu úplně náhodně, tak by mohl vzniknout pro T1, T2, T3 např. následující rozvrh. Nicméně, paralelní provedení zde nevede k očekávanému stavu DB. U libovolného sériového rozvrhu pro T1, T2, T3 bude k A přičteno celkem Po vykonání tohoto rozvrhu bude hodnota A větší pouze o 5. Problém je v R/W konfliktech nad společnými daty. Možné konfliktní dvojice: Databázové systémy, Jakub Lokoč

Uspořádatelnost • Rozvrh nazveme uspořádatelný, jestliže jeho vykonání vede ke stejnému stavu, jaký obdržíme vykonáním nějakého sériového rozvrhu na stejných transakcích • Velice silná vlastnost! Zaručuje izolaci a konzistenci (pokud jsou konzistentní T) • Nicméně prokládání transakcí může vést ke konfliktům, které mohou porušit uspořádatelnost, tyto konflikty potenciálně způsobují tzv. konfliktní dvojice • Read / Write • Write / Read • Write / Write Databázové systémy, Jakub Lokoč

(Ne)konfliktní dvojice R/R • Jako jediná nemá vliv na konzistenci DB • Pouze čtení společných dat • Příklad Předpoklad  A = 0 Sériové vykonání S(T1, T2)  A = 0, B = 10, C = 5 S(T2,T1)  A = 0, B = 10, C = 5 Paralelní vykonání P  A = 0, B = 10, C = 5 Databázové systémy, Jakub Lokoč

Konfliktní dvojice R/W • Neopakovatelné čtení (nonrepeatableread) • Transakce T2 zapíše objekt A, který předtím přečetla zatím nepotvrzená T1 • Příklad, který vede ke konfliktu Předpoklad  A = 0 Sériové vykonání S(T1, T2)  A = 10, B = 15, C = 15 S(T2,T1)  A = 15, B = 25, C = 15 Paralelní vykonání P  A = 5, B = 15, C = 15 Databázové systémy, Jakub Lokoč

Konfliktní dvojice W/R • Čtení nepotvrzených dat (dirty read) • T2 čte hodnotu A, kterou T1 zatím nepotvrdila, tj. mohou se číst nekonzistentní data • Příklad, který vede ke konfliktu Předpoklad  A = 1000, B = 1000 Sériové vykonání S(T1, T2)  A = 0, B = 2020 S(T2,T1)  A = 10, B = 2010 Paralelní vykonání P  A = 0, B = 2010 Databázové systémy, Jakub Lokoč

Konfliktní dvojice W/W • Přepsání nepotvrzených dat • T2 přepíše hodnotu B, kterou předtím zapsala stále běžící T1 • Příklad, který vede ke konfliktu Předpoklad  A = 0, B = 0 Sériové vykonání S(T1, T2)  A = 20, B = 20 S(T2,T1)  A = 10, B = 10 Paralelní vykonání P  A = 10, B = 20 Databázové systémy, Jakub Lokoč

Konfliktová uspořádatelnost • Dva rozvrhy nad stejnými transakcemi jsou konfliktově ekvivalentní, když mají stejné konfliktní dvojice (ve stejném pořadí nad stejnými objekty) • Rozvrh je konfliktově uspořádatelný, pokud je konfliktově ekvivalentní k nějakému sériovému rozvrhu (v něm nejsou konflikty) • Konfliktová uspořádatelnost se dá detekovat pomocí precedenčního grafu (nesmí obsahovat cykly – ty představují konflikty) RW T2 T1 WW Databázové systémy, Jakub Lokoč

Konfliktová uspořádatelnost • Přísnější než uspořádatelnost (zachování konzistence) • Eliminuje rozvrhy s konflikty RW, WR, WW • Nicméně, zatím jsme uvažovali jen zjednodušenou DB • Co když ale akce neskončí pomocí COMMIT? • Rozvrh nemusí být zotavitelný • Co když nemáme statickou DB? • Může se vyskytnout fantom Databázové systémy, Jakub Lokoč

Zotavitelný rozvrh • Pokud transakce T zapisuje data a jiná transakce R je čte a dále s nimi pracuje, tak musí R provést COMMIT až po T • Jinak by v případě ABORT u T byla R již potvrzena (R četla nekonzistentní data!!!) • Rozvrh může být také zabezpečen proti kaskádovému rušenítj. čtou se změny pouze potvrzených transakcí • Jak jde vidět – čím výhodnější rozvrh, tím méně možností prokládání • Existuje ještě pohledová ekvivalence a uspořádatelnost, její testování je ale NP-úplný problém, nahrazuje se tedy v praxi konfliktově uspořádatelnými a zotavitelnými rozvrhy Databázové systémy, Jakub Lokoč

Zotavitelný rozvrh Který z následujících rozvrhů je zotavitelný? Jak by musel rozvrh vypadat, aby byl zabezpečen proti kaskádovému rušení? Databázové systémy, Jakub Lokoč

Rekapitulace • V konkurenčním prostředí můžou vznikat pro více běžících transakcí libovolné rozvrhy, ty mohou vést k nekonzistentní DB • Rozvrhy mohou mít specifické vlastnosti, zajišťující míru izolace • Sériový • Uspořádatelný • Konfliktově uspořádatelný • Zotavitelný • Jak ale zajistit, abychom získali rozvrh s požadovanými vlastnostmi? • Rozvrhovač transakcí musí dodržovat určitá pravidla během „pouštění“ transakcí – tzv. protokol Databázové systémy, Jakub Lokoč

Protokoly • Sada pravidel zajišťující požadované vlastnosti • Paralelizace (odezva, výkon) • Uspořádatelnost (izolace, konzistence) • Zotavitelnost (konzistence) • Uzamykací protokoly • Uzamykání DB entit (řádky, tabulky, …) • Detekce uváznutí • Fantom • Alternativní protokoly • Optimistické řízení – málo konfliktů • Časová razítka Databázové systémy, Jakub Lokoč

Uzamykání entit • Nástroj pro zajištění konfliktové uspořádatelnosti (zámky určují pevné pořadí vykonávání) • Exkluzivní X(A) a sdílené zámky S(A) • Pokud není zámek k dispozici, transakce čeká (spí) • Uzamykání je uživateli skryto, používá je rozvrhovač (zámky v rozvrhu – historie přidělení zámků rozvrhovačem) • O přidělování zámků transakcím se stará správce zámků • Zamykání a odemykání musí být atomické operace Databázové systémy, Jakub Lokoč

Dvoufázový uzamykací protokol (2PL) • 2PL uplatňuje při sestavování rozvrhu • Nejdříve se musí uzamknout objekt, než se s ním pracuje (čtení, modifikace) • Transakce nepožaduje zámek, pokud již nějaký uvolnila (tj. dvě fáze zamykání a odemykání) • Důsledek pravidel • Precedenční graf je acyklický • Negarantuje však zotavitelnost • Zotavitelnost garantuje až striktní 2PL • Upravuje druhou podmínku – zámky jsou uvolněny až při ukončení transakce • Garantuje také zabezpečení proti kaskádovému rušení transakcí Databázové systémy, Jakub Lokoč

Uváznutí (deadlock) • Transakce T žádá prostředky, které drží jiná transakce R. Ta požaduje zase prostředky, které drží transakce T. Obě jsou tudíž zablokovány. • Může nastat i při S2PL • Jak se vypořádat s uváznutím • Detekce a vyřešení • Waits-for graf, transakce dlouho nic nedělá, … • Restartovat transakci dle nějakého kritéria • Prevence • Prioritní upřednostňování (časové razítko určuje prioritu) • Konzervativní 2PL protokol (všechny zámky už na začátku) Databázové systémy, Jakub Lokoč

Fantom • Výskyt v dynamických DB (vkládání, mazání, …) • Jedna transakce pracuje s množinou entit a druhá ji mění  nekonzistence DB • Prevence – zamknout vše co se dá nebo pomocí indexu hlídat zamčenou oblast • V příkladu rozvrh s fantomem (mají být zamčeni všichni muži) Sériové vykonání S(T1, T2)  A = 24, B = 20 S(T2,T1)  A = 22, B = 22 Paralelní vykonání P  A = 24, B = 22 Databázové systémy, Jakub Lokoč

Úrovně izolace v SQL 92 WR = Čtení nepotvrzených dat (dirty read) RW = Neopakovatelné čtení (nonrepeatable read) Databázové systémy, Jakub Lokoč

Alternativní protokoly • Optimistické řízení • Konflikty jen zřídka, zamykání zbytečně zatěžuje systém • Read/Validation/Write protokol • Data jsou čtena z DB • Transakce pracuje v lokálním datovém prostoru • Před potvrzením SŘBD rozhodne jestli nedošlo ke konfliktu • Pokud ano, tak se transakce restartuje, jinak zápis do DB • Časová razítka • Každá transakce dostane na začátku časové razítko TS(Ti) • Pokud dojde ke konfliktu akcí T1.A1 a T2.A2, pak musí platit že TS(T1) < TS(T2) • Kvůli zotavitelnosti se musí ukládat informace o všech zápisech (dokud transakce nepotvrdí) Databázové systémy, Jakub Lokoč

Zotavení po havárii systému • Správce zotavení musí zajistit • Atomicitu – v případě zrušení transakce • Trvanlivost – potvrzené transakce v DB i po havárii (pokud se data nestihla zapsat na disk) • ARIES – algoritmus pro zotavení • Analýza modifikovaných, ale nezapsaných stránek, aktivní transakce v okamžiku havárie • Zopakování transakcí od určitého příslušného místa v logu • Odvolání akcí těch transakcí, které nestačily potvrdit • Principy ARIES • Write-Ahead logging - každá změna DB do logu • Repeating history during redo - znovu provede akce z logu • Logging changes during undo – rušení transakce se také loguje Databázové systémy, Jakub Lokoč

Cvičení 1 • IS dopravního podniku • Evidence řidičů, jízd a typy DP na které majílicenci • Kdy a kde provádělkontrolurevizor • Koho chytil (ČP) a jakou mu udělilpokutu • Seznam zastávek a linky které jimi projíždí • Jízdní řád s časy odjezdů linek ze zastávek • Které DPzajišťují které linky • Pasažéři, kteří mají koupenoulegitku • Typy dotazů • Kdo byl chycen revizorem a přitom má legitku (nechal doma)? • Jak se dostanu ze stanice A do B s maximálně dvěma přestupy? • Jak dlouho jede linka ze stanice A do B? Databázové systémy, Jakub Lokoč

Úvod do databázových systémů