Objektorientierte Datenbanken Ralf Möller, FH-Wedel Beim vorigen Mal: Architektur von DB-Systemen Grundlagen der Entity-Relationship-Modellierung Probleme beim Übergang in relationale Modellierung Heute: Anfragesprachen: SQL (kurz) Mehrbenutzerbetrieb und Sperren Transaktionen Anbindung an Programmiersprachen Probleme der relationalen Datenbanktechnologie Übung Ziel: Vertiefung des Vorlesungsstoffes ... ... durch Lösen von Aufgaben ... durch Beantwortung von Fragen Durchführung: Christine Apfel, Katrin Fitz Termin: Mi, 8.00 Uhr Ort: RZ2 Beginn: 16.4.03 Literatur, Details und Zusatzinformationen Präsentationen: http://www.fh-wedel.de/~mo/lectures/oodb-sose-03.html Literatur: QuickTime™ and a TIFF (Uncompress ed) decompress or are needed to s ee this picture. QuickTime™ and a TIFF (Uncomp resse d) de com press or are nee ded to s ee this picture. Weitere Literatur und Danksagung A. Kemper, A. Eickler, Datenbanksysteme: Eine Einführung Diese Vorlesung basiert auf Präsentationsmaterial zu diesem Buch Vom Entwurfs- zum Implementierungsmodell Vorlesungen Professoren Titel SWS Gelesen Von 5001 Grundzüge 4 2137 5041 Ethik 4 2125 PersNr Name 2125 Sokrates C4 226 5043 Erkenntnistheorie 3 2126 2126 Russel C4 232 5049 Mäeutik 2 2125 2127 Kopernikus C3 310 4052 Logik 4 2125 2133 Popper C3 52 5052 Wissenschaftstheorie 3 2126 2134 Augustinus C3 309 5216 Bioethik 2 2126 2136 Curie C4 36 5259 Der Wiener Kreis 2 2133 2137 Kant C4 7 5022 Glaube und Wissen 2 2134 4630 Die 3 Kritiken 4 2137 Professoren Rang Raum VorlNr 1 lesen N Vorlesungen Vorsicht: So geht es NICHT Vorlesungen Professoren Titel SWS 5001 Grundzüge 4 5041 Ethik 4 PersNr Name 2125 Sokrates C4 226 5041 5043 Erkenntnistheorie 3 2125 Sokrates C4 226 5049 5049 Mäeutik 2 2125 Sokrates C4 226 4052 4052 Logik 4 ... ... ... ... ... 5052 Wissenschaftstheorie 3 2134 Augustinus C3 309 5022 5216 Bioethik 2 2136 Curie C4 36 ?? 5259 Der Wiener Kreis 2 5022 Glaube und Wissen 2 4630 Die 3 Kritiken 4 Professoren Rang Raum liest VorlNr 1 lesen N Vorlesungen Anomalien Professoren PersNr Name Rang Raum liest 2125 Sokrates C4 226 5041 2125 Sokrates C4 226 5049 2125 Sokrates C4 226 4052 ... ... ... ... ... 2134 Augustinus C3 309 5022 2136 Curie C4 36 ?? VorlNr 5001 Vorlesungen Titel Grundzüge SWS 4 5041 5043 5049 4052 5052 5216 Ethik Erkenntnistheorie Mäeutik Logik Wissenschaftstheorie Bioethik 4 3 2 4 3 2 5259 Der Wiener Kreis 2 5022 Glaube und Wissen 2 4630 Die 3 Kritiken 4 Update-Anomalie: Was passiert wenn Sokrates umzieht Lösch-Anomalie: Was passiert wenn „Glaube und Wissen“ wegfällt Einfügeanomalie: Curie ist neu und liest noch keine Vorlesungen Relationale Modellierung der Generalisierung Fachgebiet Assistenten is_a Professoren Raum Angestellte PersNr Name Rang Angestellte: {[PersNr, Name]} Professoren: {[PersNr, Rang, Raum]} Assistenten: {[PersNr, Fachgebiet]} Relationale Modellierung schwacher Entitytypen Studenten 1 ablegen N Note Prüfungen PrüfTeil N MatrNr umfassen VorlNr M Vorlesungen N abhalten M PersNr Professoren Prüfungen: {[MatrNr: integer, PrüfTeil: string, Note: integer]} umfassen: {[MatrNr: integer, PrüfTeil: string, VorlNr: integer]} abhalten: {[MatrNr: integer, PrüfTeil: string, PersNr: integer]} Relationale Modellierung schwacher Entitytypen Man beachte, dass in diesem Fall der (global eindeutige) Schlüssel der Relation Prüfung nämlich MatrNr und PrüfTeil als Fremdschlüssel in die Relationen umfassen und abhalten übernommen werden muß. SQL standardisierte - Datendefinitions (DDL)- Datenmanipulations (DML)- Anfrage (Query)-Sprache derzeit aktueller Standard ist SQL 99 objektrelationale Erweiterung Uni-Schema voraussetzen Nachfolger Vorgänger MatrNr N Name Semester Studenten N hören M 1 Assistenten N arbeitenFür N SWS Titel lesen prüfen PersNr Fachgebiet M Vorlesungen N Note Name M VorlNr 1 1 Rang Professoren PersNr Name Raum Studenten Professoren Rang Raum MatrNr Vorlesungen Name Semester 24002 Xenokrates 18 232 25403 Jonas 2127 Kopernikus C3 310 26120 2133 C3 52 26830 2134 Augustinus C3 309 2136 Curie C4 2137 Kant C4 PersNr Name 2125 Sokrates C4 226 2126 Russel C4 Popper VorlNr Titel 12 5001 Grundzüge 4 2137 Fichte 10 5041 Ethik 4 2125 Aristoxenos 8 5043 Erkenntnistheorie 3 2126 27550 Schopenhauer 6 5049 Mäeutik 2 2125 36 28106 3 4052 Logik 4 2125 7 29120 Theophrastos 2 5052 Wissenschaftstheorie 3 2126 29555 2 5216 Bioethik 2 2126 5259 Der Wiener Kreis 2 2133 5022 Glaube und Wissen 2 2134 4630 Die 3 Kritiken 4 2137 voraussetzen Carnap Feuerbach hören Vorgänger Nachfolger SWS gelesenVo n 5041 MatrNr VorlNr 5001 5043 26120 5001 5001 5049 27550 5001 5041 5216 27550 4052 5043 5052 28106 5041 5041 5052 28106 5052 PerslNr Name Fachgebiet Boss 5052 5259 28106 5216 3002 Platon Ideenlehre 2125 28106 5259 3003 Aristoteles Syllogistik 2125 prüfen 29120 5001 3004 Wittgenstein Sprachtheorie 2126 MatrNr VorlNr PersNr Note 29120 5041 3005 Rhetikus Planetenbewegung 2127 28106 5001 2126 1 29120 5049 3006 Newton Keplersche Gesetze 2127 25403 5041 2125 2 29555 5022 3007 Spinoza Gott und Natur 2126 27550 4630 2137 2 25403 5022 5001 Assistenten (Einfache) Datendefinition in SQL Datentypen character (n), char (n) character varying (n), varchar (n) numeric (p,s), integer blob oder raw für sehr große binäre Daten clob für sehr große String-Attribute date für Datumsangaben Anlegen von Tabelle create table Professoren (PersNr integer not null, Name varchar (30) not null Rang character (2) ); Einfache SQL-Anfrage select PersNr, Name from Professoren where Rang= ´C4´; PersNr 2125 Name Sokrates 2126 Russel 2136 2137 Curie Kant Einfache SQL-Anfragen Sortierung select PersNr, Name, Rang from Professoren order by Rang desc, Name asc; PersNr 2136 Name Curie Rang C4 2137 2126 2125 Kant Russel Sokrates C4 C4 C4 2134 2127 2133 Augustinus Kopernikus Popper C3 C3 C3 Duplikateliminierung select distinct Rang from Professoren Rang C3 C4 Studenten Professoren Rang Raum MatrNr Vorlesungen Name Semester 24002 Xenokrates 18 232 25403 Jonas 2127 Kopernikus C3 310 26120 2133 C3 52 26830 2134 Augustinus C3 309 2136 Curie C4 2137 Kant C4 PersNr Name 2125 Sokrates C4 226 2126 Russel C4 Popper VorlNr Titel 12 5001 Grundzüge 4 2137 Fichte 10 5041 Ethik 4 2125 Aristoxenos 8 5043 Erkenntnistheorie 3 2126 27550 Schopenhauer 6 5049 Mäeutik 2 2125 36 28106 3 4052 Logik 4 2125 7 29120 Theophrastos 2 5052 Wissenschaftstheorie 3 2126 29555 2 5216 Bioethik 2 2126 5259 Der Wiener Kreis 2 2133 5022 Glaube und Wissen 2 2134 4630 Die 3 Kritiken 4 2137 voraussetzen Carnap Feuerbach hören Vorgänger Nachfolger SWS gelesenVo n 5041 MatrNr VorlNr 5001 5043 26120 5001 5001 5049 27550 5001 5041 5216 27550 4052 5043 5052 28106 5041 5041 5052 28106 5052 PerslNr Name Fachgebiet Boss 5052 5259 28106 5216 3002 Platon Ideenlehre 2125 28106 5259 3003 Aristoteles Syllogistik 2125 prüfen 29120 5001 3004 Wittgenstein Sprachtheorie 2126 MatrNr VorlNr PersNr Note 29120 5041 3005 Rhetikus Planetenbewegung 2127 28106 5001 2126 1 29120 5049 3006 Newton Keplersche Gesetze 2127 25403 5041 2125 2 29555 5022 3007 Spinoza Gott und Natur 2126 27550 4630 2137 2 25403 5022 5001 Assistenten Anfragen über mehrere Relationen Welcher Professor liest "Mäeutik"? select Name, Titel from Professoren, Vorlesungen where PersNr = gelesenVon and Titel = `Mäeutik‘ ; Name, Titel ( PersNr gelesenVon Titel ' Mäeutik' (Professor en Vorlesunge n)) Anfragen über mehrere Relationen PersNr 2125 2126 2137 Professoren Name Rang Raum Sokrates C4 226 Russel C4 232 Kant C4 7 VorlNr 5001 5041 Vorlesungen Titel SWS Grundzüge 4 Ethik 4 gelesen Von 2137 2125 5049 Mäeutik 2 2125 4630 Die 3 Kritiken 4 2137 Verknüpfung PersNr 2125 Name Sokrates Rang C4 Raum 226 VorlNr 5001 Titel Grundzüge 1225 Sokrates C4 226 5041 Ethik 4 2125 2125 Sokrates C4 226 5049 Mäeutik 2 2125 2126 2126 Russel Russel C4 C4 232 232 5001 5041 Grundzüge Ethik 4 4 2137 2125 2137 Kant C4 7 4630 Die 3 Kritiken 4 2137 Pers Nr Name Auswahl Rang Raum VorlNr 2125 Sokrates C4 226 5049 Projektion Name Titel Sokrates Mäeutik Titel Mäeutik SWS gelesen Von 4 2137 SWS gelesen Von 2 2125 Anfragen über mehrere Relationen Welche Studenten hören welche Vorlesungen? select Name, Titel from Studenten, hören, Vorlesungen where Studenten.MatrNr = hören.MatrNr and hören.VorlNr = Vorlesungen.VorlNr; Alternativ: select s.Name, v.Titel from Studenten s, hören h, Vorlesungen v where s. MatrNr = h. MatrNr and h.VorlNr = v.VorlNr Veränderung am Datenbestand Einfügen von Tupeln insert into hören select MatrNr, VorlNr from Studenten, Vorlesungen where Titel= `Logik‘ ; insert into Studenten (MatrNr, Name) values (28121, `Archimedes‘); MatrNr Studenten Name Semester 29120 29555 28121 Theophrastos Feuerbach Archimedes 2 2 - Null-Wert Veränderungen am Datenbestand Löschen von Tupeln delete Studenten where Semester > 13; Verändern von Tupeln update Studenten set Semester= Semester + 1; Nebenläufigkeit und Transaktionen Der erste Teil dieser Vorlesung (13 Präsentationen) baut auf der Vorlesung "P3" von Bernd Neumann an der Universität Hamburg auf. Für eine Vertiefung des Themas Transaktionen und Sperren (Locks) siehe Kapitel 12 aus: Beispiel Kontoführung Prozeß 1: Umbuchung eines Betrages von Konto A nach Konto B Prozeß 2: Zinsgutschrift für Konto A Umbuchung Zinsgutschrift Möglicher verzahnter Ablauf: read (A, a1) a1 := a1 - 300 write (A, a1) read (B, b1) b1 := b1 + 300 write (B, b1) read (A, a2) a2 := a2 * 1.03 write (A, a2) Umbuchung Zinsgutschrift read (A, a1) a1 := a1 - 300 read (A, a2) a2 := a2 * 1.03 write (A, a2) write (A, a1) read (B, b1) b1 := b1 + 300 write (B, b1) Wo ist die Zinsgutschrift geblieben?? Beispiel Besucherzählung Drehkreuz1: Drehkreuz2: loop { read (Counter, c1) if (c1 ≥ MaxN) lock if (c1 < MaxN) open if enter incr(c1) if leave decr(c1) write (Counter, c1) } loop { read (Counter, c2) if (c2 ≥ MaxN) lock if (c2 < MaxN) open if enter incr(c2) if leave decr(c2) write (Counter, c2) } Verzahnte Ausführung der zwei Prozesse Drehkreuz1 und Drehkreuz2 mit Zugriff auf gemeinsamen Counter kann inkorrekte Besucherzahl ergeben! => Überfüllung, Panik, Katastrophen durch Studium der Nebenläufigkeit vermeiden Mehrbenutzersynchronisation Die nebenläufige Ausführung mehrerer Prozesse auf einem Rechner kann grundsätzlich zu einer besseren Ausnutzung des Prozessors führen, weil Wartezeiten eines Prozesses (z.B. auf ein I/O-Gerät) durch Aktivitäten eines anderen Prozesses ausgefüllt werden können. Zeit unverzahnte Ausführung verzahnte Ausführung Prozesse synchronisieren = partielle zeitliche Ordnung herstellen Mehrbenutzerbetrieb von Datenbanksystemen Um Probleme durch unerwünschte Verzahnung nebenläufiger Zugriffe (s. Beispiel Kontoführung) zu vermeiden, werden atomare Aktionen zu größeren Einheiten geklammert: Transaktionen. Eine Transaktion ist eine Folge von Aktionen (Anweisungen), die ununterbrechbar ausgeführt werden soll. Da Fehler während einer Transaktion auftreten können, muß eine Transaktionsverwaltung dafür sorgen, daß unvollständige Transaktionen ggf. zurückgenommen werden können. Befehle für Transaktionsverwaltung: • begin of transaction (BOT) Beginn der Anweisungsfolge einer Transaktion • commit Einleitung des Endes einer Transaktion, Änderungen der Datenbasis werden festgeschrieben • abort Abbruch der Transaktion, Datenbasis wird in den Zustand vor der Transaktion zurückversetzt Eigenschaften von Transaktionen ACID-Paradigma steht für 4 Eigenschaften: Atomicity (Atomarität) Eine Transaktion wird als unteilbare Einheit behandelt ("alles-oder-nichts"). Consistency (Konsistenz) Eine Transaktion hinterläßt nach (erfolgreicher oder erfolgloser) Beendigung eine konsistente Datenbasis. Isolation Nebenläufig ausgeführte Transaktionen beeinflussen sich nicht gegenseitig. Durability (Dauerhaftigkeit) Eine erfolgreich abgeschlossene Transaktion hat dauerhafte Wirkung auf die Datenbank, auch bei Hardware- und Software-Fehlern. Mehrbenutzerbetrieb in DBsystemen Synchronisation mehrerer nebenläufiger Transaktionen: • Bewahrung der indendierten Semantik einzelner Transaktionen • Protokolle zur Sicherung der Serialisierbarkeit • Sicherung von Rücksetzmöglichkeiten im Falle von Abbrüchen • Vermeidung von Schneeballeffekten beim Rücksetzen • Behandlung von Verklemmungen Synchronisation bei Mehrbenutzerbetrieb Synchronisationsproblem = verzahnte sequentielle Ausführung nebenläufiger Transaktionen, so daß deren Wirkung der intendierten unverzahnten ("seriellen") Hintereinanderausführung der Transaktionen entspricht. Konfliktursache im DB-Kontext ist read und write von zwei Prozessen i und k auf dasselbe Datum A: readi(A) readi(A) writei(A) writei(A) readk(A) writek(A) readk(A) writek(A) Reihenfolge irrelevant, kein Konflikt Reihenfolge muß spezifiziert werden, Konflikt analog Reihenfolge muß spezifiziert werden, Konflikt Serialisierbarkeitsgraph: Knoten = atomare Operationen (read, write) Kanten = Ordnungsbeziehung (Operation i vor Operation k) Serialisierbarkeitstheorem: Eine partiell geordnete Menge nebenläufiger Operationen ist genau dann serialisierbar, wenn der Serialisierungsgraph zyklenfrei ist. Beispiel für nicht serialisierbare Historie T1 T2 BOT read(A) write(A) BOT read(A) write(A) read(B) write(B) commit read(B) write(B) commit verzahnte Historie T1 T2 T1 T2 BOT read(A) write(A) read(B) write(B) commit BOT read(A) write(A) read(B) write(B) commit BOT read(A) write(A) read(B) write(B) commit Serialisierung 1 BOT read(A) write(A) read(B) write(B) commit Serialisierung 2 Der Effekt dieser Verzahnung entspricht keiner der 2 möglichen Serialisierungen T1 vor T2 oder T2 vor T1: Die Historie ist nicht serialisierbar Sperrsynchronisation Viele Datenbank-Scheduler verwenden Sperranweisungen zur Erzeugung konfliktfreier Abläufe: • Sperrmodus S (shared, read lock, Lesesperre) Wenn Transaktion Ti eine S-Sperre für ein Datum A besitzt, kann Ti read(A) ausführen. Mehrere Transaktionen können gleichzeitig eine S-Sperre für dasselbe Objekt A besitzen. • Sperrmodus X (exclusive, write lock, Schreibsperre) Nur eine einzige Transaktion , die eine X-Sperre für A besitzt, darf write(A) ausführen. Verträglichkeit der Sperren untereinander: (NL = no lock, keine Sperrung) NL S X S ok ok - X ok - - Zwei-Phasen-Sperrprotokoll (Englisch: two-phase locking, 2PL) Protokoll gewährleistet die Serialisierbarkeit von Transaktionen. Für jede individuelle Transaktion muß gelten: 1. Jedes von einer Transaktion betroffene Objekt muß vorher entsprechend gesperrt werden. 2. Eine Transaktion fordert eine Sperre, die sie besitzt, nicht erneut an. 3. Eine Transaktion muß solange warten, bis es eine erforderliche Sperre entsprechend der Verträglichkeitstabelle erhalten kann. 4. Jede Transaktion durchläuft 2 Phasen: - in Wachstumsphase werden Sperren angefordert, aber nicht freigegeben - in Schrumpfungsphase werden Sperren freigegeben, aber nicht angefordert 5. Bei EOT (Transaktionsende) muß eine Transaktion alle ihre Sperren zurückgeben. Verschärfung zum "Strengen 2PL-Protokoll" zur Vermeidung von Schneeballeffekten beim Zurücksetzen: Keine Schrumpfungsphase, alle Sperren werden bei EOT freigegeben. Beispiel für 2PL-Verzahnung T1: Modifikation von A und B (z.B. Umbuchung) T2: Lesen von A und B (z.B. Addieren der Salden) T1 T2 BOT lockX(A) read(A) write(A) BOT lockS(A) lockX(B) read(B) unlockX(A) T2 muß warten T2 wecken read(A) lockS(B) write(B) unlock(B) T2 muß warten T2 wecken read(B) commit unlockS(A) unlockS(B) commit Verklemmungen (Deadlocks) Sperrbasierte Synchronisationsmethoden können (unvermeidbar) zu Verklemmungen führen: Gegenseitiges Warten auf Freigabe von Sperren Transaktionen leicht modifiziert: T1 BOT lockX(A) BOT lockS(B) read(B) T1: Modifikation von A und B (z.B. Umbuchung) T2: Lesen von B und A (z.B. Addieren der Salden) T2 read(A) write(A) lockX(B) lockS(A) T1 muß auf T2 warten T2 muß auf T1 warten => Deadlock Strategien zur Erkennung und Vermeidung von Verklemmungen 1. Wartegraph hat Zyklen w T1 T2 w T4 w w T3 w = wartet auf Nach Erkennen eines Zyklus muß Verklemmung durch Zurücksetzen einer geeigneten Transaktion beseitigt werden. 2. Preclaiming - Vorabforderung aller Sperren Beginn einer Transaktion erst, nachdem die für diese Transaktion insgesamt erforderlichen Sperren erfolgt sind. Problem: Vorab die erforderlichen Sperren erkennen 3. Zeitstempel Transaktionen werden durch Zeitstempel priorisiert. Zurücksetzen statt Warten, wenn T1 Sperre fordert, T2 aber Sperre erst freigeben muß: • Strategie Wound-wait: Abbruch von T2, falls T2 jünger als T1, sonst warten • Strategie Wait-die: Abbruch von T1, wenn T1 jünger als T2, sonst warten Zugriff auf Daten in Progr.sprachen: Embedded SQL #include <stdio.h> /*Kommunikationsvariablen deklarieren */ exec sql begin declare section; varchar user_passwd[30]; int exMatrNr; exec sql end declare section; exec sql include SQLCA; main() { printf("Name/Password:"); scanf("%", user_passwd.arr); user_passwd.len=strlen(user_passwd.arr); exec sql wheneversqlerror goto error; exec sql connect :user_passwd; while (1) { printf("Matrikelnummer (0 zum beenden):"); scanf("%d", &ecMatrNr); if (!exMatrNr) break; exec sql delete from Studenten where MatrNr= :exMatrNr; } exec sql commit work release; exit(0); error: exec sql whenever sqlerror continue; exec sql rollback work release; printf(“Fehler aufgetreten!\n"); exit(-1); } Anfragen in Anwendungsprogrammen genau ein Tupel im Ergebnis exec sql select avg (Semester) into :avgsem from Studenten; Anfragen in Anwendungsprogrammen • mehrere Tupel im Ergebnis Satzorientierte Programmiersprache 3. Tupel sequentiell verarbeiten 1. Anfrage 4. Cursor/Iterator schließen mengenorientiertes DBMS 2. Anfrage auswerten, Ergebnistupel im Cursor/Iterator/ ResultSet bereitstellen Cursor-Schnittstelle in SQL 1. exec sql declare c4profs cursor for select Name, Raum from Professoren where Rang=‘C4‘; 2. exec sql open c4profs; 3. exec sql fetch c4profs into :pname, :praum; 4. exec sql close c4profs; Impedance Mismatch Datenmodellierungsform in Programmiersprachen paßt nicht zu Form in Datenbanken Programmiersprachen: Record/Tupelorientiert Mit hoher Frequenz einfache Operationen durchführen Datenbanksysteme: Mengenorientiert Mit niedriger Frequenz komplexe Operationen durchführen Probleme relationaler Datenbanktechnologie Zwar methodisch saubere aber schwierig zu lernende manuelle Umsetzung des Entwurfsmodells (ERM) in das Implementierungsmodell Impedance Mismatch Joins bei Navigierendem Zugriff sehr aufwendig Sprache für Integritätsbedingungen meist schwach (hier nicht vertieft) Zusammenfassung, Kernpunkte Anfragesprachen: SQL Mehrbenutzerbetrieb und Sperren Transaktionen Anbindung an Programmiersprachen Probleme der relationalen Datenbanktechnologie Was kommt beim nächsten Mal? Objektorientierte Modellierung