MySQL – Vor- und Nachteile gesperrter Tabellen (table lock)

MySQL bietet die Möglichkeit des exklusiven Tabellenzugriffs für eine bestehende Verbindung. Die Tabelle wird also für andere Verbindungen gesperrt. In diesem Beitrag werde ich die dafür notwendigen SQL-Befehle sowie die Vor- und Nachteile gesperrter Tabellen beleuchten.

SQL-Befehle zum Sperren und Freigeben von Tabellen

Sperren

Die Syntax zum Sperren einer Tabelle ist denkbar einfach:

und

Anhand des Befehls TABLES könnt ihr schon erkennen, dass auch mehrere Tabellen auf einmal gesperrt werden können. Dazu werden die Tabellennamen mit dem jeweiligen Zugriffsrecht (Lesen/Schreiben) durch Komma getrennt aufgelistet.

Achtung: Jedes Mal, wenn mit obigen Befehlen neue Tabellen gesperrt werden sollen, werden die zuvor gesperrten automatisch freigegeben. Ihr dürft also nicht für jede Tabelle einzeln eine Anfrage schicken, sondern müsst alle auf einmal anfordern.

Freigeben

Zum freigeben aller zuvor gesperrten Tabellen genügt ein

Automatische Freigabe

Um den weiteren Betrieb der Datenbank nicht zu behindern, indem Tabellen für immer gesperrt bleiben, werden alle Tabellen automatisch wieder freigegeben, wenn die Verbindung beendet wird – egal ob regulär oder außerplanmäßig.

Zu Beginn einer Transaktion werden ebenfalls alle Tabellen implizit freigegeben.

Ist der Tabellentyp InnoDB dann wird auch nach einem ALTER TABLE die entsprechende Tabelle freigegeben.

Berechtigung

Um Tabellen sperren zu dürfen, benötigt der entsprechende Benutzer die Rechte LOCK_TABLES und SELECT. Beides sollte normalerweise vorhanden sein.

Wer hat Zugriff auf gesperrte Tabellen?

Zum Lesen (READ) gesperrte Tabellen

Auf die Tabellen noch zugreifen darf:

  • Die Session selbst, solange sie nur lesen will (zum Beispiel mit SELECT)
  • Jede andere Verbindung/Session, solange sie ebenfalls nur lesen will
  • Andere Verbindungen können die Tabelle ebenfalls für Lesen (READ) sperren

Nicht mehr erlaubt ist:

  • Lesen einer anderen nicht gesperrten Tabelle
  • Jede Schreiboperation egal von welcher Session

Zum Schreiben (WRITE) gesperrte Tabellen

Auf die Tabellen noch zugreifen darf:

  • Die Session selbst darf sowohl lesen als auch schreiben
  • Die Session selbst darf auch DROP und TRUNCATE Operationen ausführen

Nicht mehr erlaubt ist:

  • Jeglicher Zugriff von anderen Sessions
  • Andere Sessions erhalten auch keinen Lock für die Tabelle

Vor- und Nachteile einer gesperrten Tabelle

Generell sollte sehr sparsam mit LOCK_TABLES umgegangen werden, da sie alle anderen Prozesse beeinträchtigen. Sperren wir eine Tabelle für den Schreibzugriff, müssen alle anderen Verbindungen auf den Abschluss unserer Aktion warten, bevor sie ihre Anfrage weiter bearbeiten können. Insbesondere auf stark frequentierten Tabellen wird die deutlich spürbar sein.

Zum Verständnis: MySQL mit InnoDB sperrt selbstständig eine Zeile (bei UPDATE), bzw. das Ende der Tabelle (bei INSERT), wenn Schreibzugriffe ausgeführt werden. Dies blockiert andere Verbindungen aber nur, wenn sie ebenfalls genau diese Zeile benötigen. Andernfalls können sie unterbrechungsfrei agieren.

Wann ist jetzt das Sperren einer Tabelle sinnvoll?

Bei MySQL erfolgt ein Update in vier Schritten:

  • Betroffene Zeile sperren
  • Datensatz aktualisieren
  • Indizes anpassen
  • Zeile wieder freigeben

Auch wenn mehrere Datensätze nacheinander verändert werden, werden diese vier Schritte für jeden Datensatz einzeln durchgeführt. Die Anpassung der Indizes könnte aber problemlos warten, bis alle Zeilen aktualisiert sind. Dann müsste sie nur einmal ausgeführt werden, was wesentlich effizienter wäre. Insbesondere, wenn die Tabelle mehrere und komplizierte Indizes enthält, kann hier viel Zeit gespart werden.

In so einem Fall kann die Aktualisierung der Datensätze mit einer Tabellensperre optimiert werden. MySQL führt dann nur noch Schritt 2 (Datensatz aktualisieren) für jeden Eintrag einzeln aus. Die Indizes werden erst aktualisiert, wenn die Tabelle wieder freigegeben wird. Die Schritte 1 und 4 entfallen natürlich komplett.