Practical MySQL & MariaDB

MySQL-Locking erklärt: Sperrstatus prüfen, Sperren sicher freigeben und Deadlocks verhindern (5.6–8.0)

Serverkonfiguration & Administration
目次

1. Einführung

MySQL wird häufig als Datenbankverwaltungssystem eingesetzt, aber wenn mehrere Abfragen versuchen, auf dieselben Daten zuzugreifen, wird ein Sperrmechanismus ausgelöst. Sperren sind entscheidend für die Aufrechterhaltung der Datenkonsistenz; jedoch kann eine unsachgemäße Verwaltung zu Deadlocks und Leistungsverschlechterungen führen.

In diesem Artikel erklären wir die grundlegenden Konzepte von Sperren in MySQL und geben detaillierte Anleitungen, wie man den Sperrstatus prüft, Sperren freigibt und Deadlocks verhindert.

Was Sie in diesem Artikel lernen werden

  • Die Arten von MySQL‑Sperren und ihre Auswirkungen
  • Wie man Sperren nach MySQL‑Version prüft
  • Sichere Verfahren zum Freigeben von Sperren
  • Praktische Tipps zur Vermeidung von Deadlocks

Beginnen wir mit der Erklärung der grundlegenden Konzepte von MySQL‑Sperren.

2. Grundlegende Konzepte von MySQL‑Sperren

In einer Datenbank ist eine „Sperre“ ein Mechanismus, der den Zugriff einschränkt, um die Datenintegrität zu wahren, wenn mehrere Transaktionen gleichzeitig versuchen, Daten zu ändern. Wenn Sperren nicht richtig verwaltet werden, können Leistungsverschlechterungen und Deadlocks auftreten.

2.1 Haupttypen von Sperren

In MySQL gibt es verschiedene Sperrarten, abhängig vom erforderlichen Schutzgrad der Daten.

Zeilensperre

  • Sperrt nur bestimmte Zeilen und minimiert die Auswirkungen auf andere Transaktionen.
  • Wird nur von der InnoDB‑Speicher-Engine unterstützt.
  • Wird ausgelöst, wenn SELECT ... FOR UPDATE oder SELECT ... LOCK IN SHARE MODE verwendet wird.

Tabellensperre

  • Sperrt die gesamte Tabelle und verhindert, dass mehrere Abfragen gleichzeitig ausgeführt werden.
  • Wird häufig mit der MyISAM‑Speicher-Engine verwendet.
  • Wird ausgelöst, wenn die Anweisung LOCK TABLES verwendet wird.

Intentionssperre

  • Eine Sperre, die verwendet wird, um Zeilen- und Tabellensperren zu koordinieren und Konflikte zu vermeiden.
  • Wird nur in InnoDB verwendet und automatisch verwaltet.

Deadlock

  • Ein Zustand, in dem mehrere Transaktionen auf die Sperren der jeweils anderen warten.
  • Wenn Transaktionen nicht korrekt gestaltet sind, kann die Verarbeitung vollständig zum Stillstand kommen.

2.2 Beispiele für das Auftreten von Sperren

Betrachten wir konkrete SQL‑Abfragen, um zu verstehen, wie Sperren entstehen.

Beispiel für Zeilensperre

Die Ausführung des folgenden SQL sperrt eine bestimmte Zeile.

BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 100;
-- Other sessions cannot update this row until this transaction is COMMIT or ROLLBACK

Wenn eine andere Sitzung versucht, dieselbe Zeile zu aktualisieren, gerät sie in einen Sperrwartestatus (Sperrkonkurrenz).

Beispiel für Tabellensperre

Um eine gesamte Tabelle zu sperren, verwenden Sie den folgenden Befehl.

LOCK TABLES products WRITE;
-- Other sessions cannot modify the products table until all read/write operations are complete

Solange diese Sperre nicht aufgehoben ist, können andere Benutzer die Daten in der Tabelle products nicht ändern.

Beispiel für Deadlock

Im Folgenden wird ein typisches Deadlock‑Szenario gezeigt.

-- Session 1
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
-- Waiting for Session 2...

-- Session 2
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
-- Waiting for Session 1...

-- Session 1 (executes another statement)
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- Deadlock occurs here

In dieser Situation wartet jede Transaktion darauf, dass die andere ihre Sperre freigibt, was zu einem Deadlock führt.

3. Wie man den MySQL‑Sperrstatus prüft (nach Version)

Um festzustellen, ob Sperren auftreten, müssen Sie Befehle ausführen, die zu Ihrer MySQL‑Version passen.

3.1 Wie man Sperren in MySQL 5.6 und früher prüft

In MySQL 5.6 und früher können Sie Sperrinformationen mit SHOW ENGINE INNODB STATUS\G; abfragen.

SHOW ENGINE INNODB STATUS\G;

Die Ausführung dieses Befehls zeigt detaillierte Informationen über aktuell aktive Sperren an.

3.2 Wie man Sperren in MySQL 5.7 prüft

In MySQL 5.7 und später ist die einfachste Methode, die Tabelle sys.innodb_lock_waits zu verwenden.

SELECT * FROM sys.innodb_lock_waits;

Durch Abfragen dieser Tabelle können Sie feststellen, welche Transaktionen auf Sperren warten.

3.3 So überprüfen Sie Sperren in MySQL 8.0 und höher

In MySQL 8.0 und höher können Sie detailliertere Sperrinformationen mit performance_schema.data_locks abrufen.

SELECT * FROM performance_schema.data_locks;

Um die Sitzung zu identifizieren, die die Sperre hält, verwenden Sie den folgenden SQL:

SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = <process_id>;

Dies ermöglicht es Ihnen, den Prozess zu identifizieren, der für die Sperre verantwortlich ist.

4. So geben Sie MySQL-Sperren frei (Risiken erklärt)

Wenn eine Sperre in MySQL auftritt und nicht richtig gehandhabt wird, kann die Verarbeitung stocken und die Datenbankleistung abnehmen.
In diesem Abschnitt erklären wir, wie Sie Sperren freigeben und welche Risiken damit verbunden sind.

4.1 Identifizieren der Sitzung, die die Sperre hält

Bevor Sie eine Sperre freigeben, müssen Sie feststellen, welche Sitzung sie hält. Verwenden Sie den folgenden SQL, um Sitzungen zu überprüfen, die auf Sperren warten:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE='Waiting for table metadata lock';

Diese Abfrage listet Sitzungen auf, die auf eine Sperre warten.

In MySQL 8.0 und höher können Sie detaillierte Sperrinformationen mit folgendem erhalten:

SELECT * FROM performance_schema.data_locks;

4.2 Sperren mit dem KILL-Befehl freigeben

Sobald Sie die Sitzung identifiziert haben, die die Sperre hält, können Sie sie freigeben, indem Sie den Prozess zwangsweise beenden.

1. Überprüfen Sie den Prozess, der die Sperre hält

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;

2. Beenden Sie die Sitzung mit dem KILL-Befehl

KILL <process_id>;

Zum Beispiel, um einen Prozess mit ID=12345 zu beenden, führen Sie aus:

KILL 12345;

⚠️ Risiken des KILL-Befehls

  • Zwangsweise beendete Transaktionen werden zurückgerollt
  • Zum Beispiel können Änderungen, die durch eine unterbrochene UPDATE-Anweisung vorgenommen wurden, verworfen werden.
  • Es kann zu Anwendungsfehlern führen
  • Wenn Sie häufig KILL verwenden müssen, sollten Sie Ihr Anwendungsdesign überprüfen.

4.3 Sperren mit ROLLBACK freigeben (sichere Methode)

Bevor Sie den KILL-Befehl verwenden, versuchen Sie, falls möglich, die Transaktion manuell zu beenden, die die Sperre verursacht.

1. Zuerst überprüfen Sie die aktuellen Transaktionen

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. Wenn Sie die problematische Transaktion finden, führen Sie ROLLBACK aus

ROLLBACK;

Diese Methode ermöglicht es Ihnen, die Sperre freizugeben, während die Datenkonsistenz erhalten bleibt.

4.4 Automatisierung der Sperrbehandlung mit SET innodb_lock_wait_timeout

Anstatt Sperren manuell freizugeben, können Sie eine Sperrwartetimeout konfigurieren, sodass die Transaktion automatisch timeoutet, wenn die Sperre innerhalb einer bestimmten Zeit nicht freigegeben wird.

SET innodb_lock_wait_timeout = 10;

Mit dieser Einstellung gibt MySQL, wenn die Sperre innerhalb von 10 Sekunden nicht freigegeben wird, einen Fehler zurück und beendet die Transaktion automatisch.

5. Wichtige Hinweise und Best Practices für MySQL-Sperren

Ein ordnungsgemäßes Sperrmanagement hilft, das Risiko von Deadlocks und Leistungsabfall zu reduzieren. Nachfolgend sind Best Practices aufgeführt, um Sperren zu minimieren und sie effizient zu verwalten.

5.1 So verhindern Sie Deadlocks

Um Deadlocks zu verhindern, beachten Sie die folgenden Punkte:

1. Standardisieren Sie die Reihenfolge der Transaktionsausführung

  • Zum Beispiel, beim Aktualisieren mehrerer Tabellen, aktualisieren Sie sie immer in der gleichen Reihenfolge .
  • Beispiel:
    -- OK: Always update in the order orders → customers
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

× NG: Verschiedene Ausführungsreihenfolgen können Deadlocks verursachen

-- Session 1
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
COMMIT;

-- Sitzung 2 (Deadlock kann auftreten, wenn in umgekehrter Reihenfolge ausgeführt)
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

2. Keep Transactions Short

  • Commit or roll back as quickly as possible
  • Avoid long-running transactions, as they can negatively impact other processes.

3. Set Appropriate Indexes

  • Creating proper indexes helps avoid unnecessary locks .
  • Example: Adding an index on customer_id in the orders table ensures that only specific rows are locked .
    CREATE INDEX idx_customer_id ON orders (customer_id);

6. Summary

  • MySQL locks include row locks, table locks, and intention locks . Improper management can lead to deadlocks and performance issues.
  • The method for checking lock status varies depending on the MySQL version , so choose the appropriate approach for your environment.
  • Be cautious when releasing locks!
  • Try ROLLBACK before using the KILL command.
  • Use SET innodb_lock_wait_timeout to automatically handle lock timeouts.
  • To prevent deadlocks, standardize transaction execution order and keep transactions short .

7. FAQ (Frequently Asked Questions)

Q1. What is the easiest command to check MySQL lock status?

  • A1. In MySQL 8.0 and later, use SELECT * FROM performance_schema.data_locks; to easily check lock status.

Q2. What should I do if a deadlock occurs?

  • A2. First, run SHOW ENGINE INNODB STATUS\G; to identify the cause of the deadlock. Then review and standardize the transaction execution order to prevent recurrence.

Q3. Can using the KILL command corrupt data?

  • A3. When forcibly terminating a session, unfinished transactions are rolled back, which may affect data consistency. Use it with caution.

Q4. How can I prevent deadlocks?

  • A4. The following methods are effective:
  • Standardize transaction execution order
  • Keep transactions short
  • Set appropriate indexes

Q5. How can I reduce locks and improve MySQL performance?

  • A5.
  • Design proper indexes to reduce unnecessary locks
  • Keep transactions short to minimize lock duration
  • Avoid full table locks (LOCK TABLES)
  • Use read replicas to distribute read workloads