Die Versionen 3.23-max und alle Versionen ab 4.0 von MySQL
Server unterstützen Transaktionen mit transaktionalen
InnoDB
- und
BDB
-Speicher-Engines.
InnoDB
bietet
vollständige
ACID
-Konformität. Siehe auch
Kapitel 14, Speicher-Engines und Tabellentypen. Informationen zu
InnoDB
-spezifischen Unterschieden zu
Standard-SQL bezüglich der Behandlung von Transaktionsfehlern
finden Sie in Abschnitt 14.2.15, „InnoDB
-Fehlerbehandlung“.
Die anderen nichttransaktionalen Speicher-Engines in MySQL
Server (wie etwa MyISAM
) folgen einem
anderen Muster der Datenintegrität, welches „atomare
Operationen“ genannt wird. Aus transaktionaler Sicht
arbeiten MyISAM
-Tabellen quasi immer im
Modus AUTOCOMMIT=1
. Atomare Operationen
bieten häufig vergleichbare Integrität bei besserer
Leistung.
Da MySQL Server beide Muster unterstützt, können Sie selbst entscheiden, ob Ihre Anwendungen besser mit der Geschwindigkeit atomarer Operationen oder der Verwendung der Transaktionsfunktionalität bedient sind. Diese Auswahl lässt sich pro Tabelle treffen.
Wie bereits angemerkt, fällt die Entscheidung zwischen
transaktionalen und nichttransaktionalen Speicher-Engines in
erster Linie aufgrund von Leistungsanforderungen.
Transaktionale Tabellen haben einen wesentlich höheren Bedarf
an Speicher- und Festplattenkapazität und benötigen zudem
mehr Prozessorleistung. Andererseits bieten transaktionale
Speicher-Engines wie InnoDB
auch viele
wesentliche Vorteile. Der modulare Aufbau von MySQL Server
erlaubt die gleichzeitige Nutzung verschiedener
Speicher-Engines zur Erfüllung unterschiedlicher Bedürfnisse
und für optimale Leistung in allen Situationen.
Wie aber nutzen Sie nun die Funktionen von MySQL Server zur
Aufrechterhaltung einer strikten Integrität auch bei
nichttransaktionalen MyISAM
-Tabellen, und
wie stehen diese Funktionen im Vergleich mit transaktionalen
Speicher-Engines da?
Wenn Ihre Anwendungen so geschrieben sind, dass sie in
kritischen Situationen von der Fähigkeit zum Aufruf von
ROLLBACK
anstelle von
COMMIT
abhängen, dann sind
Transaktionen praktischer. Mit Transaktionen lässt sich
auch sicherstellen, dass nicht abgeschlossene Updates oder
beschädigende Aktionen nicht in die Datenbank übertragen
werden; der Server hat die Möglichkeit, einen
automatischen Rollback durchzuführen, und Ihre Datenbank
ist gerettet.
Wenn Sie nichttransaktionale Tabellen verwenden, gestattet Ihnen MySQL Server in fast allen Fällen die Lösung potenzieller Probleme durch Integrierung einfacher Prüfungen vor Updates und Ausführung simpler Skripten, die die Datenbank auf Inkonsistenzen untersuchen und diese ggf. automatisch reparieren bzw. Warnmeldungen anzeigen. Beachten Sie, dass Sie Tabellen normalerweise mithilfe der MySQL-Logdatei oder einer zusätzlichen Logdatei reparieren, ohne dass die Datenintegrität hiervon beeinträchtigt würde.
In der Mehrzahl der Fälle lassen sich kritische
transaktionale Updates so umformulieren, dass sie atomar
sind. Allgemein gesprochen lassen sich alle
Integritätsprobleme, die sich mit Transaktionen lösen
lassen, mit LOCK TABLES
oder atomaren
Updates beseitigen. Hierdurch ist sichergestellt, dass ein
Vorgang serverseitig nicht abgebrochen wird – ein
Problem, welches bei transaktionalen Datenbanksystemen
häufig auftritt.
Um unabhängig davon, ob Sie transaktionale Tabellen verwenden, mit MySQL Server sicher arbeiten zu können, müssen Sie nur über Backups verfügen und die binäre Protokollierung aktiviert haben. Sind diese Voraussetzungen erfüllt, dann können Sie wie bei anderen transaktionalen Datenbanksystemen einen fehlerlosen Zustand jederzeit wiederherstellen. Sicherungskopien sollten immer vorhanden sein – egal, welches Datenbanksystem Sie verwenden.
Das transaktionale Muster hat Vor- und Nachteile. Viele Benutzer und Anwendungsentwickler wissen die Einfachheit zu schätzen, sich um Probleme „herumzuprogrammieren“, bei denen ein Abbruch erforderlich ist oder erforderlich zu sein scheint. Doch sogar dann, wenn Sie mit dem atomaren Operationsmuster noch nicht oder mit Transaktionen recht gut vertraut sind, sollten Sie den Geschwindigkeitsvorteil berücksichtigen, den nicht transaktionale Tabellen bieten; immerhin liegt die Verarbeitungsgeschwindigkeit drei- bis fünfmal höher als bei optimal programmierten transaktionalen Tabellen.
In Situationen, in denen die Integrität eine entscheidende
Rolle spielt, bietet MySQL Server die Zuverlässigkeit und
Integrität transaktionaler Tabellen auch für
nichttransaktionale Tabellen. Wenn Sie Tabellen mit
LOCK TABLES
sperren, werden alle Updates so
lange angehalten, bis die Integritätsprüfungen durchgeführt
wurden. Wenn Sie eine READ LOCAL
-Sperre (im
Gegensatz zur Schreibsperre) für eine Tabelle setzen, die
gleichzeitiges Einfügen am Tabellenende gestattet, ist das
Lesen ebenso möglich wie das Einfügen durch andere Clients.
Der Client, für den die Lesesperre gesetzt ist, sieht die neu
hinzugefügten Datensätze erst, wenn die Sperre aufgehoben
wird. Mit INSERT DELAYED
können Sie
Einfügungen schreiben, die in eine lokale Warteschlange
aufgenommen werden, bis die Sperren aufgehoben wurden. In
diesem Fall muss der Client nicht warten, bis der
Einfügevorgang abgeschlossen ist. Siehe auch
Abschnitt 7.3.3, „Gleichzeitige Einfügevorgänge“, und
Abschnitt 13.2.4.2, „INSERT DELAYED
“.
„Atomar“, wie wir es hier verstehen, hat nichts mit Zauberei zu tun. Damit ist lediglich gemeint, dass gewährleistet ist, dass, solange ein bestimmtes Update durchgeführt wird, dieses von keinem anderen Benutzer unterbrochen werden kann und dass es keinen automatischen Rollback gibt (was bei transaktionalen Tabellen immer möglich ist, wenn Sie nicht mit extremer Sorgfalt vorgehen). MySQL Server garantiert außerdem, dass es nicht zur Dirty Reads kommt.
Die folgende Liste erläutert ein paar Methoden für die Arbeit mit nichttransaktionalen Tabellen:
Schleifen, die Transaktionen erfordern, lassen sich
normalerweise mithilfe von LOCK TABLES
kopieren. Sie benötigen keine Cursor, um Datensätze
direkt zu aktualisieren.
Um die Verwendung von ROLLBACK
zu
umgehen, können Sie die folgende Strategie verwenden:
Sperren Sie alle Tabellen, auf die Sie zugreifen
wollen, mit LOCK TABLES
.
Prüfen Sie die Bedingungen, die wahr sein müssen, bevor Sie das Update durchführen.
Sind alle Bedingungen erfüllt, dann führen Sie das Update durch.
Heben Sie die Sperrungen Ihrer Tabellen mit
UNLOCK TABLES
auf.
Wenn auch nicht immer, so funktioniert dies in der Regel doch wesentlich schneller als die Verwendung von Transaktionen mit möglichen Rollbacks. Die einzige Situation, die mit dieser Lösung nicht bereinigt werden kann, liegt vor, wenn jemand die Threads während eines Updates beendet. In diesem Fall werden alle Sperren aufgehoben, aber unter Umständen wurden manche Updates nicht ausgeführt.
Sie können auch Funktionen zur Aktualisierung von Datensätzen in einer einzigen Operation verwenden. Mithilfe der folgenden Methode erhalten Sie eine sehr effiziente Anwendung:
Ändern Sie die Spalten relativ zum aktuellen Wert.
Aktualisieren Sie nur solche Spalten, die tatsächlich geändert wurden.
Wenn wir beispielsweise Kundendaten aktualisieren, dann
aktualisieren wir nur diejenigen Kundendaten, die sich
auch geändert haben, und überprüfen nachfolgend
lediglich, ob sich geänderte Daten oder Daten, die von
den geänderten Daten abhängen, verglichen mit dem
ursprünglichen Datensatz geändert haben. Die
Überprüfung auf geänderte Daten erfolgt mit der
WHERE
-Klausel in der
UPDATE
-Anweisung. Wurde der Datensatz
nicht aktualisiert, dann erhält der Client folgende
Mitteilung: „Some of the data you have changed has
been changed by another user.“ („Einige
Daten, die Sie geändert haben, wurden von einem anderen
Benutzer geändert.“) Dann werden der alte und der
neue Datensatz in einem Fenster zu Vergleichszwecken
angezeigt, damit der Benutzer entscheiden kann, welche
Version des Kundendatensatzes zukünftig verwendet werden
soll.
Auf diese Weise erhalten wir etwas, dass der Sperrung
einer Spalte ähnelt, tatsächlich aber noch besser ist,
da nur einige Spalten mithilfe von Werten aktualisiert
werden, die relativ zu den aktuellen Werten sind. Dies
wiederum bedeutet, dass eine typische
UPDATE
-Anweisung etwa so aussieht:
UPDATE tablename SET pay_back=pay_back+125; UPDATE customer SET customer_date='current_date', address='new address', phone='new phone', money_owed_to_us=money_owed_to_us-125 WHERE customer_id=id AND address='old address' AND phone='old phone';
Dies ist sehr effizient und funktioniert auch dann, wenn
ein anderer Client die Werte in den Spalten
pay_back
oder
money_owed_to_us
geändert hat.
In vielen Fällen wünschten sich Benutzer LOCK
TABLES
oder ROLLBACK
, um
eindeutige Bezeichner verwalten zu können. Dies lässt
sich jedoch weitaus effizienter ohne Sperrung oder
Rollback realisieren, indem man eine
AUTO_INCREMENT
-Spalte und entweder die
SQL-Funktion LAST_INSERT_ID()
oder die
C-API-Funktion mysql_insert_id()
verwendet. Siehe auch
Abschnitt 12.10.3, „Informationsfunktionen“, und
Abschnitt 24.2.3.36, „mysql_insert_id()
“.
Sie können die Notwendigkeit, eine Sperrung auf
Datensatzebene vorzunehmen, im Allgemeinen durch
entsprechende Programmierung umgehen. In manchen
Situationen jedoch ist diese Sperrung erforderlich, und
InnoDB
-Tabellen unterstützen sie auch.
Andernfalls können Sie bei
MyISAM
-Tabellen eine Flag-Spalte
verwenden und etwa Folgendes machen:
UPDATE tbl_name
SET row_flag=1 WHERE id=ID;
MySQL gibt 1
als Anzahl der betroffenen
Datensätze zurück, wenn der Datensatz gefunden wurde und
row_flag
im ursprünglichen Datensatz
nicht 1
war. Sie können sich das so
vorstellen, als ob MySQL Server die obige Anweisung wie
folgt geändert hätte:
UPDATE tbl_name
SET row_flag=1 WHERE id=ID AND row_flag <> 1;
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.