Detaillierte Erklärung der Bedeutung und des Unterschieds zwischen MySQL-Zeilensperren und Tabellensperren

1. Einleitung

Der Unterschied zwischen Zeilensperren und Tabellensperren sollte in Interviews häufig vorkommen. Wir sollten ein systematisches Verständnis von Sperren in MySQL haben. Weitere Einzelheiten müssen Sie selbst nachschlagen. Dieser Artikel ist eine allgemeine Zusammenfassung der Antwort.

Zu den gängigen MySQL-Engines gehören MyISAM und InnoDB, wobei InnoDB die Standard-Engine von MySQL ist. MyISAM unterstützt keine Zeilensperren, während InnoDB Zeilensperren und Tabellensperren unterstützt.

Im Vergleich zu anderen Datenbanken ist der Sperrmechanismus von MySQL relativ einfach. Sein bemerkenswertestes Merkmal ist, dass verschiedene Speicher-Engines unterschiedliche Sperrmechanismen unterstützen.

MySQL kann grob in die folgenden drei Sperrtypen zusammengefasst werden:

• Sperre auf Tabellenebene: geringer Overhead, schnelle Sperrung, kein Deadlock, hohe Sperrgranularität, höchste Wahrscheinlichkeit eines Sperrkonflikts und geringste Parallelität.
• Sperre auf Zeilenebene: Hoher Overhead, langsame Sperrung; es kann zu Deadlocks kommen; die Sperrgranularität ist am geringsten, die Wahrscheinlichkeit eines Sperrkonflikts am geringsten und die Parallelität am höchsten.
• Seitensperre: Der Overhead und die Sperrzeit liegen zwischen Tabellensperre und Zeilensperre. Es kann zu Deadlocks kommen. Die Sperrgranularität liegt zwischen Tabellensperre und Zeilensperre und die Parallelität ist durchschnittlich.

Wie sperren?

Vor der Ausführung einer Abfrageanweisung (SELECT) fügt MyISAM automatisch eine Lesesperre für alle beteiligten Tabellen hinzu. Vor der Ausführung eines Aktualisierungsvorgangs (UPDATE, DELETE, INSERT usw.) fügt es automatisch eine Schreibsperre für die beteiligten Tabellen hinzu. Dieser Vorgang erfordert kein Eingreifen des Benutzers, sodass Benutzer die MyISAM-Tabelle im Allgemeinen nicht explizit direkt mit dem Befehl LOCK TABLE sperren müssen.

Explizite Sperrung:

So schreiben Sie eine gemeinsam genutzte Sperre (Lesesperre): Sperren Sie im gemeinsam genutzten Modus, zum Beispiel:

Wählen Sie Mathematik aus zje, wo Mathematik > 60 ist, im Freigabemodus sperren;

Die exklusive Sperre (Schreibsperre) wird beispielsweise wie beim Update geschrieben:

Wählen Sie Mathematik aus zje, wo Mathematik >60 für Aktualisierung ist;

2. Tischsperre

Es kommt zu keinem Deadlock, die Wahrscheinlichkeit eines Sperrkonflikts ist hoch und die Parallelität gering.

MyISAM-Engine

Vor der Ausführung einer Abfrageanweisung (Auswählen) fügt MyISAM automatisch eine Lesesperre für alle beteiligten Tabellen hinzu und vor der Durchführung von Hinzufügungs-, Lösch- und Änderungsvorgängen fügt es automatisch eine Schreibsperre für die beteiligten Tabellen hinzu.

MySQL-Sperren auf Tabellenebene haben zwei Modi:

• Gemeinsame Lesesperre für Tabellen
• Exklusive Schreibsperre für Tabelle

Eine Lesesperre blockiert Schreibvorgänge, und eine Schreibsperre blockiert sowohl Lese- als auch Schreibvorgänge.

• Lesevorgänge auf MyISAM-Tabellen blockieren nicht die Leseanforderungen anderer Prozesse für die gleiche Tabelle, wohl aber die Schreibanforderungen für die gleiche Tabelle. Erst wenn die Lesesperre aufgehoben wird, können Schreibvorgänge von anderen Prozessen aus durchgeführt werden.
• Schreibvorgänge auf der MyISAM-Tabelle blockieren die Lese- und Schreibvorgänge anderer Prozesse auf derselben Tabelle. Erst wenn die Schreibsperre aufgehoben wird, können die Lese- und Schreibvorgänge anderer Prozesse ausgeführt werden.

MyISAM eignet sich nicht als Schreib-Primärtabellen-Engine, da andere Threads nach der Schreibsperre keine Vorgänge mehr ausführen können und eine große Anzahl von Aktualisierungen es Abfragen erschwert, Sperren zu erhalten, was zu einer dauerhaften Blockierung führt.

3. Zeilensperre

Es kommt zu einem Deadlock, die Wahrscheinlichkeit eines Sperrkonflikts ist gering und die Parallelität hoch.

Die InnoDB-Engine von MySQL unterstützt Zeilensperren. Im Gegensatz zu Oracle werden die Zeilensperren von MySQL über Indizes geladen, d. h. Zeilensperren werden den Zeilen hinzugefügt, die dem Index entsprechen. Wenn die entsprechende SQL-Anweisung den Index nicht verwendet, wird die gesamte Tabelle gescannt und Zeilensperren können nicht erreicht werden. Stattdessen werden Tabellensperren verwendet. Zu diesem Zeitpunkt können andere Transaktionen keine Aktualisierungs- oder Einfügevorgänge für die aktuelle Tabelle durchführen.

CREATE TABLE `Benutzer` (
`name` VARCHAR(32) DEFAULT NULL,
`Anzahl` INT(11) DEFAULT NULL,
`id` INT(11) NICHT NULL AUTO_INCREMENT,
PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=INNODB AUTO_INCREMENT=7 STANDARD-CHARSET=utf8

-- Hier erstellen wir eine Benutzertabelle mit dem Primärschlüssel als ID

 

-- A führt einen Einfügevorgang mit dem Primärschlüssel aus, aber die Transaktion wird nicht festgeschrieben. Update User Set Count=10, wobei ID=1 ist.
- B führt zu diesem Zeitpunkt auch den Aktualisierungsvorgang aus: update user set count=10 where id=2;
-- Da es durch den Primärschlüssel ausgewählt wird, handelt es sich um eine Sperre auf Zeilenebene. A und B arbeiten nicht mit derselben Zeile, daher ist die von B ausgeführte Operation ausführbar. -- A führt eine Einfügeoperation mit dem Namen aus, aber die Transaktion wird nicht festgeschrieben. Update User Set Count=10, wobei Name='xxx';
- B führt zu diesem Zeitpunkt auch den Aktualisierungsvorgang aus: update user set count=10 where id=2;
– Da die Auswahl nicht über einen Primärschlüssel oder Index erfolgt, wird es auf eine Sperre auf Tabellenebene aktualisiert.
-- B kann die Tabelle nicht aktualisieren oder einfügen. Erst wenn A die Transaktion festschreibt, kann B sie erfolgreich ausführen.

für Update

Wenn nach einer Select-Anweisung ein for update-Befehl hinzugefügt wird, wird den abgefragten Daten eine exklusive Sperre zugewiesen. Andere Transaktionen können die Daten lesen, aber keine Update- oder Einfügevorgänge ausführen.

- Benutzer A sperrt den Datensatz mit der ID=1. select * from user where id=1 for update;

- Benutzer B kann an diesem Datensatz keine Vorgänge ausführen. Benutzersatzanzahl=10 aktualisieren, wobei ID=1 ist.

-- Nachdem Benutzer A den Datensatz bestätigt hat, kann Benutzer B ihn bearbeiten.

Die Implementierung von Zeilensperren erfordert Aufmerksamkeit:

• Zeilensperren können nur mit Indizes implementiert werden, andernfalls wird die gesamte Tabelle automatisch gesperrt und es handelt sich nicht um eine Zeilensperre.
• Zwei Transaktionen können nicht denselben Index sperren.
• Einfügen, Löschen und Aktualisieren fügen Transaktionen standardmäßig automatisch exklusive Sperren hinzu.

Zeilensperrenszenario:

Wenn Benutzer A einen Einkauf tätigt, fragt die Serviceschicht zunächst den Kontostand des Benutzers ab. Wenn der Kontostand ausreichend ist, führt sie anschließend Abzüge durch. In diesem Fall sollte der Datensatz während der Abfrage gesperrt sein.

Andernfalls überweist Benutzer B das Geld vom Konto von Benutzer A, bevor Benutzer A nach der Kontoabfrage einen Kauf tätigt, und Benutzer A hat bereits festgestellt, ob das Guthaben des Benutzers ausreicht. Dann reicht das Guthaben möglicherweise nicht aus, die Abbuchung ist jedoch erfolgreich.

Um diese Situation zu vermeiden, müssen Sie eine Aktualisierungssperre ausführen, wenn Benutzer A den Datensatz bearbeitet.

Erweiterung: Gap Lock

Wenn wir zum Abrufen von Daten Bereichsbedingungen anstelle von Gleichheitsbedingungen verwenden und gemeinsame oder exklusive Sperren anfordern, sperrt InnoDB die Indexelemente vorhandener Datensätze, die die Bedingungen erfüllen. Bei Datensätzen, deren Schlüsselwerte nicht im Bedingungsbereich vorhanden sind, wird dies als Lücke bezeichnet.

InnoDB wird auch diese "Lücke" sperren, dieser Sperrmechanismus wird als Lückensperre bezeichnet

-- Benutzer A
Benutzersatz aktualisieren count=8 wobei id>2 und id<6

-- Benutzer B
Benutzersatz aktualisieren: Anzahl=10, wobei ID=5;

Wenn Benutzer A die Transaktion nach der Durchführung der obigen Vorgänge nicht bestätigt hat, kann B die Datensätze zwischen 2 und 6 nicht aktualisieren oder einfügen und wird blockiert. Wenn A die Transaktion bestätigt, wird der Aktualisierungsvorgang von B ausgeführt.

Anregung:

• Versuchen Sie, alle Datenabrufe über Indizes durchzuführen, um zu vermeiden, dass Zeilensperren, die nicht auf Indizes verweisen, zu Tabellensperren eskalieren.
• Gestalten Sie Indizes sinnvoll, um den Umfang von Sperren zu minimieren
• Minimieren Sie Indexbedingungen und vermeiden Sie Lückensperren
• Versuchen Sie, die Transaktionsgröße zu kontrollieren und die Menge der gesperrten Ressourcen und die Dauer zu reduzieren

Damit ist dieser Artikel über die Bedeutung und Unterschiede zwischen MySQL-Zeilensperren und Tabellensperren abgeschlossen. Weitere Informationen zu MySQL-Zeilensperren und Tabellensperren finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!