Lösung PÜ1

Dr. H. Schuldt
Eidgen
ossische Technische Hochschule Z
urich
Swiss Federal Institute of Technology Zurich
Transaktionsverwaltung in modernen IS
Praktische U bung 1
Beispiellosung
Einleitung
Das Ziel dieser U bung ist die Untersuchung, wie die in der Vorlesung vorgestellten Konzepte zur Serialisierbarkeit in kommerziellen Systemen umgesetzt werden.
Hierzu betrachten wir das Datenbanksystem Oracle9i. Am Beispiel kleiner Anwendungsszenarien sollen die hier realisierten Protokolle analysiert werden.
Verwenden Sie als Referenz bei Fragen zu den von Oracle implementierten Verfahren
die Oracle9i Dokumentation (speziell Kapitel 20: Data Concurrency and Consistency), verfugbar unter:
http://www.dbs.ethz.ch/timi/WS 02 03/docs/Oracle9i-Concepts.pdf.
Vorbereitung
Zunachst benotigen Sie einen Account fur den Zugri auf die Oracle{Datenbank.
U ber folgendes Web{Formular konnen Sie die Login-Information beantragen:
http://www.dbs.ethz.ch/timi/WS 02 03/oracleAccount.html. Nach Angabe
Ihres Namens und Ihrer Email-Adresse erhalten sie eine Mail, in der Ihnen Ihr Oracle
User Name, das zugehorige Passwort sowie der Host String (letzterer wird benotigt,
um die Datenbank, mit der die U bung stattndet, zu lokalisieren) mitgeteilt wird.
Sie konnen damit diese praktische Ubung
unter Windows NT auf den Rechnern in
Raum E31 oder C31 durchfuhren. Zur Vorbereitung sind folgende Schritte notig
(detailliertere Informationen zu Oracle 9i und zum Arbeiten mit Oracle an der ETH
ndet sich auch unter http://www.dbs.ethz.ch/oracle/):
1. Netzwerklaufwerk verbinden: Zuerst muss eine Verbindung zum File-Server
hergestellt werden. Hierzu muss nndbs12nsqlplus als Laufwerkbuchstabe U:
bereitstehen. Daher muss man entweder im Command Prompt >:
net use U: nndbs12nsqlplus bzw. net use U: nn129.132.14.16nsqlplus
eingeben bzw. im Windows{Explorer unter Extras ! Netzlaufwerk verbinden
das Laufwerk U: wie oben beschrieben belegen.
2. Startdatei und SQL-Skript kopieren: Auf dem Laufwerk U: nden Sie eine Datei namens sqlplus u.bat. Diese Datei startet SQLPLUS, das SQL-Frontend
1
zu unserer Oracle{Datenbank. Kopieren Sie diese Datei in Ihr Home{Verzeichnis
(H:). Im Unterverzeichnis U:ntimi nden Sie zudem das SQL-Skript
bung verwendete Beispiel-Datenbank
createAndFill.sql, das die in dieser U
anlegt und mit Daten fullt. Kopieren Sie auch diese Datei in Ihr Home{
Verzeichnis (in den gleichen Ordner, in den Sie zuvor auch sqlplus u.bat
abgelegt haben).
3. SQLPLUS starten: Mit Hilfe der ins Home{Verzeichnis kopierten Batch{Datei
sqlplus u.bat.
4. Anmelden an der Datenbank: Sie werden neben Username und Passwort auch
nach dem Host{String gefragt, der die Datenbank{Instanz, an die Sie sich
verbinden mochten, speziziert. In unserem Falle ist dies STUD1.
5. Skripte ausfuhren: In SQLPLUS konnen Sie mittels @filename (mit Pfad,
aber ohne .sql) sogenannte Skripten ausfuhren. Dies sind SQL{Befehlsdateien,
deren Name auf .sql endet. Die Beispieldatenbank wird also im SQLPLUS{
Fenster mittels @createAndFill angelegt.
Verwendete Daten
Die Basis der Aufgaben ist eine kleine relationale Datenbank (die Sie gerade eben
mittels createAndFill angelegt haben) aus einem Bankenszenario, welche folgende
Relationen enthalt:
Kunde (KundenNr, Name, Vorname, Wohnort)
Konto (KontoNr, Saldo, KundenNr)
Zins (KontoNr, Zinssatz)
(Primarschlussel sind unterstrichen, Fremdschlussel kursiv gesetzt)
Starten Sie in mehreren Fenstern SQLPLUS, d.h. bauen sie gleichzeitig mehrere
Datenbank{Verbindungen (unter dem gleichen Datenbank{Login) auf, um parallele
Benutzer zu simulieren. Experimentieren Sie mit den verschiedenen Moglichkeiten,
welche Ihnen Oracle in Bezug auf die Transaktionsverwaltung bietet, um folgende
Fragen zu beantworten. Ziehen Sie dazu auch die oben erwahnte Dokumentation zu
Rate.
2
Aufgabe 1: Konsistenz und Isolationslevel in Oracle
a) Geben Sie eine Denition der Konsistenz an, wie sie von Oracle aus gesehen wird
(Oracle9i-Concepts, Chapter 1: Data Concurrency and Consistency Overview).
Was wird unter Lese-Konsistenz verstanden, wie wird es in Oracle eingesetzt
und was wird dadurch vom System garantiert?
Oracle vermeidet Dirty Reads durch die Schreibsperren, die automatisch durch
das System gesetzt werden, wenn Update-, Delete- oder Insert-Statements ausgefuhrt werden. Oracle spricht von \Statement-Level Read Consistency" und
meint, dass ein einzelnes SQL-Statement eine konsistente Sicht der Datenbank
hat. Es ist jedoch moglich, dass aufeinanderfolgende Statements innerhalb der
gleichen Transaktion unterschiedliche Zustande sehen konnen.
b) Welches sind die Vorteile der Konsistenz, so wie sie in Oracle deniert sind? Wo
sind die Schwachstellen?
Oracle's Konsistenzbegri
ist auf grotmogliche Ezienz durch maximale Parallelitat von Transaktionen ausgerichtet. Deswegen wird so wenig wie moglich
gesperrt. Allerdings sind Transaktionen in Oracle nicht notwendigerweise koniktserialisierbar. (Beispiele hierfur gibt es in den folgenden Aufgaben).
c) Welche Isolationslevel werden von Oracle unterstutzt? Was ist jeweils die Semantik dieser Isolationslevel?
Oracle unterstutzt drei Isolationslevel: Read Committed, Serializable sowie
Read Only, wobei Read Committed die Standard-Einstellung ist. Es ist in keinem der Falle moglich, noch nicht committete Werte zu lesen, so dass inconsistent (dirty) reads nicht auftreten konnen.
Read Committed (siehe Aufgabe 2) besagt, dass nur Werte von erfolgreich beendeten Transaktionen gelesen werden. Ferner garantiert Oracle durch die \Statement-Level Read Consistency", dass fur die Dauer der Ausfuhrung des Statements der konsistente Datenbankzustand gesehen wird, der zu Beginn des Statements vorlag (so ist es zum Beispiel nicht moglich, dass ein Cursor Tupel sieht,
die nach seinem O
nen
erzeugt wurden).
Repeatable Read wird hingegen fur die Isolationsstufe Read Committed nicht garantiert. Da Konsistenz nur bezuglich eines Statements gilt, ist es moglich, dass
zwei identische Select-Anfragen derselben Transaktion unterschiedliche Werte
zuruckliefern. Repeatable Read kann jedoch fur Nur-Lese-Transaktionen erzwungen werden, indem die Transaktion als read-only deklariert wird (SET TRANSACTION READ ONLY, siehe Aufgabe 3).
Mit dem Isolationslevel Serializable (siehe Aufgabe 4) schliesslich werden nur
SSI{Schedules erlaubt.
Aufgabe 2: Isolationslevel Read
Committed
a) In der Vorlesung IS-G wurde das Problem der verlorengegangenen A nderung
(lost update) vorgestellt. Untersuchen Sie anhand eines Beispiels, ob dieses
Problem auch in Oracle mit der Default-Einstellung des Isolationslevels (Read
3
) moglich ist. Was kann ein Anwendungs- bzw. SQL-Programmierer
tun, um lost updates zu vermeiden?
Folgender Beispielablauf zeigt, dass lost updates moglich sind (f1 und f2 sind
dabei die unterschiedlichen Fenster, in denen die Eingaben erfolgen):
Committed
f1: Select Saldo from Konto where KontoNr = 1111111 (*)
f2: Select Saldo from Konto where KontoNr = 1111111
f2: /* Berechnung der Anderung des Kontostandes aufgrund des
zuvor gelesenen Wertes */
Update Konto set Saldo = 18000 where KontoNr = 1111111 (#)
f2: commit work
f1: /* Berechnung der Anderung des Kontostandes aufgrund des
zuvor gelesenen Wertes */
Update Konto set Saldo = 17000 where KontoNr = 1111111 (%)
f1: commit work
Obiges Beispiel zeigt, dass in Oracle nicht-koniktserialisierbare Ablaufe moglich
sind.
Ein solcher Ablauf kann verhindert werden, wenn der Anwendungsprogrammierer von Transaktion 1 (in Fenster f1) explizit angibt, dass die gelesenen Werte
spater auch geschrieben werden sollen. Anstelle von (*) sollte daher
Select Saldo from Konto where KontoNr = 1111111 for Update
verwendet werden. Dadurch werden die gelesenen Datensatze implizit gesperrt
und ein nachfolgendes Schreiben einer anderen Transaktion (#) ist nur dann
moglich, wenn T 1 abgeschlossen ist.
b) Reinitialisieren Sie Ihre Datenbank (mit createAndFill) und betrachten sie die
beiden folgenden Transaktionen:
1. T 1 : Select * From Konto where Saldo > 10000
2. T 1 : Select * From Kunde where Name = 'Del Medico' (*)
3. T 2 : Select * From Kunde where Name = 'Del Medico' (%)
4. T 2 : Select * From Konto where Saldo > 10000
5. T 1 : Update Konto set Saldo = Saldo * 1.05 where Saldo > 10000
6. T 2 : Update Kunde set Name = 'Felix' where Name = 'Del Medico'
7. T 2 : Update Konto set Saldo = Saldo * 1.05 where Saldo > 10000
8. T 1 : Update Kunde set Name = 'Felix' where Name = 'Del Medico'
Was stellen Sie bei diesem Ablauf fest? Was wurde hier SELECT FOR UPDATE bewirken? Unter welchen Umstanden konnen dadurch Deadlocks vermieden werden? Konstruieren Sie einen Fall, in dem dies nicht moglich ist!
Gibt es eine Moglichkeit, Deadlocks in jedem Fall zu verhindern? Wenn ja, was
sind die Konsequenzen?
Der vorgegebene Ablauf ist so nicht moglich, da ein Deadlock eintritt. Die Verwendung vom SELECT FOR UPDATE sorgt, wie wir in der vorigen Teilaufgabe bereits festgestellt haben, dafur, dass fur die Tupel einer Leseaktion Schreibsperren
gesetzt werden. Dies blockiert andere Transaktionen mit konigierenden Operationen. Im obigen Beispiel wird auf diese Weise ein Deadlock verhindert, da T 2
4
blockiert wird, bis T 1 beendet ist. Vertauscht man im obigen Beispiel die Operationen (*) und (%), so tritt jedoch wieder ein Deadlock auf. Dies liegt an der
\ungunstigen" Reihenfolge der Operationen.
Generell lassen sich Deadlocks nur vollstandig vermeiden, indem zirkulares Warten verhindert wird. Dies kann entweder durch \Preclaiming" erreicht werden
(alle Transaktionen erwerben alle Sperren, die sie benotigen werden, sofort zu
Beginn der Transaktion in einer atomaren Sperranforderung), oder durch eine
vordenierte Reihenfolge, in der Sperren erworben werden mussen. Legt man im
obigen Beispiel zum Beispiel fest, dass jede Transaktion zuerst ihre Sperren fur
die \Konto"-Tabelle setzen muss, bevor sie Sperren fur die \Kunde"- Tabelle
setzen kann, so werden Deadlocks vermieden.
Aufgabe 3: Read Only-Transaktionen
a) Ein weiteres, aus IS-G bekanntes Problem ist das inkonsistente Lesen. Ist dies in
Oracle fur die Isolationsstufe READ COMMITTED moglich? Konnen Sie hier ein Beispiel nden, bei dem das Repeatable Read nicht gewahrleistet ist? Was kann ein
Anwendungsprogrammierer tun, damit zwei Select-Statements innerhalb einer
Transaktion dasselbe Resultat liefern?
Repeatable Read wird vom Isolatonslevel Read Committed nicht garantiert. Wurde man im Beispiel aus Aufgabe 2.a anstelle von (%) erneut das Select-Statement
(*) ausfuhren, so wurden beide unterschiedliche Resultate zuruckliefern. Repeatable Read kann fur Nur-Lese-Transaktionen erzwungen werden, indem die Transaktion als read-only deklariert wird (SET TRANSACTION READ ONLY). Fur Trans
aktionen, die auch Anderungen
vornehmen, muss das Isolationslevel SERIALIZABLE verwendet werden bzw. es m
ussen explizit Sperren gesetzt werden.
b) Zeigen Sie die Auswirkungen der Option SET TRANSACTION READ ONLY, wenn
gleichzeitig A nderungen erfolgen. Konstruieren Sie einen Fall, in welchem zu
einer Zeit ein und dieselbe Query in drei verschiedenen Fenstern unterschiedliche
Resultate liefert.
Was sagt das Handbuch zu der Option SET TRANSACTION READ ONLY? Ordnen
Sie sie in die Vorlesung ein. Zeigen Sie insbesondere die Zusammenhange zur
Mehrversionenverwaltung.
Das folgende Beispiel zeigt eine Folge von Operationen dreier Transaktionen T 1,
T 2 und T 3, wobei fur jede Transaktion ein eigenes SQLPLUS-Fenster verwendet
wird:
1. T 1: Set transaction Read Only
2. T 2: Update Kunde set Wohnort = 'Basel' where KundenNr = 1
3. T 2: commit work
4. T 2: Set transaction Read Only
5. T 3: Update Kunde set Wohnort = 'Bern' where KundenNr = 1
6. T 3: Commit work
7. T 3: Set transaction read only
5
8. T 1: Select * from Kunde where KundenNr = 1
9. T 2: Select * from Kunde where KundenNr = 1
10. T 3: Select * from Kunde where KundenNr = 1
Wurde die Option SET TRANSACTION READ ONLY gesetzt, so speichert Oracle den
Zeitpunkt des Beginns der Transaktion. Leseaktion der Transaktion sehen den
Zustand der Datenbank, wie er diesem Zeitpunkt existierte. Dies wird erreicht,
indem der alte Wert aus dem Log gelesen wird, falls sich der Wert eines Tupels
seit Transaktionsbeginn geandert hat.
Aufgabe 4: Isolationslevel Serializable
Im Isolationslevel Serializable verwendet Oracle das in der Vorlesung vorgestellte
Mehrversionen-Concurrency-Control-Protokoll mit der Bezeichnung Snapshot Isolation. Jede Transaktion arbeitet auf einer eigenen Version der Datenbank (snapshot ),
die den konsistenten Zustand aller committeter Daten zum Zeitpunkt des Transaktionsbeginns reektiert. Leseaktionen einer Transaktion lesen also Werte so, wie
sie zu Beginn der Transaktion vorlagen (ausser naturlich Werte, welche von dieser
Transaktion selbst geschrieben wurden). Beim Schreiben wird eine neue Version des
jeweiligen Objektes erzeugt, das bis zum erfolgreichen Commit nur fur die eigene
Transaktion sichtbar ist.
a) Setzen Sie die Isolationsstufe auf Set Transaction Isolation Level Serializable und testen Sie einen nicht mehrversionen-view-serialisierbaren Ablauf,
bei dem zwei parallele Transktionen schreibend auf dasselbe Objekt zugreifen.
Lasst sich die in der Vorlesung besprochene Eigenschaft der Snapshot{Isolation
(SSI) hier beobachten?
Wir betrachten den folgenden Ablauf:
1. T 1: Set transaction Isolation Level Serializable
2. T 2: Set transaction Isolation Level Serializable
3. T 1: Select * from Kunde where KundenNr = 1
4. T 2: Select * from Kunde where KundenNr = 1
5. T 2: Update Kunde set Wohnort = 'Bern' where KundenNr = 1
6. T 2: Commit work
7. T 1: Update Kunde set Vorname = 'Ruedi' where KundenNr = 1
Die Transaktion T 1 wird mit der Fehlermeldung ORA-08177: can't serialize
access for this transaction abgebrochen, da keine Serialisierbarkeit mehr
gewahrleistet werden kann.
Um lost updates zu vermeiden (und um die Bedingung SSI{W einzuhalten), wird
fur Schreibaktionen die folgende Regelung eingefuhrt: Wenn eine Transaktion T 1
ein Objekt schreiben will, das von einer anderen Transaktion T 2 geschrieben wurde, so wird |wie im obigen Beispielablauf| T 1 abgebrochen, falls das Commit
von T 2 nach dem Beginn von T 1 stattfand.
Oracle verbietet also korrekterweise diesen Ablauf, der nicht mehrversionenview{serialisierbar ist.
6
b) Modizieren Sie nun Ihren Beispielablauf so, dass einerseits die beiden parallelen Transktionen jeweils unterschiedliche Objekte schreiben, dass aber andererseits die Mehrversionen-View-Serialisierbarkeit immer noch verletzt ist (siehe
praktisches Beispiel, Folien II-108 { II-110 der Vorlesung). Was stellen Sie fest?
Was konnen Sie daraus uber die Korrektheit des von Oracle implementierten
Snapshot-Isolation-Protokolls schliessen?
Das Snapshot Isolation Protokoll erkennt lediglich Konikte zwischen Schreibern
unterschiedlicher Versionen. In dem hier betrachteten Beispielschedule liegen
jedoch keine solchen w/w-Konikte vor.
1. T 1: Set transaction Isolation Level Serializable
2. T 2: Set transaction Isolation Level Serializable
3. T 1: Select * from Kunde where KundenNr = 3
4. T 2: Select * from Konto where KontoNr = 1111111
5. T 2: Update Kunde set Name = 'Ramazotti' where KundenNr = 3
6. T 1: Update Konto set Saldo = Saldo * 1.05 where KontoNr = 1111111
7. T 1: Commit work
8. T 2: Commit work
Uberraschenderweise
ist dieser Ablauf in Oracle ohne Fehler moglich obwohl
dieser Schedule nicht mehrversionen-view-serialisierbar ist.
Dieses Beispiel zeigt also, dass die von Oracle unterstutzte Snapshot-Serialisierbarkeit nicht der in der Vorlesung vorgestellten Mehrversionen-View-Serialisierbarkeit entspricht und diese Implementierung o
ensichtlich Schedules ermoglicht,
die nicht serialisierbar sind.
7