Skip navigation links
MySQL 5.1 Referenzhandbuch :: 7 Optimierung :: 7.3 Probleme mit Sperren :: 7.3.1 Wie MySQL Tabellen sperrt
7.3.2 Themen, die Tabellensperren betreffen »
Section Navigation      [Toggle]

7.3.1. Wie MySQL Tabellen sperrt

MySQL verwendet die Sperrung auf Tabellenebene für MyISAM- und MEMORY-Tabellen, die Sperrung auf Seitenebene für BDB-Tabellen und die Sperrung auf Datensatzebene für InnoDB-Tabellen.

In vielen Fällen können Sie eine begründete Annahme dazu treffen, welcher Sperrungstyp der beste für eine Anwendung ist; generell ist es aber schwierig zu sagen, dass ein bestimmter Sperrungstyp besser ist als ein anderer. Alles hängt von der Anwendung ab, und verschiedene Teile einer Anwendung können unterschiedliche Sperrungstypen erfordern.

Um zu entscheiden, ob Sie eine Speicher-Engine mit Sperrung auf Datensatzebene verwenden sollten, müssen Sie einschätzen, was Ihre Anwendung tut und welche Mischung aus Auswahl- und Änderungsanweisungen sie verwendet. Die meisten Webanwendungen führen beispielsweise sehr viele Auswahloperationen, relativ wenig Löschvorgänge, vorzugsweise auf Schlüsselwerten basierende Änderungen und Einfügungen in nur ein paar bestimmte Tabellen durch. Die MyISAM-Basiskonfiguration von MySQL ist für solche Zwecke hervorragend geeignet.

Die Tabellensperrung in MySQL schließt ein Deadlock bei Speicher-Engines aus, die Sperren auf Tabellenebene verwenden. Die Deadlock-Vermeidung wird verwaltet, indem zu Anfang einer Abfrage immer alle erforderlichen Sperren auf einmal angefordert und die Tabellen immer in derselben Reihenfolge gesperrt werden.

Die Tabellensperrmethode, die MySQL für WRITE-Sperren verwendet, funktioniert wie folgt:

  • Wenn keine Sperre für die Tabelle vorhanden ist, wird eine Schreibsperre verhängt.

  • Andernfalls wird die Sperranforderung in die Warteschlange für Schreibsperranforderungen gesetzt.

Die Tabellensperrmethode, die MySQL für READ-Sperren verwendet, funktioniert wie folgt:

  • Wenn keine Schreibsperre für die Tabelle vorhanden ist, wird eine Lesesperre verhängt.

  • Andernfalls wird die Sperranforderung in die Warteschlange für Lesesperranforderungen gesetzt.

Wenn eine Sperre aufgehoben wird, wird die Sperrmöglichkeit zunächst den Threads in der Warteschleife für Schreibsperranforderungen und dann den Threads in der Warteschleife für Lesesperranforderungen verfügbar gemacht: Wenn Sie also viele Änderungen an einer Tabelle vorgenommen haben, warten SELECT-Anweisungen, bis keine Änderungen mehr anhängig sind.

Sie können die Zugriffssituation bezüglich der Tabellensperren auf Ihrem System analysieren, indem Sie die Statusvariablen Table_locks_waited und Table_locks_immediate überprüfen: 

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

Wenn eine MyISAM-Tabelle keine freien Blöcke in der Mitte enthält, werden Datensätze immer am Ende der Datendatei eingefügt. In diesem Fall können Sie INSERT- und SELECT-Anweisungen bei einer MyISAM-Tabelle ohne Sperre gleichzeitig verwenden. Sie können also Datensätze in eine MyISAM-Tabelle einfügen, während andere Clients zur selben Zeit daraus lesen. (Löcher können entstehen, wenn Datensätze in der Mitte der Tabelle gelöscht oder aktualisiert wurden. Wenn Löcher vorhanden sind, sind gleichzeitige Einfügeoperationen nicht möglich; sie werden allerdings sofort wieder verfügbar, sobald alle Löcher mit neuen Daten gefüllt wurden.)

Wenn Sie viele INSERT- und SELECT-Operationen an einer Tabelle durchführen wollen, während gleichzeitige Einfügeoperationen gerade nicht möglich sind, dann können Sie Datensätze in eine Temporärtabelle einfügen und die echte Tabelle dann sporadisch mit den Datensätzen aus dieser Temporärtabelle aktualisieren. Dies ist etwa mit dem folgenden Code möglich: 

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

InnoDB verwendet Datensatzsperren, BDB hingegen Seitensperren. Bei diesen beiden Speicher-Engines sind Deadlocks möglich, weil sie während der Verarbeitung von SQL-Anweisungen automatisch Sperren erwirken – und nicht gleich beim Start der Transaktion.

Vorteile der Sperrung auf Datensatzebene:

  • weniger Sperrkonflikte beim Zugriff auf verschiedene Datensätze in vielen Threads

  • weniger Änderungen bei Rollbacks

  • Möglichkeit einer sehr langen Sperrdauer für einen Datensatz

Nachteile der Sperrung auf Datensatzebene:

  • erfordert mehr Speicher als die Sperrung auf Seiten- oder Tabellenebene

  • langsamer als die Sperrung auf Seiten- oder Tabellenebene, wenn sie für einen großen Teil der Tabelle verwendet wird, weil Sie wesentlich mehr Sperren erwirken müssen

  • definitiv wesentlich langsamer als die anderen Sperrungsoptionen, wenn Sie häufig GROUP BY-Operationen für einen großen Teil der Daten durchführen oder oft die gesamte Tabelle scannen müssen

Tabellensperren sind Sperren auf Seiten- oder Datensatzebene in den folgenden Fällen überlegen:

  • Die meisten Anweisungen für die Tabelle sind Leseoperationen.

  • Eine Mischung von Lese- und Schreiboperationen, wobei die Schreiboperationen Updates oder Löschvorgänge für einen einzelnen Datensatz sind, der mit einem Schlüssellesevorgang geholt werden kann: 

    UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;

  • SELECT in Kombination mit gleichzeitigen INSERT-Anweisungen und sehr wenigen UPDATE- oder DELETE-Anweisungen.

  • Viele Scans oder GROUP BY-Operationen über die gesamte Tabelle ohne Schreiber.

Bei Sperren höherer Ebene können Sie Anwendungen einfacher optimieren, indem Sie Sperren verschiedener Typen unterstützen, weil der Overhead wesentlich geringer ist als bei Sperren auf Datensatzebene.

Andere Optionen als Sperrungen auf Datensatz- und Seitenebene:

  • Versionierung (wie sie beispielsweise in MySQL für nebenläufige Einfügeoperationen verwendet wird). Hierbei können ein Schreiber und mehrere Leser nebeneinander arbeiten. Dies bedeutet, dass die Datenbank oder Tabelle verschiedene Views der Daten abhängig vom Zeitpunkt des Zugriffsbeginns unterstützt. Andere gängige Bezeichnungen hierfür sind „Zeitreise“, „Copy on Write“ (Kopieren beim Schreiben) und „Copy on Demand“ (Kopieren bei Bedarf).

  • Copy on Demand ist der Sperrung auf Seiten- oder Datensatzebene in vielen Fällen überlegen. Schlimmstenfalls kann sie jedoch wesentlich mehr Speicher beanspruchen als normale Sperren.

  • Anstelle von Sperren auf Datensatzebene können Sie auch Sperren auf Anwendungsebene verwenden – in MySQL etwa GET_LOCK() und RELEASE_LOCK(). Dies sind beratende Sperren, d. h., sie funktionieren nur in „wohlerzogenen“ Anwendungen.

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.