Um eine sehr hohe Sperrgeschwindigkeit zu erzielen, verwendet
MySQL die Tabellensperrung (statt der Sperrung auf Seiten-,
Datensatz- oder Spaltenebene) für alle Speicher-Engines mit
Ausnahme von InnoDB
und
BDB
.
Bei InnoDB
- und
BDB
-Tabellen setzt MySQL die Tabellensperrung
nur dann ein, wenn Sie die Tabelle mit LOCK
TABLES
explizit sperren. Bei diesen Speicher-Engines
raten wir von der Verwendung von LOCK TABLES
vollständig ab, weil InnoDB
eine
automatische Sperrung auf Datensatzebene und
BDB
die Sperrung auf Seitenebene verwendet,
um die Transaktionsisolierung sicherzustellen.
Bei großen Tabellen ist die Tabellensperrung für die meisten Anwendungen wesentlich besser als die Datensatzsperrung. Es gibt allerdings auch ein paar Fallstricke:
Die Tabellensperrung gestattet mehreren Threads das Lesen aus der Tabelle zur selben Zeit; will ein Thread hingegen in eine Tabelle schreiben, so muss er zunächst exklusiven Zugriff erhalten. Alle anderen Threads, die auf die betreffende Tabelle zugreifen wollen, müssen warten, bis das Update abgeschlossen ist.
Tabellenupdates erhalten eine höhere Priorität als
Abrufvorgänge, weil sie normalerweise als wichtiger
betrachtet werden. Auf diese Weise soll sichergestellt
werden, dass Updates für eine Tabelle auch dann nicht
„verhungern“, wenn sehr viele
SELECT
-Anweisungen für die Tabelle
abgesetzt werden.
Die Tabellensperre kann beispielsweise Probleme verursachen, wenn ein Thread wartet, weil die Festplatte voll ist und erst freier Speicher benötigt wird, damit er fortgesetzt werden kann. In diesem Fall werden alle Threads, die auf die problematische Tabelle zugreifen wollen, ebenfalls in einen Wartezustand versetzt, bis wieder genug Festplattenkapazität verfügbar ist.
Die Tabellensperre ist auch in folgendem Szenario nachteilig:
Ein Client setzt eine SELECT
-Anweisung
ab, deren Ausführung sehr lange dauert.
Ein anderer Client setzt dann eine
UPDATE
-Anweisung für dieselbe Tabelle
ab. Dieser Client wartet, bis die
SELECT
-Anweisung abgeschlossen ist.
Wieder ein anderer Client setzt eine weitere
SELECT
-Anweisung für dieselbe Tabelle
ab. Weil UPDATE
aber eine höhere
Priorität hat als SELECT
, wartet diese
SELECT
-Anweisung darauf, dass die
UPDATE
-Anweisung wie
auch die erste
SELECT
-Anweisung fertig gestellt werden.
Die folgenden Punkte beschreiben einige Möglichkeiten, um den konkurrierenden Zugriff, der durch Tabellensperren verursacht werden kann, zu vermeiden oder zumindest zu verringern:
Versuchen Sie die Ausführung der
SELECT
-Anweisungen zu beschleunigen,
sodass die Tabellensperren verkürzt werden. Zu diesem Zweck
müssen Sie unter Umständen einige Zusammenfassungstabellen
erstellen.
Starten Sie mysqld mit der Option
--low-priority-updates
. So erhalten alle
Anweisungen, die eine Tabelle aktualisieren (also ändern),
eine niedrigere Priorität gegenüber
SELECT
-Anweisungen. In diesem Fall würde
die zweite SELECT
-Anweisung in obigem
Beispiel vor der UPDATE
-Anweisung
ausgeführt werden, und Sie müssten nicht warten, bis die
erste SELECT
-Anweisung abgeschlossen ist.
Sie können mit der Anweisung SET
LOW_PRIORITY_UPDATES=1
festlegen, dass alle
Aktualisierungen, die über eine bestimmte Verbindung
erfolgen, mit niedrigerer Priorität verarbeitet werden
sollen. Siehe auch Abschnitt 13.5.3, „SET
“.
Sie können einer bestimmten INSERT
-,
UPDATE
- oder
DELETE
-Anweisung mit dem Attribut
LOW_PRIORITY
eine niedrigere Priorität
zuweisen.
Sie können einer bestimmten
SELECT
-Anweisung mit dem Attribut
HIGH_PRIORITY
eine höhere Priorität
zuweisen. Siehe auch Abschnitt 13.2.7, „SELECT
“.
Sie können mysqld mit einem niedrigen
Wert für die Systemvariable
max_write_lock_count
starten: In diesem
Fall wird MySQL gezwungen, die Priorität aller
SELECT
-Anweisungen, die auf eine Tabelle
warten, zu senken, nachdem eine bestimmte Anzahl von
Einfügeoperationen in die Tabelle stattgefunden hat. Auf
diese Weise können nach einer bestimmten Anzahl von
WRITE
-Sperren auch
READ
-Sperren gesetzt werden.
Wenn Sie Probleme mit INSERT
in
Verbindung mit SELECT
haben, sollten Sie
in Betracht ziehen, auf MyISAM
-Tabellen
umzustellen, denn diese unterstützen gleichzeitige
SELECT
- und
INSERT
-Anweisungen.
Mischen Sie Einfüge- und Löschoperationen in derselben
Tabelle, dann kann INSERT DELAYED
sehr
hilfreich sein. Siehe auch Abschnitt 13.2.4.2, „INSERT DELAYED
“.
Wenn Sie Probleme mit gemischten SELECT
-
und DELETE
-Anweisungen haben, dann kann
die Option LIMIT
für
DELETE
nützlich sein. Siehe auch
Abschnitt 13.2.1, „DELETE
“.
Die Verwendung von SQL_BUFFER_RESULT
bei
SELECT
-Anweisungen kann dabei helfen, die
Dauer der Tabellensperren zu verkürzen. Siehe auch
Abschnitt 13.2.7, „SELECT
“.
Sie können den Sperrcode in
mysys/thr_lock.c
so abändern, dass nur
eine einzelne Warteschlange verwendet wird. In diesem Fall
hätten Schreib- und Lesesperren dieselbe Priorität, was
bei einigen Anwendungen von Nutzen sein kann.
Hier noch ein paar Tipps zu Tabellensperren in MySQL:
Gleichzeitige Benutzer stellen kein Problem dar, wenn Sie Update- nicht mit Auswahloperationen mischen, die viele Datensätze in derselben Tabelle untersuchen müssen.
Sie können mit LOCK TABLES
die
Geschwindigkeit erhöhen, weil viele Updates innerhalb einer
einzelnen Sperre wesentlich schneller erfolgen als die
Durchführung von Updates ohne Sperre. Auch das Aufteilen
des Tabelleninhalts auf mehrere Tabellen kann hilfreich
sein.
Wenn Sie auf geschwindigkeitsspezifische Probleme in
Verbindung mit Tabellensperren bei MySQL treffen, können
Sie die Leistung möglicherweise durch Konvertierung einiger
Ihrer Tabellen in InnoDB
- oder
BDB
-Tabellen verbessern. Siehe auch
Abschnitt 14.2, „InnoDB
-Tabellen“, und
Abschnitt 14.5, „Die BDB
-Speicher-Engine“.
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.