TCL – Ablaufsteuerung

(Einführung in SQL: TCL - Ablaufsteuerung)

(Einführung in SQL: TCL - Ablaufsteuerung)

Dieses Kapitel gibt eine kurze Einführung in die Transaction Control Language (TCL). Deren Befehle sorgen für die Datensicherheit innerhalb einer Datenbank.

MySQL verfolgt eine „offenere“ Philosophie und arbeitet neben Transaktionen auch mit anderen Sicherungsmaßnahmen. Der Ersteller einer Datenbank muss sich für ein Verfahren entscheiden, kann aber auch danach noch variieren.

Beispiele

Eine SQL-Datenbank speichert Daten in der Regel in verschiedenen Tabellen. Wenn Daten geändert werden sollen, müssen folglich mehrere Tabellen simultan verändert werden. Dazu wäre es nötig, dass die betreffenden Befehle immer gleichzeitig ausgeführt werden. Da der Computer Befehle nur nacheinander ausführen kann, muss sichergestellt sein, dass nicht der eine Befehl ausgeführt wird, während der andere Befehl scheitert.

• Zu einer Überweisung bei der Bank gehören immer zwei Buchungen: die Gutschrift auf dem einen und die Lastschrift auf dem anderen Konto; häufig gehören die Konten zu verschiedenen Banken. Es wäre völlig unerträglich, wenn die Gutschrift ausgeführt würde und die (externe) Lastschrift nicht, weil in diesem Moment die Datenleitung unterbrochen wird.

• Wenn in dem Versicherungsunternehmen der Beispieldatenbank ein neuer Vertrag abgeschlossen wird, gehören dazu mehrere INSERT-Befehle, und zwar in die Tabellen Fahrzeug, Versicherungsnehmer, Versicherungsvertrag. Zuerst müssen Fahrzeug und Versicherungsnehmer gespeichert werden; aber wenn das Speichern des Vertrags „schiefgeht“, hängen die beiden anderen Datensätze nutzlos in der Datenbank herum.

• Wenn dort eine Abteilung ausgelagert wird, werden alle ihre Mitarbeiter gestrichen, weil sie nicht mehr zum Unternehmen gehören. Wie soll verfahren werden, wenn nur ein Teil der DELETE-Befehle erfolgreich war?

• Eine Menge einzelner Befehle (z. B. 1000 INSERTs innerhalb einer Schleife) dauert „ewig lange“.

Solche Probleme können nicht nur durch die Hardware entstehen, sondern auch dadurch, dass parallel andere Nutzer denselben Datenbestand ändern wollen.

Transaktionen

Alle solche Ungereimtheiten werden vermieden, indem SQL-Befehle in Transaktionen zusammengefasst und ausgeführt werden:

• Entweder alle Befehle können ausgeführt werden. Dann wird die Transaktion bestätigt und erfolgreich abgeschlossen.
• Oder (mindestens) ein Befehl kann nicht ausgeführt werden. Dann wird die Transaktion für ungültig erklärt; alle Befehle werden rückgängig gemacht.
• Auch das Problem mit der langen Arbeitszeit von 1000 INSERTs wird vermieden, wenn das DBMS nicht jeden Befehl einzeln prüft und bestätigt, sondern erst alle 1000 am Schluss „am Stück“.

Es gibt verschiedene Arten von Transaktionen. Diese hängen vom DBMS und dessen Version, der Hardware (Einzel- oder Mehrplatzsystem) und dem Datenzugriff (direkt oder über Anwenderprogramme) ab.

• Wenn die Datenbank auf AUTOCOMMIT eingestellt ist, wird jeder SQL-Befehl als einzelne Transaktion behandelt und sofort gültig. (Das wäre die Situation mit der langen Arbeitszeit von 1000 INSERTs.)

• Wenn ein Stapel von Befehlen mit COMMIT bestätigt oder mit ROLLBACK verworfen wird, dann wird mit dem nächsten Befehl implizit eine neue Transaktion begonnen.

• Mit einem ausdrücklichen TRANSACTION-Befehl wird explizit eine neue Transaktion begonnen:

 BEGIN TRANSACTION <TName>;   /* bei MS-SQL   */
 SET   TRANSACTION <TName>;   /* bei Firebird */
 START TRANSACTION;           /* bei MySQL    */
 START TRANSACTION;          /* PostgreSQL 9.6.3 */ 

Eine Transaktion kann mit einem Namen versehen werden. Dies ist vor allem dann nützlich, wenn Transaktionen geschachtelt werden. Außerdem gibt es je nach DBMS noch viele weitere Optionen, mit denen eine Transaktion detailliert gesteuert werden kann.

• Eine Transaktion, die implizit oder explizit begonnen wird, ist ausdrücklich abzuschließen durch COMMIT oder ROLLBACK. Wenn dies vergessen wird, wird die Transaktion erst dadurch beendet, dass die Verbindung mit der Datenbank geschlossen wird.

Transaktion erfolgreich beenden

Eine Transaktion wird mit einem der folgenden Befehle erfolgreich abgeschlossen und beendet:

 COMMIT [ TRANSACTION | WORK ] <TName>;

Die genaue Schreibweise und Varianten müssen in der DBMS-Dokumentation nachgelesen werden.

Dieser Befehl bestätigt alle vorangegangenen Befehle einer Transaktion und sorgt dafür, dass sie „am Stück“ gespeichert werden.

Aufgabe

Auszug aus dem Skript zum Erstellen der Beispieldatenbank:

Firebird-Quelltext

COMMIT;
/* damit wird die vorherige Transaktion abgeschlossen und implizit eine neue Transaktion gestartet */
 
 INSERT INTO Dienstwagen (Kennzeichen, Farbe, Fahrzeugtyp_ID, Mitarbeiter_ID)
   VALUES ('DO-WB 111', 'elfenbein', 16, NULL);
 INSERT INTO Dienstwagen (Kennzeichen, Farbe, Fahrzeugtyp_ID, Mitarbeiter_ID)
   SELECT 'DO-WB 3' || Abteilung_ID || SUBSTRING(Personalnummer FROM 5 FOR 1),
          'gelb', SUBSTRING(Personalnummer FROM 5 FOR 1), ID
     FROM Mitarbeiter
    WHERE Abteilung_ID IN (5, 8)
      AND Ist_Leiter = 'N';

/* damit wird diese Transaktion abgeschlossen */ 
COMMIT;

Sicherungspunkte

Mit dem folgenden Befehl wird eine Transaktion in „sichere“ Abschnitte geteilt:

 SAVEPOINT <SPName>;

Bis zu diesem Sicherungspunkt werden die Befehle auch dann als gültig abgeschlossen, wenn die Transaktion am Ende für ungültig erklärt wird.

Transaktion rückgängig machen

Eine Transaktion wird wie folgt für ungültig erklärt:

 ROLLBACK [ TRANSACTION | WORK ] <TName> [ TO <SPName> ] ;

Damit werden alle Befehle der Transaktion <TName> für ungültig erklärt und rückgängig gemacht. Sofern ein Sicherungspunkt <SPName> angegeben ist, werden die Befehle bis zu diesem Sicherungspunkt für gültig erklärt und erst alle folgenden für ungültig.

Die genaue Schreibweise und Varianten müssen in der DBMS-Dokumentation nachgelesen werden.

Aufgabe

Mit dem folgenden Beispiel werden zu Testzwecken einige Daten geändert und abgerufen; abschließend werden die Änderungen rückgängig gemacht.

update Dienstwagen
   set Farbe = 'goldgelb/violett gestreift'
 where ID >= 14;
select * from Dienstwagen;
ROLLBACK;

Zusammenfassung

In diesem Kapitel lernten Sie die Grundbegriffe von Transaktionen kennen:

• Eine Transaktion wird implizit oder explizit begonnen.
• Eine Transaktion wird mit einem ausdrücklichen Befehl (oder durch Ende der Verbindung) abgeschlossen.
• Mit COMMIT wird eine Transaktion erfolgreich abgeschlossen; die Daten werden abschließend gespeichert.
• Mit ROLLBACK werden die Änderungen verworfen, ggf. ab einem bestimmten SAVEPOINT.

Übungen

Übung 1

Zusammengehörende Befehle

Zur Lösung

Skizzieren Sie die Befehle, die gemeinsam ausgeführt werden müssen, wenn ein neues Fahrzeug mit einem noch nicht registrierten Fahrzeugtyp und Fahrzeughersteller gespeichert werden soll.

Übung 2

Zusammengehörende Befehle

Zur Lösung

Skizzieren Sie die Befehle, die gemeinsam ausgeführt werden müssen, wenn ein neuer Schadensfall ohne weitere beteiligte Fahrzeuge registriert werden soll.

Übung 3

Zusammengehörende Befehle

Zur Lösung

Skizzieren Sie die Befehle, die gemeinsam ausgeführt werden müssen, wenn ein neuer Schaden durch einen „Eigenen Kunden“ gemeldet wird; dabei sollen ein zweiter „Eigener Kunde“ sowie ein „Fremdkunde“ einer bisher nicht gespeicherten Versicherungsgesellschaft beteiligt sein. Erwähnen Sie dabei auch den Inhalt des betreffenden Eintrags.

Übung 4

Transaktionen

Zur Lösung

Welche der folgenden Maßnahmen starten immer eine neue Transaktion, welche unter Umständen, welche sind unzulässig?

1. das Herstellen der Verbindung zur Datenbank
2. der Befehl START TRANSACTION;
3. der Befehl SET TRANSACTION ACTIVE;
4. der Befehl SAVEPOINT <name> wird ausgeführt
5. der Befehl ROLLBACK wird ausgeführt

Übung 5

Transaktionen

Zur Lösung

Welche der folgenden Maßnahmen beenden immer eine Transaktion, welche unter Umständen, welche sind unzulässig?

1. das Schließen der Verbindung zur Datenbank
2. der Befehl END TRANSACTION;
3. der Befehl SET TRANSACTION INACTIVE;
4. der Befehl SAVEPOINT <name> wird ausgeführt
5. der Befehl ROLLBACK wird ausgeführt

Lösungen

Lösung zu Übung 1

Zusammengehörende Befehle

Zur Übung

• INSERT INTO Fahrzeughersteller
• INSERT INTO Fahrzeugtyp
• INSERT INTO Fahrzeug

Lösung zu Übung 2

Zusammengehörende Befehle

Zur Übung

• INSERT INTO Schadensfall
• INSERT INTO Zuordnung_SF_FZ

Lösung zu Übung 3

Zusammengehörende Befehle

Zur Übung

• INSERT INTO Schadensfall
• INSERT INTO Versicherungsgesellschaft /* für den zusätzlichen Fremdkunden */
• INSERT INTO Versicherungsnehmer /* für den zusätzlichen Fremdkunden */
• INSERT INTO Fahrzeug /* für den zusätzlichen Fremdkunden */
• INSERT INTO Zuordnung_SF_FZ /* für den zusätzlichen Fremdkunden */
• INSERT INTO Zuordnung_SF_FZ /* für den beteiligten Eigenen Kunden */
• INSERT INTO Zuordnung_SF_FZ /* für den Eigenen Kunden laut Schadensmeldung */

Lösung zu Übung 4

Transaktionen

Zur Übung

1. ja, sofern AUTOCOMMIT festgelegt ist
2. ja, aber nur, wenn das DBMS diese Variante vorsieht (wie MySQL)
3. ja, aber nur, wenn das DBMS diese Variante vorsieht (wie Firebird); denn das Wort „ACTIVE“ wird nicht als Schlüsselwort, sondern als Name der Transaction interpretiert
4. nein, das ist nur ein Sicherungspunkt innerhalb einer Transaktion
5. ja, nämlich implizit für die folgenden Befehle

Lösung zu Übung 5

Transaktionen

Zur Übung

1. ja, und zwar immer
2. nein, diesen Befehl gibt es nicht
3. nein, dieser Befehl ist unzulässig; wenn das DBMS diese Variante kennt (wie Firebird), dann ist es der Start einer Transaktion namens „INACTIVE“
4. teilweise, das ist ein Sicherungspunkt innerhalb einer Transaktion und bestätigt die bisherigen Befehle, setzen aber dieselbe Transaktion fort
5. ja, nämlich explizit durch Widerruf aller Befehle