Teil 3: SQL – erweitert: Datensichten, Transaktionen, Benutzerrechte…

Grundkurs Datenbanken: erweiterte SQL-Funktionen
Keine Kommentare

SQL bietet mehr als das Jonglieren mit Daten! In diesem Teil unserer einführenden Artikelserie werfen wir einen Blick auf die so genannten erweiterten Themen der Sprache. Wir beschäftigen uns mit Datensichten, Transaktionen und der Ausgestaltung von Zugriffs- und Benutzerrechten. Gerade letztere ist unter den Aspekten der zunehmenden Anforderungen des Datenschutzes von großer Bedeutung.

Im ersten Teil unsere Artikelserie standen die Konzepte des Datenbankentwurfs im Fokus. Zunächst war die Datenbank in ihrer Struktur festzulegen (Abb. 1). Mit der Festlegung dieser Struktur wurden die Voraussetzungen geschaffen, um mit der Datenbank und den Daten zu arbeiten. Es handelt sich um Daten einfügen, Daten ändern und Daten löschen oder in SQL-Syntax: INSERT, UPDATE und DELETE. Die „Königsdisziplin“ ist und bleibt das Abfragen der Datensätze. Zentrales Element von Abfragen ist die SELECT-Anweisung. Deren Syntax und Facetten wurden ebenfalls im zweiten Teil erläutert [1].

Abb. 1: Struktur unserer Beispieldatenbank

Abb. 1: Struktur unserer Beispieldatenbank

Aber es gibt noch mehr über SQL zu erfahren. Zu den fortgeschrittenen Themen gehören die so genannten Views. Dabei handelt es sich um bestimmte Sichten auf die gespeicherten Daten. Diese Sichten sollen letztendlich die Arbeit mit den Daten erleichtern. Datenbanken werden heute (meist) im Kontext von Mehrbenutzerumgebungen verwendet. Dieses impliziert, dass klar zu regeln ist, welche Personen bzw. Personenkreis Zugriff auf die Daten oder Teildaten hat. Ein Beispiel: In einer Patientendatenbank werden sowohl administrative, als auch medizinische Daten gespeichert. Man muss sicherstellen, dass die Mitarbeiter der Verwaltung nur diejenigen Daten einsehen können, welche für die Erledigung ihrer Aufgaben unbedingt notwendig sind. Die sensiblen medizinischen Daten müssen auf jeden Fall vor einem unberechtigten Zugriff geschützt werden. Dieses Thema wird unter der Rubrik Rechte- und Rollenkonzept betrachtet.

Nun die Beschäftigung damit – dass kann man ruhig zugeben – löst im Regelfall wenig Freude aus. Für den Datenbankadministrator ist es eine zusätzliche Belastung und viel Verwaltungsarbeit die korrekten Zugriffsrechte bzw. Zugriffsbeschränkungen festzulegen. Die Zuständigkeit ist eigentlich auch eher auf administrativer Ebene angesiedelt. Und anderseits: Die Mitarbeiter in der Administration fühlen sich mit diesem – so aus ihrer Sicht – sehr technischen „Kram“ überfordert oder zumindest belästigt. Dass das zu großen Problemen führen kann, sieht man, wenn man die Meldungen in der Presse über Vorfälle in bekannten Großunternehmen verfolgt. Beklagt wurde der teilweise mehr als nachlässige Umgang mit den digitalen Gütern. Datenschutz- und Datensicherheitsprobleme sind die Folge. Technisch betrachtet handelt es sich um die folgende Frage: Verfügen Personen über unangemessene Zugriffsrechte auf die Datenbank?

Ein weiteres wichtiges Thema sind Transaktionen, d. h. die Zusammenfassung von inhaltlich zusammengehörigen SQL-Befehlen, um die Integrität der Datenbank jederzeit sicherzustellen. Auch mit dieser Frage beschäftigen wir uns; zunächst mit der Theorie und dann mit der Umsetzung in SQL. Während die klassischen SQL-Befehle im Regelfall kompakte Anfragen und Anweisungen an die Datenbank darstellen, gibt es auch die Möglichkeit der Automatisierung. Die Überschrift dazu lautet „Routinen und Trigger“. Hinweise finden Sie am Ende des Beitrags. Eine gute Einführung in SQL geben die zahlreichen Fachbücher. Für unseren Artikel haben wir u. a. [2] und [3] genutzt. Aber auch hier gilt: Selber probieren, zum Beispiel mit der kostenfreien Software SQL-Teacher.

Views

Wir befassen uns als Nächstes mit den so genannten Sichten (engl. Views) auf eine Datenbank. Wie dargestellt, dient der SELECT-Befehl dazu, Inhalte aus Tabellen abzufragen. Bei Abfragen über mehrere Tabellen können diese Tabellen über so genannte JOINS miteinander verknüpft werden. Das Konzept der Views beschreibt die Technik, wie diese Abfragen auf eine oder mehrere Tabellen gespeichert werden können. Views selber enthalten keine Daten, sondern verweisen auf die entsprechenden Daten in Spalten der Tabellen. Eine View kann als Abbild einer Teilmenge der Daten aus diesen Tabellen verstanden werden. Man spricht in diesem Zusammenhang auch von virtuellen oder imaginären Tabellen.

Abb. 2: Das Prinzip von Views [3]

Abb. 2: Das Prinzip von Views [3]

Abbildung 2 zeigt die generelle Funktionsweise von Views. Man sieht zwei Tabellen (A und B) mit entsprechenden Spalten. Die erzeugte View (ViewAB) besteht aus Teilmengen der Spalten von Tabelle A und Tabelle B. Sie wirkt in der Darstellung und Funktionsweise wie eine eigenständige Tabelle und kann teilweise auch so verwendet werden. Für den weiteren Einsatz bietet sich die Vergabe eines Namens an, der den Zusatz (Präfix) view tragen sollte, zum Beispiel folgende Syntax: view_Name. Die Verwendung des Präfixes dient dazu, Views von Tabellen zu unterscheiden. In SQL gibt es den Befehl CREATE VIEW für diese Aufgabe. Eine View arbeitet wie eine Abfrage (mittels SELECT-Klausel) mit den Spalten der betreffenden Tabelle. Die Syntax dafür lautet:

CREATE
    [OR REPLACE]
    VIEW view_name [(column_list)]
    AS select_statement

Wie man sieht, umfasst der Befehl CREATE VIEW eine SELECT-Anweisung. Die SELECT-Abfrage kann beliebig ausgestaltet sein. Ein Beispiel: Wir betrachten eine Abfrage, die eine Liste aller Kunden inklusive ihrer Bestellungen ausgibt. Die zugehörige SELECT-Anweisung wird in Listing 1 aufgezeigt.

SELECT k.Kundennummer, k.Name, k.Vorname, 
b.Bestellnummer, b.Status, b.Datum
FROM bestellung b, kunde k
WHERE b.Kundennummer = k.Kundennummer
ORDER BY k.Kundennummer ASC;

Nun soll diese Abfrage weiter spezifiziert werden, sodass nur Rechnungen in der Liste erscheinen, die den Status bezahlt aufweisen. Die Anweisung ist also wie in Listing 2 zu erweitern.

SELECT k.Kundennummer, k.Name, k.Vorname, 
b.Bestellnummer, b.Status, b.Datum
FROM bestellung b, kunde k
WHERE b.Kundennummer = k.Kundennummer
AND b.Status = "bezahlt"
ORDER BY k.Kundennummer ASC;

Eine Vereinfachung zu dieser Vorgehensweise besteht in der Erstellung einer View mit integrierter SELECT-Abfrage dar (Listing 3).

CREATE OR REPLACE 
ALGORITHM = UNDEFINED
VIEW `BezahlteRechnungen` AS 
SELECT k.Kundennummer, k.Name, k.Vorname, 
b.Bestellnummer, b.Status, b.Datum
FROM bestellung b, kunde k
WHERE b.Kundennummer = k.Kundennummer
AND b.Status = "bezahlt"
ORDER BY k.Kundennummer ASC;

Statt immer die umfassende SELECT-Anweisung zu bemühen, kann auf die erzeugte View – hier mit dem Namen BezahlteRechnungen – zurückgegriffen werden. Insgesamt lassen sich die Vorteile der Verwendung einer View wie folgt zusammenfassen:

  • Sie bietet die Möglichkeit, einen personalisierten Zugriff auf Datensätze innerhalb einer Datenbank einzurichten.
  • Sie dient der Vereinfachung von komplexer Datenbankabfragelogik. Insbesondere hilft sie der Befriedigung des Informationsbedarfs, wenn die Daten aus mehreren Tabellen stammen. Zum Beispiel gestaltet sich ein Update auf eine View einfacher, als ein Update mit dem notwendigen Zugriff auf alle betroffenen Basistabellen.
  • Ergebnisse von Berechnungen müssen nicht in die Basistabellen aufgenommen werden.

    Allerdings stehen diesen Vorteilen auch Nachteile entgegen (sonst wäre es ja zu schön…):

  • Verlängerung der Abfragezeiten, da das Datenbankmanagementsystem (DBMS) zunächst auf die View und dann auf die referenzierten Basistabellen zurückgreifen muss.
  • Es wird zusätzlicher Speicherplatz benötigt.

Dennoch ist das Konzept der Views schlüssig und sollte Anwendung bei komplexen Datenbankoperationen finden.

Rechte und Rollen

Datenbanken werden heute (meist) im Kontext von Mehrbenutzerumgebungen verwendet. Dieses impliziert, dass klar zu regeln ist, welche Person bzw. Personenkreis Zugriff auf die Daten oder Teildaten hat. Ein Beispiel: In einer Patientendatenbank werden sowohl administrative, als auch medizinische Daten gespeichert. Man muss sicherstellen, dass die Mitarbeiter der Verwaltung nur diejenigen Daten einsehen können, welche für die Erledigung ihrer Aufgaben unbedingt notwendig sind. Die sensiblen medizinischen Daten müssen auf jeden Fall vor einem unberechtigten Zugriff geschützt werden. Der Datenbankadministrator verfügt grundsätzlich über alle Rechte im Bezug auf die Datenbank (Textkasten: Aufgaben eines Datenbankadministrators). Dieses betrifft sowohl die Änderungen an der Struktur, als auch Änderungen an den Inhalten der Datenbank. Die Angelegenheiten der Rechtezuweisung werden unter „Rechte und Rollen“ betrachtet. Dazu bietet SQL folgende Möglichkeiten:

  • Benutzer anlegen
  • Benutzer löschen
  • Rechtevergabe an Benutzer

Für Datenbanken im Mehrbenutzerbetrieb (im Regelfall innerhalb eines Netzwerks) ist eine differenzierte Rechtevergabe gewünscht und notwendig. Die Zugriffsrechte auf eine Datenbank werden als Privilegien bezeichnet. Diese Privilegien sind an einen Benutzer gebunden. Mit der Anmeldung des Benutzers an der Datenbank werden die Privilegien zugewiesen. Der Administrator der Datenbank verfügt über die vollständigen Rechte. Für das Zuweisen von Benutzerrechten steht in SQL der Befehl GRANT zur Verfügung. Es geht um:

  • Leserechte
  • Löschrechte
  • Änderungsrechte
  • Einfügerechte

Diese Rechte beziehen sich auf bestimmte Datenbankobjekte, wie zum Beispiel Tabellen oder Views. Die Zuordnung der Privilegien auf der Ebene einzelner Nutzer kann sehr aufwändig sein und, im Falle sehr vieler Nutzer, ein unlösbares Problem darstellen. Denken Sie in diesem Zusammenhang beispielsweise an ein Großunternehmen mit mehreren tausend Beschäftigten. Abhilfe schafft das Konzept der Rolle. Eine Rolle ist eine Eigenschaft, die einem Benutzer zugeordnet wird. Dieser Rolle wiederum können nun Privilegien zugeordnet werden. Ein Beispiel: Die Rolle „Sachbearbeiter“ nehmen alle Benutzer einer Unternehmung (Abteilung) an, die in dieser Funktion arbeiten. Der Vorteil ist, dass man nur einmal die Rechte (Privilegien) der Rolle vergeben muss. Mit der Zuordnung eines Benutzers zu einer Rolle, verfügt dieser dann über die notwendigen Rechte bzw. Einschränkungen. Änderungen an den Rollen lassen sich dann gleichermaßen einfach realisieren. Der Befehl zum Erstellen einer Rolle lautet:

CREATE ROLE [Rollenname]

Kommen wir nun zu dem Einrichten der Benutzerprivilegien. Man hat zwei Möglichkeiten, beide sind Varianten des GRANT-Befehls. Die erste Variante gibt einem einzelnen Benutzer oder einer Rolle das Privileg bestimmte Aktionen auszuführen. Die Syntax lautet:

GRANT {Privilegienliste | ALL} ON TABLE [Tabellenname] TO {Benutzerliste | PUBLIC}.

Man sieht, es gibt auch die Möglichkeit der Verwendung von Wildcards, wie zum Beispiel ALL, für die Vergabe maximaler Rechte. Das Attribut PUBLIC hinter dem Schlüsselwort TO weist die vergebenen Rechte den Benutzern zu. Die zweite Vorgehensweise ist bezüglich der Syntax umfangreicher, erlaubt jedoch auch mehrere Optionen (Listing 4).

GRANT {USAGE | UNDER | TRIGGER | EXECUTE |}
ON Object
TO {benutzerliste | PUBLIC}
|WITH GRAND OPTION|

In diesem Fall gestattet man, dass der Benutzer ein bestimmtes Datenbankobjekt verwenden darf. Es stehen verschiedene Zugriffsstufen zur Verfügung. Deren Ausgestaltung ist vom eingesetzten Datenbanksystem abhängig. Eine weitere Detaillierung der Privilegien ist möglich. Dazu sind die betreffenden Spalten einer Tabelle anzugeben:

privileg (spaltenliste)

Eine Alternative ist die Erstellung einer View. Diese würde nur die entsprechenden Spalten enthalten. Bei Tabellen kann man Einschränkungen im Bezug auf die zugelassenen Befehle vornehmen: Mit SELECT gestattet man dem Benutzer alle (oder die von ihm gewünschten Spalten) zu lesen. INSERT erlaubt das Einfügen neuer Datensätze. Wenn sie nur die Rechte für bestimmte Spalten setzen, wird in die anderen Spalten der Tabelle der Default-Wert angenommen. Mit UPDATE darf der Benutzer Daten ändern. Mit REFERENCES vergibt man das Recht, eine neue Tabelle anzulegen, die eine Spalte dieser Tabelle als Fremdschlüssel verwendet.

Im Bezug auf Objekte gibt es folgende Rechte: USAGE erlaubt die Verwendung des Datensatzes. Mit UNDER ermöglicht man es, einen definierten Typ in einem CREATE-TABLE-Befehl zu verwenden. Das betrifft objektorientierte Ansätze des Programmzugriffs, die hier nicht besprochen werden. TRIGGER erlaubt dem Benutzer diese zu verwenden, und die Angabe von EXECUTE erlaubt den Einsatz eigener Prozeduren und Funktionen.

API Conference 2018

API Management – was braucht man um erfolgreich zu sein?

mit Andre Karalus und Carsten Sensler (ArtOfArc)

Web APIs mit Node.js entwickeln

mit Sebastian Springer (MaibornWolff GmbH)

Der TO-Teil des Befehls leitet die Liste der Benutzer ein, denen die Rechte zugewiesen werden. PUBLIC stellt die Variante der globalen Benutzerzuweisung dar. Fügt man WITH GRAND OPTION hinzu, dann können privilegierte Benutzer die ihnen gewährten Rechte weitergeben. Privilegien werden für Datenbankobjekte vergeben, so auch für existierende Views. Diese Kombination von Views und Privilegien ist optimal, um einen effektiven Zugangsschutz aufzubauen: Mit der Definition der View werden in einem ersten Schritt die gewünschten Daten ausgewählt. Mit der Vergabe der entsprechenden Benutzerrechte, kann im nächsten Schritt der Zugriff weiter beschränkt werden. Vergebene Zugriffsrechte können auch wieder zurückgenommen werden. Dazu dient der REVOKE-Befehl wie in Listing 5.

REVOKE [GRANT OPTION FOR] {Privilegienliste | ALL}
ON object
FROM {benutzerliste | public}
[{RESTRICT | CASCADE}]

Aufgaben eines Datenbankadministrators

Datenbankadministratoren sind absolute IT-Spezialisten und werden vom Arbeitsspektrum den so genannten Lösungsbetreuern zugeordnet. Ihre Aufgaben sind das Überwachen bestehender Datenbanken und die Sicherung der Betriebsbereitschaft. Weiteres Aufgabengebiete sind Planung, Installation, Konfiguration sowie das Testen von neu erstellten Datenbanken. Sie sind u. a. zuständig für die Datensicherheit, die Verfügbarkeit der Daten und deren Wiederherstellung im Fehlerfall, letzteres mittels Back-up- und Recovery-Verfahren. Eine enge Verknüpfung ist auch zum Netzwerk- und Systembereich zu verzeichnen. Oft sind sie auch beratend tätig, z. B. unterstützen sie Projektleiter in technischen Fragen des Datenbankmanagements.

Voraussetzungen zur Tätigkeit als Datenbankadministrator sind ein hohes Qualifikationsniveau auf der Basis einschlägiger Berufsausbildung in der Informations- und Telekommunikationstechnik und/ oder umfassende Berufserfahrung. Selbstverständlich muss sich ein Datenbankadministrator auch kontinuierlich fortbilden und sich über Neuerungen auf diesem Gebiet informieren. Die Einsatzgebiete und -orte von Datenbankadministratoren sind sehr vielfältig. Sie arbeiten nicht nur in IT-Unternehmen, sondern mittlerweile in jeglicher Art von Unternehmen, die große Mengen an Daten produzieren bzw. verwalten.

Transaktionen

Bei der Arbeit mit Datenbanken ist ein wichtiges Zielkriterium, dass stets die Datenintegrität sichergestellt ist. Hierzu dient u. a. das Konzept der Transaktionen. Unter einer Transaktion versteht man eine Folge von Operationen, die nur komplett oder gar nicht durchgeführt werden. Das bedeutet, eine Transaktion ist ein Verbund aus nicht mehr teilbaren (atomaren) Aktionen. Dessen Notwendigkeit kann man am besten an einem Beispiel eines datenbankgestützten Buchungssystems erklären: Nehmen wir an, dass wir einen Flug und ein Taxi zum Flughafen buchen. Wir fangen also mit dem Taxi an und buchen es für 13 Uhr. Wenn wir dann den Flug buchen wollen, sehen wir, dass es gar keinen Flug passend zu dem Taxi gibt. Es ist sinnvoll, diese beiden einzelnen Aktionen zu einer Transaktion zusammenzufassen. Würde innerhalb der Transaktion ein Fehler auftreten (in unserem Beispiel, dass kein Flug mehr verfügbar wäre), wird die gesamte Transaktion automatisch zurückgenommen.

Auf diese Weise soll jederzeit für eine Korrektheit der Daten (Integrität) gesorgt werden. Um dies innerhalb der Datenbank zu realisieren, bedient man sich der so genannten Sperre (auch Lock genannt). Eine Sperre ist ein Mechanismus (durch den Datenbankserver sichergestellt) um Daten, die innerhalb einer aktiven Transaktion verändert werden, gegenüber anderen Zugriffen zu schützen. Wenn ein Teil der Daten gesperrt ist, müssen weitere Benutzer, die auf diese Daten zugreifen wollen, warten bis die Sperre wieder aufgehoben wurde. Man differenziert zwischen Schreib- und Lesesperren. Dabei können auf einen bestimmten Teil der Daten immer nur eine Schreibsperre, aber beliebig viele Lesesperren gesetzt werden. Damit können Datensatzkonflikte und Versionierungsprobleme verhindert werden. Mittels der Sperren wird Folgendes erreicht: Fordert ein Nutzer einen Datensatz innerhalb einer Transaktion an, so erhält er den aktuellen Stand der Daten. Für diesen Benutzer ändern sich die Daten bis zum Ende der Transaktion nicht. Aus der Definition einer Transaktion ergibt sich deren grundsätzlicher Aufbau. Transaktionen müssen einen Anfang haben, umfassen einen oder mehrere SQL-Befehle und müssen auch einen definierten Endpunkt aufweisen:

Beginn der Transaktion 
SQL-Befehle
Ende der Transaktion

Die Anzahl der SQL-Befehle, die während einer Transaktion ausgeführt werden, ist nicht limitiert, im Extremfall besteht eine Transaktion auch nur aus einem einzigen Befehl. Transaktionen werden auch mittels des Begriffs ACID (Atomicity, Consistency, Isolation und Durability) beschrieben. Dabei bedeuten:

  • Atomicity (Atomarität): kennzeichnet Transaktionen dahingehend, dass sie entweder komplett oder gar nicht ausgeführt werden
  • Consistency (Konsistenz): Eine Transaktion überführt eine Datenbank immer von einem konsistenten Zustand in einen anderen konsistenten Zustand. Anders ausgedrückt: Es kann nicht vorkommen, dass Befehle unvollständig ausgeführt werden.
  • Isolation: Transaktionen werden in ihrer Ausführung nicht durch parallel ausgeführte Befehle beeinträchtigt. Das Ergebnis einer Transaktion ist konstant.
  • Durability (Dauerhaftigkeit): Wird eine Transaktion erfolgreich ausgeführt, wird das Ergebnis dauerhaft in der Datenbank gespeichert.

Um nun eine Transaktion mit SQL zu realisieren, brauchen wir eine Menge an Wissen und Handwerkszeug, um dies umzusetzen. In vielen Datenbanksystemen gibt es keinen expliziten Befehl für den Beginn einer Transaktion. Der Gedanke ist, dass Datenbanken, die ein transaktionales Modell unterstützen, alle Befehle in einer Transaktion ablaufen lassen. Das bedeutet, dass man ab dem ersten SQL-Befehl automatisch in einer Transaktion arbeitet. Die Datenbank beginnt also selbstständig mit der Eröffnung einer Transaktion. Bei folgenden Befehlen wird in der Regel eine Transaktion begonnen, soweit nicht schon eine Transaktion aktiv ist: ALTER, CREATE, DROP, GRANT, REVOKE. Ebenso Befehle zu Änderung von Daten: DELETE, INSERT und UPDATE.

Die Arbeit mit Transaktionen bedeutet für den Benutzer, den Endzeitpunkt zu bestimmen. Eine Transaktion kann auf zwei Arten beendet werden:

  • Mit dem Befehl COMMIT, der bedeutet, dass alle bis hierhin übergebenen Befehle auch wirklich ausgeführt werden.
  • Beendigung einer Transaktion mit ROLLBACK. Bei dieser Option werden alle Befehle bis zum letzten COMMIT oder bis zum Beginn der Session zurückgenommen bzw. erst gar nicht ausgeführt. Damit ist der bisherige Zustand der Datenbank – wie vor dem Beginn der Transaktion – wiederhergestellt.

Wenn man nun keinen besonderen Wert auf die explizite Verwendung von Transaktionen legt, gibt es in den meisten Datenbanksystemen die Möglichkeit, ein so genanntes AUTOCOMMIT zu aktivieren. In diesem Fall werden alle Befehle als separate Transaktion behandelt, automatisch mit COMMIT abgeschlossen und zur Ausführung gebracht. AUTOCOMMIT aktiviert man wie folgt:

SET AUTOCOMMIT {ON | OFF}

Transaktionen im Multi-User-Betrieb

Außer der Transaktionssteuerung mit COMMIT und ROLLACK besteht weiterhin die Möglichkeit, das Verhalten von Transaktionen zu beeinflussen. Moderne Datenbanken sind Multi-User-fähig sind, d. h. ein mehrfacher Zugriff, zum Beispiel durch Benutzer A und B auf gleiche Datensätze, ist denkbar. Schlimmstenfalls können Update-Informationen verloren gehen oder die Rückgabe von SELECT-Befehlen unterschiedlich ausfallen. So genannte Isolationsphänomene sind Effekte, die zu Inkonsistenzen führen können, wenn man die Befehlsfolgen mehrerer Transaktionen simultan betrachtet. Eine Übersicht gibt Tabelle 1.

Isolationsphänomen Beschreibung
Lost Update Bezeichnet den Effekt, dass Änderungen verloren gehen. Ein Beispiel liefert Abbildung 3: Die Änderungen, die durch Transaktion 1 durchgeführt wurden, werden durch Transaktion 2 erneut überschrieben und gehen damit verloren. Transaktion 1 sieht eigentlich eine Korrektur auf den Wert „4“ vor, Transaktion 2 überschreibt diese Änderung auf den Wert „2“. Für beide Transaktionen war der Ausgangswert (ggf. bedeutsam für Berechnungen) jedoch der Wert „3“.
Dirty Read Bezeichnet den Effekt, dass Daten gelesen werden, die noch nicht durch ein COMMIT bestätigt sind. Abbildung 4 zeigt ein Beispiel: In diesem Fall würde die Transaktion 1 Informationen lesen, die jedoch wenig später durch Transaktion 2 in Form eines ROLLBACK wieder rückgängig gemacht wurden. Wir benutzen also Daten in Transaktion 1, die noch gar nicht für die Produktion freigegeben wurden.
Non Repeatable Read Beschreibt den Sachverhalt, dass eine Abfrage innerhalb einer Transaktion unterschiedliche Ergebnisse liefert. Das Ergebnis einer Abfrage konnte also nicht wiederholt werden. Ein Beispiel liefert Abbildung 5: Zwischen der ersten und zweiten Leseanforderung innerhalb von Transaktion 1, werden die Daten durch Transaktion 2 modifiziert.
Phantom Innerhalb einer Transaktion liefert eine Abfrage beim ersten Aufruf kein Ergebnis und beim nächsten Aufruf jedoch ein Ergebnis (Abb. 6). In diesem Fall würde die Transaktion 1 bei der ersten Ausführung der SELECT-Anweisung keinen Datensatz zurückgeben. Bei der Wiederholung der Abfrage ist (bedingt durch den INSERT-Befehl der Transaktion 2) dann ein entsprechender Datensatz vorhanden.


Tabelle 1: Mögliche Phänomene bei parallel ablaufenden Transaktionen

Abb. 3: Lost-Update-Effekt

Abb. 3: Lost-Update-Effekt

Abb. 4: Dirty-Read-Effekt

Abb. 4: Dirty-Read-Effekt

Abb. 5: Non-Repeatable Read-Effekt

Abb. 5: Non-Repeatable Read-Effekt

Abb. 6: Phantom-Effekt

Abb. 6: Phantom-Effekt

Die Kenntnis solcher möglicher Phänomene ist die eine Ebene, deren Handhabung eine andere. Für Transaktionen kann definiert werden, welche der beschriebenen Effekte zulässig und welche vermieden werden. Dies erfolgt mithilfe der Definition so genannter Isolationsebenen für eine Transaktion. Isolationsebenen werden für Transaktionen aktiv mithilfe des Befehls SET TRANSAKTION definiert. Folgende Isolationsebenen können definiert werden:

  • READ UNCOMMITED: Es handelt sich um das geringste Isolationslevel. Es können auch Änderungen gelesen werden, die noch nicht durch ein COMMIT dauerhaft gespeichert wurden.
  • READ COMMITED: Ausschluss von Dirty Reads
  • REPEATABLEREAD: Das nächste Isolationslevel unterbindet außer DirtyReads auch NON_REPEATABLE READS.
  • SERIALIZE: Die höchste Ebene, bei der alle weiter oben beschriebenen Phänomene vermieden werden.

Wichtig ist es jedoch zu wissen, dass Transaktionen nur im Mehrbenutzerbetrieb eine Rolle spielen und auch nur in einer solchen Umgebung sinnvoll getestet werden können. In Anwendungen, die lediglich eine (lokale) Datenbank als Speicher verwenden, hat das Programm die volle Kontrolle für die Daten und deren jeweiligen Zustand, d. h. Inkonsistenzen sind aus einer parallelen Verarbeitung nicht zu befürchten.

Routinen und Trigger

Um Befehlsabläufe zu realisieren, bedient man sich der Definition von Routinen. Trigger dienen dazu, auf Ereignisse in der Datenbank zu reagieren. Beides erinnert an die Möglichkeiten einer Programmiersprache und tatsächlich – die Formulierung und die sprachlichen Mittel sind ähnlich. Eine Funktion (auch als Routine oder Prozedur bezeichnet) fasst Codeteile zu einer Einheit zusammen. Die Befehle zur Definition lauten CREATE FUNCTION bzw. CREATE PROCEDURE. Die genaue Syntax und die zur Verfügung stehenden sprachlichen Mittel sind vom Datenbanksystem abhängig. Funktionen weisen den folgenden allgemeinen Aufbau auf:

  • Definition eines Funktionsnamens (eindeutig) und der Parameterliste im Header
  • Im Funktionsbody werden die Anweisungen definiert, dazu stehen u. a. zur Verfügung: lokale Variablen, Rechenoperationen, Schleifen, Kontrollstrukturen (IF…THEN…ELSE) und Anweisungen zur Fehlerbehandlung.

Trigger sind im Sinne einer Programmiersprache Ereignis-Handler. In SQL sind es automatisch ablaufende Befehle. Mit Triggern können zum Beispiel Bedingungen beim Einfügen oder Löschen eines Datensatzes geprüft werden. Zum Beispiel: Die Kundendaten dürfen nur gelöscht werden, wenn keine aktive Bestellung vorliegt. Oder ein anderes Beispiel: Es wird eine E-Mail-Nachricht an die Einkaufabteilung versendet, wenn die Zahl der bevorrateten Artikel in einem Warenwirtschaftssystem unter eine kritische Bestandsgrenze gefallen ist. Der Befehl zur Definition eines Triggers lautet CREATE TRIGGER. Die Ausführungsbefehle werden in einen BEGINEND-Block eingeschlossen. Aufgrund der Heterogenität der einzelnen Datenbanksysteme (dazu auch im nächsten und abschließenden Teil mehr), muss an dieser Stelle auf die Dokumentation des betreffenden Systems verwiesen werden.

Links&Literatur

[1] Dr. Krypczyk, Veikko: „Datenbanken: Grundlagen und Entwurf. Teil 2: Grundoperationen in Datenbanken: Datenbankdefinition, Datensätze“ in Entwickler Magazin 3.2011, S. 68–74.
[2] Beaulieu, A.; Heymann-Reder, D,; Schulten, L.: Einführung in SQL, 2009
[3] Throll, Marcus; Bartosch, Oliver: Einstieg in SQL: Verstehen, einsetzen, nachschlagen, 2010

Unsere Redaktion empfiehlt:

Relevante Beiträge

X
- Gib Deinen Standort ein -
- or -