Informationssystem och databasteknik 2I1100

1. Informationssystem och databasteknik 2I1100 Relationsdatabashanteringssystem RDBHS Elmasri Navathe kapitel 18-22

2. • lagring, återvinning och uppdatering av data • behörighetskontroller • kontrollerad hantering av data för datakonsekvens • återskapande av data efter fel av olika slag • transaktionshantering • samtidig bearbetning av data utan att data förvanskas • effektivisera den interna hanteringen av data Relationsdatabashanteringssystem Ett antal program för att möjliggöra

3. Optimization kap 18 Concurrency kap 19-20 Recovery kap 21 Security kap 22 Integrity (kap 7) RDBHS

4. QUERY OPTIMIZATION optimerar utsökning av information ur en databas I de traditionella databasmodellerna, hierarkiska och nätverk, måste detta överlåtas åt applikations- programmeraren. Relationsdatabashanteringssystemet har inbyggda s.k. optimizers! OPTIMERING

5. • väljer väg • har tillgång till mer info • kan testa fler varianter • kan utnyttja kunskap som DBA, system prog. och forskare byggt in Optimeraren Styrka att kunna optimera! (DBMS konkurrensmedel)

6. Uppgift för optimeraren • Minimera total accesskostnad för SQL-kommandon • Bestämmer hur utsökning skall genomföras • Bestämmer vilka index som skall användas • Undviker eller minimerar sorteringar • Bestämmer Join-strategi

7. Så vad är en optimerare ? • Expertsystem, med kunskaper som forskare byggt in • ”Svart låda” (DBMS konkurrensmedel) • Optimeraren har tillgång till mer info än användaren • Tabellstorlek, lagringsstruktur och index för tabellerna • Statistik för tabellinnehållet • defaultvärden • framställd statistik (update statistics)

8. Frågetransformering SQL-fråga Relationsalgebra Optimering Syntaxkontroll Scanner resp parser Optimerad Relationsalgebra Syntaktisk korrekt SQL-fråga Strategival Validering Exekveringsplan Giltig SQL-fråga Interpreterad Kompilerad Kodgenerering Översättning Query tree eller Query graph Kod

9. 1. Sekvensiell genomsökning (scanning) 2. Binärsökning 3. Hashad nyckel eller primärindex (= PK) för enstaka rad 4. Primärindex för flera rader (> PK eller < PK) 5. Klustrat index för flera rader (> attr med klustr index) 6. B*-index som sekundärt index 7. Sökning med sammansatta index 8. Snitt av index (matchning av postpekare) Sökstrategier

10. Conjungtive / Disjunctive condition Konjungtivt villkor är flera villkor sammansatta med OCH. Då väljes i första hand det mest selektiva villkoret först Disjunktivt villkor är flera villkor sammansatta med ELLER. Kräver för det mesta genomgång av en tabell flera gånger

11. 1. Nested loop 2. Använda index eller hashnyckel 3. Sort-merge join 4. Hash-join JOIN-strategier!

12. Alt 1: Alla rader i R1 jämföres med alla rader i R2: 4 + 4 * 8 = 36 rader läses Alt 2: Alla rader i R2 jämföres med alla rader i R1: 8 + 8 * 4 = 40 rader läses Nested loop R1 R2 A B C D 1 C 3 D 4 C 6 B 7 A 9 A 12 C 14 B Slutsats: Välj den minsta tabellen som "yttre loop". OBS: I verkligheten är "filerna" förstås blockade

13. Använda index eller hashnyckel Effektiv när det gäller att göra join på två tabeller när man söker ett fåtal värden. (Matchning PK - FK) Den ena tabellen läses i sin helhet och joinattributet (ofta Främmande nyckeln) användes för att läsa den andra tabellens rader en i taget med hjälp av index eller hashing. R2 R1 1 C 3 D 4 C 6 B 7 A 9 A 12 C 14 B A B C D R1 läses radvis, och för varje rad användes R2´s primärindex för att läsa motsvarande rad i R2. Resultatet hamnar i en resultat-buffert i primärminnet

14. En mycket effektiv metod om hög "träffgrad" väntas. Inledande sorteringar + sort-merge realistiskt alternativ Sort-Merge join Alla ingående tabeller måste vara sorterade på samma attribut

15. Hash JOIN Båda tabellernas rader läggs in i samma minnesbuffert med hjälp av en hashalgoritm som appliceras på join-attributen. Tabellernas rader kan vara i godtycklig ordning. Fördel att båda tabellerna bara läses en gång. Om mellanresultatet blir så stort att det inte ryms i PM är metoden ej effektiv.

16. Aggregatfunktioner och Index MIN MAX COUNT AVG SUM Om det finns ett index på attributet som skall beräknas så behöver förstås inte några enskilda rader accessas utan det räcker med att läsa index

17. Tag fram namn och adress för alla kunder som beställt bokhyllan POMPE (artnr 105436-8) Heuristisk optimeringoptimerad relationsalgebra  kundnamn, kundadress  artnr = 105436-8  |X| kundnrnr KUND |X| ordernr ORDER ORDERRAD

18. Tag fram namn och adress för alla kunder som beställt bokhyllan POMPE (artnr 105436-8) Optimerat träd  kundnamn, kundadress |X| kundnrnr  Kundnr, kundnamn, kundadress |X| ordernr KUND  Ordernr, kundnr  Ordernr  artnr = 105436-8 ORDER ORDERRAD

19. 1. Gör SELECT så tidigt som möjligt! Utnyttja "lagarna" för att föra selektionen så långt ned i trädet som möjligt 2. Gör PROJECT tidigt! Utnyttja "lagarna" för att föra projektionen så långt ned i trädet som möjligt 3. Minimera mellanresultat! Använd associativa lagen så att den selection som ger minsta mellanresultatet utföres först 4. Gör JOIN först när det är nödvändigt Tumregler

20. 1. Associativa lagen (R * S) * T = R * (S * T) 2. Kommutativa lagen R * S = S * R 3. Distributiva lagen (Cascades) u(R * S) = u(R) * u(S) Läroboken visar de olika lagarna med exempel Relationsalgebraiska lagar

21. • Access-kostnad till sekundärminne - typ av access-struktur index klustrade index • Lagringskostnad för mellanresultat • Bearbetningskostnad sortering, sökning, join, beräkningar Vad kostar en fråga? Optimeringen görs inte enbart med hänsyn till heuristiska regler. Kostnaden uppskattas för olika alternativ så att den billigaste frågestrategin kan väljas.

22. Antal rader per tabell (kardinalitet) Radlängd Antal olika värden i en kolumn (selektivitet) Max/Min-värden inom en kolumn Statistik Optimeraren måste ha statistikuppgifter för att kunna sköta jobbet! DATA DICTIONARY (Systemkatalogen) av avgörande betydelse för optimeraren

23. Internt: allt 'jobb' som en användares atomära transaktion genererar Exempel Flytta pengar från konto A till konto B Transaktionshantering Vad är en transaktion? Läs konto A, finns pengar?, subtrahera belopp, skriv A, läs B,addera belopp, skriv B DBMS-krav: Allt eller inget!

24. Före transaktionens start är databasen i ”consistent state” Under transaktionen är databasen i ”inconsistent state” Transaktionen kan avslutas på två sätt: COMMIT eller ABORT COMMIT för databasen till ett nytt konsistent läge ABORT återställer databasen till läget före BEGIN TRANSACTION Databastransaktion - Logical Unit of Work

25. A Atomicity (transaktionen är atomär) C Concistency (koncistensen skall vidmakthållas) I Isolation. Varje transaktion verkar köras isolerad D Duration. Oavsett om olika fel inträffar skall resultatet av en rätt utförd transaktion bestå*. A C I D Krav på DBMS * Kan dock ändras med kompensationstransaktion

26. Var finns prestandaproblemen? • Design 45% • - Datamodellering, Tabeller, Lagringsstruktur, Index • Applikation 40% • -Transaktionshantering, SQL, Partitionering • Konfiguration 10% • - Låshantering, Transaktionslogg • Operativsystem 5% • - Disk-layout, systemparametrar, minneshantering

27. Parallellitetsstyrning, handlar om att skydda data i databasen från skadlig påverkan av interfolierande (samtidiga) transaktioner. Löses med hjälp av lås. Concurrency

28. Den förlorade uppdateringen Trans A tid Trans B Läs Tal = 20 = 20 Läs Tal Add 50 = 70 = 0 Sub 20 Skriv Tal = 70 = 0 Skriv Tal Värdet i databasen för Tal = 0. Korrekt värde skall vara 50! (20 +50 -20)

29. Beroende till en backad trans Trans A tid Trans B = 20 Läs Tal = 70 Add 50 Läs Tal = 70 = 20 ROLLBACK Trans A ser data som "aldrig existerat"! (s.k. ”dirty data”)

30. Beroende till en backad trans Trans A tid Trans B = 20 Läs Tal = 70 Add 50 Läs Tal = 70 Add 20 = 90 = 20 ROLLBACK Trans A opererar på data som "aldrig existerat"!

31. Läslås Shared Lock (PS) Shared lock sättes på ett objekt som skall läsas. Andra transaktioner tillåts att läsa objektet Skrivlås Exclusive Lock (PX) Exclusive lock sättes på ett objekt som skall skrivas. Andra transaktioner får ej tillgång till objektet Locking protocol

32. Läslås Shared Lock Shared lock sättes på ett objekt som skall läsas. Andra transaktioner tillåts att läsa objektet Skrivlås Exclusive Lock Exclusive lock sättes på ett objekt som skall skrivas. Andra transaktioner får ej tillgång till objektet Locking protocol

33. Scheduler producerar exekveringsplan Scheduler ställer upp en Conflct-graph (konflikt-graf, precedensgraf) T2 T1 T4 T3 Seriell ordning: T1 - T3 - T2 - T4

34. Alla transaktioner följer följande regler: I. Innan den opererar på något objekt sätter den ett lås på objektet II. Efter att ha släppt ett lås begär den aldrig några nya lås Two-Phase Locking (2PL) Detta medför att alla interfolierade exekveringar av sådana transaktioner är serialiserbara

35. Def: En given interfolierad exekvering av ett antal transaktioner är serialiserbara om och endast om den producerar samma resultat som en seriell exekvering av samma transaktioner Korrekthetsvillkor: En given interfolierad exekvering av ett antal transaktioner är korrekt om den är serialiserbar Serialiserbarhet Varje transaktion är korrekt i sig Transaktionerna är logiskt oberoende av varandra

36. De två faserna är: - en växande fas, där låsen begäres - en krympande fas, där låsen släpps Two-Phase Locking antal lås transaktionens tid Flera varianter av 2PL finns. De vanligaste är basic 2PL (ovan), Conservative 2PLsom sätter alla sina lås samtidigt och Strict 2PL som släpper alla sina skrivlås samtidigt efter commit.Rigorous 2PL håller samtliga lås tills commit.

37. Deadlock Ett system som tillämpar låsning riskerar DEADLOCK. Systemet måste ha en rutin för att upptäcka DEADLOCK. I regel går detta till så att systemet har en väntegraf (Wait-for-graph) WFG som man analyserar för att upptäcka om det finns cykler i grafen. Vanligtvis sker analysen antingen när någon begärt ett lås men satts på väntelista eller annars periodiskt T1 T4 T2 T3

38. Lösa deadlock En transaktion utses till "offer" och rullas ut för senare återstart. Man väljer t ex • den yngsta • den som har minst antal lås • den som gjort minst antal uppdateringar • den som har mest kvar Passa "starvation", dvs att samma trans väljs som offer under lång tid. Tid är vanligast p g a rättvisekrav

39. Conflict serializability Två transaktioner är konflikterande om de accessar samma data, och en av dem innehåller en skrivoperation, ocd innehåller de konflikterande operationerna i samma ordning För att upptäcka ”konflikterande serialiserbarhet” så konstrueras precedensgrafer: B A A B Non-Conflicting Conflicting C C

40. A schedule where, for each pair of transactions Ta och Tb, if Tb reads a data item previosly written by Ta, then the commit operation of Ta precedes the commit operation of Tb Recoverable schedule Detta beror förstås på att om Tb skulle göra COMMIT före Ta, och Ta därefter avslutas med ABORT så kan ju inte Tb rullas tillbaka (D= Duration)

41. Låsningsgranularitet Granularitet = storlek på objektet man låser Databas Tabell Tablespace (eller motsvarande) Sida Rad Systemet kan alltid låsa en större enhet än vad som är logiskt nödvändigt Systemet kan alltid hålla låsen längre än vad som är logiskt nödvändigt

42. Level of Isolation 1. "Dirty read" Låt denna process se dirty data 2. Committed read Låt inte denna process se dirty data 3. Cursor stability Låt ingen annan uppdatera min rad som är "current" 4. Repeatable read Låt ingen annan uppdatera någon av de rader jag sett förrän jag är klar

43. Två huvudsakliga metoder Pessimistiska protokoll som antar att konkurrerande uppdateringar sker frekvent. Metod för att hantera konkurrensen innefattar ofta tidsstämpling Optimistiska protokoll som utgår ifrån att konflikter är sällsynta

44. Time-stamping • • varje transaktion stämplas. • • varje dataelement (sida) har två stämplar, en läs- och en skrivtid. (När sidan senast lästes eller skrevs) • • konflikt uppstår när en transaktion vill • - se en post som en yngre trans uppdaterat • - uppdatera en post som redan setts eller uppdaterats av en yngre trans • Lösning: • Återstarta den begärande transaktionen

45. Undvika deadlock En transaktion Ta försöker låsa X som är låst av Tb • wait-die: Om Ta är äldre än Tb så får Tavänta , annars så backas Ta ut och startas om senare med oförändrad tidsstämpel wound-wait: Om Ta är äldre än Tb så backas Tb ut och startas om senare med oförändrad tidsstämpel

46. Optimistisk metod En transaktion begär att en datasida läses in i UWA (User Working Area) Uppdaterande data sparas också i UWA. Uppdatering sker i en lokal kopia i UWA Lås begäres på sidan Sidan läses in och kontrolleras mot sidan i UWA Om den ser likadan ut så har ingen ”mellankommande” transaktion uppdaterat sidan och den lokala kopian kan skrivas till databasen Om den är förändrad så gör man om hela proceduren men nu med den uppdaterade sidan som ”original”

47. Timestamp Ordering Timestamp ordering säkerställer att alla konflikterande läs- och skrivoperationer görs i tidsordning (timestamp order) • Antag att Ti begär läs(Q) • Om TS(Ti) < W-timestamp(Q), så betyder det att Ti skulle behöva läsa ett värde på Q som är överskrivet. T gör ROLLBACK! • Om TS(Ti) > W-timestamp(Q) så utföres läsningen och R-timestamp får den högsta timstamp av TS(Ti) och R-timestamp(Q) • Antag att Ti begär skriv(Q) • Om TS(Ti) < R-timestamp(Q), så betyder det att Q har lästs av en annan transaktion . T gör ROLLBACK! • Om TS(Ti) < W-timestamp(Q) så betyder det att Ti vill skriva ett gammalt värde på Q och Ti gör ROLLBACK! • Annars exekveras skrivoperationen och W-timestamp(Q) får den högsta timstamp av TS(Ti) och W-timestamp(Q) T som gör ROLLBACK får en ny tidssämpel och startas om!

48. Intent locking Ett protokoll för INTENT LOCKING medger att sätta INTENT lock på en högre granularitet. IX (intent exclusive) på en tabell betyder att det finns X-lås på t ex en sida eller på rader inom tabeller. Om nödvändigt kommer Intent lock att escalera i granularitet Kompatibilitetsmatris • IS IX S SIX X • 1 1 1 1 0 • 1 1 0 0 0 • 1 0 1 0 0 • 1 0 0 0 0 • 0 0 0 0 0 IS IX S SIX X

49. Recovery handlar om återskapande av data efter olika slags fel. • systemkrasch • mediakrasch • systemfel • programfel • ”DBMS-fel” (t ex deadlock, triggers) DBMS förutsätts stödja olika typer av "loggar" Recovery (Återskapande)

50. Logg Before 2 Back-up (UNDO) 1 3 5 After Databas 4 (REDO) 1 och 2 före bearbetning 3 - 5 efter bearbetning Logg Loggar användes vid rcovery (återskapande) av databasen