12. Views und Datenbankverwaltung • Views • Änderungen in Views

12. Views und Datenbankverwaltung
• Views
• Änderungen in Views
• Organisation der DB
• Zugriffsrechte
Datenbanksysteme
Prof. Dr. Stephan Kleuker
282
Sichtkonzept (Views 1/2)
• Sicht (View): mit eigenem Namen bezeichnete, aus
Basisrelation abgeleitete, virtuelle Relation (ViewName wie Tabellen-Name verwendbar)
• Views sind das Ergebnis einer Anfrage, auf dem
weitere Operationen durchgeführt werden können
• Views können jedes mal neu erzeugt werden oder
nur einmal und dann gespeichert (materialized view)
• Gespeicherte Views müssen nach jedem Update der
Basisrelationen geändert werden
• Wir betrachten keine materialized Views
• Korrespondenz zum externen Schema bei ANSI
SPARC (Benutzer sieht jedoch mehrere Views und
Basisrelationen)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
283
Sichtkonzept (Views 2/2)
CREATE VIEW Germany AS
SELECT name, population
FROM City
WHERE Country='D'
• Vorteile
– Erhöhung der Benutzerfreundlichkeit (z.B.
Verbergen komplexer Joins in einer View)
– Datenschutz
• Löschen eines Views
DROP VIEW Germany
Datenbanksysteme
Prof. Dr. Stephan Kleuker
284
View-Update-Problem
• Änderungsoperationen auf Sichten erfordern, dass
zu jedem Tupel der Sicht zugrunde liegende Tupel
der Basisrelationen eindeutig identifizierbar sind
• Sichten auf einer Basisrelation sind nur änderbar,
wenn der Primärschlüssel in der Sicht enthalten ist
• Wenn Tupelanteile bei INSERT eindeutig auf die
darunter liegenden Basisrelationen abgebildet
werden können, könnten fehlende Werte durch NULL
aufgefüllt werden (Constraints sind zu beachten)
[Allerdings, in Oracle grundsätzlich keine
Veränderung auf zusammengesetzten View möglich]
Datenbanksysteme
Prof. Dr. Stephan Kleuker
285
Problemquellen bei Views
1. Zeilen löschen, wenn der View Gruppenfunktionen
(z.B. COUNT), GROUP BY oder DISTINCT enthält oder
in der View-Definition WITH READ ONLY steht
2. Zeilen ändern, wenn 1. oder es berechnete Spalten
(z.B. A+B) gibt
3. Zeilen hinzufügen, wenn 1. oder 2. oder es in den
Basistabellen eine Spalte mit NOT NULL gibt, die
nicht im View liegt
Hinweis: Infos, was man in Oracle darf, erhält man mit
SELECT * FROM USER_UPDATABLE_COLUMNS;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
286
Beispiel: View-Probleme
R
A B
- a b
x b
S
B C
- b c
b z
RS
A B C
- - a b c
x b c
a b z
x b z
CREATE VIEW RS AS
SELECT A, R.B, C
FROM R, S
WHERE R.B=S.B;
• Löschen von (a,b,c) führte zum Verlust von
(a,b,z)
• alleiniges Ändern von (a,b,c) nach (a,b,d) geht
nicht
• Einfügen von (a,b,d) ebenfalls
Datenbanksysteme
Prof. Dr. Stephan Kleuker
287
Aktualisierung von Views
• Tupel können durch Update aus dem Wertebereich
des Views hinausfallen
• Views häufig verwendet, um den "Aktionsradius"
eines Benutzers einzuschränken
• Verlassen des Wertebereichs kann durch WITH
CHECK OPTION verhindert werden:
CREATE OR REPLACE VIEW UScities AS
SELECT *
FROM City
WHERE City.Country = 'USA'
WITH CHECK OPTION;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
288
Beispiel: WITH CHECK OPTION
A
B
CREATE TABLE XX(
A NUMBER, B NUMBER, C NUMBER, ---------- ---------1
2
PRIMARY KEY(A));
2
1
INSERT INTO XX VALUES(1,2,3);
INSERT INTO XX VALUES(2,1,3);
A
B
INSERT INTO XX VALUES(3,4,3);
---------- ---------2
1
CREATE OR REPLACE VIEW V1X AS
SELECT A,B
1 Zeile wurde erstellt.
FROM XX
INSERT INTO V1X VALUES(5,5)
WHERE B<3
*
WITH CHECK OPTION;
FEHLER in Zeile 1:
SELECT * FROM V1X;
ORA-01402: Verletzung der WHERE-Klausel
einer View WITH CHECK OPTION
UPDATE XX
SET B=5 WHERE B=2;
A
B
C
---------- ---------- ---------SELECT * FROM V1X;
1
5
3
INSERT INTO V1X VALUES(4,2);
2
1
3
INSERT INTO V1X VALUES(5,5);
3
4
3
4
2
SELECT * FROM XX;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
289
INSTEAD OF –Trigger
• View Updates: Updates müssen auf Basistabellen umgesetzt
werden
• View-Update-Mechanismen eingeschränkt
• INSTEAD OF -Trigger: Änderung an einem View wird durch
andere SQL-Anweisungen ersetzt.
CREATE [OR REPLACE] TRIGGER <trigger-name>
INSTEAD OF
{INSERT | DELETE | UPDATE} ON <view>
[FOR EACH ROW]
<p1/sql-block>;
• Keine Einschränkung auf bestimmte Spalten möglich
• Keine WHEN -Klausel
• Default: FOR EACH ROW
Datenbanksysteme
Prof. Dr. Stephan Kleuker
290
View-Update ohne Trigger
CREATE OR REPLACE VIEW AllCountry AS
SELECT Name, Code, Population, Area,
GDP, Population/Area AS Density,
Inflation, population_growth,
infant_mortality
FROM Country, Economy, Population
WHERE Country.Code = Economy.Country
AND Country.Code = Population.Country;
INSERT INTO AllCountry
(Name, Code, Population, Area, GDP, Inflation,
population_growth, infant_mortality) VALUES
('Lummerland','LU',4,1,0.5,0,25,0);
• Fehlermeldung: Über ein Join-View kann nur eine Basistabelle
modifiziert werden.
Datenbanksysteme
Prof. Dr. Stephan Kleuker
291
View-Update mit Trigger
CREATE OR REPLACE TRIGGER InsAllCountry
INSTEAD OF INSERT ON AllCountry
FOR EACH ROW
BEGIN
INSERT INTO Country (Name,Code,Population,Area)
VALUES (:NEW.Name, :NEW.Code, :NEW.Population,
:NEW.Area);
INSERT INTO Economy (Country,Inflation)
VALUES (:NEW.Code, :NEW.Inflation);
INSERT INTO Population (Country,
Population_growth,infant_mortality)
VALUES (:NEW.Code, :NEW.Population_growth,
:NEW.infant_mortality);
END;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
292
Organisation der DB (in Oracle)
Data Dictionary
• Besteht aus Tabellen und Views, die Metadaten über
die Datenbank enthalten
• Mit SELECT * FROM DICTIONARY (kurz SELECT *
FROM DICT) erklärt sich das Data Dictionary selber
• ALL_OBJECTS: Enthält alle Objekte, die einem
Benutzer zugänglich sind
• ALL_CATALOG: Enthält alle Tabellen, Views und
Synonyme, die einem Benutzer zugänglich sind
• ALL TABLES: Enthält alle Tabellen, die einem
Benutzer zugänglich sind
• Analog für diverse andere Dinge
SELECT * FROM ALL_CATALOG
WHERE TABLE_NAME LIKE 'ALL%';
Datenbanksysteme
Prof. Dr. Stephan Kleuker
293
Informationen über/für User
• USER_OBJECTS: enthält alle Objekte, die einem
Benutzer gehören
• Analog für die anderen, meistens existieren für USER
... auch Abkürzungen, etwa OBJ für
USER_OBJECTS
• ALL_USERS: Enthält Informationen über alle
Benutzer der Datenbank
Datenbanksysteme
Prof. Dr. Stephan Kleuker
294
Einrichtung von Usern (Oracle)
CREATE USER <user>
IDENTIFIED BY <password>;
CREATE USER Egon
IDENTIFIED BY ottilie01
QUOTA 5M ON system;
• nächster Schritt: Einrichtung der Systemprivilegien
des Nutzers (viele Möglichkeiten), u.a.
GRANT create session TO Egon
Datenbanksysteme
Prof. Dr. Stephan Kleuker
295
Verwaltung einer Datenbank
• Für DB-Projekte gibt es meist zwei Administratoren
– DB-Systemadministratoren: Physikalische
Einrichtung von Datenbanken (z.B. Name,
Speicherbereich), Nutzerverwaltung
– Projekt-DB-Administratoren: Verantwortlich für
die Tabellen des Projekts, wer hat welche Rechte
auf welchen Tabellen
• Abhängig vom DB-System müssen beide eng
zusammenarbeiten
• Hinweis: Sie haben auf unserem System grob die
Rechte eines Projekt-Admin und können
Zugriffsmöglichkeiten für Andere einrichten
Datenbanksysteme
Prof. Dr. Stephan Kleuker
296
Systemprivilegien
GRANT <privilege-list>
TO <user-list> | PUBLIC
[WITH ADMIN OPTION];
• PUBLIC: jeder erhält das Recht.
• ADMIN OPTION: Empfänger darf dieses Recht
weitergeben
Rechte entziehen:
REVOKE <privilege-list> | ALL
FROM <user-list> | PUBLIC;
• nur wenn man dieses Recht selbst vergeben hat (im
Fall von ADMIN OPTION kaskadierend)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
297
Systemprivilegien
• berechtigen zu Schemaoperationen
• CREATE [ANY] TABLE / VIEW / TYPE / INDEX /
CLUSTER / TRIGGER/ PROCEDURE:
Benutzer darf die entsprechenden Schema-Objekte
erzeugen
• ALTER [ANY] TABLE / TYPE/ TRIGGER /
PROCEDURE:
Benutzer darf die entsprechenden Schema-Objekte
verändern
Datenbanksysteme
Prof. Dr. Stephan Kleuker
298
Systemprivilegien
• DROP [ANY] TABLE / VIEW / TYPE / INDEX /
CLUSTER / TRIGGER / PROCEDURE:
Benutzer darf die entsprechenden Schema-Objekte
löschen
• SELECT / INSERT / UPDATE / DELETE [ANY]
TABLE:
Benutzer darf in Tabellen Tupel lesen/ erzeugen/
verändern/ entfernen
• ANY: Operation in jedem Schema erlaubt,
• ohne ANY: Operation nur im eigenen Schema erlaubt
Datenbanksysteme
Prof. Dr. Stephan Kleuker
299
Rollen
• Privilegien können Rollen zugeordnet werden, die
dann wieder Nutzern zugeordnet werden können.
CREATE ROLE manager;
GRANT create table, create view
TO manager;
GRANT manager TO i03d09, i00d02;
• Der Entwurf einer sinnvollen Rollenmatrix ist nicht
trivial!
Datenbanksysteme
Prof. Dr. Stephan Kleuker
300
Objektprivilegien (1/4) [entspricht Projektebene]
berechtigen dazu, Operationen auf existierenden
Objekten auszuführen:
• Niemand sonst darf mit einem Datenbankobjekt
eines Nutzers arbeiten, außer
• Eigentümer (oder DBA) erteilt explizit entsprechende
Rechte:
GRANT <privilege-list> | ALL
[(<column-list>)]
ON <object>
TO <user-list> | PUBLIC
[ WITH GRANT OPTION ];
Datenbanksysteme
Prof. Dr. Stephan Kleuker
301
Objektprivilegien (2/4)
• <object>: TABLE, VIEW, PROCEDURE/FUNCTION,
TYPE
• Tabellen und Views: Genauere Einschränkung für
INSERT, REFERENCES und UPDATE durch <columnlist>
• <privilege-list>: DELETE, INSERT, SELECT,
UPDATE für Tabellen und Views,
INDEX, ALTER und REFERENCES für Tabellen
EXECUTE für Prozeduren, Funktionen und TYPEn
• ALL: alle Privilegien, die man an dem beschriebenen
Objekt hat
• WITH GRANT OPTION: Der Empfänger darf das
Recht weitergeben
Datenbanksysteme
Prof. Dr. Stephan Kleuker
302
Objektprivilegien (3/4)
• Rechte entziehen:
REVOKE <privilege-list> | ALL
ON <object>
FROM <user-list> | PUBLIC
[CASCADE CONSTRAINTS];
• CASCADE CONSTRAINTS (bei REFERENCES): alle
referenziellen Integritätsbedingungen, die auf einem
entzogenen REFERENCES-Privileg beruhen, fallen
weg
• Berechtigung von mehreren Benutzern erhalten:
Fällt mit dem letzten REVOKE weg
• im Fall von GRANT OPTION kaskadierend.
Datenbanksysteme
Prof. Dr. Stephan Kleuker
303
Objektprivilegien (4/4)
• Überblick über vergebene/erhaltene Rechte:
SELECT * FROM USER_TAB_PRIVS;
• Rechte, die man für eigene Tabellen vergeben hat,
Rechte, die man für fremde Tabellen bekommen hat
SELECT * FROM USER_COL_PRIVS;
SELECT * FROM USER_TAB_PRIVS_MADE;
SELECT * FROM USER_COL_PRIVS_MADE;
SELECT * FROM USER_TAB_PRIVS_RECD;
SELECT * FROM USER_COL_PRIVS_RECD;
• Stichwort: Rollenkonzept
Datenbanksysteme
Prof. Dr. Stephan Kleuker
304
Beispiele
GRANT select,
update(name,code)
ON Country
TO egon, manager
GRANT select,insert
ON City
TO PUBLIC
REVOKE select,insert
ON Country
FROM manager
Datenbanksysteme
Prof. Dr. Stephan Kleuker
305
Zugriffsrechte innerhalb von Oracle
• Zugriffsrechte an ORACLE-Account gekoppelt
• Ausgangsrechte vom DBA vergeben
Schema-Konzept
• Jedem Benutzer ist sein Database Schema
zugeordnet, in dem "seine" Objekte liegen.
• Bezeichnung der Tabellen global durch
<username>.<table> (z.B. xmaier.City für die
Tabelle City des Nutzers xmaier),
• im eigenen Schema durch <table> oder
<ich>.<table> nutzbar
Datenbanksysteme
Prof. Dr. Stephan Kleuker
306
Synonyme
• Schemaobjekt unter einem anderen Namen als
ursprünglich ansprechbar:
CREATE [PUBLIC] SYNONYM <synonym>
FOR <schema>.<object>;
• Ohne PUBLIC: Synonym ist nur für den Benutzer
definiert
• PUBLIC ist das Synonym systemweit verwendbar.
Geht nur mit CREATE ANY SYNONYM -Privileg
Beispiel:
CREATE SYNONYM City2
FOR db07ws65.City
(man muss Zugriffsrechte auf die Tabelle haben)
löschen:
DROP SYNONYM <synonym>
Datenbanksysteme
Prof. Dr. Stephan Kleuker
307
Zugriffseinschränkung über Views (1/2)
• GRANT SELECT kann nicht auf Spalten eingeschränkt werden
Stattdessen: Views verwenden.
GRANT SELECT [<column-list>] -- nicht erlaubt
ON <table>
TO <user-list> | PUBLIC
[WITH GRANT OPTION];
• kann ersetzt werden durch
CREATE VIEW <view> AS
SELECT <column-list> FROM <table>;
GRANT SELECT
ON <view>
TO <user-list> | PUBLIC
[WITH GRANT OPTION];
Datenbanksysteme
Prof. Dr. Stephan Kleuker
308
Zugriffseinschränkung über Views (2/2)
• db07ws65 ist Besitzer der Tabelle Country, will
Country ohne Hauptstadt und deren Lage für
db07ws00 Ies- und schreibbar machen
• View mit Lese- und Schreibrecht für db07ws00 :
CREATE VIEW pubCountry AS
SELECT Name, Code, Population, Area
FROM Country;
GRANT SELECT, INSERT, DELETE, UPDATE
ON pubCountry
TO db76ws00;
Datenbanksysteme
Prof. Dr. Stephan Kleuker
309
13. Integrität innerhalb der DB
• Integrität in relationalen DB
• ACID-Transaktionen
• Probleme beim verteilten Arbeiten
• Transaktionen: Praxis (Oracle)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
310
Wiederholung: Überblick über SQL
•
•
•
DDL Data Definition Language
– CREATE (Anlegen von Tabellen, Sichten, Indexen, ...)
– ALTER (Ändern)
– DROP (Löschen)
– RENAME (Umbenennen)
DML Data Manipulation Language
– SELECT (Anfragen)
– INSERT (Einfügen von Zeilen)
– UPDATE (Ändern)
– DELETE (Löschen)
DCL Data Control Language
– GRANT (Vergabe von Zugriffsrechten)
– REVOKE (Zurücknahme von Zugriffsrechten)
– COMMIT (Abschluss einer Transaktion)
– ROLLBACK (Abbruch einer Transaktion)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
311
Erinnerung: COMMIT, Transaktion, Rechte
i00x
CREATE TABLE User
(NAME VARCHAR(9));
GRANT ALL ON User TO i00y;
INSERT INTO User
VALUES(‘egon‘);
SELECT * FROM USER;
NAME
egon
i00y
INSERT INTO i00x.User
VALUES(‘hugo‘);
SELECT * FROM i00x.User;
NAME
hugo
COMMIT;
SELECT * FROM USER;
NAME
egon
hugo
Datenbanksysteme
Prof. Dr. Stephan Kleuker
312
ACID-Transaktionen
Atomicity
(Atomarität)
Transaktionen werden entweder ganz
oder gar nicht ausgeführt
Consistency Transaktionen überführen die Datenbank
(Konsistenz)
Isolation
(Isolation)
Durability
(Dauerhaftigkeit)
Datenbanksysteme
von einem konsistenten Zustand in einen
anderen konsistenten Zustand
Nebenläufige (gleichzeitige) Transaktionen
laufen jede für sich so ab, als ob sie alleine
ablaufen würden.
Die Wirkung einer abgeschlossenen
Transaktion bleibt (auch nach einem
Systemausfall) erhalten.
Prof. Dr. Stephan Kleuker
313
Zu ACID-Transaktionen
Tatsächlich muss ein DBMS nur garantieren:
• Das Ergebnis einer Transaktion ist gleichwertig zu
einer ACID-Transaktion
Die Transaktionsbehandlung betrifft:
• die Synchronisation von mehreren gleichzeitig
ablaufenden Transaktionen
• das Recovery, d.h. die Behebung von Fehlern
Transaktionen können erfolgreich (commit) oder
erfolglos (abort, für DB-Nutzer rollback)
abgeschlossen werden
Datenbanksysteme
Prof. Dr. Stephan Kleuker
314
Transaktionen
• Zustände einer Transaktion
Datenbanksysteme
Prof. Dr. Stephan Kleuker
315
Transaktion in SQL
Erste SQL-Anfrage
Folge von SQL-Anfragen
commit
Beginn der ersten Transaktion
Nächste SQL-Anfrage
Folge von SQL-Anfragen
rollback
Beginn der nächsten Transaktion
Festschreiben
Rücksetzen zum
vorhergehenden commit
Nächste SQL-Anfrage
Folge von SQL-Anfragen
commit
Datenbanksysteme
Beginn der nächsten Transaktion
Festschreiben
Prof. Dr. Stephan Kleuker
316
Atomarität (1/2)
Eine Transaktion wird entweder ganz oder gar nicht
ausgeführt
• Konto-Beispiel: Umbuchung von K1 auf K2
– Abbuchung von Konto K1
– Zubuchung auf Konto K2
entweder beide Operationen werden
durchgeführt oder keine
Abbruch kann stattfinden aufgrund:
• Selbstaufgabe (z.B. Benutzerabbruch)
• Systemabbruch durch das DBMS (z.B. wg. Deadlock)
• Crash (Hardware-/Softwarefehler)
Datenbanksysteme
Prof. Dr. Stephan Kleuker
317
Atomarität (2/2)
Wie stellt das Datenbanksystem die Atomarität sicher?
• bei einer lesenden Transaktion: kein Problem
• bei einer schreibenden Transaktion:
– bei Abbruch: Änderungen müssen rückgängig
gemacht werden (bzw. dürfen gar nicht erst
sichtbar werden)
– im Erfolgsfall: alle Änderungen müssen sichtbar
werden
Realisierungsmöglichkeit: das Schattenspeicherverfahren
Datenbanksysteme
Prof. Dr. Stephan Kleuker
318
Schattenspeicherkopie
Realisierungsmöglichkeit: das
Schattenspeicherverfahren
• Kopie einer DB: Arbeitskopie
Durchführung einer Transaktion:
• Änderung: wird nur auf der Arbeitskopie gemacht
• Commit (erfolgreich): DB := Arbeitskopie
• Abort (oder erfolgloses Commit): Arbeitskopie
wegwerfen
Datenbanksysteme
Prof. Dr. Stephan Kleuker
319
Konsistenz
Konsistenz betrifft alle vorgegebenen Regeln:
- Datentypen und Bereiche bei Attributen
- PRIMARY KEY
- FOREIGN KEY
- CONSTRAINTs
- TRIGGER (s. später)
technische Realisierung z.B. wie bei der Atomarität
Datenbanksysteme
Prof. Dr. Stephan Kleuker
320
Isolation
Parallel ablaufende Transaktionen sollen sich
gegenseitig nicht beeinflussen, d.h. jede läuft für
sich so ab, als sei sie die einzige Transaktion im
System
Dient zur Vermeidung div. Probleme:
• lost-update-Problem
• dirty read
• non-repeatable-read
• phantom read
Datenbanksysteme
Prof. Dr. Stephan Kleuker
321
Beispiel: Mehrnutzerbetrieb
• zwei parallel verlaufende Transaktionen:
T1:
T2:
BEGIN A=A+100, B=B-100 END
BEGIN A=1.06*A, B=1.06*B END
• Die erste Transaktion transferiert 100 € vom Konto B
zum Konto A. Die zweite Transaktion schreibt beiden
Konten 6 % Zinsen gut.
• Es gibt keine Garantie, dass T1 vor T2 ausgeführt
wird (oder umgekehrt), wenn beide zusammen
gestartet werden. Jedoch gilt: Der Nettoeffekt muss
äquivalent zu beiden Transaktionen sein, wenn sie
seriell in irgendeiner Reihenfolge ablaufen würden.
Datenbanksysteme
Prof. Dr. Stephan Kleuker
322
Beispiele für Schedules
• Betrachte folgenden Ablauf mit ineinander
geschachtelten Transaktionen (Schedules) :
T1:
T2:
A=A+100,
B=B-100
A=1.06*A,
B=1.06*B
• Kein Problem, aber bei diesem Beispiel:
T1:
A=A+100,
B=B-100
T2:
A=1.06*A, B=1.06*B
• Zweiter Schedule aus Sicht des DBMS:
T1:
T2:
Datenbanksysteme
R(A), W(A),
R(B), W(B)
R(A), W(A), R(B), W(B)
Prof. Dr. Stephan Kleuker
323
Anomalien im Mehrbenutzerbetrieb (1/4)
• Verlorengegangene Änderungen (Lost Update)
– WW-Konflikt
– Gleichzeitige Änderung desselben Objekts durch
zwei Transaktionen
– Erste Änderung (aus nicht beendeter Transaktion)
wird durch die zweite überschrieben
T1:
T2:
W(A),
W(B), C
W(A), W(B), C
C für Commit
Datenbanksysteme
Prof. Dr. Stephan Kleuker
324
Anomalien im Mehrbenutzerbetrieb (2/4)
• Zugriff auf schmutzige Daten (Dirty Read)
– WR-Konflikt
– “schmutzige“ Daten = geänderte Objekte, deren
Änderungen von Transaktionen stammen, die
noch nicht beendet sind
– Dauerhaftigkeit der Änderungen nicht garantiert,
da Transaktionen noch zurückgesetzt werden
– Ungültige Daten werden somit durch andere
Transaktion gelesen und (schlimmer noch!)
vielleicht noch weiterpropagiert
T1:
T2:
R(A), W(A),
Datenbanksysteme
R(B), W(B), Rollback
R(A), W(A), C
Prof. Dr. Stephan Kleuker
325
Anomalien im Mehrbenutzerbetrieb (3/4)
• Nicht-wiederholbares Lesen (Unrepeatable Read)
– RW-Konflikt
– Eine Transaktion sieht (bedingt durch parallele
Änderungen) während ihrer Ausführung
unterschiedliche Zustände des Objekts. Erneutes
Lesen in der Transaktion liefert somit anderen
Wert
T1:
T2:
R(A),
Datenbanksysteme
R(A), W(A), C
R(A), W(A), C
Prof. Dr. Stephan Kleuker
326
Anomalien im Mehrbenutzerbetrieb (4/4)
• Phantom-Problem
– Spezielle Form des Unrepeatable Read
– Lesetransaktion: Mengenorientiertes Lesen über
ein bestimmtes Suchprädikat P
– Parallel läuft Änderungstransaktion, die die
Menge der sich für das Prädikat qualifizierenden
Objekte ändert
– Folge: Phantom-Objekte, die durch parallele
Einfüge- oder Löschvorgänge in Ergebnismenge
auftauchen und/oder daraus verschwinden
Datenbanksysteme
Prof. Dr. Stephan Kleuker
327
Lost Update Beispiel
Zeit
Gehaltsänderung T1
SELECT GEHALT
INTO :gehalt
FROM PERS
WHERE PNR=2345;
gehalt:=
gehalt+2000;
UPDATE PERS
SET GEHALT=:gehalt
WHERE PNR=2345;
Datenbanksysteme
DBDB-Inhalt
(PNR, GEHALT)
2345 39.000
Gehaltsänderung T2
SELECT GEHALT
INTO :gehalt2
FROM PERS
WHERE PNR=2345;
gehalt2:=
gehalt2+1000;
UPDATE PERS
SET
GEHALT=:gehalt2
WHERE
PNR=2345;
Prof. Dr. Stephan
Kleuker
2345 41.000
2345 40.000
328
Dirty Read Beispiel
DBDB-Inhalt
(PNR, GEHALT)
Gehaltsänderung T1
UPDATE PERS
SET GEHALT=
GEHALT+1000
WHERE PNR=2345
. . .
Gehaltsänderung T2
2345
39.000
2345
40.000
3456
42.000
2345
39.000
SELECT GEHALT
INTO :gehalt
FROM PERS
WHERE PNR=2345
gehalt:=gehalt*1.05;
ROLLBACK
Datenbanksysteme
UPDATE PERS
SET GEHALT=:gehalt
WHERE PNR=3456
COMMIT
Prof. Dr. Stephan Kleuker
329
Unrepeatable Read Beispiel
DBDB-Inhalt
(PNR, GEHALT)
Gehaltsänderung T1
Gehaltssumme T2
UPDATE PERS
SET GEHALT=
GEHALT+1000
WHERE PNR=2345
. . .
UPDATE PERS
SET GEHALT=
GEHALT+2000
WHERE PNR=3456
SELECT SUM(GEHALT)
INTO :summe
FROM PERS
WHERE PNR IN
(2345,3456)
39.000
45.000
2345
40.000
3456
47.000
Inkonsistente Analyse
summe=85.000
COMMIT
Datenbanksysteme
2345
3456
Zeit
Prof. Dr. Stephan Kleuker
330
Unrepeatable Read Beispiel
Gehaltssumme T2
DBDB-Inhalt
(PNR, GEHALT)
Gehaltsänderung T1
UPDATE PERS
SET GEHALT=
GEHALT+1000
WHERE PNR=2345
SELECT GEHALT INTO :g1
FROM PERS
WHERE PNR=2345
UPDATE PERS
SET GEHALT=
GEHALT+2000
WHERE PNR=3456
COMMIT
39.000
45.000
2345
40.000
3456
47.000
SELECT GEHALT INTO :g2
FROM PERS
WHERE PNR=3456
summe:=g1+g2
Inkonsistente Analyse ohne schmutziges
Lesen
Datenbanksysteme
2345
3456
Prof. Dr. Stephan Kleuker
Zeit
331
Phantom-Problem Beispiel
Lesetransaktion (Gehaltssumme der
Abteilung 17 bestimmen)
Änderungstransaktion (Einfügen
eines neuen Angestellten in
Abteilung 17)
SELECT SUM(GEHALT) INTO :sum1
FROM PERS
WHERE ANR=17
INSERT INTO PERS
(PNR,ANR,GEHALT)
VALUES(4567,17,55.000)
. . .
SELECT SUM(GEHALT) INTO :sum2
FROM PERS
WHERE ANR=17
IF sum1<>sum2 THEN
<Fehlerbehandlung>
Zeit
Datenbanksysteme
Prof. Dr. Stephan Kleuker
332
Isolationsgrade
Die gezeigten Effekte treten je nach Isolationsgrad (isolation level)
auf:
Isolationsgrad
mögliche Effekte
0
Dirty Read, Non-Repeatable-Read, Phantom
1
Non-Repeatable-Read, Phantom
2
Phantom
3
Standard ist Isolationsgrad 3; geringere Grade können für
bestimmte Anwendungen Sinn machen (z.B. grobe Statistik)
• man erreicht so eine Leistungssteigerung
Auch hier gilt: man sollte wissen, was man tut...
Datenbanksysteme
Prof. Dr. Stephan Kleuker
333
Isolationsgrade in SQL (eine Version)
Setzen des Isolationsgrades in SQL (aber nicht Oracle):
SET TRANSACTION ISOLATION LEVEL <nr.>;
oder
SET TRANSACTION ISOLATION LEVEL
[ READ UNCOMMITED –- nicht Oracle
| READ COMMITTED -- Oracle (default)
| REPEATABLE READ –- nicht Oracle
| SERIALIZABLE
-- Oracle];
Zusätzlich kann eingestellt werden, ob die Transaktion nur liest:
SET TRANSACTION [ READ ONLY | READ WRITE ];
Einstellung gilt für aktuelle Verbindung, nach COMMIT oder
ROLLBACK wieder zurück gesetzt
Datenbanksysteme
Prof. Dr. Stephan Kleuker
334
Isolation - Realisierung
Die Isolationsgrade werden erreicht durch Sperren (locks)
• Lesesperren (shared lock, s)
• Schreibsperren (exclusive lock, x)
Lesesperren sind mit anderen Lesesperren kompatibel,
aber nicht mit Schreibsperren;
Schreibsperren sind mit nichts kompatibel
Die Sperrgranularität ist von der Implementierung eines DBMS
abhängig (z.B. Tabellensperren vs. Tupelsperren)
Oracle:
LOCK TABLE T IN EXCLUSIVE MODE;
LOCK TABLE T IN EXCLUSIVE MODE NOWAIT;
Freigabe der Sperren nach Transaktionsende
Datenbanksysteme
Prof. Dr. Stephan Kleuker
335
2-Phasen-Sperrprotokoll
Relationale Datenbanken arbeiten üblicherweise
pessimistisch,
und zwar nach dem 2-Phasen-Sperrprotokoll
• eine Transaktion muss jedes Objekt, das sie lesen
will, mit einer Lesesperre (s-lock) versehen
• eine Transaktion muss jedes Objekt, das sie ändern
will, mit einer Schreibsperre (x-lock) versehen
• eine Transaktion darf kein Objekt mehr neu sperren,
nachdem sie die erste Sperre freigegeben hat
Datenbanksysteme
Prof. Dr. Stephan Kleuker
336
Probleme mit ACID-Transaktionen
• Entwurfsanwendungen: Entwurf startet, Transaktion beginnt,
nach drei Monaten ist der Entwurf fertig, Transaktion wird aber
beim commit abgebrochen (… bitte von vorne anfangen...)
gefragt sind sog. lange Transaktionen: Syncpoints oder
Sagas
• Mobile Geräte: Daten werden auf Datenbank in (Notebook, PDA)
geladen, danach Trennung von Hauptdatenbank,
Benutzer macht Änderungen, verbindet sich wieder mit der DB,
was tun? alle Änderungen zurückweisen? Haupt-DB
sperren?
Synchronisation ist gefragt, evtl. mit vorheriger Anzeige
möglicher Arbeitsbereiche
Datenbanksysteme
Prof. Dr. Stephan Kleuker
337