LOCK TABLEStbl_name
[ASalias
] {READ [LOCAL] | [LOW_PRIORITY] WRITE} [,tbl_name
[ASalias
] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ... UNLOCK TABLES
LOCK TABLES
sperrt Tabellen für den
aktuellen Thread. Wenn eine Tabelle von anderen Threads gesperrt
ist, wird der laufende Vorgang angehalten, bis alle Sperren
erwirkt werden können. UNLOCK TABLES
hebt
Sperren auf, die vom aktuellen Thread gehalten werden. Die
Sperren aller Tabellen, die vom aktuellen Thread gesperrt
werden, werden implizit aufgehoben, wenn der Thread eine andere
LOCK TABLES
-Anweisung absetzt oder die
Verbindung zum Server geschlossen wird.
Eine Tabellensperre schützt nur gegen unangemessene Lese- und
Schreiboperationen durch andere Clients. Der Client, der die
Sperre (auch eine Lesesperre) hält, kann Operationen auf
Tabellenebene – wie etwa DROP TABLE
–
durchführen.
Beachten Sie die folgenden Angaben zur Verwendung von
LOCK TABLES
bei transaktionssicheren
Tabellen:
LOCK TABLES
ist nicht transaktionssicher
und übergibt implizit alle aktiven Transaktionen, bevor
versucht wird, die Sperre für die Tabelle zu erwirken. Auch
das Starten einer Transaktion (z. B. bei START
TRANSACTION
) führt implizit eine UNLOCK
TABLES
-Anweisung aus. (Siehe auch
Abschnitt 13.4.3, „Anweisungen, die implizite Commits verursachen“.)
Die korrekte Vorgehensweise bei der Verwendung von
LOCK TABLES
in Verbindung mit
transaktionssicheren Tabellen wie InnoDB
besteht darin, AUTOCOMMIT = 0
zu setzen
und UNLOCK TABLES
erst dann aufzurufen,
wenn die Transaktion explizit übergeben werden soll. Wenn
Sie LOCK TABLES
aufrufen, setzt
InnoDB
intern eine eigene Tabellensperre.
Auch MySQL setzt eine eigene Tabellensperre.
InnoDB
hebt seine Sperre beim nächsten
Commit-Vorgang auf; damit MySQL seinerseits die Sperre
aufhebt, müssen Sie UNLOCK TABLES
aufrufen. Sie sollten AUTOCOMMIT = 1
nicht setzen, weil InnoDB
dann seine
Tabellensperre direkt nach dem Aufruf von LOCK
TABLES
aufhebt; auf diese Weise kann es zu einer
vollständigen Sperrung kommen. Beachten Sie, dass bei
AUTOCOMMIT = 1
die
InnoDB
-Tabellensperre überhaupt nicht
gesetzt wird, damit ältere Anwendungen sich nicht
versehentlich vollständig ausschließen.
ROLLBACK
hebt die Sperren bei den
nichttransaktionssicheren Tabellen von MySQL nicht auf.
Um LOCK TABLES
zu verwenden, benötigen Sie
die Berechtigungen LOCK TABLES
und
SELECT
für die betreffenden Tabellen.
Die wichtigsten Gründe zur Verwendung von LOCK
TABLES
sind die Emulation von Transaktionen oder die
Beschleunigung der Aktualisierung von Tabellen. Wir werden dies
weiter unten ausführlicher erläutern.
Wenn ein Thread eine Lesesperre für eine Tabelle erwirkt, kann dieser Thread (wie auch alle übrigen Threads) aus der Tabelle nur lesen. Wenn ein Thread eine Schreibsperre für eine Tabelle erwirkt, dann kann nur der Thread, der die Sperre hält, in die Tabelle schreiben. Anderen Threads wird dies untersagt, bis die Sperre aufgehoben ist.
Der Unterschied zwischen READ LOCAL
und
READ
besteht darin, dass READ
LOCAL
bei aktiver Sperre die Ausführung von
INSERT
-Anweisungen (also nebenläufigen
Einfügeoperationen) zulässt, sofern sie keine Konflikte
auslösen. Allerdings kann diese Funktion nicht verwendet
werden, um die Datenbankdateien bei aktiver Sperre außerhalb
von MySQL zu manipulieren. Bei
InnoDB
-Tabellen entspricht READ
LOCAL
READ
.
Wenn Sie LOCK TABLES
einsetzen, müssen Sie
alle Tabellen sperren, die Sie in Ihren Abfragen zu benutzen
beabsichtigen. Solange die mit einer LOCK
TABLES
-Anweisung erwirkten Sperren gültig sind,
können Sie nicht auf Tabellen zugreifen, die nicht durch die
Anweisung gesperrt wurden. Ferner können Sie eine gesperrte
Tabelle auch nicht mehrfach in derselben Abfrage verwenden.
Benutzen Sie stattdessen Aliase. In diesem Fall müssen Sie
allerdings für jeden Alias separat eine Sperre erwirken.
mysql>LOCK TABLE t WRITE, t AS t1 WRITE;
mysql>INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES mysql>INSERT INTO t SELECT * FROM t AS t1;
Wenn Ihre Abfragen eine Tabelle unter Verwendung eines Alias referenzieren, müssen Sie die Tabelle sperren, die denselben Alias benutzt. Die Tabelle zu sperren, ohne den Alias anzugeben, funktioniert nicht:
mysql>LOCK TABLE t READ;
mysql>SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES
Umgekehrt müssen Sie, wenn Sie eine Tabelle unter Verwendung eines Alias sperren, diese Tabelle unter Verwendung dieses Alias in Ihren Abfragen referenzieren:
mysql>LOCK TABLE t AS myalias READ;
mysql>SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES mysql>SELECT * FROM t AS myalias;
WRITE
-Sperren haben in der Regel Vorrang vor
READ
-Sperren. Hierdurch soll sichergestellt
werden, dass Aktualisierungen so schnell wie möglich
verarbeitet werden. Das bedeutet, dass, wenn ein Thread eine
READ
-Sperre erwirkt und dann ein anderer
Thread eine WRITE
-Sperre anfordert,
nachfolgende READ
-Sperranforderungen warten
müssen, bis der WRITE
-Thread die Sperre
wieder aufgehoben hat. Sie können mit LOW_PRIORITY
WRITE
-Sperren anderen Threads das Erwirken von
READ
-Sperren gestatten, während der Thread
auf die WRITE
-Sperre wartet.
LOW_PRIORITY WRITE
-Sperren sollten Sie nur
dann verwenden, wenn Sie sicher sind, dass zu einem
nachfolgenden Zeitpunkt keine Threads mehr eine
READ
-Sperre halten werden.
LOCK TABLES
funktioniert wie folgt:
Alle zu sperrenden Tabellen werden in einer intern definierten Reihenfolge sortiert. Aus Sicht des Benutzers ist diese Reihenfolge undefiniert.
Wenn eine Tabelle mit einer Lese- und einer Schreibsperre gesperrt ist, wird die Schreib- vor der Lesesperre gesetzt.
Es wird immer nur eine Tabelle gleichzeitig gesperrt, bis der Thread alle Sperren erhalten hat.
Diese Vorgehensweise gewährleistet, dass ein Aussperren ausgeschlossen ist. Es gibt jedoch ein paar andere Aspekte, die Sie bezüglich der Vorgehensweise beachten sollten:
Wenn Sie eine LOW_PRIORITY WRITE
-Sperre für
eine Tabelle verwenden, bedeutet dies lediglich, dass MySQL auf
diese bestimmte Sperre wartet, bis keine Threads mehr vorhanden
sind, die eine READ
-Sperre benötigen. Wenn
der Thread die WRITE
-Sperre erwirkt hat und
darauf wartet, die Sperre für die nächste Tabelle in der
Sperrliste zu erwirken, warten alle anderen Threads darauf, dass
die WRITE
-Sperre aufgehoben wird. Wenn dies
in Bezug auf Ihre Anwendung ein schwerwiegendes Problem
darstellen sollte, dann sollten Sie in Betracht ziehen, einige
Ihrer Tabellen in transaktionssichere Tabellen umzuwandeln.
Sie können einen Thread, der auf eine Tabellensperre wartet,
problemlos mit KILL
terminieren. Siehe auch
Abschnitt 13.5.5.3, „KILL
“.
Beachten Sie, dass Sie keine Tabellen
sperren sollen, die Sie mit INSERT DELAYED
verwenden, weil die Einfügeoperation in diesem Fall durch einen
separaten Thread durchgeführt wird.
Normalerweise müssen Sie Tabellen nicht sperren, weil alle
UPDATE
-Anweisungen für sich atomisch sind:
Kein anderer Thread kann eine andere derzeit ausgeführte
SQL-Anweisung unterbrechen. Allerdings gibt es einige wenige
Fälle, in denen das Sperren von Tabellen von Vorteil sein kann:
Wenn Sie viele Operationen für eine Anzahl von
MyISAM
-Tabellen ausführen wollen, geht
dies schneller, wenn Sie die zu verwendenden Tabellen
sperren. Das Sperren von MyISAM
-Tabellen
beschleunigt das Einfügen, Aktualisieren und Löschen in
diesen Tabellen. Der Nachteil besteht darin, dass kein
Thread eine READ
-gesperrte Tabelle
ändern kann (einschließlich derjenigen, die die Sperre
hält) und dass kein Thread auf eine
WRITE
-gesperrte Tabelle mit Ausnahme
derjenigen zugreifen kann, die die Sperre hält.
Der Grund dafür, dass MyISAM
-Operationen
unter LOCK TABLES
schneller ausgeführt
werden, besteht darin, dass MySQL den Schlüssel-Cache
gesperrter Tabellen erst beim Aufruf von UNLOCK
TABLES
synchronisiert. Normalerweise wird der
Schlüssel-Cache nach jeder SQL-Anweisung synchronisiert.
Wenn Sie eine Speicher-Engine in MySQL verwenden, die
Transaktionen nicht unterstützt, müssen Sie LOCK
TABLES
einsetzen, um sicherzustellen, dass kein
anderer Thread zwischen eine SELECT
- und
eine UPDATE
-Anweisung gelangt. Folgendes
Beispiel erfordert LOCK TABLES
, um sicher
ausgeführt werden zu können:
LOCK TABLES trans READ, customer WRITE; SELECT SUM(value) FROM trans WHERE customer_id=some_id
; UPDATE customer SET total_value=sum_from_previous_statement
WHERE customer_id=some_id
; UNLOCK TABLES;
Ohne LOCK TABLES
könnte ein anderer
Thread unter Umständen zwischen der Ausführung der
SELECT
- und der Ausführung der
UPDATE
-Anweisung einen neuen Datensatz in
die trans
-Tabelle einfügen.
Sie können die Verwendung von LOCK TABLES
in
vielen Fällen umgehen, indem Sie relative Updates
(UPDATE customer SET
)
oder die Funktion value
=value
+new_value
LAST_INSERT_ID()
verwenden.
Siehe auch Abschnitt 1.9.5.3, „Transaktionen“.
Sie können das Sperren von Tabellen in manchen Fällen auch
umgehen, indem Sie Funktionen für beratende Sperrungen auf
Benutzerebene (GET_LOCK()
und
RELEASE_LOCK()
) verwenden. Diese Sperren
werden in einer Hash-Tabelle auf dem Server gespeichert und mit
pthread_mutex_lock()
und
pthread_mutex_unlock()
mit dem Ziel einer
hohen Geschwindigkeit implementiert. Siehe auch
Abschnitt 12.10.4, „Verschiedene Funktionen“.
Weitere Informationen zu Methoden beim Sperren finden Sie in Abschnitt 7.3.1, „Wie MySQL Tabellen sperrt“.
Sie können mit der Anweisung FLUSH TABLES WITH READ
LOCK
Lesesperren für alle Tabellen in allen
Datenbanken setzen. Siehe auch Abschnitt 13.5.5.2, „FLUSH
“. Dies ist
eine recht praktische Möglichkeit der Datensicherung, wenn Sie
ein Dateisystem wie Veritas einsetzen, das rechtzeitig
Schnappschüsse erstellt.
Hinweis: Wenn Sie
ALTER TABLE
für eine gesperrte Tabelle
ausführen, kann die Sperre unter Umständen aufgehoben werden.
Siehe auch Abschnitt A.7.1, „Probleme mit ALTER TABLE
“.
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.