Skip navigation links
MySQL 5.1 Referenzhandbuch :: 14 Speicher-Engines und Tabellentypen :: 14.2 InnoDB-Tabellen :: 14.2.10 InnoDB-Transaktionsmodell :: 14.2.10.8 Sperren, die in InnoDB durch unterschiedliche SQL-Anweisungen gesetzt werden
14.2.10.9 Implizites Commit und Rollback von Transaktionen »
Section Navigation      [Toggle]

14.2.10.8. Sperren, die in InnoDB durch unterschiedliche SQL-Anweisungen gesetzt werden

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:

  • SELECT … FROM ist ein konsistenter Lesevorgang, der einen Snapshot der Datenbank betrachtet und nur dann Sperren errichtet, wenn die Transaktionsisolationsebene SERIALIZABLE ist. In diesem Fall werden Shared Next-Key-Sperren auf die gefundenen Indexeinträge gesetzt.

  • SELECT … FROM … LOCK IN SHARE MODE setzt Shared Next-Key-Sperren auf alle Indexeinträge, die der Lesevorgang findet.

  • SELECT … FROM … FOR UPDATE setzt exklusive Next-Key-Sperren auf alle Indexeinträge, die der Lesevorgang findet.

  • INSERT INTO … VALUES (…) setzt eine exklusive Sperre auf die eingefügte Zeile. Beachten Sie, dass diese Sperre keine Next-Key-Sperre ist und daher andere Benutzer nicht daran hindert, in die Lücke vor der neuen Zeile etwas einzufügen. Wenn ein Fehler wegen Schlüsselduplikaten auftritt, wird eine Shared-Sperre auf dem Duplikat-Indexeintrag gesetzt.

  • Bei der Initialisierung einer zuvor angegebenen AUTO_INCREMENT-Spalte auf einer Tabelle setzt InnoDB eine exklusive Sperre auf das Ende des mit dieser AUTO_INCREMENT-Spalte verbundenen Index. Für den Zugriff auf den Auto-Increment-Zähler nutzt InnoDB einen bestimmten Tabellensperrmodus namens AUTO-INC, mit dem die Sperre nur bis zum Ende der aktuellen SQL-Anweisung und nicht der gesamten Transaktion aufrecht erhalten bleibt. Siehe Abschnitt 14.2.10.2, „InnoDB und AUTOCOMMIT“.

    InnoDB ruft den Wert einer initialisierten AUTO_INCREMENT-Spalte ab, ohne Sperren zu setzen.

  • INSERT INTO T SELECT … FROM S WHERE … errichtet eine exklusive Sperre (keine Next-Key-Sperre) auf jeder in T eingefügten Zeile. InnoDB erwirbt Shared Next-Key-Sperren auf S, wenn nicht innodb_locks_unsafe_for_binlog aktiviert ist. In diesem Fall durchsucht es S mit einer konsistenten Leseoperation. InnoDB muss im zweiten Fall Sperren setzen: Bei einer Roll-forward-Recovery von einem Backup muss jede SQL-Anweisung in genau derselben Weise wie ursprünglich ausgeführt werden.

  • CREATE TABLE … SELECT … führt das SELECT als konsistente Leseoperation oder mit Shared-Sperren durch, wie oben beschrieben.

  • REPLACE wird wie ein Insert ausgeführt, wenn keine Konflikte auf einem eindeutigen Schlüssel entstehen. Andernfalls wird eine exklusive Next-Key-Sperre auf der zu aktualisierenden Zeile errichtet.

  • UPDATE … WHERE … setzt eine exklusive Next-Key-Sperre auf jeden bei der Suche gefundenen Datensatz.

  • DELETE FROM … WHERE … setzt eine exklusive Next-Key-Sperre auf jeden bei der Suche gefundenen Datensatz.

  • Wenn ein FOREIGN KEY-Constraint auf einer Tabelle definiert ist, setzt jedes Insert, Update oder Delete, bei dem die Constraint-Bedingung geprüft werden muss, Shared-Zeilensperren auf die betrachteten Datensätze, um den Constraint zu überprüfen. InnoDB setzt diese Sperren auch dann, wenn der Constraint versagt.

  • LOCK TABLES setzt Tabellensperren, allerdings auf der MySQL-Ebene, die höher als die InnoDB-Ebene liegt. InnoDB weiß von den 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, an denen solche Tabellensperren beteiligt sind. Außerdem wäre es dann möglich, eine Tabellensperre auf einer Tabelle zu errichten, in der ein anderer Benutzer gleichzeitig Zeilensperren hält, weil die höhere MySQL-Ebene diese Zeilensperren nicht kennt. Dies bedroht allerdings nicht die Transaktionsintegrität, wie in Abschnitt 14.2.10.10, „Blockierungserkennung und Rollback“ erläutert. Siehe auch Abschnitt 14.2.16, „Beschränkungen von InnoDB-Tabellen“.

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.