Das statische (Festlängen-) Format ist für MyISAM-Tabellen voreingestellt. Es wird verwendet, wenn die Tabelle keine Spalten mit variabler Länge (VARCHAR-, VARBINARY-, BLOB- oder TEXT-Spalten) enthält. Jede Zeile wird dann mit einer festgelegten Anzahl von Bytes gespeichert.

Von den drei Speicherformaten, die MyISAM unterstützt, ist das statische Format das einfachste und sicherste (für Schäden am wenigsten anfällige). Außerdem bietet es wegen der Leichtigkeit, mit der die Zeilen der Datendatei auf der Festplatte gefunden werden, auch den schnellsten Festplattenzugriff. Um eine Zeile nach der Index-Zeilennummer nachzuschlagen, multiplizieren Sie die Nummer der Zeile mit ihrer Länge, um ihre Position zu ermitteln. Darüber hinaus ist es beim Durchsuchen einer Tabelle sehr einfach, mit jedem Festplattenzugriff eine konstante Anzahl Zeilen zu lesen.

Die Sicherheit erweist sich, wenn Ihr Computer abstürzt, während der MySQL-Server eine MyISAM-Datei mit festgelegtem Format schreibt. In einem solchen Fall kann myisamchk ganz leicht feststellen, wo die Zeilen beginnen und enden. Normalerweise ist es dadurch in der Lage, alle Zeilen außer der einen, die nur teilweise geschrieben wurde, zurückzugewinnen. Beachten Sie, dass MyISAM-Tabellenindizes immer anhand der Datenzeilen rekonstruiert werden können.

Kennzeichen von statischen Tabellen:

Ein dynamisches Speicherformat wird verwendet, wenn eine MyISAM-Tabelle Spalten variabler Länge enthält (VARCHAR-, VARBINARY-, BLOB- oder TEXT-Spalten), oder wenn die Tabelle mit der Tabellenoption ROW_FORMAT=DYNAMIC angelegt wurde.

Das dynamische Format ist ein wenig komplizierter als das statische, da jede Zeile einen Header mit einer Längenangabe besitzt. Eine Zeile kann fragmentiert (an nicht-benachbarten Orten gespeichert) werden, wenn sie aufgrund eines Updates länger wird.

Mit OPTIMIZE TABLE oder myisamchk -r lassen sich Tabellen defragmentieren. Wenn in einer Tabelle, die auch Spalten variabler Länge besitzt, Spalten mit festgelegter Länge vorliegen, die oft angesprochen oder geändert werden, so empfiehlt es sich, die Spalten mit variabler Länge in andere Tabellen auszulagern, um Fragmentierung zu verhindern.

Kennzeichen von dynamischen Tabellen:

Das komprimierte Speicherformat ist ein nur-lesbares Format, das mit myisampack angelegt wird. Komprimierte Tabellen lassen sich mit myisamchk auch wieder dekomprimieren.

Kennzeichen von komprimierten Tabellen:

Obwohl das Tabellenformat MyISAM sehr zuverlässig ist (alle Änderungen, die eine SQL-Anweisung an einer Tabelle vornimmt, werden geschrieben, ehe die Anweisung zurückkehrt), können dennoch unter folgenden Umständen Tabellen beschädigt werden:

Die folgenden Symptome sind typisch für eine beschädigte Tabelle:

Mit der CHECK TABLE-Anweisung können Sie die Integrität einer MyISAM-Tabelle überprüfen und mit REPAIR TABLE können Sie sie reparieren, wenn sie beschädigt ist. Wenn mysqld nicht läuft, können Sie eine Tabelle auch mit dem Befehlmyisamchk prüfen oder reparieren Siehe hierzu auch Abschnitt 13.5.2.3, „CHECK TABLE“, Abschnitt 13.5.2.6, „REPAIR TABLE“ und Abschnitt 8.2, „myisamchk — Hilfsprogramm für die Tabellenwartung von MyISAM“.

Werden Ihre Tabellen häufig beschädigt, so sollten Sie nach den Ursachen forschen. Am wichtigsten ist es, festzustellen, ob die Tabelle infolge eines Server-Absturzes beschädigt wurde. Das können Sie leicht daran erkennen, dass im Fehler-Log eine restarted mysqld-Nachricht jüngeren Datums gespeichert ist. Wenn ja, dann wurde die Tabelle wahrscheinlich durch den Absturz des Servers beschädigt. Andernfalls kann der Schaden auch im normalen Betrieb aufgetreten sein. Das wäre dann ein Bug. Versuchen Sie in diesem Fall, einen reproduzierbaren Testfall zu erstellen, der das Problem demonstriert. Siehe auch Abschnitt A.4.2, „Was zu tun ist, wenn MySQL andauernd abstürzt“ und Abschnitt E.1.6, „Erzeugen eines Testfalls, wenn Sie Tabellenbeschädigung feststellen“.

Jede MyISAM-Indexdatei (.MYI-Datei) besitzt im Header einen Zähler, an dem sich erkennen lässt, ob eine Tabelle ordnungsgemäß geschlossen wurde. Liefert CHECK TABLE oder myisamchk folgende Warnung, so bedeutet dies, dass der Zähler nicht mehr synchron läuft:

clients are using or haven't closed the table properly

Diese Warnung bedeutet zwar nicht unbedingt, dass die Tabelle beschädigt ist, aber Sie sollten zumindest eine Überprüfung vornehmen.

Der Zähler funktioniert folgendermaßen:

Anders ausgedrückt: Der Zähler kann unter folgenden Bedingungen nicht mehr synchron sein:

Sie können jede InnoDB-Tabelle und ihre Indizes in ihrer eigenen Datei speichern. Dieses Feature nennt man „Multi-Tablespaces“ da im Endeffekt jede Tabelle ihren eigenen Tablespace bekommt.

Multi-Tablespaces sind praktisch für Benutzer, die bestimmte Tabellen auf separate physikalische Platten verlagern oder Backups einzelner Tabellen rasch wiederherstellen möchten, ohne die Arbeit mit den übrigen InnoDB-Tabellen zu unterbrechen.

Aktivieren Sie Multi-Tablespaces mit folgender Zeile im [mysqld]-Abschnitt von my.cnf:

[mysqld]
innodb_file_per_table

Nach dem Server-Neustart speichert InnoDB jede neu erzeugte Tabelle in einer eigenen Datei tbl_name.ibd in dem Datenbankverzeichnis, zu dem die Tabelle gehört. Das ähnelt dem Vorgehen der MyISAM-Speicher-Engine, doch diese spaltet die Tabellen in eine Datendatei tbl_name.MYD und eine Indexdatei tbl_name.MYI. Mit InnoDB werden Daten und Indizes gemeinsam in der.ibd-Datei gespeichert. Die tbl_name.frm-Datei wird wie üblich angelegt.

Wenn Sie die innodb_file_per_table-Zeile aus my.cnf löschen und den Server neu starten, erzeugt InnoDB die Tabellen wieder in Shared Tablespace-Dateien.

innodb_file_per_table wirkt sich nur auf eine einzelne Tabellenerzeugung aus, und beeinflusst nicht den Zugriff auf bestehende Tabellen. Wenn Sie den Server mit dieser Option starten, werden neue Tabellen mit .ibd-Dateien angelegt, aber alte liegen immer noch im Shared Tablespace. Entfernen Sie die Option wieder und starten dann den Server neu, werden die neuen Tabellen im Shared Tablespace angelegt, aber Tabellen, die mit Multi-Tablespaces angelegt wurden, bleiben weiterhin zugänglich.

InnoDB benötigt immer den Shared Tablespace, da es sein internes Data Dictionary und seine Undo-Logs dort speichert. Die .ibd-Dateien reichen InnoDB zum Funktionieren nicht aus.

Hinweis: Sie können .ibd-Dateien genau wie MyISAM-Tabellendateien nach Belieben zwischen Datenbankverzeichnissen hin- und herschieben, weil die im Shared Tablespace von InnoDB gespeicherte Tabellendefinition den Datenbanknamen enthält und weil InnoDB die Konsistenz von Transaktions-IDs und Lognummern beibehalten muss.

Um eine .ibd-Datei und die zugehörige Tabelle von einer Datenbank in eine andere zu verlagern, verwenden Sie eine RENAME TABLE-Anweisung:

RENAME TABLE db1.tbl_name TO db2.tbl_name;

Wenn Sie über ein „sauberes“ Backup einer .ibd-Datei verfügen, können Sie diese in ihrer angestammten MySQL-Installation folgendermaßen wiederherstellen:

Ein „sauberes“ Backup einer .ibd-Datei bedeutet in diesem Zusammenhang:

Mit folgender Methode können Sie eine saubere .ibd-Backup-Datei anlegen:

Eine andere Möglichkeit, an eine saubere Kopie einer .ibd-Datei zu kommen, ist die Verwendung eines kommerziellen InnoDB Hot Backup-Tools:

Sie können auch rohe Festplattenpartitionen für die Datendateien im Shared Tablespace verwenden. So können sie ungepufferte E/A-Zugriffe ohne Dateisystem-Overhead auf Windows und einigen Unix-Systemen implementieren und die Performance dadurch steigern.

Wenn Sie eine neue Datendatei anlegen, müssen Sie in innodb_data_file_path das Schlüsselwort newraw direkt hinter die Größe der Datendatei setzen. Die Partition muss mindestens die angegebene Größe haben. Beachten Sie, dass 1MB in InnoDB 1024 × 1024 Bytes sind, während 1MB in Festplattenspezifikationen normalerweise 1.000.000 Bytes bedeutet.

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

Wenn Sie den Server das nächste Mal starten, bemerkt InnoDB das Schlüsselwort newraw und initialisiert die neue Partition. Sie dürfen jetzt aber noch keine InnoDB-Tabellen ändern oder anlegen, sonst reinitialisiert InnoDB beim nächsten Serverstart die Partition und Ihre Änderungen gehen verloren. (Als Sicherheitsmaßnahme hindert InnoDB die Benutzer daran, irgendwelche Daten zu ändern, wenn eine Partition mit newraw definiert wurde.)

Nachdem InnoDB die neue Partition initialisiert hat, halten Sie den Server an und ändern newraw in der Datendateispezifikation in raw um:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw

Wenn Sie nun den Server erneut starten, erlaubt InnoDB auch Änderungen.

Auf Windows können Sie eine Festplattenpartition folgendermaßen als Datendatei zuweisen:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Gnewraw

Das //./ entspricht beim Zugriff auf physikalische Platten der Windows-Syntax \\.\.

Wenn Sie rohe Festplattenpartitionen benutzen, achten Sie bitte darauf, dass ihre Berechtigungseinstellungen dem vom MySQL-Server benutzten Konto auch Lese- und Schreiboperationen gestatten.

Wenn InnoDB während einer Dateioperation einen Betriebssystemfehler ausgibt, hat das Problem in der Regel eine der folgenden Ursachen:

Wenn InnoDB beim Versuch scheitert, seinen Tablespace oder seine Logdateien zu initialisieren, sollten Sie alle von InnoDB erzeugten Dateien löschen. Dazu gehören alle ibdata-Dateien und alle ib_logfile-Dateien. Falls Sie bereits InnoDB-Tabellen angelegt haben, müssen Sie auch die .frm-Dateien (und, wenn Sie Multi-Tablespaces benutzen, die .ibd-Dateien) aus den MySQL-Datenbankverzeichnissen löschen. Danach können Sie erneut versuchen, die InnoDB-Datenbank anzulegen. Am besten starten Sie den MySQL-Server von einer Kommandozeile, um erkennen zu können, was geschieht.

Standardmäßig startet jeder Client, der sich mit dem MySQL-Server verbindet, mit eingeschaltetem Autocommit-Modus. Das bedeutet, dass jede SQL-Anweisung direkt bei der Ausführung automatisch in der Datenbank festgeschrieben wird. Um Transaktionen zu verwenden, die aus mehreren Anweisungen bestehen, schalten Sie Autocommit mit der SQL-Anweisung SET AUTOCOMMIT = 0 aus und verwenden COMMIT und ROLLBACK, um Ihre Transaktionen zu committen oder zurückzurollen. Wenn Sie Autocommit eingeschaltet lassen möchten, können Sie Ihre Transaktionen zwischen START TRANSACTION und entweder COMMIT oder ROLLBACK einschließen. Das folgende Beispiel zeigt zwei Transaktionen: Die erste wird committet und die zweite zurückgerollt.

shell> mysql test

mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A    | B      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

In APIs wie PHP, Perl DBI, JDBC, ODBC oder der Standardschnittstelle für C-Aufrufe von MySQL können Sie Anweisungen zur Transaktionssteuerung wie etwa COMMIT wie alle anderen SQL-Anweisungen, beispielsweise SELECT oder INSERT, als Strings an den MySQL-Server senden. Manche APIs bieten auch eigene, spezielle Transaktionsfunktionen oder -methoden für Commit und Rollback.

Wichtig: Bitte konvertieren Sie keine Systemtabellen von MySQL in der mysql-Datenbank (wie etwa user oder host) in den Typ InnoDB. Diese Operation wird nicht unterstützt. Die Systemtabellen müssen immer den Typ MyISAM haben.

Wenn Sie alle Tabellen (außer den Systemtabellen) als InnoDB-Tabellen anlegen möchten, fügen Sie einfach die Zeile default-storage-engine=innodb in den [mysqld]-Abschnitt Ihrer Serveroptionsdatei ein.

InnoDB kennt im Gegensatz zu MyISAM keine spezielle Optimierung für eine separate Indexerstellung. Daher lohnt es sich nicht, zuerst die Tabellen zu exportieren und importieren und danach die Indizes anzulegen. Der schnellste Weg, eine Tabelle in InnoDB zu ändern, besteht darin, die Einfügeoperationen direkt auf der InnoDB-Tabelle vorzunehmen. Verwenden Sie also ALTER TABLE … ENGINE=INNODB oder erzeugen Sie eine leere InnoDB-Tabelle mit denselben Definitionen und fügen Sie darin die Zeilen mit INSERT INTO … SELECT * FROM … ein.

Wenn Sie UNIQUE-Constraints auf Sekundärschlüsseln haben, können Sie einen Tabellenimport beschleunigen, indem Sie die Eindeutigkeitsprüfungen während der Importoperation vorübergehend abschalten:

SET UNIQUE_CHECKS=0;
... import operation ...
SET UNIQUE_CHECKS=1;

Bei großen Tabellen spart das eine Menge Festplattenzugriffe, da InnoDB dann seinen Insert-Puffer nutzen kann, um die Sekundärindexeinträge in einer Batch-Operation zu schreiben.

Um eine bessere Kontrolle über den Einfügungsprozess zu haben, ist es manchmal besser, Daten einer großen Tabelle in Stücken einzufügen:

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

Wenn alle Datensätze eingefügt wurden, können Sie die Tabellen umbenennen.

Während der Konvertierung großer Tabellen sollten Sie den InnoDB-Bufferpool vergrößern, damit weniger Plattenzugriffe erforderlich sind. Reservieren Sie jedoch nicht mehr als 80% des physikalischen Arbeitsspeichers. Sie können auch die InnoDB-Logdateien vergrößern.

Achten Sie darauf, dass der Tablespace nicht vollläuft: InnoDB-Tabellen brauchen auf der Festplatte viel mehr Speicherplatz als MyISAM-Tabellen. Wenn einer ALTER TABLE-Operation der Platz ausgeht, startet sie ein Rollback, und das kann Stunden dauern, wenn es an die Festplatte gebunden ist. Bei Inserts nutzt InnoDB den Insert-Puffer, um Sekundärindexeinträge in Batches mit dem Index zusammenzuführen. Das spart viele Schreiboperationen auf der Festplatte. Da für den Rollback kein solcher Mechanismus existiert, kann dieser 30-mal länger als die Einfügeoperationen dauern.

Wenn ein Rollback-Prozess außer Kontrolle gerät und Sie keine wertvollen Daten in Ihrer Datenbank haben, ist es manchmal ratsam, den Datenbankprozess anzuhalten, anstatt Millionen von Lese/Schreiboperationen auf der Festplatte abzuwarten. Den gesamten Vorgang können Sie unter Abschnitt 14.2.8.1, „Erzwingen einer InnoDB-Wiederherstellung (Recovery)“ nachlesen.

Wenn Sie eine AUTO_INCREMENT-Spalte für eine InnoDB-Tabelle definieren, enthält der Tabellen-Handle im InnoDB-Data Dictionary einen speziellen Zähler namens Auto-Increment-Zähler, der zur Zuweisung neuer Werte zu dieser Spalte benutzt wird. Dieser Zähler wird nur im Arbeitsspeicher und nicht auf der Festplatte gespeichert.

InnoDB initialisiert den Auto-Increment-Zähler für eine Tabelle T, die eine AUTO_INCREMENT-Spalte namens ai_col enthält, wie folgt: Nach dem Serverstart führt InnoDB für die erste Einfügung in die Tabelle T das Äquivalent folgender Anweisung aus:

SELECT MAX(ai_col) FROM T FOR UPDATE;

InnoDB inkrementiert den von der Anweisung abgerufenen Wert um eins und weist ihn der Spalte und dem Auto-Increment-Zähler für die Tabelle zu. Ist die Tabelle leer, verwendet InnoDB den Wert 1. Wenn ein Benutzer eine SHOW TABLE STATUS-Anweisung gibt, die Ausgabe für die Tabelle T anzeigt, und der Auto-Increment-Zähler noch nicht initialisiert wurde, nimmt InnoDB die Initialisierung vor, inkrementiert aber nicht den Wert und speichert ihn nicht für spätere Einfügeoperationen. Beachten Sie, dass für diese Initialisierung eine normale Schreiboperation mit exklusiver Sperre auf der Tabelle ausgeführt wird und die Sperre bis zum Ende der Transaktion aufrecht erhalten bleibt.

Genauso geht InnoDB vor, wenn ein Auto-Increment-Zähler für eine neu erzeugte Tabelle initialisiert wird.

Wenn ein Auto-Increment-Zähler initialisiert wurde und ein Benutzer nicht ausdrücklich einen Wert für eine AUTO_INCREMENT-Spalte angibt, inkrementiert InnoDB den Zähler um eins und weist der Spalte den neuen Wert zu. Wenn der Benutzer eine Zeile einfügt, die den Spaltenwert explizit angibt, und dieser Wert größer als der aktuelle Zähler ist, wird der Zähler auf den angegebenen Spaltenwert gesetzt.

Eventuell treten in der Abfolge der Werte einer AUTO_INCREMENT-Spalte Lücken auf, wenn Sie Transaktionen zurückrollen, die Nummern mithilfe des Zählers zugewiesen haben.

Wenn der Benutzer in einem INSERT den Wert NULL oder 0 für die AUTO_INCREMENT-Spalte angibt, behandelt InnoDB die betreffende Zeile, als wäre überhaupt kein Wert vorhanden, und generiert einen neuen Wert für sie.

Das Verhalten des Auto-Increment-Mechanismus ist nicht definiert, wenn ein Benutzer der Spalte einen negativen Wert zuweist, oder wenn der Wert die größte Ganzzahl übersteigt, die in dem angegebenen Integertyp gespeichert werden kann.

InnoDB benutzt für den Zugriff auf den Auto-Increment-Zähler eine spezielle AUTO-INC-Tabellensperre, die bis zum Ende der laufenden SQL-Anweisung (nicht der Transaktion) gehalten wird. Die spezielle Strategie zum Aufheben der Sperre wurde eingefügt, um die Nebenläufigkeit von Einfügeoperationen in Tabellen mit AUTO_INCREMENT-Spalten zu verbessern. Es können nicht zwei Transaktionen zugleich auf derselben Tabelle eine AUTO-INC-Sperre halten.

InnoDB nutzt den speicherresidenten Auto-Increment-Zähler so lange, wie der Server läuft. Wird der Server angehalten und neu gestartet, initialisiert InnoDB den Zähler bei der ersten INSERT-Operation auf der Tabelle neu, wie oben bereits beschrieben.

InnoDB unterstützt die Tabellenoption AUTO_INCREMENT = N in CREATE TABLE und ALTER TABLE-Anweisungen, um den Anfangswert des Zählers einzustellen oder seinen laufenden Wert zu ändern. Die Wirkung dieser Option wird durch einen Neustart des Servers aufgehoben, und zwar aus Gründen, die bereits weiter oben dargestellt wurden.

InnoDB unterstützt auch Fremdschlüssel-Constraints, die in InnoDB mit folgender Syntax definiert werden:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Für Fremdschlüsseldefinitionen gelten folgende Bedingungen:

InnoDB weist jede INSERT- oder UPDATE-Operation zurück, die versucht, einen Fremdschlüsselwert in einer Kindtabelle anzulegen, wenn kein passender Schlüsselwert in der Elterntabelle vorhanden ist. Was InnoDB mit einer INSERT- oder UPDATE-Operation anfängt, die versucht, in der Elterntabelle einen Schlüsselwert zu ändern oder zu löschen, zu dem in der Kindtabelle passende Zeilen vorhanden sind, hängt davon ab, welche Referenzaktion in den Teilklauseln ON UPDATE und ON DELETE der FOREIGN KEY-Klausel angegeben ist. Wenn der Benutzer versucht, in der Elterntabelle eine Zeile zu ändern oder zu löschen, zu der in der Kindtabelle eine oder mehr passende Zeilen vorhanden sind, bietet InnoDB fünf mögliche Optionen:

Beachten Sie, dass InnoDB Fremdschlüsselreferenzen in einer Tabelle unterstützt. In solchen Fällen sind „Datensätze der Kindtabelle“ in Wirklichkeit abhängige Datensätze in derselben Tabelle.

Da InnoDB Indizes auf Fremdschlüsseln und referenzierten Schlüsseln verlangt, können Fremdschlüsselprüfungen schnell durchgeführt werden und erfordern keinen Tabellen-Scan. Der Index auf den Fremdschlüsseln wird automatisch angelegt. Das war in manchen älteren Versionen anders, wo Indizes explizit angelegt werden mussten, da sonst keine Fremdschlüssel-Constraints angelegt werden konnten.

Zusammengehörige Spalten im Fremdschlüssel und referenzierten Schlüssel müssen in InnoDB ähnliche Datentypen haben, damit sie sich ohne Typkonvertierung vergleichen lassen. Die Größe und das Vorzeichen von Integer-Typen müssen gleich sein. Die Länge von String-Typen muss nicht unbedingt identisch sein. Wenn Sie SET NULL verlangen, dürfen die Spalten der Kindtabelle nicht als NOT NULL deklariert sein.

Wenn MySQL die Fehlernummer 1005 aus einer CREATE TABLE-Anweisung meldet und die Fehlermeldung sich auf Fehlernummer 150 bezieht, schlug die Tabellenerstellung fehl, weil ein Fremdschlüssel-Constraint nicht wohlgeformt war. Wenn ein ALTER TABLE scheitert und auf Fehlernummer 150 verweist, bedeutet dies, dass eine Fremdschlüsseldefinition für die geänderte Tabelle nicht korrekt war. Die Anweisung SHOW ENGINE INNODB STATUS zeigt eine detaillierte Erklärung des letzten InnoDB-Fremdschlüsselfehlers im Server an.

Hinweis: InnoDB prüft Fremdschlüssel-Constraints nicht auf Fremdschlüsseln oder referenzierten Schlüsseln, die eine NULL-Spalte enthalten.

Hinweis: Trigger werden von kaskadierenden Fremdschlüsselaktionen derzeit nicht aktiviert.

Abweichung von SQL-Standards: Wenn mehrere Zeilen in der Elterntabelle denselben Referenzschlüsselwert haben verhält sich InnoDB bei Fremdschlüsselprüfungen so, als würden die anderen Zeilen der Elterntabelle, also die mit demselben Schlüsselwert, gar nicht vorhanden. Wenn Sie beispielsweise einen RESTRICT-Typ-Constraint definiert haben und es eine Kindzeile mit mehreren Elternzeilen gibt, verbietet InnoDB das Löschen irgendeiner dieser Elternzeilen.

InnoDB führt kaskadierende Operationen mit einem Depth-First-Algorithmus durch, beruhend auf Einträgen in den Indizes, die zu den Fremdschlüssel-Constraints gehören.

Abweichung von SQL-Standards: Ein FOREIGN KEY-Constraint, der einen Nicht-UNIQUE-Schlüssel referenziert, ist nicht Standard-SQL, sondern eine InnoDB-Erweiterung dieses Standards.

Abweichung von SQL-Standards: Wenn ON UPDATE CASCADE oder ON UPDATE SET NULL rekursiv dieselbe Tabelle ändert, die zuvor während der kaskadierenden Änderung auch bereits aktualisiert wurde, verhält es sich wie RESTRICT. Sie können also keine rückbezüglichen ON UPDATE CASCADE- oder ON UPDATE SET NULL-Operationen ausführen. Dadurch sollen Endlosschleifen wegen kaskadierender Updates verhindert werden. Andererseits ist ein rückbezügliches ON DELETE SET NULL jedoch möglich, nämlich als rückbezügliches ON DELETE CASCADE. Die maximale Schachtelungstiefe von kaskadierenden Operationen beträgt 15 Ebenen.

Abweichung von SQL-Standards: Wie immer in MySQL prüft InnoDB für jede SQL-Anweisung, die viele Zeilen einfügt, löscht oder ändert, die UNIQUE- und FOREIGN KEY-Constraints zeilenweise. Nach dem SQL-Standard sollte eigentlich eine verzögerte Prüfung vorgenommen werden, bei der Constraints erst nach der Verarbeitung der gesamten SQL-Anweisung geprüft werden. Bis die verzögerte Constraint-Prüfung auch in InnoDB implementiert ist, werden einige Dinge nicht möglich sein, etwa das Löschen eines Datensatzes, der sich über einen Fremdschlüssel auf sich selbst bezieht.

In dem folgenden einfachen Beispiel sind parent- und child-Tabellen über einen Einzelspalten-Fremdschlüssel verbunden:

CREATE TABLE parent (id INT NOT NULL,
                    PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                   INDEX par_ind (parent_id),
                   FOREIGN KEY (parent_id) REFERENCES parent(id)
                     ON DELETE CASCADE
) ENGINE=INNODB;

Das folgende, komplexere Beispiel zeigt eine product_order-Tabelle, die Fremdschlüssel für zwei andere Tabellen besitzt. Ein Fremdschlüssel referenziert einen Zwei-Spalten-Index in der product-Tabelle und der andere einen Ein-Spalten-Index in der customer-Tabelle:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                      product_category INT NOT NULL,
                      product_id INT NOT NULL,
                      customer_id INT NOT NULL,
                      PRIMARY KEY(no),
                      INDEX (product_category, product_id),
                      FOREIGN KEY (product_category, product_id)
                        REFERENCES product(category, id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
                      INDEX (customer_id),
                      FOREIGN KEY (customer_id)
                        REFERENCES customer(id)) ENGINE=INNODB;

InnoDB ermöglicht es, mit ALTER TABLE einer Tabelle einen Fremdschlüssel-Constraint hinzuzufügen:

ALTER TABLE tbl_name
    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Bitte legen Sie immer zuerst die erforderlichen Indizes an. Sie können einer Tabelle mit ALTER TABLE auch einen rückbezüglichen Fremdschlüssel-Constraint hinzufügen.

InnoDB ermöglicht es überdies, mit ALTER TABLE Fremdschlüssel zu löschen:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

Wenn die FOREIGN KEY-Klausel bei der Erzeugung des Fremdschlüssels einen CONSTRAINT-Namen enthielt, können Sie beim Löschen dieses Fremdschlüssels denselben Namen nennen. Ansonsten wird beim Anlegen des Fremdschlüssels intern ein fk_symbol-Wert generiert. Um zum Löschen eines Fremdschlüssels diesen Symbolwert zu ermitteln, dient die SHOW CREATE TABLE-Anweisung. Ein Beispiel:

mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
       Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
  `A` int(11) NOT NULL auto_increment,
  `D` int(11) NOT NULL default '0',
  `B` varchar(200) NOT NULL default '',
  `C` varchar(175) default NULL,
  PRIMARY KEY  (`A`,`D`,`B`),
  KEY `B` (`B`,`C`),
  KEY `C` (`C`),
  CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;

Sie können einen Fremdschlüssel nicht in separaten Klauseln derselben ALTER TABLE-Anweisung anlegen und löschen. Hierzu sind zwei getrennte Anweisungen erforderlich.

Der Parser von InnoDB gestattet es, für Tabellen- und Spaltenbezeichner in einer FOREIGN KEY … REFERENCES …-Klausel Backticks als Anführungszeichen zu verwenden. (Alternativ können doppelte Anführungszeichen gesetzt werden, wenn der SQL-Modus ANSI_QUOTES SQL eingeschaltet ist.) Außerdem berücksichtigt der InnoDB-Parser die Einstellung der Systemvariablen lower_case_table_names.

InnoDB gibt die Fremdschlüsseldefinitionen einer Tabelle im Rahmen der SHOW CREATE TABLE-Anweisung zurück:

SHOW CREATE TABLE tbl_name;

mysqldump erstellt ebenfalls die korrekten Definitionen der Tabellen in der Dump-Datei und vergisst dabei auch die Fremdschlüssel nicht.

Die Fremdschlüssel-Constraints einer Tabelle können wie folgt angezeigt werden:

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

Die Fremdschlüssel-Constraints stehen in der Comment-Spalte der Ausgabe.

Bei den Fremdschlüsselprüfungen errichtet InnoDB Shared-Sperren auf Zeilenebene auf den relevanten Datensätzen der Kind- oder Elterntabellen. InnoDB prüft die Fremdschlüssel-Constraints sofort und nicht erst beim Committen der Transaktion.

Damit Dump-Dateien, die Fremdschlüsselbeziehungen haben, leichter geladen werden können bindet mysqldump automatisch in die Dump-Ausgabe eine Anweisung ein, die FOREIGN_KEY_CHECKS auf 0 setzt. So entstehen keine Probleme mit Tabellen, die beim Neuladen der Dump-Dateien eigentlich in einer bestimmten Reihenfolge geladen werden müssen. Diese Variable kann auch manuell gesetzt werden:

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

So können Sie die Tabellen in beliebiger Reihenfolge importieren, wenn die Dump-Datei Tabellen enthält, die eigentlich gemäß ihrer Fremdschlüssel nicht richtig geordnet sind. Außerdem wird der Import beschleunigt. Auch in Fällen, wo Sie Fremdschlüssel-Constraints in LOAD DATA- und ALTER TABLE-Operationen ignorieren möchten, kann es sinnvoll sein, FOREIGN_KEY_CHECKS auf 0 zu setzen.

Mit InnoDB können Sie eine durch einen FOREIGN KEY-Constraint referenzierte Tabelle nur löschen, wenn SET FOREIGN_KEY_CHECKS=0 eingestellt wurde. Wenn Sie eine Tabelle löschen, werden die in ihrer Erzeugungsanweisung definierten Constraints mit gelöscht.

Wenn Sie eine gelöschte Tabelle wiederherstellen, muss ihre Definition zu den auf sie bezogenen Fremdschlüssel-Constraints passen. Sie muss die richtigen Spaltennamen und -typen sowie Indizes auf den referenzierten Schlüsseln haben. Ist dies nicht der Fall, gibt MySQL die Fehlernummer 1005 zurück und nennt die Fehlernummer 150 in der Fehlermeldung.

In MySQL funktioniert die Replikation mit InnoDB-Tabellen wie mit MyISAM-Tabellen. Es sind auch Replikationsumgebungen möglich, bei denen der Slave eine andere Speicher-Engine als der Master verwendet. So können Sie beispielsweise Änderungen, die auf dem Master an einer InnoDB-Tabelle vorgenommen werden, in einer MyISAM-Tabelle auf dem Slave replizieren.

Um einen neuen Slave für den Master einzurichten, müssen Sie den InnoDB-Tablespace und die Logdateien sowie die .frm-Dateien der InnoDB-Tabellen kopieren und diese Kopien auf den Slave übertragen. Wenn die Variable innodb_file_per_table eingeschaltet ist, müssen auch die .ibd-Dateien kopiert werden. Wie dies genau geht, erfahren Sie unter Abschnitt 14.2.8, „Sichern und Wiederherstellen einer InnoDB-Datenbank“.

Wenn Sie den Master oder einen vorhandenen Slave herunterfahren, können Sie ein kaltes Backup des InnoDB-Tablespace und der Logdateien erstellen und damit einen Slave einrichten. Um einen neuen Slave aufzusetzen, ohne gleich den Server herunterzufahren, können Sie das kommerzielle (also kostenpflichtige) Tool InnoDB Hot Backup verwenden.

Die InnoDB-Replikation wird mit der LOAD TABLE FROM MASTER-Anweisung eingerichtet, die allerdings nur mit MyISAM-Tabellen funktioniert. Dafür gibt es jedoch zwei Workarounds:

Transaktionen, die auf dem Master scheitern, haben auf die Replikation keinerlei Auswirkungen. Die Replikation beruht in MySQL auf dem Binärlog, wo die SQL-Anweisungen, die Daten modifizieren, festgehalten werden. Eine Transaktion, die scheitert (beispielsweise wegen Verstoß gegen einen Fremdschlüssel-Constraint oder weil sie zurückgerollt wird), gelangt gar nicht ins Binärlog und ergo auch nicht auf die Slaves. Siehe Abschnitt 13.4.1, „BEGIN/COMMIT/ROLLBACK“.

Wenn Schäden an Datenbankseiten vorhanden sind, sollten Sie Ihre Tabellen mit SELECT INTO OUTFILE dumpen. Normalerweise sind die meisten auf diese Weise geretteten Daten intakt. Trotzdem kann der Schaden dazu führen, dass SELECT * FROM tbl_name-Anweisungen oder InnoDB-Hintergrundoperationen abstürzen oder sich durchsetzen oder gar die Roll-forward-Recovery von InnoDB abstürzen lassen. Sie können einen Neustart von InnoDB erzwingen und gleichzeitig die Hintergrundoperationen anhalten, sodass ein Tabellen-Dump möglich ist. Zum Beispiel könnten Sie dem Abschnitt [mysqld] Ihrer Optionsdatei vor dem Server-Neustart folgende Zeile hinzufügen:

[mysqld]
innodb_force_recovery = 4

Weiter unten erfahren Sie, welche von null verschiedenen Werte innodb_force_recovery annehmen kann. Größere Werte schließen alle Sicherheitsmaßnahmen der kleineren Werte mit ein. Wenn Sie in der Lage sind, Ihre Tabellen mit einem Optionswert von höchstens 4 zu dumpen, können Sie davon ausgehen, dass nur wenige Daten auf einzelnen beschädigten Seiten verloren gegangen sind. Der Wert 6 ist schon drastischer: Hier bleiben Datenbankseiten in einem obsoleten Zustand zurück, was zu zusätzlichen Schäden in B-Bäumen und anderen Datenbankstrukturen führen kann.

Selbst bei einer erzwungenen Recovery können Sie Tabellen mit SELECT dumpen oder sie mit DROP löschen oder mit CREATE anlegen. Wenn Sie wissen, dass eine bestimmte Tabelle beim Zurückrollen einen Absturz verursacht, können Sie sie löschen. Oder Sie verwenden diese Option, um ein Rollback anzuhalten, das wegen eines gescheiterten Massen-Imports oder einer fehlgeschlagenen ALTER TABLE-Operation außer Kontrolle geraten ist. Sie können den mysqld-Prozess anhalten und innodb_force_recovery auf 3 setzen, um die Datenbank ohne den Rollback wieder ans Laufen zu bringen, und dann mit DROP die Tabelle löschen, die den Endlos-Rollback verursacht hatte.

Die Datenbank darf auf keine andere Weise mit einem von null verschiedenen innodb_force_recovery-Wert benutzt werden. Zur Sicherheit hindert InnoDB die Benutzer an INSERT-, UPDATE- und DELETE-Operationen, wenn innodb_force_recovery größer als 0 ist.

InnoDB implementiert einen „fuzzy“ Checkpoint-Mechanismus. InnoDB schreibt Datenbankseiten, die geändert wurden, in kleinen Batches aus dem Bufferpool auf die Platte. Es ist nicht nötig, den Bufferpool in einem einzigen, großen Batch auf die Platte zu schreiben, da dies in der Praxis dazu führen würde, dass Benutzer während des Checkpointing-Prozesses keine SQL-Anweisungen erteilen können.

Bei einer Recovery sucht InnoDB ein Checkpoint-Label in den Logdateien, da es weiß, dass alle Datenbankmodifikationen, die diesem Label vorausgingen, im Disk-Image der Datenbank vorhanden sind. Dann scannt InnoDB die Logdateien von dem Checkpoint aus nach vorne durch und wendet die dort protokollierten Modifikationen auf die Datenbank an.

InnoDB schreibt Daten in rotierender Weise in seine Logdateien. Alle committeten Modifikationen, die dazu führen, dass Datenbankseiten im Bufferpool von den Images auf der Festplatte abweichen, müssen in den Logdateien vorhanden sein, falls InnoDB einmal eine Recovery durchführen muss. Wenn InnoDB eine Logdatei benutzt, muss es also dafür sorgen, dass die Datenbankseiten-Images auf der Platte die in der für die Recovery verwendeten Logdatei protokollierten Modifikationen enthalten. Mit anderen Worten muss InnoDB einen Checkpoint anlegen, wozu häufig modifizierte Datenbankseiten auf die Platte zurückgeschrieben werden müssen.

Dies erklärt auch, warum sehr große Logdateien die Plattenzugriffe beim Checkpointing reduzieren. Oft ist es sinnvoll, die Gesamtgröße der Logdateien auf die Größe des Bufferpools oder sogar einen noch höheren Wert einzustellen. Der Nachteil großer Logdateien: Die Recovery kann dann länger dauern, da mehr Informationen in die Datenbank eingebracht werden müssen.

InnoDB implementiert zwei Arten von Standard-Zeilensperren:

Wenn die Transaktion T1 eine Shared-Sperre (S) auf dem Tupel t hält, dann

Wenn eine Transaktion T1 eine exklusive (X) Sperre auf Tupel t hält, kann einer Forderung einer anderen Transaktion T2 nach einer Sperre egal welchen Typs auf t nicht sofort stattgegeben werden. Transaktion T2 muss warten, bis Transaktion T1 ihre Sperre auf Tupel t wieder freigegeben hat.

Außerdem unterstützt InnoDB Multigranuläre Sperren: So können Zeilen und Tabellensperren koexistieren. Um Sperren auf mehreren Granularitätsebenen in die Praxis umzusetzen, gibt es die so genannten Intention Locks. Mithilfe dieser intendierten Sperren kann eine Transaktion anzeigen, welchen Sperrentyp (Shared oder exklusiv) sie später auf einer Zeile einer Tabelle benötigt. In InnoDB werden zwei Arten von Intention Locks verwendet (wir gehen davon aus, dass Transaktion T eine Sperre des angegebenen Typs auf Tabelle R) angefordert hat:

Das Protokoll für Intention Locking ist wie folgt:

Diese Regeln lassen sich gut in einer Matrix der Sperrtypenkompatibilität zusammenfassen:

Der Sperranforderung einer Transaktion wird stattgegeben, wenn die Sperre mit den bereits vorhandenen Sperren kompatibel ist. Ihr wird nicht stattgegeben, wenn die Sperre mit den vorhandenen in Konflikt treten würde. Dann muss die Transaktion abwarten, bis die andere Sperre, die den Konflikt verursachen würde, freigegeben wurde. Wenn eine Sperranforderung mit einer vorhandenen Sperre in Konflikt tritt und nicht gewährt werden kann, weil sie einen Deadlock verursachen würde, wird ein Fehler ausgelöst.

Somit können Intention Locks nichts blockieren, außer vielleicht Sperranforderungen für ganze Tabellen (beispielsweise LOCK TABLES … WRITE). IX und IS sollen vor allem anzeigen, dass jemand eine Tabellenzeile sperrt oder sperren wird.

Das folgende Beispiel zeigt, wie ein Fehler ausgelöst wird, wenn eine Sperranforderung einen Deadlock zur Folge haben würde. An dem Beispiel sind zwei Clients beteiligt, nämlich A und B.

Zuerst legt Client A eine Tabelle mit einer Zeile an und beginnt dann eine Transaktion. Inmitten der Transaktion erwirbt A eine S-Sperre auf der Zeile, indem er im Shared-Modus ein Select auf ihr ausführt:

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.10 sec)

Dann beginnt Client B eine Transaktion und versucht, die Zeile aus der Tabelle zu löschen:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

Die Löschoperation macht eine X-Sperre erforderlich. Diese kann jedoch nicht erteilt werden, da sie mit der S-Sperre von Client A inkompatibel ist. So wird der Request in die Schlange der Sperranforderungen auf diese Zeile gestellt und Client B wird blockiert.

Endlich versucht Client A ebenfalls, die Zeile aus der Tabelle zu löschen:

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

Hier tritt der Deadlock auf, da Client A eine X-Sperre benötigt, um die Zeile sperren zu können. Diese Sperre kann jedoch nicht gewährt werden, da Client B schon vorher eine X-Sperre angefordert hatte, und nur noch darauf wartet, dass A seine S-Sperre freigibt. Doch auch die S-Sperre von A kann nicht auf eine X-Sperre hochgesetzt werden, da B schon vorher eine X-Sperre angefordert hatte. Infolgedessen meldet InnoDB dem Client A einen Fehler und gibt dessen Sperre frei. Jetzt kann der Sperranforderung von Client B stattgegeben werden und B löscht die Zeile aus der Tabelle.

In InnoDB findet jegliche Benutzeraktivität in einer Transaktion statt. Wenn der Autocommit-Modus eingeschaltet ist, bildet jede SQL-Anweisung eine eigene Transaktion. Standardmäßig ist dieser Modus immer aktiv, wenn MySQL hochfährt.

Wird Autocommit mit SET AUTOCOMMIT = 0 ausgeschaltet, gehen wir davon aus, dass der Benutzer immer eine Transaktion offen hat. Eine COMMIT- oder ROLLBACK-Anweisung beendet die laufende Transaktion und eine neue beginnt. COMMIT bedeutet, dass die Änderungen, die in der aktuellen Transaktion vorgenommen wurden, in der Datenbank dauerhaft festgeschrieben und für andere Benutzer sichtbar gemacht werden. Eine ROLLBACK-Anweisung hingegen macht alle Änderungen in der aktuellen Transaktion rückgängig. Beide Anweisungen geben auch alle InnoDB-Sperren frei, die in der Transaktion gesetzt wurden.

Wenn auf einer Verbindung Autocommit eingeschaltet ist, kann der Benutzer dennoch Transaktionen aus mehreren Anweisungen ausführen, wenn er sie mit einem expliziten START TRANSACTION oder BEGIN einleitet und mit COMMIT oder ROLLBACK beendet.

Was die Transaktionsisolationsebenen von SQL:1992 angeht, so hat InnoDB die Standardeinstellung REPEATABLE READ. InnoDB bietet alle vier Transaktionsisolationsebenen des SQL-Standards. Die Standardisolationsebene können Sie mit der --transaction-isolation-Option auf der Kommandozeile oder in einer Optionsdatei einstellen. So können Sie beispielsweise im [mysqld]-Abschnitt einer Optionsdatei folgendes eintragen:

[mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                         | REPEATABLE-READ | SERIALIZABLE}

Ein Benutzer kann die Isolationsebene für eine einzelne Session oder alle neuen Verbindungen mit der SET TRANSACTION-Anweisung einstellen, die folgende Syntax hat:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                        | REPEATABLE READ | SERIALIZABLE}

Beachten Sie, dass in den Namen der Ebenen in der --transaction-isolation-Option Bindestriche verwendet werden, aber nicht in der SET TRANSACTION-Anweisung.

Standardmäßig wird die Isolationsebene immer für die nächste (noch nicht begonnene) Transaktion eingestellt. Mit dem Schlüsselwort GLOBAL stellt die Anweisung die Standard-Transaktionsebene global für alle neuen Verbindungen ein, die ab diesem Punkt aufgebaut werden (aber nicht für die schon bestehenden Verbindungen). Um dies zu tun, benötigen Sie die SUPER-Berechtigung. Das Schlüsselwort SESSION stellt die Standard-Transaktionsebene für alle zukünftigen Transaktionen auf aktuellen Verbindung ein.

Jeder Client kann die Session-Isolationsebene (sogar inmitten einer Transaktion), oder die Isolationsebene der nächsten Transaktion haben.

Ob die Transaktionsisolationsebene global oder für die einzelne Session eingestellt wurde, können Sie anhand der Systemvariablen tx_isolation mit folgenden Anweisungen ermitteln:

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

Für Zeilensperren verwendet InnoDB Next-Key-Locking. Das bedeutet, dass InnoDB zusätzlich zu den Indexeinträgen auch die „Lücke“ vor einem Indexeintrag sperrt, um zu verhindern, dass andere Benutzer gerade dort etwas einfügen. Eine Next-Key-Sperre ist eine Sperre, die nicht nur den Indexeintrag, sondern auch die Lücke vor diesem blockiert. Eine Gap-Sperre ist eine Sperre, die lediglich die Lücke vor einem Indexeintrag betrifft.

Im Folgenden werden die Isolationsebenen in InnoDB genauer erläutert:

Bei einer konsistenten Leseoperation verwendet InnoDB Multiversionierung, um einer Anfrage einen Snapshot der Datenbank für einen bestimmten Zeitpunkt (Point-in-Time) zu präsentieren. Die Anfrage erkennt die Änderungen, die Transaktionen vor diesem Zeitpunkt vorgenommen hatten, aber keine Änderungen späterer oder noch unvollendeter Transaktionen. Die Ausnahme: Änderungen von früheren Anweisungen in derselben Transaktion sind für die Anweisung sichtbar.

Wenn Sie die Standardisolationsebene REPEATABLE READ benutzen, lesen alle konsistenten Leseoperationen innerhalb derselben Transaktion den Snapshot, der durch die erste Leseoperation in dieser Transaktion erzeugt wurde. Einen frischeren Snapshot für Ihre Anfragen erhalten Sie, indem Sie die laufende Transaktion committen und danach neue Anfragen absetzen.

Konsistentes Lesen ist der Standardmodus, in dem InnoDB SELECT-Anfragen auf den Isolationsebenenen READ COMMITTED und REPEATABLE READ verarbeitet. Konsistentes Lesen errichtet keine Sperren auf den benutzten Tabellen, sodass andere Benutzer diese Tabellen nach Belieben modifizieren können, während eine konsistente Leseoperation auf ihnen abläuft.

Beachten Sie, dass konsistente Leseoperationen nicht mit DROP TABLE und ALTER TABLE funktionieren. Mit DROP TABLE nicht, weil MySQL eine Tabelle, die gelöscht wurde, nicht mehr benutzen kann und InnoDB sie zerstört, und mit ALTER TABLE nicht, weil dieser Befehl innerhalb einer Transaktion ausgeführt wird, die eine neue Tabelle anlegt und Zeilen aus der alten Tabelle in sie einfügt. Wenn Sie die konsistente Leseoperation neu ausführen, kann sie die Zeilen der neuen Tabelle nicht erkennen, da sie in einer Transaktion eingefügt wurden, die in dem von der konsistenten Leseoperation verwendeten Snapshot nicht sichtbar war.

In manchen Fällen ist eine konsistente Leseoperation nicht das Richtige. Vielleicht möchten Sie ja eine neue Zeile in Ihre child-Tabelle einfügen und dabei sicherstellen, dass das Kind in der Tabelle parent auch wirklich ein Elternteil hat. Das folgende Beispiel zeigt, wie Sie referenzielle Integrität in Ihren Anwendungscode implementieren können.

Angenommen, Sie verwenden eine konsistente Leseoperation, um die Tabelle parent zu lesen, und sehen auch tatsächlich die Elternzeile des Kindes in der Tabelle. Können Sie die Kindzeile nun auch beruhigt in die Tabelle child einfügen? Nein, denn es kann ja sein, dass irgendein anderer Benutzer zwischenzeitlich die Elternzeile aus der Tabelle parent gelöscht hat, ohne dass Sie es merkten.

Die Lösung: Sie führen das SELECT in dem Sperrmodus LOCK IN SHARE MODE aus:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

Wird eine Leseoperation im Share-Modus ausgeführt, so liest sie die aktuellsten verfügbaren Daten und errichtet eine Shared-Sperre auf den Zeilen, die sie liest. Diese verhindert, dass andere Benutzer die Zeile ändern oder löschen. Wenn die Daten zu einer noch unvollendeten Transaktion einer anderen Clientverbindung gehören, warten wir, bis die Transaktion committet ist. wenn wir gesehen haben, dass die obige Anfrage den Parent 'Jones' zurückgibt, können wir unseren Eintrag beruhigt in die child-Tabelle einfügen und unsere Transaktion committen.

Betrachten wir ein anderes Beispiel: Wir haben in der Tabelle child_codes ein Zählerfeld eines Integer-Typs, das wir dazu benutzen, jedem Kind, das der child-Tabelle hinzugefügt wird, eine eindeutige Kennnummer zu geben. Da wäre es natürlich keine gute Idee, den Wert des Zählers mit einer konsistenten Leseoperation oder im Shared-Modus zu lesen, da zwei Datenbanknutzer dann vielleicht denselben Zählerwert sehen und einen Fehler wegen Schlüsselduplikaten auslösen, sofern sie versuchen, Kindeinträge mit derselben Nummer in die Tabelle einzufügen.

Hier ist LOCK IN SHARE MODE keine gute Lösung. Denn wenn zwei Benutzer gleichzeitig den Zähler lesen, könnte mindestens einer von ihnen in einen Deadlock geraten, wenn er versucht, den Zähler zu aktualisieren.

Hier haben Sie zwei gute Möglichkeiten, das Lesen und Inkrementieren des Zählers zu implementieren: (1) Sie inkrementierten zuerst den Zähler um 1 und führen erst dann die Leseoperation durch, oder (2) Sie lesen den Zähler zuerst im Sperrmodus FOR UPDATE und inkrementieren ihn danach. Der zweite Ansatz kann folgendermaßen implementiert werden:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

Ein SELECT … FOR UPDATE liest die neuesten verfügbaren Daten und errichtet eine exklusive Sperre auf jeder Zeile, die es liest. Somit setzt es dieselben Sperren, die auch ein Searched SQL UPDATE auf den Zeilen erwerben würde.

Die obige Beschreibung ist nur ein Beispiel dafür, wie SELECT … FOR UPDATE funktioniert. In MySQL können Sie einen eindeutigen Identifier grundsätzlich mit nur einem einzigen Tabellenzugriff generieren:

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

Die SELECT-Anweisung ruft nur die Identifier-Information ab (die für die aktuelle Verbindung spezifisch ist). Sie greift auf keine Tabellen zu.

Sperren von IN SHARE MODE- und FOR UPDATE-Leseoperationen werden freigegeben, wenn die Transaktion committet oder zurückgerollt wird.

In Zeilensperren verwendet InnoDB einen Algorithmus namens Next-Key-Locking. InnoDB sperrt Zeilen so, dass es beim Durchsuchen oder Scannen eines Tabellenindex Shared- oder exklusive Sperren auf den gefundenen Indexeinträgen errichtet. Also sind die Zeilensperren in Wirklichkeit Indexeintragssperren.

Die Sperren, die InnoDB auf Indexeinträgen errichtet, betreffen auch die „Lücke“ vor den Einträgen. Wenn ein Benutzer eine Shared- oder exklusive Sperre auf Eintrag R eines Index hält, kann kein anderer Benutzer unmittelbar vor R einen Eintrag in die Indexreihenfolge einfügen. Durch dieses so genannte „Gap-Locking“ wird das „Phantom-Problem“ gelöst. Angenommen, Sie wollten alle Kinder der child-Tabelle lesen und sperren, deren Identifier-Wert größer als 100 ist, um später eine Spalte in den ausgewählten Zeilen zu ändern:

SELECT * FROM child WHERE id > 100 FOR UPDATE;

Angenommen, Sie haben einen Index auf der Spalte id. Die Anfrage scannt diesen Index ab dem ersten Eintrag, dessen id größer als 100 ist. Wenn die Sperren auf den Indexeinträgen Einfügungen in die Lücken nicht verhindern würden, könnte in der Zwischenzeit eine neue Zeile in die Tabelle eingefügt werden. Wenn Sie dasselbe SELECT in derselben Transaktion ein zweites Mal ausführten, hätten Sie plötzlich eine zusätzliche Zeile in der Ergebnismenge. Das verstößt aber gegen das Isolationsprinzip von Transaktionen: Eine Transaktion muss so ablaufen, dass sich die Daten, die sie liest, während ihrer Laufzeit nicht ändern können. Wenn wir eine Zeilenmenge als ein Datenelement betrachten, würde die neue „Phantomzeile“ dieses Isolationsprinzip verletzen.

Wenn InnoDB einen Index scannt, kann es auch die Lücke hinter dem letzten Eintrag im Index sperren. Genau dies geschieht auch im obigen Beispiel: Die von InnoDB gesetzten Sperren verhindern jede Einfügung in die Tabelle an Stellen, deren id größer als 100 ist.

Mit Next-Key-Locking können Sie eine Eindeutigkeitsprüfung in Ihrer Anwendung implementieren. Wenn Sie Ihre Daten im Share-Modus lesen und kein Duplikat der Zeile sehen, die Sie einfügen möchten, dann können Sie die Einfügung beruhigt ausführen und wissen, dass die Next-Key-Sperre auf dem Nachfolger Ihrer Zeile verhindert, dass in der Zwischenzeit jemand anders ein Duplikat Ihrer Zeile einfügt.

Angenommen, Sie verwenden die Standardisolationsebene REPEATABLE READ. Wenn Sie eine konsistente Leseoperation ausführen (also eine normale SELECT-Anweisung), weist InnoDB Ihrer Transaktion einen Zeitpunkt zu, an dem Ihre Anfrage die Datenbank sieht. Wenn eine andere Transaktion nach diesem Zeitpunkt eine Zeile löscht und committet, können Sie dies nicht sehen. Einfügungen und Änderungen werden genauso behandelt.

Sie können Ihren Zeitpunkt aktualisieren, indem Sie Ihre Transaktion committen und eine neue SELECT-Anfrage starten.

Dies bezeichnet man als Nebenläufigkeitssteuerung mit Multiversionierung.

               User A                 User B

           SET AUTOCOMMIT=0;      SET AUTOCOMMIT=0;
time
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------
           1 row in set

In diesem Beispiel sieht Benutzer A die von Benutzer B eingefügte Zeile erst dann, wenn sowohl B als auch A ihre Operationen committet haben, sodass der Zeitpunkt sich auf die Zeit nach dem Commit von B verschiebt.

Wenn Sie den „frischsten“ Zustand der Datenbank sehen möchten, stellen Sie entweder die Isolationsebene READ COMMITTED oder einen Locking-Read ein:

SELECT * FROM t LOCK IN SHARE MODE;

Ein Locking Read, ein UPDATE oder ein DELETE errichten normalerweise Zeilensperren auf jedem Indexeintrag, der bei der Verarbeitung der SQL-Anfrage gescannt wird. Ob irgendwelche WHERE-Bedingungen in der Anfrage die Zeile eigentlich ausschließen würden, spielt keine Rolle. InnoDB merkt sich nicht die genaue WHERE-Bedigungn, sondern weiß nur, welche Indexbereiche gescannt werden. Die Sperren auf den Einträgen sind normalerweise Next-Key-Sperren, die auch Einfügungen in die „Lücke“ vor dem Eintrag blockieren.

Wenn exklusive Sperren errichtet werden, ruft InnoDB immer auch den geclusterten Indexeintrag ab und sperrt ihn.

Wenn Sie nicht die geeigneten Indizes für Ihre Anweisung haben und MySQL die gesamte Tabelle scannen muss, um die Anfrage zu verarbeiten, werden alle Tabellenzeilen und somit auch alle Einfügeoperationen anderer Benutzer auf der Tabelle gesperrt. Es ist also wichtig, gute Indizes anzulegen, damit Ihre Anfragen nicht unnötig viele Zeilen scannen müssen.

InnoDB setzt bestimmte Arten von Sperren wie folgt:

Nach Voreinstellung beginnt MySQL jede Clientverbindung mit eingeschaltetem Autocommit. Dabei wird nach jeder fehlerfrei gelaufenen SQL-Anweisung ein Commit durchgeführt. Gibt die Anweisung einen Fehler zurück, so entscheidet die Art des Fehlers darüber, ob ein Commit oder ein Rollback ausgeführt wird. Siehe Abschnitt 14.2.15, „InnoDB-Fehlerbehandlung“.

Wenn Sie Autocommit ausgeschaltet haben und eine Verbindung schließen, ohne explizit die letzte Transaktion zu committen, rollt MySQL diese Transaktion zurück.

Jede der folgenden Anweisungen (und ihre Synonyme) beendet implizit eine Transaktion, als hätten Sie COMMIT gesagt:

Transaktionen können nicht geschachtelt werden. Dies ist eine Konsequenz des impliziten COMMIT, das jede laufende Transaktion ausführt, wenn Sie eine START TRANSACTION-Anweisung oder ein Synonym erteilen.

InnoDB erkennt automatisch einen Deadlock von Transaktionen und rollt eine oder mehrere Transaktion(en) zurück, um den Deadlock aufzulösen. InnoDB versucht, für den Rollback kleine Transaktionen herauszusuchen, wobei die Größe einer Transaktion anhand der Anzahl der Zeilen bestimmt wird, die sie einfügt, ändert oder löscht.

InnoDB weiß von Tabellensperren, wenn innodb_table_locks=1 (die Standardeinstellung) und AUTOCOMMIT=0 eingestellt ist, und die MySQL-Ebene oberhalb von InnoDB weiß ebenso von den Zeilensperren. Sonst kann die automatische Deadlock-Erkennung von InnoDB keine Deadlocks erkennen, wenn eine von der MySQL-Anweisung LOCK TABLES gesetzte Tabellensperre oder eine von einer anderen Speicher-Engine als InnoDB gesetzte Sperre beteiligt ist. Solche Situationen müssen Sie durch Einstellen der Systemvariablen innodb_lock_wait_timeout beherrschen.

Wenn InnoDB eine Transaktion vollständig zurückrollt, werden alle von ihr gehaltenen Sperren freigegeben. Wird jedoch nur eine einzige SQL-Anweisung infolge eines Fehlers zurückgerollt, bleiben manche von ihr errichteten Sperren eventuell erhalten, da InnoDB Zeilensperren in einem Format speichert, an dem es später nicht mehr erkennen kann, welche Anweisung welche Sperren gesetzt hat.

Deadlocks in Transaktionsdatenbanken sind ein klassisches Problem. Gefährlich werden sie aber erst dann, wenn sie so häufig auftreten, dass bestimmte Transaktionen nicht mehr möglich sind. Normalerweise müssen Sie Ihre Anwendungen so erstellen, dass sie immer bereit sind, eine Transaktion neu zu starten, wenn sie wegen eines Deadlocks zurückgerollt wurde.

InnoDB benutzt automatische Zeilensperren. Deadlocks können sogar bei Transaktionen auftreten, die nur eine einzige Zeile einfügen oder löschen. Da diese Operationen nicht wirklich „atomar“ sind, errichten sie automatisch Sperren auf den (vielleicht mehreren) Indexeinträgen der eingefügten oder gelöschten Zeile.

Folgende Techniken helfen Ihnen, mit Deadlocks fertigzuwerden und ihre Zahl zu reduzieren:

InnoDB hat Monitore, die Informationen über den internen Zustand von InnoDB ausgeben. Sie können jederzeit eine SHOW ENGINE INNODB STATUS-Anweisung erteilen, um die Ausgabe des InnoDB-Standardmonitors in Ihren SQL-Client zu holen. Diese Informationen sind nützlich für das Performance-Tuning. (Wenn Sie den interaktiven SQL-Client mysql benutzen, ist die Ausgabe einfacher zu lesen, wenn Sie das übliche Semikolon am Ende von Anweisungen durch \G ersetzen.) Die Sperrmodi von InnoDB werden in Abschnitt 14.2.10.1, „InnoDB-Sperrmodi“ erklärt.

mysql> SHOW ENGINE INNODB STATUS\G

Eine andere Möglichkeit, InnoDB-Monitore zu nutzen, besteht darin, sie in regelmäßigen Abständen Daten in die Standardausgabe des mysqld-Servers schreiben zu lassen. In diesem Fall wird keine Ausgabe an die Clients gesandt. Wenn sie eingeschaltet sind, geben InnoDB-Monitore etwa alle 15 Sekunden Daten aus. Die Server-Ausgabe wird normalerweise an das .err-Log im MySQL Data Directory geschickt. Diese Daten sind nützlich für das Performance-Tuning. Auf Windows müssen Sie den Server von einer Eingabeaufforderung in einem Konsolenfenster mit der --console-Option starten, wenn Sie die Ausgabe an das Fenster statt in das Fehlerlog schicken wollen.

Die Monitorausgabe enthält folgende Informationen:

Damit der InnoDB-Standardmonitor seine Daten an die Standardausgabe von mysqld sendet, geben Sie folgende SQL-Anweisung:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

Folgende Anweisung hält den Monitor an:

DROP TABLE innodb_monitor;

Die CREATE TABLE-Syntax ist nur eine Möglichkeit, über den SQL-Parser von MySQL einen Befehl an InnoDB zu übergeben. Es ist nur wichtig, dass der Tabellenname innodb_monitor und der Tabellentyp InnoDB ist. Die Tabellenstruktur ist für den InnoDB-Monitor nicht von Belang. (Diese Syntax kann beim Herunterfahren des Servers wichtig sein, der Monitor fährt bei einem Neustart des Servers nicht automatisch wieder hoch. Sie müssen die Monitortabelle löschen und eine neue CREATE TABLE-Anweisung geben, um den Monitor zu starten. Das kann sich in einem künftigen Release noch ändern.)

innodb_lock_monitor kann ähnlich eingesetzt werden. Dieser gleicht dem innodb_monitor, nur dass er außerdem viele Sperrinformationen gibt. Ein separater innodb_tablespace_monitor gibt eine Liste der angelegten Dateisegmente im Tablespace aus und validiert die Zuweisung der Datenstrukturen im Tablespace. Zusätzlich gibt es den innodb_table_monitor, mit dem Sie den Inhalt des internen Data Dictionary von InnoDB ausgeben können.

Hier sehen Sie eine Beispielausgabe des InnoDB-Monitors:

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
030709 13:00:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 413452, signal count 378357
--Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the
semaphore: X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135
a writer (thread id 32782) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0sea.c line 731
Last time write locked in file btr0sea.c line 1347
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits
375485
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030709 13:00:59 Transaction:
TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831
inserting
15 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
Foreign key constraint fails for table test/ibtest11a:
,
  CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`,
  `D`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index PRIMARY tuple:
 0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2:
 len 4; hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4:
 len 7; hex 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;
But in parent table test/ibtest11b, in index PRIMARY,
the closest match we can find is record:
RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex
80000005; asc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex
0000111ef3eb; asc ......;; 4: len 7; hex 800001001e0084; asc .......;; 5:
len 3; hex 6b6864; asc khd;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
030709 12:59:58
*** (1) TRANSACTION:
TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733
inserting
LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146
MySQL thread id 21, query id 4553379 localhost heikki update
INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t',
'e187358f','g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d
%H:%i'),7
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
symbole trx id 0 290252780 lock mode S waiting
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;
asc aa35818;; 1:
*** (2) TRANSACTION:
TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782
inserting
130 lock struct(s), heap size 11584, undo log entries 437
MySQL thread id 23, query id 4554396 localhost heikki update
REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','',
NULL,'h396', NULL, NULL, 7.31,7.31,7.31,200)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
symbole trx id 0 290251546 lock_mode X locks rec but not gap
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;
asc aa35818;; 1:
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
symbole trx id 0 290251546 lock_mode X locks gap before rec insert intention
waiting
Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230;
asc aa35720;; 1:
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
Total number of lock structs in row lock hash table 70
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002
MySQL thread id 32, query id 4668737 localhost heikki
show innodb status
---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id
38929 inserting
1 lock struct(s), heap size 320
MySQL thread id 29, query id 4668736 localhost heikki update
insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjg
jlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh
---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id
28684 committing
1 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 19, query id 4668734 localhost heikki update
insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgj
gjlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf
---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id
36880 starting index read
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 27, query id 4668644 localhost heikki Searching rows for
update
update ibtest11a set B = 'kHdkkkk' where A = 89572
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index
PRIMARY trx id 0 290328327 lock_mode X waiting
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00;
asc supremum.;;
------------------
---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id
34831 rollback of SQL statement
ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id
32782
58 lock struct(s), heap size 5504, undo log entries 159
MySQL thread id 23, query id 4668732 localhost heikki update
REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t',
'e200498f','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d
%H:%i'),
---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id
30733 inserting
4 lock struct(s), heap size 1024, undo log entries 165
MySQL thread id 21, query id 4668735 localhost heikki update
INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','',
NULL,'h321', NULL, NULL, 7.31,7.31,7.31,200)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs
25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 19, seg size 21,
85004 inserts, 85004 merged recs, 26669 merges
Hash table size 207619, used cells 14461, node heap has 16 buffer(s)
1877.67 hash searches/s, 5121.10 non-hash searches/s
---
LOG
---
Log sequence number 18 1212842764
Log flushed up to   18 1212665295
Last checkpoint at  18 1135877290
0 pending log writes, 0 pending chkp writes
4341 log i/o's done, 1.22 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 84966343; in additional pool allocated 1402624
Buffer pool size   3200
Free buffers       110
Database pages     3074
Modified db pages  2674
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 171380, created 51968, written 194688
28.72 reads/s, 20.72 creates/s, 47.55 writes/s
Buffer pool hit rate 999 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 3004, id 7176, state: purging
Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779
1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Einige Hinweise zu dieser Ausgabe:

InnoDB sendet Diagnose-Ausgabe an stderr oder an Dateien anstatt an die stdout-Ausgabe oder Arbeitsspeicherpuffer fester Größe, um Puffer-Überläufe zu verhindern. Als Nebeneffekt wird die Ausgabe von SHOW ENGINE INNODB STATUS alle 15 Sekunden in eine Statusdatei im MySQL Data Directory geschrieben. Diese Datei heißt innodb_status.pid, wobei pid die Server-Prozess-ID ist. InnoDB entfernt diese Datei bei einem normalen Shutdown. Bei einem unnormalen Shutdown können Instanzen dieser Statusdateien überleben und müssen manuell gelöscht werden. Bevor Sie dies tun, sollten Sie allerdings hineinschauen, um zu sehen ob sie irgendwelche Hinweise auf die Ursache der unnormalen Shutdowns enthalten. Die Datei innodb_status.pid wird nur angelegt, wenn die Konfigurationsoption innodb_status_file=1 gesetzt ist.

Alle InnoDB-Indizes sind B-Trees, wobei die Indexeinträge in den Blattseiten des Baums gespeichert werden. Die Standardgröße einer Indexseite beträgt 16KB. Wenn neue Einträge eingefügt werden, versucht InnoDB, 1/16 der Seite für zukünftige Einfügungen und Änderungen der Indexeinträge frei zu halten.

Wenn Indexeinträge in (auf- oder absteigender) sequenzieller Reihenfolge eingefügt werden, werden die Indexseiten zu 15/16 voll. Werden die Einträge in zufälliger Reihenfolge eingefügt, werden die Seiten nur zu 1/2 bis 15/16 voll. Wenn der Füllstand einer Indexseite unter 1/2 fällt, versucht InnoDB mit dem Indexbaum zu vereinbaren, dass die Seite freigegeben wird.

In Datenbankanwendungen kommt es häufig vor, dass ein Primärschlüssel ein eindeutiger Identifier ist und neue Zeilen in der aufsteigenden Reihenfolge des Primärschlüssels eingefügt werden. So sind für Einfügungen in den geclusterten Index keine willkürlichen Lesezugriffe auf eine Festplatte erforderlich.

Dagegen sind Sekundärindizes normalerweise nicht-eindeutig und Einfügungen in sie finden in relativ willkürlicher Reihenfolge statt. Dies würde eine Menge willkürlicher E/A-Operationen auf der Festplatte erfordern, wenn es in InnoDB nicht einen speziellen Mechanismus gäbe.

Wenn ein Indexeintrag in einen nicht-eindeutigen Sekundärindex eingefügt werden soll, prüft InnoDB, ob dieser Sekundärindex im Bufferpool liegt. Wenn ja, führt InnoDB die Einfügung direkt auf der Indexseite durch. Wenn nicht, fügt InnoDB den Eintrag in eine spezielle Insert-Puffer-Struktur ein. Der Insert-Puffer wird so klein gehalten, dass er komplett in den Bufferpool passt und Einfügungen sehr schnell erledigt werden können.

Dieser Insert-Puffer wird regelmäßig mit den Sekundärindexbäumen in der Datenbank zusammengeführt. Oft können mehrere Einfügeoperationen auf derselben Seite des Indexbaums gleichzeitig zusammengeführt werden, was Festplattenzugriffe spart. Messungen haben ergeben, dass der Insert-Puffer Einfügungen in eine Tabelle bis zu 15-mal schneller laufen lässt.

Das Zusammenführen von Insert-Puffer-Daten kann auch dann weitergehen, nachdem die einfügende Transaktion committet wurde, ja sogar nach dem Herunterfahren und Neustart des Servers (siehe Abschnitt 14.2.8.1, „Erzwingen einer InnoDB-Wiederherstellung (Recovery)“).

Das Zusammenführen von Insert-Puffer-Daten kann viele Stunden dauern, wenn viele Sekundärindizes aktualisiert werden müssen und viele Zeilen eingefügt wurden. In dieser Zeit erhöht sich die E/A-Aktivität, sodass festplattengebundene Anfragen eventuell viel langsamer laufen. Eine andere, wichtige E/A-Hintergrundoperation ist der Purge-Thread (siehe Abschnitt 14.2.12, „Implementierung der Multiversionierung“).

Wenn eine Tabelle fast komplett in den Hauptspeicher passt, sind Hash-Indizes das schnellste Mittel, um Anfragen auszuführen. InnoDB hat einen Mechanismus, der Index-Suchen auf den Indizes einer Tabelle beobachtet. Wenn InnoDB bemerkt, dass Anfragen von einem Hash-Index profitieren könnten, baut es automatisch einen auf.

Beachten Sie, dass der Hash-Index immer auf einem vorhandenen B-Baum-Index der Tabelle aufbaut. InnoDB kann einen Hash-Index auf einem beliebig langen Präfix des für den B-Baum definierten Schlüssels aufbauen, je nachdem, welches Suchmuster InnoDB für den B-Baum-Index beachtet. Ein Hash-Index kann auch partiell sein: Es ist nicht notwendig, den gesamten B-Baum-Index im Bufferpool zu cachen. InnoDB baut Hash-Indizes nach Bedarf für diejenigen Indexseiten auf, die oft angesprochen werden.

In gewissem Sinne legt sich InnoDB durch den anpassungsfähigen Hash-Index-Mechanismus selbst auf einen großen Arbeitsspeicher hin aus und kommt dadurch der Architektur von Hauptspeicher-Datenbanken näher.

Datensätze von InnoDB-Tabellen haben folgende Merkmale:

InnoDB verwendet simulierte, asynchrone Festplattenein- und -ausgaben (E/A) und erzeugt mehrere Threads für E/A-Operationen wie beispielsweise Read-ahead.

Es gibt zwei Read-ahead-Heuristiken in InnoDB:

InnoDB verwendet eine neue Technik namens Doublewrite, um Dateien auf die Festplatte zurückzuschreiben. Dadurch wird die Recovery nach einem Betriebssystemabsturz oder einem Stromausfall sicherer und die Leistung der meisten Unix-Varianten besser, da nicht mehr so viele fsync()-Operationen nötig sind.

Doublewrite sorgt dafür, dass InnoDB Seiten nicht gleich in die Datendatei, sondern zuerst in einen zusammenhängenden Tablespace-Bereich namens Doublewrite-Puffer schreibt. Erst wenn dieser Vorgang abgeschlossen ist, werden die Seiten in ihre angestammten Plätze in der Datendatei geschrieben. Stürzt das Betriebssystem mitten im Schreiben einer Seite ab, kann InnoDB später bei der Recovery eine gute Kopie der Seite im Doublewrite-Puffer finden.

Die in der Konfigurationsdatei definierten Datendateien bilden den Tablespace von InnoDB. Die Dateien werden einfach aneinander gehängt, um diesen Tablespace zu bilden. Es wird kein Striping verwendet. Zurzeit können Sie noch nicht bestimmen, an welche Stelle des Tablespaces Ihre Tabellen zugewiesen werden. Doch in einem neuen Tablespace wird der Platz beginnend mit der ersten Datendatei zugewiesen.

Der Tablespace besteht aus Datenbankseiten mit einer Standardgröße von 16KB. Die Seiten werden zu Extents von 64 aufeinander folgenden Seiten zusammengefasst. Die „Dateien“ eines Tablespace nennt man in InnoDB Segmente. Der Begriff „Rollback-Segment“ ist etwas irreführend, da er in Wirklichkeit viele Tablespace-Segmente umfasst.

Für jeden Index in InnoDB werden zwei Segmente zugewiesen. eines für Nicht-Blattknoten und das andere für Blattknoten des B-Baums. Dadurch wird eine bessere Abfolge der Blattknoten, die die eigentlichen Daten enthalten, erreicht.

Wenn ein Segment im Tablespace anwächst, weist ihm InnoDB die ersten 32 Seiten einzeln zu, und danach nur noch vollständige Extents. InnoDB kann einem großen Segment bis zu 4 Extents zugleich zuweisen, um eine gute Datensequenzialität zu gewährleisten.

Da manche Seiten im Tablespace Bitmaps anderer Seiten enthalten, können einige Extents eines InnoDB-Tablespace den Segmenten nicht als Ganzes, sondern nur in Form einzelner Seiten zugewiesen werden.

Wenn Sie mit einer SHOW TABLE STATUS-Anweisung fragen, wie viel Platz in einem Tablespace noch frei ist, meldet InnoDB nur die Extents, die darin definitiv noch nicht belegt sind. InnoDB reserviert immer einige Extents für die Reinigungsaufgaben und andere interne Zwecke. Diese reservierten Extents sind in dem freien Platz nicht inbegriffen.

Wenn Sie Daten aus einer Tabelle löschen, kontrahiert InnoDB die entsprechenden B-Baum-Indizes. Ob der frei gewordene Platz für ander Benutzer verfügbar wird, hängt davon ab, ob das Löschungsmuster einzelne Seiten oder ganze Extents des Tablespace freigibt. Wenn Sie eine Tabelle oder alle in ihr befindlichen Zeilen löschen, dann wird garantiert der Platz für andere Benutzer freigegeben, aber vergessen Sie nicht, dass gelöschte Zeilen physikalisch erst in einer (automatischen) Purge-Operation entfernt werden, wenn sie auch für Transaktions-Rollbacks und konsistente Leseoperationen nicht mehr gebraucht werden. (Siehe Abschnitt 14.2.12, „Implementierung der Multiversionierung“.)

Wenn willkürliche Einfügungen oder Löschungen in den Indizes einer Tabelle vorgenommen werden, können diese Indizes fragmentiert werden. Fragmentierung bedeutet, dass die physikalische Reihenfolge der Indexseiten auf der Platte nicht der Reihenfolge des Index für die Datensätze der Seiten entspricht, oder dass viele ungenutzte Seiten in den 64-Seiten-Blöcken vorhanden sind, die dem Index zugewiesen wurden.

Ein Fragmentierungssymptom liegt vor, wenn eine Tabelle mehr Platz belegt, als sie „sollte“. Wieviel mehr, das ist in der Praxis schwer zu sagen. Alle InnoDB-Daten und -Indizes werden in B-Bäumen gespeichert und ihr Füllfaktor kann zwischen 50% und 100% variieren. Ein anderes Symptom für Fragmentierung wäre es, wenn ein Tabellenscan wie dieser mehr Zeit braucht, als er „sollte“:

SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;

(In der obigen Anfrage „überlisten“ wir die SQL-Optimierung, damit sie statt des Sekundärindex den geclusterten Index durchsucht.) Die meisten Festplatten können 10 bis 50MB/s lesen. Anhand dieses Werts können sie einschätzen, wie schnell ein Tabellenscan eigentlich laufen sollte.

Index-Scans können schneller laufen, wenn Sie regelmäßig eine „Null“-ALTER TABLE-Operation durchführen:

ALTER TABLE tbl_name ENGINE=INNODB

Dies veranlasst MySQL, die Tabelle neu zu erstellen. Eine andere Möglichkeit für eine Defragmentierungsoperation wäre es, die Tabelle mit mysqldump in eine Textdatei zu dumpen, zu löschen und aus der Dump-Datei neu zu laden.

Wenn die Einfügungen in einen Index immer in aufsteigender Reihenfolge und Löschungen nur an seinem Ende stattfinden, garantiert der Dateiraumverwaltungs-Algorithmus von InnoDB, dass keine Fragmentierung in diesem Index auftreten kann.

Es folgt eine (nicht vollständige) Liste häufiger InnoDB-spezifischer Fehler, einschließlich ihrer Ursachen und Lösungsmöglichkeiten.

Die Bedeutung einer Betriebssystem-Fehlernummer können Sie mit dem Programm perror ermitteln, das zur MySQL-Distribution gehört.

Die folgende Tabelle enthält eine Liste der häufigsten Systemfehlercodes von Linux. Eine vollständigere Aufstellung finden Sie unter Linux-Quellcode.

Die folgende Tabelle enthält eine Liste häufiger Windows-Systemfehlercodes. Eine vollständigere Aufstellung finden Sie auf der Website von Microsoft.

Ein besonderes Problem mit Tabellen besteht darin, dass der MySQL-Server Data Dictionary-Informationen in .frm-Dateien in Datenbankverzeichnissen ablegt, während InnoDB die Informationen auch in sein eigenes Data Dictionary innerhalb der Tablespace-Dateien speichert. Wenn Sie .frm-Dateien verschieben oder der Server inmitten einer Data Dictionary-Operation abstürzt, kann es geschehen, dass die Speicherorte der .frm-Dateien hinterher nicht mehr zu den Angaben im InnoDB-internen Data Dictionary passen.

Eine gescheiterte CREATE TABLE-Anweisung ist ein Symptom für ein nicht mehr synchrones Data Dictionary. Wenn dies passiert, schauen Sie im Fehlerlog des Servers nach. Wird dort behauptet, dass die Tabelle im InnoDB-internen Data Dictionary bereits existiert, so haben Sie in den InnoDB-Tablespace-Dateien, eine verwaiste Tabelle, zu der keine .frm-Datei gehört. Die Fehlermeldung sieht wie folgt aus:

InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

Nach den Instruktionen der Fehlermeldung können Sie die verwaiste Tabelle löschen. Wenn Sie DROP TABLE immer noch nicht ausführen können, kann das Problem auch an der AutoVervollständigung von Namen durch den mysql-Client liegen. Um dieses zu verhindern, starten Sie den mysql-Client mit der --disable-auto-rehash-Option und versuchen noch einmal Ihr DROP TABLE. (Bei eingeschalteter Namensvervollständigung versucht mysql eine Liste von Tabellennamen zu erstellen. Das scheitert, wenn ein Problem wie das oben beschriebene auftritt.)

Ein anderes Symptom für ein nicht mehr synchrones Data Dictionary liegt vor, wenn MySQL meldet, dass eine .InnoDB-Datei nicht geöffnet werden kann:

ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)

Im Fehlerlog können Sie dann eine Nachricht wie diese vorfinden:

InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?

Dies bedeutet, dass Sie eine verwaiste .frm-Datei haben, zu der es in InnoDB keine Tabelle gibt. Die verwaiste .frm-Datei können Sie dann manuell löschen.

Wenn MySQL mitten in einer ALTER TABLE-Operation abstürzt, haben Sie hinterher vielleicht eine verwaiste temporäre Tabelle im InnoDB-Tablespace. Mit innodb_table_monitor sehen Sie dann eine Tabelle namens #sql-... aufgeführt. Sie können SQL-Anweisungen auf Tabellen ausführen, deren Name das Zeichen ‘#’ enthält, wenn Sie den Namen in Backticks setzen. So können Sie die verwaiste Tabelle wie jede andere auch mit den zuvor beschriebenen Methoden löschen. Achtung: Um eine Datei in der Unix-Shell zu kopieren oder umzubenennen, müssen Sie den Dateinamen, wenn er ‘#’ enthält, in doppelte Anführungszeichen setzen.