Die Welt von MySQL und MariaDB

Verstehen und Verwalten von MySQL-Locks: Typen, Erkennung und Vermeidung von Deadlocks

Serverkonfiguration und Verwaltung
目次

1. Einführung

MySQL wird häufig als Datenbankverwaltungssystem verwendet, aber wenn mehrere Abfragen auf dieselben Daten zugreifen, wird ein Sperrmechanismus ausgelöst. Sperren sind entscheidend, um Datenkonsistenz sicherzustellen, aber falsches Management kann zu Deadlocks und Leistungsverschlechterungen führen.

Dieser Artikel erklärt die grundlegenden Konzepte von Sperren in MySQL und beschreibt wie man den Sperrstatus prüft, Sperren freigibt und Deadlocks verhindert.

Was Sie lernen werden

  • Arten von MySQL‑Sperren und ihre Auswirkungen
  • Versionsspezifische Methoden zur Überprüfung von Sperren
  • Sichere Verfahren zum Freigeben von Sperren
  • Praktische Strategien zur Vermeidung von Deadlocks

Lassen Sie uns mit dem grundlegenden Konzept von MySQL‑Sperren beginnen.

2. Grundlegende Konzepte von MySQL‑Sperren

In Datenbanken ist eine „Sperre“ ein Mechanismus, der den Zugriff einschränkt, um die Datenintegrität zu gewährleisten, wenn mehrere Transaktionen gleichzeitig versuchen, Daten zu ändern. Unpassendes Sperrmanagement kann Leistungsprobleme oder Deadlocks verursachen.

2.1 Haupttypen von Sperren

MySQL bietet verschiedene Sperrtypen, je nach benötigtem Schutzniveau.

Zeilensperre

  • Sperrt nur bestimmte Zeilen, minimiert die Auswirkungen auf andere Transaktionen.
  • Unterstützt nur die InnoDB‑Engine.
  • Tritt auf, wenn SELECT ... FOR UPDATE oder SELECT ... LOCK IN SHARE MODE verwendet wird.

Tabellensperre

  • Sperrt die gesamte Tabelle, verhindert gleichzeitige Ausführung mehrerer Abfragen.
  • Häufig von der MyISAM‑Engine verwendet.
  • Ausgelöst durch die Anweisung LOCK TABLES.

Intention‑Lock

  • Koordiniert Zeilen‑ und Tabellen‑Sperren, um Konflikte zu vermeiden.
  • Wird ausschließlich in InnoDB verwendet und automatisch verwaltet.

Deadlock

  • Tritt auf, wenn mehrere Transaktionen unendlich lange auf die Sperren anderer warten.
  • Ungeeignetes Transaktionsdesign kann den Prozess zum Stillstand bringen.

2.2 Beispiele für Sperren

Die folgenden Beispiele zeigen, wie Sperren in tatsächlichen SQL‑Abfragen auftreten.

Beispiel Zeilensperre

Das folgende SQL sperrt bestimmte Zeilen.

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

Wenn eine andere Sitzung versucht, dieselbe Zeile zu aktualisieren, tritt sie in einen Wartestatus (Sperrkonflikt) ein.

Beispiel Tabellensperre

Um eine ganze Tabelle zu sperren, verwenden Sie folgenden Befehl:

LOCK TABLES products WRITE;
-- Prevents other sessions from modifying the products table until the lock is released

Bis diese Sperre freigegeben wird, kann kein anderer Benutzer Daten in der Tabelle products ändern.

Beispiel Deadlock

Ein typisches Deadlock‑Szenario sieht so aus:

-- 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 next)
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- Deadlock occurs here

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

3. Prüfen des MySQL‑Sperrstatus (nach Version)

Um festzustellen, ob Sperren aktiv sind, verwenden Sie Befehle, die für Ihre MySQL‑Version geeignet sind.

3.1 MySQL 5.6 und früher

In MySQL 5.6 und früher verwenden Sie SHOW ENGINE INNODB STATUSG;, um Sperrdetails zu prüfen.

SHOW ENGINE INNODB STATUSG;

Dieser Befehl zeigt detaillierte Informationen über aktuelle Sperren.

3.2 MySQL 5.7

Ab MySQL 5.7 ist es einfacher, die Tabelle sys.innodb_lock_waits zu nutzen.

SELECT * FROM sys.innodb_lock_waits;

Diese Abfrage zeigt, welche Transaktionen derzeit auf Sperren warten.

3.3 MySQL 8.0 und neuer

In MySQL 8.0 und späteren Versionen können Sie detailliertere Informationen mit performance_schema.data_locks erhalten.

SELECT * FROM performance_schema.data_locks;

Um festzustellen, welche Sitzung die Sperre hält:

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

Dies hilft, den Prozess zu identifizieren, der die Sperre hält.

4. Wie man Sperren in MySQL freigibt (Risiken erklärt)

Wenn in MySQL eine Sperre auftritt und nicht ordnungsgemäß freigegeben wird, kann sie Prozesse verlangsamen und die Datenbankleistung verringern.
Dieser Abschnitt erklärt, wie man Sperren sicher freigibt und welche Risiken dabei entstehen können.

4.1 Sitzungen identifizieren, die Sperren halten

Bevor Sie eine Sperre freigeben, identifizieren Sie, welche Sitzung sie hält. Verwenden Sie das folgende SQL, um Sitzungen zu prüfen, die auf Sperren warten:

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

Diese Abfrage listet Sitzungen auf, die derzeit auf Tabellen‑Metadaten‑Sperren warten.

In MySQL 8.0 und später können Sie detaillierte Sperrdaten mit folgendem Befehl abrufen:

SELECT * FROM performance_schema.data_locks;

4.2 Sperren mit dem Befehl KILL freigeben

Sobald Sie die Sitzung identifiziert haben, die die Sperre hält, können Sie den Prozess beenden, um sie freizugeben.

1. Prozesse prüfen, die Sperren halten

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

2. Sitzung mit dem KILL‑Befehl beenden

KILL <process_id>;

Beispiel: Um den Prozess ID=12345 zu beenden, führen Sie aus:

KILL 12345;

⚠️ Risiken bei Verwendung von KILL

  • Terminierte Transaktionen werden automatisch zurückgesetzt (ROLLBACK)
  • Beispiel: Unvollendete UPDATE‑Operationen können ausstehende Änderungen verwerfen.
  • Kann zu Anwendungsfehlern führen
  • Wenn häufig KILL‑Operationen erforderlich sind, überprüfen Sie das Transaktionsdesign Ihrer Anwendung.

4.3 Sperren sicher mit ROLLBACK freigeben

Bevor Sie KILL verwenden, versuchen Sie, die Transaktion manuell zu beenden, wenn möglich.

1. Aktuelle Sitzungen prüfen

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. Die problematische Transaktion identifizieren und ROLLBACK ausführen

ROLLBACK;

Diese Methode gibt Sperren sicher frei und erhält die Datenkonsistenz.

4.4 Automatisierte Sperrfreigabe mit SET innodb_lock_wait_timeout

Anstelle der manuellen Freigabe können Sie einen Timeout konfigurieren, sodass Sperren nach einer bestimmten Zeit automatisch verfallen.

SET innodb_lock_wait_timeout = 10;

Diese Einstellung führt dazu, dass eine Transaktion automatisch beendet wird, wenn eine Sperre innerhalb von 10 Sekunden nicht freigegeben wird, wodurch lange Stillstände verhindert werden.

5. Wichtige Punkte und bewährte Verfahren für MySQL‑Sperren

Eine ordnungsgemäße Sperrverwaltung reduziert Deadlocks und Leistungsverschlechterung. Nachfolgend finden Sie bewährte Verfahren für eine effiziente Sperrverwaltung.

5.1 Vermeidung von Deadlocks

Um Deadlocks zu vermeiden, beachten Sie folgende Prinzipien:

1. Halten Sie eine konsistente Transaktionsreihenfolge

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

× Falsch: Inkonsistente Reihenfolge verursacht Deadlocks

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

-- Session 2 (executed in reverse order → possible deadlock)
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

2. Halten Sie Transaktionen kurz

  • Commiten oder Rollbacken Sie immer sofort
  • Länger andauernde Transaktionen blockieren andere und erhöhen das Risiko von Deadlocks.

3. Verwenden Sie passende Indizierung

  • Indizes reduzieren den Umfang gesperrter Zeilen und minimieren unnötige Sperren.
  • Beispiel: Durch das Hinzufügen eines Indexes auf customer_id in der Tabelle orders wird sichergestellt, dass nur relevante Zeilen gesperrt werden.
CREATE INDEX idx_customer_id ON orders (customer_id);

6. Zusammenfassung

  • MySQL‑Sperren umfassen Zeilen-, Tabellen- und Intentionssperren. Missmanagement kann zu Deadlocks und schlechter Leistung führen.
  • Die Methoden zur Sperrprüfung unterscheiden sich je nach MySQL‑Versionen.
  • Achtung beim Freigeben von Sperren!
  • Versuche ROLLBACK, bevor du KILL benutzt.
  • Verwende SET innodb_lock_wait_timeout, um die Sperrfreigabe zu automatisieren.
  • Vermeide Deadlocks, indem du die konsistente Transaktionsreihenfolge beibehältst und kurze Transaktionszeiten einhältst.

7. FAQ

Q1. Was ist der einfachste Weg, den MySQL‑Sperrstatus zu prüfen?

  • A1. In MySQL 8.0+ benutzt du SELECT * FROM performance_schema.data_locks;, um den Sperrstatus leicht anzuzeigen.

Q2. Wie sollte ich Deadlocks behandeln?

  • A2. Führe SHOW ENGINE INNODB STATUS; aus, um die Ursache zu ermitteln, und passe dann die Transaktionsreihenfolge an, um Wiederholungen zu verhindern.

Q3. Kann der Befehl KILL Daten beschädigen?

  • A3. Eine erzwungene Beendigung löst ROLLBACK für nicht abgeschlossene Transaktionen aus, was die Konsistenz beeinträchtigen kann. Nutze es mit Vorsicht.

Q4. Wie kann ich Deadlocks verhindern?

  • A4. Wende diese Regeln an:
  • konsistente Transaktionsreihenfolge beibehalten
  • kurze Transaktionen verwenden
  • passende Indizes setzen

Q5. Wie kann ich die MySQL‑Leistung verbessern, indem ich Sperren reduziere?

  • A5.
  • Effiziente Indizes entwerfen, um Sperren zu minimieren
  • Kurze Transaktionen verwenden, um die Sperrzeit zu reduzieren
  • Volltabellensperren (LOCK TABLES) vermeiden
  • Lese‑Repliken nutzen, um Leseaufkommen zu verteilen