Detaillierte Erklärung der Sperrstruktur in MySQL

Mysql unterstützt 3 Arten von Sperrstrukturen

• Sperre auf Tabellenebene, geringer Overhead, schnelle Sperrung, keine Deadlocks, große Sperrgranularität, hohe Konfliktwahrscheinlichkeit und geringste Parallelität
• Sperren auf Zeilenebene, geringer Overhead, langsames Sperren, Deadlock, kleine Sperrgranularität, niedrigste Konfliktwahrscheinlichkeit, höchste Parallelität
• Seitensperren, Overhead und Sperren liegen zwischen Tabellensperren und Zeilensperren, es können Deadlocks auftreten, die Sperrgranularität basiert auf Tabellen und Zeilen, und die Parallelität ist im Allgemeinen
  

InnoDB-Sperrproblem

Es gibt zwei große Unterschiede zwischen InnoDB und MyISAM: Einer besteht darin, dass Transaktionen unterstützt werden (TRANSACTION); der andere ist die Verwendung von Sperren auf Zeilenebene.
Es gibt viele Unterschiede zwischen Sperren auf Zeilenebene und Sperren auf Tabellenebene. Darüber hinaus bringt die Einführung von Transaktionen auch einige neue Probleme mit sich.

InnoDB-Zeilensperrmodus und Sperrmethode

InnoDB implementiert die folgenden zwei Arten von Zeilensperren.

• Gemeinsam genutzte Sperren: Ermöglicht einer Transaktion das Lesen einer Zeile und verhindert, dass andere Transaktionen exklusive Sperren für denselben Datensatz erhalten.
• Exklusive Sperre (X): Ermöglicht Transaktionen, die exklusive Sperren erhalten, das Aktualisieren von Daten und verhindert, dass andere Transaktionen gemeinsame Lesesperren und exklusive Schreibsperren für denselben Datensatz erhalten.

Um außerdem die Koexistenz von Zeilensperren und Tabellensperren zu ermöglichen und einen Sperrmechanismus mit mehreren Granularitäten zu implementieren, verfügt InnoDB auch über zwei intern verwendete Absichtssperren (Intention Locks), bei denen es sich beide um Tabellensperren handelt.

• Absichtliche gemeinsame Sperre (IS): Die Transaktion beabsichtigt, einer Datenzeile eine gemeinsame Sperre zuzuweisen. Die Transaktion muss zuerst die IS-Sperre der Tabelle erhalten, bevor sie einer Datenzeile eine gemeinsame Sperre hinzufügt.
• Absichtliche exklusive Sperre (IX): Die Transaktion beabsichtigt, einer Datenzeile eine exklusive Sperre hinzuzufügen. Die Transaktion muss zuerst die IX-Sperre der Tabelle erhalten, bevor sie einer Datenzeile eine exklusive Sperre hinzufügt.

InnoDB-Zeilensperren werden über Indexelemente im Index implementiert. Dies unterscheidet sich von MySQL und Oracle, wo dies durch Sperren der entsprechenden Datenzeilen in den Daten implementiert wird. Diese Zeilensperrenimplementierungsfunktion von InnoDB bedeutet, dass InnoDB Zeilensperren nur beim Abrufen von Daten über Indexbedingungen verwendet, andernfalls verwendet InnoDB Tabellensperren!

Next-Key-Schlösser

Wenn wir Daten unter Verwendung von Bereichsbedingungen statt Gleichheitsbedingungen abrufen und gemeinsame oder exklusive Sperren anfordern, sperrt InnoDB die Indexelemente vorhandener Daten, die die Bedingungen erfüllen. Bei Datensätzen, deren Schlüsselwerte innerhalb des Bedingungsbereichs liegen, aber nicht vorhanden sind, wird dies als „Lücke“ bezeichnet, und InnoDB sperrt auch diese „Lücke“. Dieser Sperrmechanismus ist keine sogenannte Lückensperre (Next-Key-Sperre).
Wenn beispielsweise die EMP-Tabelle nur 101 Datensätze enthält und die EMPID-Werte jeweils 1, 2, ..., 100 und 101 sind, lautet das folgende SQL:

Wählen Sie * aus emp, wo empid > 100 für Update

Dies ist eine Bereichsbedingungssuche. InnoDB sperrt nicht nur die Datensätze mit einem Empid-Wert von 101, die die Bedingungen erfüllen, sondern sperrt auch die „Lücken“, bei denen der Empid-Wert größer als 101 ist (diese Datensätze existieren nicht).
Der Zweck der Verwendung von Lückensperren durch InnoDB besteht einerseits darin, Phantomlesevorgänge zu verhindern, um die Anforderungen der entsprechenden Isolationsebenen zu erfüllen. Wenn im obigen Beispiel keine Lückensperren verwendet werden und andere Transaktionen Datensätze mit einer Empid größer als 100 einfügen, treten Phantomlesevorgänge auf, wenn diese Transaktion die obige Anweisung erneut ausführt. Andererseits besteht dies darin, die Anforderungen für die Wiederherstellung und Replikation zu erfüllen. Über die Auswirkungen seiner Wiederherstellungs- und Replikationsmechanismen und die Verwendung von Gap Locks durch InnoDB auf verschiedenen Isolationsebenen.
Wenn zum Abrufen und Sperren von Datensätzen Bereichsbedingungen verwendet werden, blockiert der Sperrmechanismus von InnoDB offensichtlich das gleichzeitige Einfügen von Schlüsselwerten, die die Bedingungen erfüllen, was häufig zu erheblichen Wartezeiten bei der Sperre führt. Daher sollten wir bei der tatsächlichen Entwicklung, insbesondere bei Anwendungen mit vielen gleichzeitigen Einfügungen, unser Bestes geben, um die Geschäftslogik zu optimieren, Gleichheitsbedingungen zum Zugreifen auf und Aktualisieren von Daten zu verwenden und die Verwendung von Bereichsbedingungen zu vermeiden.

Wann sollten Tabellensperren verwendet werden?

Für InnoDB-Tabellen sollten in den meisten Fällen Zeilensperren verwendet werden, da Transaktionen und Zeilensperren häufig die Gründe sind, warum wir uns für InnoDB-Tabellen entscheiden. Bei einigen speziellen Transaktionen können Sie jedoch auch die Verwendung von Sperren auf Tabellenebene in Betracht ziehen.

Die erste Situation ist: Die Transaktion muss die meisten oder alle Daten aktualisieren und die Tabelle ist relativ groß. Wenn die standardmäßige Zeilensperre verwendet wird, ist nicht nur die Effizienz der Transaktionsausführung gering, sondern es kann auch zu langen Sperrwartezeiten und Sperrkonflikten für andere Transaktionen kommen. In diesem Fall können Sie die Verwendung von Tabellensperren in Betracht ziehen, um die Ausführung der Transaktion zu beschleunigen.

Die zweite Situation besteht darin, dass die Transaktion mehrere Tabellen umfasst, was relativ komplex ist und zu Deadlocks und Rollbacks einer großen Anzahl von Transaktionen führen kann. In diesem Fall können Sie auch in Erwägung ziehen, alle an der Transaktion beteiligten Tabellen gleichzeitig zu sperren, um Deadlocks zu vermeiden und den durch das Zurücksetzen der Transaktion verursachten Datenbank-Overhead zu verringern.

Natürlich sollten von diesen beiden Transaktionstypen nicht zu viele in der Anwendung vorkommen, andernfalls sollten Sie die Verwendung der MyISAM-Tabelle in Betracht ziehen.
Beachten Sie in InnoDB bei der Verwendung von Tabellensperren die folgenden zwei Punkte.

(1) Obwohl LOCK TALBES verwendet werden kann, um Tabellensperren zu InnoDB hinzuzufügen, muss beachtet werden, dass Tabellensperren nicht von der InnoDB-Speicher-Engine-Schicht, sondern vom MySQL-Server der oberen Schicht verwaltet werden. Nur wenn autocommit=0 und innodb_table_lock=1 (Standardeinstellungen) kann die InnoDB-Schicht die von MySQL hinzugefügten Tabellensperren erkennen und MySQL Server kann die von InnoDB hinzugefügten Zeilensperren erkennen. In diesem Fall kann InnoDB Deadlocks, die Tabellensperren betreffen, automatisch identifizieren; andernfalls kann InnoDB solche Deadlocks nicht automatisch erkennen und behandeln.

(2) Wenn Sie LOCAK TABLES verwenden, um InnoDB zu sperren, achten Sie darauf, AUTOCOMMIT auf 0 zu setzen, da MySQL sonst die Tabelle nicht sperrt. Verwenden Sie UNLOCAK TABLES nicht, um die Tabellensperre vor dem Ende der Transaktion freizugeben, da UNLOCK TABLES die Transaktion implizit festschreibt. COMMIT oder ROLLBACK können die von LOCAK TABLES hinzugefügte Sperre auf Tabellenebene nicht freigeben. Sie müssen UNLOCK TABLES verwenden, um die Tabellensperre freizugeben. Die richtige Methode wird in der folgenden Anweisung gezeigt.
Wenn Sie beispielsweise in Tabelle t1 schreiben und aus Tabelle t lesen müssen, können Sie dies wie folgt tun:

AUTOCOMMIT SETZEN=0;
LOCAK-TABELLEN t1 SCHREIBEN, t2 LESEN, ...;
[machen Sie etwas mit den Tabellen t1 und hier];
BEGEHEN;
TABELLEN ENTSPERREN;

Deadlock

Mit Ausnahme von Transaktionen, die aus einer einzelnen SQL-Anweisung bestehen, werden Sperren in InnoDB schrittweise erworben, wodurch es bei InnoDB zu Deadlocks kommen kann.
Wenn ein Deadlock auftritt, kann InnoDB ihn im Allgemeinen automatisch erkennen und eine Transaktion dazu veranlassen, die Sperre freizugeben und ein Rollback durchzuführen, während die andere Transaktion die Sperre erwirbt und mit der Ausführung der Transaktion fortfährt. Wenn jedoch externe Sperren oder Sperren beteiligt sind, kann InnoDB Deadlocks nicht vollständig automatisch erkennen. Dies muss durch Festlegen des Parameters für das Wartezeittimeout für Sperren innodb_lock_wait_timeout gelöst werden. Es ist zu beachten, dass dieser Parameter nicht nur zur Lösung des Deadlock-Problems verwendet wird. Wenn bei hohem gleichzeitigem Zugriff eine große Anzahl von Transaktionen angehalten wird, weil sie die erforderlichen Sperren nicht sofort erhalten können, belegt dies eine große Menge an Computerressourcen, was zu schwerwiegenden Leistungsproblemen führt und sogar die Datenbank herunterzieht. Wir können diese Situation vermeiden, indem wir einen entsprechenden Schwellenwert für das Timeout der Sperre festlegen.

Im Allgemeinen sind Deadlocks Probleme des Anwendungsdesigns und können größtenteils durch die Anpassung der Geschäftsprozesse, des Datenbankobjektdesigns, der Transaktionsgröße und der für den Datenbankzugriff verwendeten SQL-Anweisungen vermieden werden. Die folgenden Beispiele stellen mehrere gängige Deadlock-Methoden vor.

(1) Wenn in einer Anwendung verschiedene Programme gleichzeitig auf mehrere Tabellen zugreifen, sollten Sie versuchen, den Zugriff auf die Tabellen in derselben Reihenfolge zu vereinbaren. Dadurch kann die Wahrscheinlichkeit eines Deadlocks erheblich verringert werden. Wenn zwei Sitzungen in unterschiedlicher Reihenfolge auf die beiden Tabellen zugreifen, ist die Wahrscheinlichkeit eines Deadlocks sehr hoch! Wenn die Zugriffe jedoch in der gleichen Reihenfolge erfolgen, können Deadlocks vermieden werden.

(2) Wenn das Programm Daten stapelweise verarbeitet und die Daten im Voraus sortiert werden, um sicherzustellen, dass jeder Thread die Datensätze in einer festen Reihenfolge verarbeitet, kann die Möglichkeit eines Deadlocks erheblich verringert werden.

(3) Wenn Sie während einer Transaktion einen Datensatz aktualisieren möchten, sollten Sie direkt eine Sperre mit ausreichender Stufe beantragen, d. h. eine exklusive Sperre, anstatt zuerst eine gemeinsame Sperre und dann beim Aktualisieren eine exklusive Sperre zu beantragen, was sogar zu einem Deadlock führen kann.

(4) Wenn auf der Isolationsebene REPEATEABLE-READ zwei Threads gleichzeitig mit SELECT ... ROR UPDATE eine exklusive Sperre für denselben Bedingungsdatensatz hinzufügen, ist die Sperrung für beide Threads erfolgreich, wenn kein Datensatz die Bedingung erfüllt. Das Programm stellt fest, dass der Datensatz noch nicht vorhanden ist, und versucht daher, einen neuen Datensatz einzufügen. Wenn zwei Threads dies tun, kommt es zu einem Deadlock. In diesem Fall kann das Problem durch Ändern der Isolationsebene auf READ COMMITTED vermieden werden.

(5) Wenn die Isolationsstufe READ COMMITED ist und beide Threads zuerst SELECT...FOR UPDATE ausführen, ermitteln sie, ob Datensätze vorhanden sind, die die Bedingungen erfüllen. Wenn nicht, fügen sie die Datensätze ein. Zu diesem Zeitpunkt kann nur ein Thread erfolgreich einfügen, und der andere Thread wartet auf die Sperre. Wenn der erste Thread übermittelt, macht der zweite Thread aufgrund des Primärschlüssels einen Fehler, aber obwohl dieser Thread einen Fehler aufweist, erhält er eine exklusive Sperre! Wenn zu diesem Zeitpunkt ein dritter Thread erneut eine exklusive Sperre beantragt, tritt ein Deadlock auf. In diesem Fall können Sie den Einfügevorgang direkt ausführen und dann die Ausnahme bezüglich der Duplizierung des Primärschlüssels abfangen. Wenn ein Fehler bezüglich der Duplizierung des Primärschlüssels auftritt, führen Sie immer ROLLBACK aus, um die erworbene exklusive Sperre freizugeben.

Unterschiede zwischen MyISAM und InnoDB

Für MyISAM-Tabellensperren gibt es hauptsächlich die folgenden Punkte

(1) Gemeinsam genutzte Lesesperren (S) sind untereinander kompatibel, jedoch schließen sich gemeinsam genutzte Lesesperren (S) und exklusive Schreibsperren (X) sowie exklusive Schreibsperren (X) gegenseitig aus, so dass Lesen und Schreiben seriell erfolgen.
(2) Unter bestimmten Bedingungen erlaubt MyISAM die gleichzeitige Ausführung von Abfragen und Einfügungen. Dies können wir nutzen, um das Sperrkonfliktproblem für dieselbe Tabelle und dieselbe Einfügung in der Anwendung zu lösen.
(3) Der Standardmechanismus zur Sperrplanung von MyISAM ist die Schreibpriorität, die nicht unbedingt für alle Anwendungen geeignet ist. Benutzer können die Konkurrenz von Lese-/Schreibsperren anpassen, indem sie den Parameter LOW_PRIPORITY_UPDATES festlegen oder die Option LOW_PRIORITY in INSERT-, UPDATE- und DELETE-Anweisungen angeben.
(4) Da die Tabellensperre eine große Sperrgranularität aufweist und Lesen und Schreiben seriell erfolgen, kann es bei vielen Aktualisierungsvorgängen bei MyISAM-Tabellen zu erheblichen Wartezeiten bei der Sperre kommen. Sie können die Verwendung von InnoDB-Tabellen in Betracht ziehen, um Sperrkonflikte zu reduzieren.

Für InnoDB-Tabellen gibt es folgende Hauptpunkte

(1) Die Vermarktung von InnoDB basiert auf Indizes. Wenn auf Daten nicht über einen Index zugegriffen wird, verwendet InnoDB eine Tabellensperre.
(2) InnoDB-Gap-Lock-Mechanismus und der Grund, warum InnoDB Gap Lock verwendet.
(3) Der Sperrmechanismus und die konsistente Lesestrategie von InnoDB unterscheiden sich auf unterschiedlichen Isolationsebenen.
(4) MySQL-Wiederherstellung und -Replikation haben auch einen erheblichen Einfluss auf den Sperrmechanismus und die konsistente Lesestrategie von InnoDB.
(5) Sperrkonflikte oder gar Deadlocks lassen sich nur schwer völlig vermeiden.

Nachdem Benutzer die Sperreigenschaften von InnoDB verstanden haben, können sie Sperrkonflikte und Deadlocks durch Design- und SQL-Anpassungsmaßnahmen reduzieren, darunter:

• Verwenden Sie wenn möglich eine niedrigere Isolationsebene
  
• Entwerfen Sie Indizes sorgfältig und verwenden Sie Indizes möglichst für den Datenzugriff, um die Sperrung präziser zu gestalten und die Wahrscheinlichkeit von Sperrkonflikten zu verringern.
• Wählen Sie eine angemessene Transaktionsgröße. Bei kleinen Transaktionen kommt es seltener zu Sperrkonflikten.
• Beim expliziten Sperren eines Recordset empfiehlt es sich, eine ausreichende Anzahl an Sperren gleichzeitig anzufordern. Wenn Sie beispielsweise Daten ändern möchten, ist es am besten, direkt eine exklusive Sperre zu beantragen, anstatt zuerst eine gemeinsame Sperre zu beantragen und dann beim Ändern eine exklusive Sperre anzufordern, was leicht zu einem Deadlock führen kann.
• Wenn verschiedene Programme auf eine Gruppe von Tabellen zugreifen, sollten sie versuchen, sich darauf zu einigen, in derselben Reihenfolge auf die Tabellen zuzugreifen. Versuchen Sie bei einer Tabelle, in einer festgelegten Reihenfolge auf die Zeilen zuzugreifen. Dadurch kann die Wahrscheinlichkeit eines Deadlocks erheblich verringert werden.
• Versuchen Sie, unter gleichen Bedingungen auf die Daten zuzugreifen, um die Auswirkungen von Lückensperren auf gleichzeitige Einfügungen zu vermeiden.
• Wenden Sie nicht mehr Sperrebenen an als tatsächlich benötigt werden. Zeigen Sie bei Abfragen keine Sperren an, es sei denn, dies ist unbedingt erforderlich.
• Für bestimmte Transaktionen können Tabellensperren verwendet werden, um die Verarbeitungsgeschwindigkeit zu erhöhen oder die Möglichkeit eines Deadlocks zu verringern.

MySql optimistische Sperre pessimistische Sperre

Pessimistische Sperre

Das Merkmal der pessimistischen Sperre besteht darin, dass zuerst die Sperre erworben und dann der Geschäftsvorgang ausgeführt wird. Mit anderen Worten, es ist "pessimistisch", zu glauben, dass der Erwerb der Sperre sehr wahrscheinlich fehlschlägt. Daher muss sichergestellt werden, dass die Sperre erfolgreich erworben wurde, bevor der Geschäftsvorgang ausgeführt wird. Das sogenannte „eine Sperre, zwei Prüfungen und drei Aktualisierungen“ bezieht sich normalerweise auf die Verwendung pessimistischer Sperren. Im Allgemeinen erfordert das pessimistische Sperren einer Datenbank Unterstützung durch die Datenbank selbst, d. h. das pessimistische Sperren wird durch die häufig verwendete Auswahl ... für Aktualisierungsvorgänge implementiert. Wenn die Datenbank Select for Update ausführt, erhält sie die Zeilensperre der ausgewählten Datenzeile. Wenn daher andere gleichzeitig ausgeführte Select for Update-Versuche dieselbe Zeile auszuwählen versuchen, werden sie ausgeschlossen (müssen warten, bis die Zeilensperre aufgehoben wird), wodurch der Sperreffekt erzielt wird. Die durch „Select for Update“ erworbene Zeilensperre wird am Ende der aktuellen Transaktion automatisch freigegeben und muss daher innerhalb einer Transaktion verwendet werden.

Hierbei ist zu beachten, dass verschiedene Datenbanken unterschiedliche Implementierungen und Unterstützungen für „Select for Update“ haben. Oracle unterstützt beispielsweise „Select for Update no wait“, was bedeutet, dass, wenn die Sperre nicht erhalten werden kann, sofort ein Fehler gemeldet wird, anstatt zu warten. MySQL verfügt nicht über die Option „no wait“. Ein weiteres Problem mit MySQL besteht darin, dass während der Ausführung der Select for Update-Anweisung alle gescannten Zeilen gesperrt werden, was leicht zu Problemen führen kann. Wenn Sie in MySQL pessimistische Sperren verwenden, achten Sie daher darauf, den Index und nicht einen vollständigen Tabellenscan zu verwenden.

Optimistisches Sperren

Die Besonderheit der optimistischen Sperrung besteht darin, dass zuerst Geschäftsvorgänge ausgeführt werden und die Sperre nur dann aufgehoben wird, wenn dies unbedingt erforderlich ist. Das heißt, wir gehen „optimistisch“ davon aus, dass das Erhalten der Sperre höchstwahrscheinlich erfolgreich sein wird, sodass wir die Sperre nur im letzten Schritt des Geschäftsvorgangs erhalten müssen, der die Daten tatsächlich aktualisiert.

Die Implementierung der optimistischen Sperre auf der Datenbank ist völlig logisch und erfordert keine besondere Unterstützung durch die Datenbank. Der allgemeine Ansatz besteht darin, den zu sperrenden Daten eine Versionsnummer oder einen Zeitstempel hinzuzufügen und dies dann wie folgt umzusetzen:

1. WÄHLEN Sie Daten als alte Daten, Version als alte Version von …;
2. Führen Sie Geschäftsvorgänge basierend auf den erfassten Daten durch, um neue Daten und eine neue Version zu erhalten
3. UPDATE SET Daten = neue Daten, Version = neue Version WHERE Version = alte Version
if (aktualisierte Zeile > 0) {
  // Der optimistische Sperrerwerb war erfolgreich, der Vorgang ist abgeschlossen} else {
  // Der Erwerb der optimistischen Sperre ist fehlgeschlagen. Rollback und erneuter Versuch}

Beim Aktualisieren derselben Zeile in der Datenbank ist keine Parallelität zulässig. Das heißt, jedes Mal, wenn die Datenbank eine Update-Anweisung ausführt, erhält sie die Schreibsperre der aktualisierten Zeile und gibt sie erst frei, wenn die Zeile erfolgreich aktualisiert wurde. Daher wird vor der Durchführung des Geschäftsvorgangs die aktuelle Versionsnummer der zu sperrenden Daten ermittelt. Wenn die Daten dann tatsächlich aktualisiert werden, wird die Versionsnummer erneut verglichen, um zu bestätigen, dass sie mit der zuvor ermittelten übereinstimmt. Anschließend wird die Versionsnummer aktualisiert, um zu bestätigen, dass in der Zwischenzeit keine gleichzeitigen Änderungen vorgenommen wurden. Wenn das Update fehlschlägt, kann davon ausgegangen werden, dass die alte Version der Daten gleichzeitig geändert wurde und nicht mehr vorhanden ist. Zu diesem Zeitpunkt wird davon ausgegangen, dass der Sperrerwerb fehlgeschlagen ist und der gesamte Geschäftsvorgang zurückgesetzt werden muss. Der gesamte Vorgang kann bei Bedarf erneut versucht werden.

Optimistisches Sperren hat einen geringeren Overhead als pessimistisches Sperren, wenn kein Sperrenfehler vorliegt, aber der Rollback-Overhead ist relativ groß, wenn ein Fehler auftritt. Daher eignet es sich für Szenarien, in denen die Wahrscheinlichkeit eines Sperrenfehlers relativ gering ist, und kann die Systemparallelitätsleistung verbessern. Optimistisches Sperren ist auch in einigen speziellen Szenarien anwendbar, z. B. wenn es unmöglich ist, während des Geschäftsbetriebs eine Verbindung mit der Datenbank aufrechtzuerhalten, und an anderen Stellen, an denen pessimistisches Sperren nicht angewendet werden kann.

Oben finden Sie eine ausführliche Erläuterung der Sperrstruktur in MySQL. Weitere Informationen zur MySQL-Sperrstruktur finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!