Die Sicherstellung der Konsistenz und Wirksamkeit des gleichzeitigen Datenzugriffs ist ein Problem, das alle Datenbanken lösen müssen. Sperrkonflikte sind ebenfalls ein wichtiger Faktor, der sich auf die Leistung des gleichzeitigen Datenbankzugriffs auswirkt. Aus dieser Perspektive sind Sperren für Datenbanken besonders wichtig und komplex.

MySQL-Sperrübersicht

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

MyISAM- und MEMORY-Speicher-Engines Sperren auf Tabellenebene.

Die InnoDB-Speicher-Engine unterstützt sowohl das Sperren auf Zeilenebene als auch das Sperren auf Tabellenebene, standardmäßig wird jedoch das Sperren auf Zeilenebene verwendet.

Die Eigenschaften dieser drei Sperren in MySQL lassen sich grob wie folgt zusammenfassen: Sperren auf Tabellenebene: geringer Overhead, keine Deadlocks, höchste Wahrscheinlichkeit von Sperrenkonflikten und niedrigste Parallelität.

Sperren auf Zeilenebene: Hoher Overhead, langsame Sperren; die Sperrgranularität ist am geringsten, die Wahrscheinlichkeit von Sperrenkonflikten ist am höchsten.

Seitensperre: Die Kosten und die Sperrzeit liegen zwischen Tabellensperren und Zeilensperren. Die Sperrgranularität liegt zwischen Tabellensperren und Zeilensperren, und die Parallelität ist durchschnittlich.

Nur aus Sicht der Sperren: Sperren auf Tabellenebene eignen sich besser für Anwendungen, die hauptsächlich auf Abfragen basieren und bei denen nur eine kleine Datenmenge entsprechend den Indexbedingungen aktualisiert wird, z. B. Sperren auf Zeilenebene Geeignet für Anwendungen mit einer großen Anzahl gleichzeitiger Aktualisierungen basierend auf Indexbedingungen. Eine kleine Menge unterschiedlicher Daten und gleichzeitige Abfrageanwendungen, wie z. B. einige Online-Transaktionsverarbeitungssysteme (OLTP). In den folgenden Abschnitten konzentrieren wir uns auf die Probleme von MySQL-Tabellensperren und InnoDB-Zeilensperren.

MyISAM-Tabellensperre

Die MyISAM-Speicher-Engine unterstützt nur Tabellensperren, was auch der einzige Sperrtyp ist, der in den ersten Versionen von MySQL unterstützt wurde. Mit der kontinuierlichen Verbesserung der Anwendungsanforderungen für Transaktionsintegrität und Parallelität begann MySQL mit der Entwicklung transaktionsbasierter Speicher-Engines. Später erschienen nach und nach die BDB-Speicher-Engine, die Seitensperren unterstützt, und die InnoDB-Speicher-Engine, die Zeilensperren unterstützt (eigentlich ist InnoDB eine separate). Ein Unternehmen, das jetzt von Oracle übernommen wurde. Allerdings ist die Tabellensperre von MyISAM immer noch der am weitesten verbreitete Sperrtyp. In diesem Abschnitt wird die Verwendung von MyISAM-Tabellensperren ausführlich vorgestellt.

Sperrenkonflikt auf Tabellenebene abfragen

Sie können den Tabellensperrenkonflikt auf dem System analysieren, indem Sie die Statusvariablen table_locks_waited und table_locks_immediate überprüfen:

mysql>show status like 'table%';

Die MySQL-Sperre auf Tabellenebene verfügt über zwei Modi: gemeinsame Lesesperre für Tabellen (Table Read Lock) und exklusive Tabellenschreibsperre (Table Write Lock).

Der Lesevorgang der MyISAM-Tabelle blockiert nicht die Leseanforderungen anderer Benutzer für dieselbe Tabelle, aber er blockiert die Schreibanforderungen für dieselbe Tabelle.

  Wenn ein Thread eine Schreibsperre für eine Tabelle erhält, kann nur der Thread, der die Sperre hält, die Tabelle aktualisieren . Lese- und Schreibvorgänge von anderen Threads warten, bis die Sperre aufgehoben wird.

Schreibsperre der Tabelle film_text erhalten

mysql> Tabelle film_text write sperren;

Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

|. 1001 |

|. 1001 |. Test |

1 Zeile im Satz (57,59 Sek.)

Wie füge ich eine Tabellensperre hinzu?

MyISAM fügt vor der Ausführung der Abfrageanweisung (SELECT) automatisch Lesesperren für alle beteiligten Tabellen hinzu. Dieser Vorgang führt automatisch Schreibsperren für die beteiligten Tabellen aus Es ist kein Benutzereingriff erforderlich. Daher müssen Benutzer im Allgemeinen den Befehl LOCK TABLE nicht direkt verwenden, um die MyISAM-Tabelle explizit zu sperren. Im Beispiel erfolgt die explizite Sperrung grundsätzlich aus Bequemlichkeitsgründen und ist nicht erforderlich.

Die Anzeigesperre von MyISAM-Tabellen wird im Allgemeinen durchgeführt, um Transaktionsvorgänge bis zu einem gewissen Grad zu simulieren und ein konsistentes Lesen mehrerer Tabellen zu einem bestimmten Zeitpunkt zu erreichen. Beispielsweise gibt es eine Bestelltabelle „orders“, die den Gesamtbetrag jeder Bestellung aufzeichnet, und eine Bestelldetailtabelle „order_detail“, die die Zwischensumme jedes Produkts jeder Bestellung aufzeichnet. Angenommen, wir müssen die beiden Tabellen überprüfen. Um zu überprüfen, ob die Gesamtbeträge übereinstimmen, müssen Sie möglicherweise die folgenden zwei SQLs ausführen:

Summe (Gesamtsumme) aus Bestellungen auswählen;

Summe (Zwischensumme) aus Bestelldetail auswählen; Wenn Sie zuerst zwei Tabellen ausführen, können falsche Ergebnisse erzeugt werden, da sich die Tabelle „order_detail“ möglicherweise während der Ausführung der ersten Anweisung geändert hat. Daher sollte die richtige Methode sein:

Tabellen sperren, Aufträge lokal lesen, order_detail lokal lesen;

Summe (Gesamtsumme) aus Aufträgen auswählen;

Summe (Zwischensumme) aus Auftragsdetails auswählen;

Tabellen entsperren;

Spezielle Anweisungen Die folgenden zwei Punkten.

Das obige Beispiel fügt die Option „lokal“ bei LOCK TABLES hinzu. Ihre Funktion besteht darin, anderen Benutzern das gleichzeitige Einfügen von Datensätzen am Ende der Tabelle zu ermöglichen Die MyISAM-Tabelle wird später ebenfalls erläutert.

Wenn Sie LOCK TABLES verwenden, um Tabellensperren explizit zu einer Tabelle hinzuzufügen, müssen alle an der Tabelle beteiligten Sperren gleichzeitig erhalten werden, und MySQL unterstützt keine Sperraktualisierungen. Das heißt, nach der Ausführung von LOCK TABLES können Sie nur auf die explizit gesperrten Tabellen zugreifen, nicht jedoch gleichzeitig auf die entsperrten Tabellen. Wenn Sie eine Lesesperre hinzufügen, können Sie nur Abfragevorgänge ausführen, jedoch keine Aktualisierungsvorgänge. Tatsächlich ist dies bei der automatischen Sperrung grundsätzlich der Fall und erhält immer alle von der SQL-Anweisung benötigten Sperren auf einmal. Aus diesem Grund werden MyISAM-Tabellen nicht blockiert (Deadlock Free).

Eine Sitzung verwendet den Befehl LOCK TABLE, um der Tabelle film_text eine Lesesperre hinzuzufügen. Bei der Aktualisierung oder beim gleichzeitigen Zugriff auf andere Tabellen wird jedoch ein Fehler angezeigt kann die Datensätze in der Tabelle abfragen, aber beim Aktualisieren kommt es zu einer Sperrwartezeit.

Bei der Verwendung von LOCK TABLES müssen Sie nicht nur alle verwendeten Tabellen auf einmal sperren, sondern Sie müssen sie auch, unabhängig davon, wie oft dieselbe Tabelle in der SQL-Anweisung erscheint, über denselben Alias ​​wie in der SQL-Anweisung sperren , sonst kommt es zu einem Fehler!

Beispiele sind wie folgt.

(1) Lesesperre für Akteurtabelle erhalten:

mysql> Tabelle Akteur lesen;

Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

(2) Der Zugriff über Alias ​​führt jedoch zu einem Fehler:

mysql> select a.first_name,a.last_name,b.first_name,b.last_name fromactor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a .last_name b.last_name;

ERROR 1100 (HY000): Tabelle „a“ wurde nicht mit LOCK TABLES gesperrt.

(3) Aliase müssen separat gesperrt werden:

MySQL> ,actor as b read;

Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

(4) Die Abfrage entsprechend dem Alias ​​kann korrekt ausgeführt werden:

mysql> select a.first_name,a.last_name,b.first_name ,b.last_name von Schauspieler a,actor b wobei a.first_name = b.first_name und a.first_name = 'Lisa' und a.last_name = 'Tom' und a.last_name

1 Zeile im Satz (0,00 Sek.)

Gleichzeitige Einfügungen

Wie oben erwähnt, erfolgt das Lesen und Schreiben von MyISAM-Tabellen seriell, dies gilt jedoch allgemein. Unter bestimmten Bedingungen unterstützen MyISAM-Tabellen auch gleichzeitige Abfrage- und Einfügevorgänge.

MyISAM-Speicher-Engine verfügt über eine Systemvariable concurrent_insert, die speziell zur Steuerung des gleichzeitigen Einfügeverhaltens verwendet wird. Ihr Wert kann jeweils 0, 1 oder 2 sein.

Wenn concurrent_insert auf 0 gesetzt ist, sind gleichzeitige Einfügungen nicht zulässig.

Wenn concurrent_insert auf 1 gesetzt ist und keine Lücken in der MyISAM-Tabelle vorhanden sind (d. h. es gibt keine gelöschten Zeilen in der Mitte der Tabelle), erlaubt MyISAM einem Prozess, die Tabelle zu lesen, während ein anderer Prozess Datensätze aus der Tabelle einfügt Ende der Tabelle. Dies ist auch die Standardeinstellung für MySQL.

Wenn concurrent_insert auf 2 gesetzt ist, dürfen Datensätze gleichzeitig am Ende der Tabelle eingefügt werden, unabhängig davon, ob Lücken in der MyISAM-Tabelle vorhanden sind.

Sie können die Funktion zum gleichzeitigen Einfügen der MyISAM-Speicher-Engine verwenden, um Sperrenkonflikte beim Abfragen und Einfügen derselben Tabelle in der Anwendung zu lösen. Wenn Sie beispielsweise die Systemvariable concurrent_insert auf 2 setzen, ist das gleichzeitige Einfügen immer möglich. Gleichzeitig wird die OPTIMIZE TABLE-Anweisung regelmäßig während der Leerlaufzeit des Systems ausgeführt, um den Speicherplatz zu defragmentieren und die durch das Löschen von Datensätzen verursachten Zwischenlücken zu schließen. Eine ausführliche Einführung in die OPTIMIZE TABLE-Anweisung finden Sie im Abschnitt „Zwei einfache und praktische Optimierungsmethoden“ in Kapitel 18.

MyISAM-Sperrplanung

Wie bereits erwähnt, schließen sich die Lesesperre und die Schreibsperre der MyISAM-Speicher-Engine gegenseitig aus und die Lese- und Schreibvorgänge erfolgen seriell. Wenn also ein Prozess eine Lesesperre für eine MyISAM-Tabelle anfordert und gleichzeitig ein anderer Prozess auch eine Schreibsperre für dieselbe Tabelle anfordert, wie geht MySQL damit um? Die Antwort ist, dass der Schreibprozess zuerst die Sperre erhält. Darüber hinaus wird die Schreibsperre vor der Lesesperranforderung eingefügt, selbst wenn die Leseanforderung zuerst in der Sperrwarteschlange eintrifft und die Schreibanforderung später eintrifft! Dies liegt daran, dass MySQL Schreibanfragen im Allgemeinen für wichtiger hält als Leseanfragen. Aus diesem Grund eignen sich MyISAM-Tabellen nicht für Anwendungen mit einer großen Anzahl von Aktualisierungs- und Abfragevorgängen, da eine große Anzahl von Aktualisierungsvorgängen es für Abfragevorgänge schwierig macht, Lesesperren zu erhalten, die möglicherweise für immer blockieren. Diese Situation kann manchmal wirklich schlimm werden! Glücklicherweise können wir das Planungsverhalten von MyISAM durch einige Einstellungen anpassen.

Durch die Angabe des Startparameters low-priority-updates gibt die MyISAM-Engine Leseanfragen standardmäßig Priorität.

Durch die Ausführung des Befehls SET LOW_PRIORITY_UPDATES=1 wird die Priorität der von dieser Verbindung ausgegebenen Update-Anfragen reduziert.

Reduzieren Sie die Priorität von INSERT-, UPDATE- und DELETE-Anweisungen, indem Sie das LOW_PRIORITY-Attribut der Anweisung angeben.

Obwohl die oben genannten drei Methoden entweder zuerst aktualisieren oder zuerst abfragen, können sie dennoch verwendet werden, um das schwerwiegende Problem des Wartens auf Lesesperren in Anwendungen zu lösen, bei denen Abfragen relativ wichtig sind (z. B. Benutzeranmeldesysteme).

Darüber hinaus bietet MySQL auch eine Kompromissmethode zum Anpassen von Lese- und Schreibkonflikten, dh das Festlegen eines geeigneten Werts für den Systemparameter max_write_lock_count. Wenn die Lesesperre einer Tabelle diesen Wert erreicht, priorisiert MySQL vorübergehend die Schreibanforderungen Der Pegel wird abgesenkt, um dem Lesevorgang eine gewisse Chance zu geben, die Sperre zu erhalten.

Die durch den Schreibprioritätsplanungsmechanismus verursachten Probleme und Lösungen wurden oben besprochen. Hier ist noch ein Punkt hervorzuheben: Einige Abfragevorgänge, die lange Laufzeiten erfordern, „verhungern“ auch den Schreibvorgang! Daher sollten Sie versuchen, langwierige Abfragevorgänge in Ihrer Anwendung zu vermeiden. Versuchen Sie nicht immer, das Problem mit einer SELECT-Anweisung zu lösen, da diese scheinbar clevere SQL-Anweisung oft komplexer ist und wenn möglich mehr Zeit in Anspruch nimmt. SQL-Anweisungen können durch den Einsatz von Zwischentabellen und anderen Maßnahmen bis zu einem gewissen Grad „zerlegt“ werden, sodass jeder Schritt der Abfrage in kürzerer Zeit abgeschlossen werden kann und so Sperrkonflikte reduziert werden. Wenn komplexe Abfragen unvermeidbar sind, sollten sie so geplant werden, dass sie während der Leerlaufzeiten der Datenbank ausgeführt werden. Beispielsweise können einige reguläre Statistiken so geplant werden, dass sie nachts ausgeführt werden.

InnoDB-Sperre

Der größte Unterschied zwischen InnoDB und MyISAM besteht in zwei Punkten: Zum einen werden Transaktionen unterstützt (TRANSACTION); zum anderen werden Sperren auf Zeilenebene verwendet. 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. Lassen Sie uns zunächst einige Hintergrundkenntnisse vorstellen und dann das Sperrproblem von InnoDB im Detail besprechen.

1. Transaktion (Transaktion) und ihre ACID-Attribute

Eine Transaktion ist eine logische Verarbeitungseinheit, die aus einer Reihe von SQL-Anweisungen besteht. Eine Transaktion verfügt über die folgenden 4 Attribute, die normalerweise als ACID-Attribute der Transaktion bezeichnet werden.

(Atomarität) Atomarität: Eine Transaktion ist eine atomare Operationseinheit und alle Änderungen an den Daten werden entweder ausgeführt oder keine.

(Konsistente) Konsistenz: Daten müssen zu Beginn und Abschluss einer Transaktion konsistent bleiben. Dies bedeutet, dass alle relevanten Datenregeln auf Transaktionsänderungen angewendet werden müssen, um die Datenintegrität am Ende der Transaktion aufrechtzuerhalten. Außerdem müssen alle internen Datenstrukturen (z. B. B-Tree-Indizes oder doppelt verknüpfte Listen) korrekt sein.

(Isolation) Isolation: Das Datenbanksystem bietet einen bestimmten Isolationsmechanismus, um sicherzustellen, dass Transaktionen in einer „unabhängigen“ Umgebung ausgeführt werden, die nicht durch externe gleichzeitige Vorgänge beeinträchtigt wird. Das bedeutet, dass Zwischenzustände während einer Transaktion für die Außenwelt nicht sichtbar sind und umgekehrt.

(Dauerhaft) Haltbarkeit: Nach Abschluss der Transaktion ist die Änderung der Daten dauerhaft und kann auch bei einem Systemausfall beibehalten werden.

Banküberweisung ist ein typisches Beispiel für eine Transaktion.

2. Probleme durch gleichzeitige Transaktionsverarbeitung

Im Vergleich zur seriellen Verarbeitung kann die gleichzeitige Transaktionsverarbeitung die Auslastung der Datenbankressourcen erheblich steigern und den Transaktionsdurchsatz des Datenbanksystems verbessern, wodurch mehr Benutzer unterstützt werden. Die gleichzeitige Transaktionsverarbeitung bringt jedoch auch einige Probleme mit sich, darunter hauptsächlich die folgenden Situationen.

Verlorene Aktualisierung: Wenn zwei oder mehr Transaktionen dieselbe Zeile auswählen und die Zeile dann basierend auf dem ursprünglich ausgewählten Wert aktualisieren, tritt das Problem der verlorenen Aktualisierung auf, da jede Transaktion nicht weiß, dass die anderen Transaktionen vorhanden sind – die letzte Aktualisierung hat Vorrang Aktualisierungen anderer Firmen. Beispielsweise erstellen zwei Redakteure elektronische Kopien desselben Dokuments. Jeder Redakteur ändert seine Kopie selbstständig und speichert dann die geänderte Kopie, wobei das Originaldokument überschrieben wird. Der Bearbeiter, der zuletzt eine Kopie seiner Änderungen gespeichert hat, überschreibt die von einem anderen Bearbeiter vorgenommenen Änderungen. Dieses Problem kann vermieden werden, wenn ein Editor nicht auf dieselbe Datei zugreifen kann, bis ein anderer Editor die Transaktion abschließt und festschreibt.

Dirty Reads: Eine Transaktion ändert einen Datensatz, die Daten dieses Datensatzes befinden sich zu diesem Zeitpunkt in einem inkonsistenten Zustand; eine andere Transaktion liest ebenfalls denselben Datensatz liest diese „schmutzigen“ Daten und führt eine entsprechende Weiterverarbeitung durch, was zu nicht festgeschriebenen Datenabhängigkeiten führt. Dieses Phänomen wird treffend als „Dirty Reading“ bezeichnet.

Nicht wiederholbare Lesevorgänge: Eine Transaktion liest die zuvor gelesenen Daten einige Zeit nach dem Lesen einiger Daten erneut und stellt dann fest, dass sich die gelesenen Daten geändert haben oder einige Der Datensatz wurde gelöscht! Dieses Phänomen wird als „nicht wiederholbares Lesen“ bezeichnet.

Phantom-Lesevorgänge: Eine Transaktion liest zuvor abgerufene Daten gemäß denselben Abfragebedingungen erneut und stellt dann fest, dass andere Transaktionen neue Daten eingefügt haben, die ihre Abfragebedingungen erfüllen. Dieses Phänomen wird als „Phantom-Lesevorgänge“ bezeichnet.

3. Transaktionsisolationsstufe

Unter den oben genannten Problemen, die durch die gleichzeitige Transaktionsverarbeitung verursacht werden, sollte „Update-Verlust“ normalerweise vollständig vermieden werden. Das Verhindern von Aktualisierungsverlusten kann jedoch nicht allein durch den Datenbanktransaktionscontroller gelöst werden. Die Anwendung muss die erforderlichen Sperren für die zu aktualisierenden Daten hinzufügen. Daher sollte die Verhinderung von Aktualisierungsverlusten in der Verantwortung der Anwendung liegen.

„Dirty Read“, „Non-Repeatable Read“ und „Phantom Read“ sind tatsächlich Datenbank-Lesekonsistenzprobleme, die durch die Bereitstellung eines bestimmten Transaktionsisolationsmechanismus durch die Datenbank gelöst werden müssen. Die Art und Weise, wie Datenbanken die Transaktionsisolation implementieren, kann grundsätzlich in die folgenden zwei Typen unterteilt werden.

Eine besteht darin, die Daten vor dem Lesen zu sperren, um zu verhindern, dass andere Transaktionen die Daten ändern.

Die andere besteht darin, über einen bestimmten Mechanismus einen konsistenten Daten-Snapshot (Snapshot) des Datenanforderungszeitpunkts zu generieren, ohne Sperren hinzuzufügen, und diesen Snapshot zu verwenden, um eine bestimmte Ebene (Anweisungsebene oder Transaktionsebene) konsistenten Lesens bereitzustellen. Aus Benutzersicht scheint es, dass die Datenbank mehrere Versionen derselben Daten bereitstellen kann. Daher wird diese Technologie als Data Multi-Version Concurrency Control (kurz MVCC oder MCC) bezeichnet, die häufig auch als Multiversionsdatenbank bezeichnet wird.

Konsistentes Lesen, auch Snapshot-Lesen genannt. Der MVCC-Mechanismus wird verwendet, um die übermittelten Daten rückgängig zu machen. Das Lesen ist also nicht blockierend.

Konsistenzlesung muss die Daten lesen, die zu einem bestimmten Zeitpunkt übermittelt wurden. Es gibt einen Sonderfall: Die in dieser Transaktion geänderten Daten, auch die nicht festgeschriebenen Daten, können im späteren Teil dieser Transaktion gelesen werden. Konsistentes Lesen bezieht sich auf eine gewöhnliche Select-Anweisung ohne Klauseln, z. B. für Update, im Share-Modus usw. Die beim Rückgängigmachen übermittelten Daten werden verwendet und es sind keine Sperren erforderlich (außer MDL). Der aktuelle Lesevorgang bezieht sich auf den Lesevorgang, der durch Anweisungen wie „Update“, „Delete“, „Select for Update“, „Select in Share Mode“ usw. durchgeführt wird. Sie lesen die neuesten Daten in der Datenbank und sperren die gelesenen Zeilen und Lücken (RR-Isolationsstunde). Wenn die Sperre nicht abgerufen werden kann, wird gewartet, bis sie abgerufen wird, oder es kommt zu einer Zeitüberschreitung.

Je strenger die Transaktionsisolation der Datenbank ist, desto geringer sind die Nebenwirkungen der Parallelität, aber desto höher ist der gezahlte Preis, da die Transaktionsisolation im Wesentlichen dazu führt, dass Transaktionen bis zu einem gewissen Grad „serialisiert“ werden, was offensichtlich im Widerspruch zur „Parallelität“ steht. Gleichzeitig stellen verschiedene Anwendungen unterschiedliche Anforderungen an die Lesekonsistenz und die Transaktionsisolation. Beispielsweise reagieren viele Anwendungen nicht empfindlich auf „nicht wiederholbare Lesevorgänge“ und „Phantomlesevorgänge“ und legen möglicherweise mehr Wert auf die Fähigkeit, gleichzeitig auf Daten zuzugreifen.

Um den Widerspruch zwischen „Isolation“ und „Parallelität“ zu lösen, definiert ISO/ANSI SQL92 4 Transaktionsisolationsstufen. Jede Stufe hat einen unterschiedlichen Isolationsgrad und ermöglicht unterschiedliche Nebenwirkungen Anforderungen gleichen den Widerspruch zwischen „Isolation“ und „Parallelität“ aus, indem Sie unterschiedliche Isolationsstufen wählen. Tabelle 20-5 bietet eine gute Zusammenfassung der Merkmale dieser vier Isolationsstufen.

Jede spezifische Datenbank implementiert nicht unbedingt vollständig die oben genannten vier Isolationsstufen. Oracle bietet nur zwei Standard-Isolationsstufen: „Lesen festgeschrieben“ und „Serialisierbar“ und stellt außerdem eine eigene definierte schreibgeschützte Isolationsstufe bereit. SQL Server unterstützt die oben genannte ISO/ANSI SQL92 Zusätzlich zu den 4 Isolationsstufen unterstützt es auch eine Isolationsstufe namens „Snapshot“, aber genau genommen handelt es sich um eine serialisierbare Isolationsstufe, die mit MVCC implementiert wird.

MySQL unterstützt alle 4 Isolationsstufen, es gibt jedoch einige Merkmale in der spezifischen Implementierung. Beispielsweise wird in einigen Isolationsstufen MVCC-konsistentes Lesen verwendet, in einigen Fällen jedoch nicht. Diese Inhalte werden in späteren Kapiteln erläutert .

4. Erhalten Sie den InnoDB-Zeilensperrenkonflikt.

Sie können den Zeilensperrenkonflikt auf dem System analysieren, indem Sie die Statusvariable InnoDB_row_lock überprüfen.

mysql> show status like 'innodb_row_lock%';

|. 0 |

5 Zeilen im Satz (0,01 Sek.)

Wenn Sie feststellen, dass der Sperrenkonflikt schwerwiegend ist, beispielsweise die Werte von InnoDB_row_lock_waits und InnoDB_row_lock_time_avg relativ hoch sind, können Sie InnoDB-Monitore auch so einstellen, dass die Tabellen und Daten weiter beobachtet werden Zeilen usw., in denen Sperrenkonflikte auftreten, und analysieren Sie die Sperren. Ursache des Konflikts.

Die spezifische Methode lautet wie folgt:

mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

Abfrage OK, 0 Zeilen betroffen (0,14 Sek.)

Dann können Sie die folgende Anweisung zum Anzeigen verwenden:

mysql> Innodb-Status anzeigen Im Inhalt werden detaillierte Informationen über die aktuelle Sperre angezeigt, einschließlich Tabellenname, Sperrtyp, Sperrdatensatzstatus usw., um die weitere Analyse und Problembestimmung zu erleichtern. Nach dem Öffnen des Monitors wird der Überwachungsinhalt standardmäßig alle 15 Sekunden im Protokoll aufgezeichnet. Wenn er über einen längeren Zeitraum geöffnet wird, wird die .err-Datei daher sehr groß Der Benutzer muss daran denken, die Überwachungstabelle zu löschen, um sie zu schließen, oder indem er den Server mit der Option „--console“ startet, um das Schreiben der Protokolldatei zu deaktivieren.

5. Der Zeilensperrmodus und die Sperrmethode von InnoDB

InnoDB implementiert die folgenden zwei Arten von Zeilensperren.

Gemeinsame Sperre (S): Ermöglicht einer Transaktion das Lesen einer Zeile und verhindert, dass andere Transaktionen eine exklusive Sperre für denselben Datensatz erhalten.

Exklusive Sperre (X): Ermöglicht einer Transaktion, die eine exklusive Sperre erhält, das Aktualisieren von Daten und verhindert, dass andere Transaktionen gemeinsame Lesesperren und exklusive Schreibsperren für denselben Datensatz erhalten.

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

Beide Absichtssperren sind Tabellensperren.

Intention Shared Lock (IS): Die Transaktion beabsichtigt, der Datenzeile eine gemeinsame Sperre hinzuzufügen. Die Transaktion muss zuerst die IS-Sperre der Tabelle erhalten, bevor sie einer Datenzeile eine gemeinsame Sperre hinzufügt.

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

Die Zusammenfassung lautet wie folgt: 1. Für UPDATE-, DELETE- und INSERT-Anweisungen fügt InnoDB automatisch exklusive Sperren (X) zu den beteiligten Datensätzen hinzu;

2. Für gewöhnliche SELECT-Anweisungen fügt InnoDB keine Sperren hinzu

3 Eine Transaktion kann dem Recordset durch die folgende Anweisung gemeinsame Sperren oder exklusive Sperren hinzufügen.

Gemeinsame Sperre(n): SELECT * FROM table_name WHERE ... SPERRE IM SHARE-MODUS.

Exklusive Sperre (X): SELECT * FROM table_name WHERE ... FOR UPDATE.

Verwenden Sie SELECT ... IN SHARE MODE, um eine gemeinsame Sperre zu erhalten. Sie wird hauptsächlich verwendet, um zu bestätigen, ob eine bestimmte Zeile von Datensätzen vorhanden ist, wenn Datenabhängigkeiten erforderlich sind, und um sicherzustellen, dass niemand UPDATE- oder DELETE-Vorgänge für diesen Datensatz ausführt.

Wenn die aktuelle Transaktion jedoch auch den Datensatz aktualisieren muss, führt dies wahrscheinlich zu einem Deadlock. Bei Anwendungen, die den Zeilendatensatz nach dem Sperren aktualisieren müssen, sollte die Methode SELECT... FOR UPDATE verwendet werden, um einen zu erhalten Exklusives Schloss.

6. Die InnoDB-Zeilensperre wird durch das Sperren der Indexelemente im Index implementiert. Letzteres wird durch das Sperren der entsprechenden Datenzeilen im Datenblock erreicht.

Die Zeilensperren-Implementierungsfunktion von InnoDB bedeutet, dass InnoDB Sperren auf Zeilenebene nur verwendet, wenn Daten über Indexbedingungen abgerufen werden. Andernfalls verwendet InnoDB Tabellensperren!

In praktischen Anwendungen sollte dieser Funktion der InnoDB-Zeilensperre besondere Aufmerksamkeit gewidmet werden, da sie sonst zu einer großen Anzahl von Sperrkonflikten führen und somit die Parallelitätsleistung beeinträchtigen kann.

(1) Bei Abfragen ohne Indexbedingungen verwendet InnoDB Tabellensperren anstelle von Zeilensperren.

(2) Da die Zeilensperre von MySQL eine Sperre für den Index und nicht für den Datensatz ist, wird zwar auf Datensätze verschiedener Zeilen zugegriffen,

aber wenn derselbe Indexschlüssel verwendet wird, kommt es zu einem Sperrkonflikt. Bitte beachten Sie dies bei der Gestaltung Ihrer Bewerbung.

(3) Wenn eine Tabelle über mehrere Indizes verfügt, können verschiedene Transaktionen unterschiedliche Indizes verwenden, um verschiedene Zeilen zu sperren.

Darüber hinaus verwendet InnoDB Zeilensperren, unabhängig davon, ob Primärschlüsselindizes oder gewöhnliche Indizes verwendet werden.

(4) Selbst wenn in der Bedingung ein Indexfeld verwendet wird, bestimmt MySQL anhand der Kosten verschiedener Ausführungspläne, ob ein vollständiger Tabellenscan effizienter ist, z Bei einigen kleinen Tabellen werden keine Indizes verwendet. In diesem Fall verwendet InnoDB Tabellensperren anstelle von Zeilensperren. Vergessen Sie daher bei der Analyse von Sperrkonflikten nicht, den SQL-Ausführungsplan zu überprüfen, um sicherzustellen, dass der Index tatsächlich verwendet wird. Eine ausführliche Diskussion der Umstände, unter denen MySQL keine Indizes verwendet, finden Sie in der Einleitung im Abschnitt „Indexprobleme“ dieses Kapitels.

7. Lückensperre (Next-Key-Sperre)

Wenn wir Bereichsbedingungen anstelle von Gleichheitsbedingungen verwenden, um Daten abzurufen und gemeinsame oder exklusive Sperren anzufordern, sperrt InnoDB die Indexeinträge vorhandener Datensätze, die die Bedingungen für Datensätze erfüllen, deren Schlüsselwerte jedoch innerhalb des Bedingungsbereichs liegen existieren, genannt „GAP“, InnoDB wird auch diese „Lücke“ sperren, dieser Sperrmechanismus ist die sogenannte Lückensperre (Next-Key-Sperre). Wenn die empid-Tabelle nur 101 Datensätze enthält, sind die empid-Werte 1, 2,...,100,101. Das folgende SQL:

Wählen Sie * aus empid > für update; Bedingungsabruf: InnoDB sperrt nicht nur die Datensätze mit einem empid-Wert von 101, die die Bedingungen erfüllen, sondern sperrt auch die „Lücken“ mit empid größer als 101 (diese Datensätze existieren nicht). Der Zweck der Verwendung von Lückensperren durch InnoDB besteht einerseits darin, Phantom-Lesevorgänge zu verhindern und die Anforderungen relevanter Isolationsstufen zu erfüllen. Wenn im obigen Beispiel keine Lückensperren verwendet werden, fügen andere Transaktionen Datensätze mit einem Emid-Wert größer als 100 ein Wenn diese Transaktion die obige Anweisung erneut ausführt, erfolgt andererseits ein Phantomlesen, um ihre Wiederherstellungs- und Replikationsanforderungen zu erfüllen. Die Auswirkungen seiner Wiederherstellung und Replikation auf den Sperrmechanismus sowie die Verwendung von Lückensperren durch InnoDB unter verschiedenen Isolationsstufen werden in den folgenden Kapiteln näher erläutert.

Wenn Bereichsbedingungen zum Abrufen und Sperren von Datensätzen verwendet werden, blockiert der Sperrmechanismus von InnoDB offensichtlich das gleichzeitige Einfügen von Schlüsselwerten innerhalb des qualifizierten Bereichs, was häufig zu erheblichen Sperrwartezeiten führt. Daher müssen wir bei der tatsächlichen Anwendungsentwicklung, insbesondere bei Anwendungen mit vielen gleichzeitigen Einfügungen, versuchen, die Geschäftslogik zu optimieren, gleiche Bedingungen für den Zugriff auf Aktualisierungsdaten zu verwenden und die Verwendung von Bereichsbedingungen zu vermeiden.

Besonderer Hinweis ist, dass InnoDB zusätzlich zur Verwendung von Lückensperren beim Sperren durch Bereichsbedingungen auch Lückensperren verwendet, wenn gleiche Bedingungen verwendet werden, um eine Sperre für einen nicht vorhandenen Datensatz anzufordern!

Der Bedarf an Wiederherstellung und Replikation, die Auswirkungen auf den InnoDB-Sperrmechanismus

MySQL verwendet BINLOG, um die erfolgreiche Ausführung von INSERT, UPDATE, DELETE und anderen SQL-Anweisungen aufzuzeichnen, die Daten aktualisieren, und realisiert so die Wiederherstellung und Master-Slave-Replikation der MySQL-Datenbank. Der Wiederherstellungsmechanismus von MySQL (die Replikation ist eigentlich eine kontinuierliche BINLOG-basierte Wiederherstellung auf Slave-MySQL) weist die folgenden Merkmale auf.

Erstens erfolgt die MySQL-Wiederherstellung auf der Ebene der SQL-Anweisung, d. h. die erneute Ausführung der SQL-Anweisung in BINLOG. Dies unterscheidet sich von der Oracle-Datenbank, die auf Datenbankdateiblöcken basiert.

Zweitens wird MySQLs BINLOG in der Reihenfolge aufgezeichnet, in der Transaktionen übermittelt werden, und die Wiederherstellung erfolgt ebenfalls in dieser Reihenfolge. Dies unterscheidet sich auch von Oracle. Oracle stellt Daten gemäß der Systemänderungsnummer (SCN) wieder her. Wenn jede Transaktion beginnt, weist Oracle eine weltweit eindeutige SCN zu. Die Reihenfolge der Transaktionsstarts ist konsistent.

Aus den beiden oben genannten Punkten geht hervor, dass der Wiederherstellungsmechanismus von MySQL Folgendes erfordert: Bevor eine Transaktion übermittelt wird, können andere gleichzeitige Transaktionen keine Datensätze einfügen, die ihre Sperrbedingungen erfüllen, d. ANSI SQL92 Die Anforderung für die Isolationsstufe „Wiederholbares Lesen“ erfordert tatsächlich, dass Transaktionen serialisiert werden.

Darüber hinaus ist dies für SQL-Anweisungen wie „insert into target_tab select * from source_tab where ...“ und „create table new_tab ...select ... From source_tab where ...(CTAS)“ nicht der Fall verfügen über source_tab Führen Sie beliebige Aktualisierungsvorgänge aus, MySQL führt jedoch eine spezielle Verarbeitung für diese Art von SQL-Anweisung durch.

(Hier fügt InnoDB eine gemeinsame Sperre zu source_tab hinzu und verwendet keine Technologie zum Lesen der Datenkonsistenz mehrerer Versionen!)

Im obigen Beispiel entspricht das einfache Lesen der Daten in der Tabelle „source_tab“ dem Ausführen einer gewöhnlichen For SELECT-Anweisung , verwenden Sie einfach eine konsistente Lesart. ORACLE tut genau dies, indem es durch die MVCC-Technologie implementierte Multiversionsdaten verwendet, um ein konsistentes Lesen zu erreichen, ohne Sperren für source_tab hinzuzufügen. Wir wissen, dass InnoDB auch Daten mit mehreren Versionen implementiert und keine Sperren für das normale SELECT-konsistente Lesen erfordert. Hier fügt InnoDB jedoch eine gemeinsame Sperre zu source_tab hinzu und verwendet keine Technologie zum konsistenten Lesen von Daten mit mehreren Versionen.

Warum macht MySQL das? Der Grund besteht darin, die Korrektheit der Wiederherstellung und Replikation sicherzustellen. Denn ohne Sperre kann es zu falschen Datenwiederherstellungsergebnissen kommen, wenn andere Transaktionen während der Ausführung der obigen Anweisung source_tab aktualisieren. Um dies zu demonstrieren, wiederholen wir das vorherige Beispiel. Der Unterschied besteht darin, dass vor der Ausführung der Transaktion durch session_1 der Wert der Systemvariablen innodb_locks_unsafe_for_binlog auf „on“ gesetzt wird (der Standardwert ist „off“)

Wie aus dem Obigen ersichtlich ist, sperrt InnoDB nach dem Setzen des Werts der Systemvariablen innodb_locks_unsafe_for_binlog den source_tab nicht mehr, und das Ergebnis stimmt mit der Anwendungslogik überein :

SET TIMESTAMP=1169175130;

BEGIN;

# bei 274

#070119 10:51:57 Server-ID 1 end_log_pos 105 Abfrage thread_id=1 exec_time=0 error_code=0

SET. TIME STEMPEL=1169175117;

update source_tab set name = '8 ' where name = '1 ';

# at 379

#070119 10:52:10 server id 1 end_log_pos 406 474 Query thread_id= 2 exec_time=0 error_code=0

SET TIMESTAMP= 1169175134;

BEGIN;

# bei 474

#070119 10:51:29 Server-ID 1 end_log_pos 119 Abfrage thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1169175089;

in target_tab einfügen, d1 auswählen, name from source_tab where name = '1';

# at 593

#070119 10:52:14 server id 1 end_log_pos 620 Der Aktualisierungsvorgang erfolgt vor INSERT...SELECT. Wenn dieses BINLOG für die Datenbankwiederherstellung verwendet wird, stimmt das Wiederherstellungsergebnis nicht mit der tatsächlichen Anwendungslogik überein, und die Master-Slave-Datenbank ist inkonsistent.

Daher können die Anweisungen INSERT...SELECT... und CREATE TABLE...SELECT... gleichzeitige Aktualisierungen der Quelltabelle verhindern und dazu führen, dass auf die Sperre der Quelltabelle gewartet wird. Wenn die Abfrage komplex ist, führt dies zu ernsthaften Leistungsproblemen, und wir sollten versuchen, sie in unseren Anwendungen nicht zu verwenden. Tatsächlich nennt MySQL diese Art von SQL nicht deterministisches SQL und wird nicht empfohlen.

Wenn diese Art von SQL zum Implementieren der Geschäftslogik in der Anwendung verwendet werden muss und Sie die gleichzeitigen Aktualisierungen der Quelltabelle nicht beeinträchtigen möchten, können Sie die folgenden zwei Maßnahmen ergreifen:

Verfolgen Sie zunächst den Ansatz im Beispiel oben und ändern Sie den Wert von innodb_locks_unsafe_for_binlog. Setzen Sie ihn auf „on“, um MySQL zu zwingen, das Lesen der Datenkonsistenz mehrerer Versionen zu verwenden. Der dafür gezahlte Preis besteht jedoch darin, dass die Daten mit Binlog möglicherweise nicht korrekt wiederhergestellt oder kopiert werden. Daher wird diese Methode nicht empfohlen.

Die zweite Möglichkeit besteht darin, dies indirekt durch die Verwendung einer Kombination aus den Anweisungen „select * from source_tab ... Into outfile“ und „load data infile ...“ zu erreichen. Auf diese Weise sperrt MySQL source_tab nicht.

8. Unterschiede beim konsistenten Lesen und Sperren von InnoDB unter verschiedenen Isolationsstufen. Wie bereits erwähnt, sind Sperren und Multiversionsdaten für InnoDB die Mittel, um konsistente Lese- und ISO/ANSI-SQL92-Isolationsstufen zu erreichen Die Isolationsstufe, die konsistente Lesestrategie und die erforderlichen Sperren, die InnoDB bei der Verarbeitung von SQL verwendet, sind unterschiedlich. Gleichzeitig haben die Merkmale der Datenwiederherstellungs- und Replikationsmechanismen auch einen großen Einfluss auf einige konsistente SQL-Lesestrategien und Sperrstrategien. Diese Merkmale sind zur Vereinfachung für den Leser wie in Tabelle 20-16 dargestellt zusammengefasst.

1: Wenn die Isolationsstufe RC ist, werden Lückensperren nicht verwendet:

Jeder konsistente Lesevorgang, auch innerhalb derselben Transaktion, legt einen eigenen neuen Snapshot fest und liest ihn. siehe Abschnitt 14.8.2.3, „Konsistente nicht sperrende Lesevorgänge“.

Beim Sperren von Lesevorgängen (SELECT mit FOR UPDATE oder LOCK IN SHARE MODE), UPDATE-Anweisungen und DELETE-Anweisungen sperrt InnoDB nur Indexdatensätze, nicht die Lücken davor und somit ermöglicht das freie Einfügen neuer Datensätze neben gesperrten Datensätzen. Die Lückensperre wird nur zur Prüfung von Fremdschlüsseleinschränkungen und zur Prüfung doppelter Schlüssel verwendet.

Die offizielle Dokumentationsadresse lautet: https://dev.mysql.com/doc/refman /5.5/en/innodb-transaction-isolation-levels.html

2: Unter der wiederholbaren Leseisolationsstufe werden Lückensperren nicht verwendet, wenn der Index eindeutig ist und die Suche ebenfalls eindeutig ist. Andernfalls werden Lückensperren verwendet Die offizielle Beschreibung lautet wie folgt:

REPEATABLE READ

Dies ist die Standardisolationsstufe für konsistente Lesevorgänge innerhalb derselben Transaktion. Dies bedeutet, dass Sie mehrere einfache (nicht sperrende) SELECT-Anweisungen innerhalb derselben Transaktion lesen In derselben Transaktion sind diese SELECT-Anweisungen auch untereinander konsistent. Siehe Abschnitt 14.8.2.3, „Konsistente nicht sperrende Lesevorgänge“. Das Sperren hängt davon ab, ob die Anweisung einen eindeutigen Index mit einer eindeutigen Suchbedingung oder eine Suchbedingung vom Bereichstyp verwendet.

Bei einem eindeutigen Index mit einer eindeutigen Suchbedingung sperrt InnoDB nur den gefundenen Indexdatensatz, nicht die Lücke davor.

Für andere Suchbedingungen sperrt InnoDB den gescannten Indexbereich mithilfe von Lückensperren oder Next-Key-Sperren, um Einfügungen anderer Sitzungen in die vom Bereich abgedeckten Lücken zu blockieren. Informationen zu Lückensperren und Next-Key-Sperren finden Sie in Abschnitt 14.8 . 1, „InnoDB-Sperre“.

Die offizielle Adresse der Dokumentbeschreibung lautet: https://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-isolation-levels.html

9. Wann werden Tabellensperren verwendet?

Für InnoDB-Tabellen In den meisten Fällen sollten Sperren auf Zeilenebene verwendet werden, da Transaktionen und Zeilensperren häufig der Grund sind, warum wir uns für InnoDB-Tabellen entscheiden. In einzelnen Sondertransaktionen können jedoch auch Sperren auf Tabellenebene in Betracht gezogen werden.

Die erste Situation ist: Die Transaktion muss die meisten oder alle Daten aktualisieren und die Tabelle ist relativ groß. Wenn die Standardzeilensperre verwendet wird, ist nicht nur die Effizienz der Transaktionsausführung gering, sondern es kann auch andere Ursachen haben Transaktionen warten lange und es kommt zu Sperrkonflikten. In diesem Fall können Sie die Verwendung von Tabellensperren in Betracht ziehen, um die Ausführungsgeschwindigkeit der Transaktion zu verbessern.

Die zweite Situation ist: Die Transaktion umfasst mehrere Tabellen, was relativ komplex ist und wahrscheinlich zu einem Deadlock und einer großen Anzahl von Transaktions-Rollbacks führt. In diesem Fall können Sie auch erwägen, die an der Transaktion beteiligten Tabellen einmal zu sperren, um Deadlocks zu vermeiden und den durch das Transaktions-Rollback verursachten Datenbank-Overhead zu reduzieren.

Natürlich sollten nicht zu viele dieser beiden Arten von Transaktionen in der Anwendung vorhanden sein, andernfalls sollten Sie über die Verwendung von MyISAM-Tabellen nachdenken.

Beachten Sie unter InnoDB die folgenden zwei Punkte, wenn Sie Tabellensperren verwenden.

(1) Obwohl Sie InnoDB mithilfe von LOCK TABLES Sperren auf Tabellenebene hinzufügen können, ist zu beachten, dass Tabellensperren nicht von der InnoDB-Speicher-Engine-Schicht verwaltet werden, sondern von der oberen Schicht: MySQL Server Autocommit = 0, innodb_table_locks = 1 (Standardeinstellung), die InnoDB-Schicht kann die von MySQL hinzugefügten Tabellensperren kennen, und MySQL Server kann auch die von InnoDB hinzugefügten Zeilensperren erkennen. In diesem Fall kann InnoDB automatisch Deadlocks auf Tabellenebene identifizieren Sperren; Andernfalls kann InnoDB solche Deadlocks nicht automatisch erkennen und behandeln. Was den Deadlock betrifft, werden wir ihn im nächsten Abschnitt weiter besprechen.

(2) Wenn Sie LOCK TABLES zum Sperren der InnoDB-Tabelle verwenden, achten Sie darauf, AUTOCOMMIT auf 0 zu setzen, da MySQL die Tabelle sonst nicht sperren wird. Verwenden Sie UNLOCK TABLES nicht, um die Tabellensperre vor Ende der Transaktion aufzuheben, da UNLOCK TABLES dies tut Die Transaktion wird implizit festgeschrieben; COMMIT oder ROLLBACK können keine Sperren auf Tabellenebene aufheben, die mit LOCK TABLES hinzugefügt wurden, und Tabellensperren müssen mit UNLOCK TABLES aufgehoben werden. Der richtige Weg ist wie folgt.

Wenn Sie beispielsweise in Tabelle t1 schreiben und aus Tabelle t lesen müssen, können Sie wie folgt vorgehen:

SET AUTOCOMMIT=0;

LOCK TABLES t1 WRITE, t2 READ, . ..;

[hier etwas mit den Tabellen t1 und t2 machen];

COMMIT;

UNLOCK TABLES;

10. Bezüglich Deadlock

Wie oben erwähnt, sind MyISAM-Tabellensperren Deadlock-frei, da MyISAM sie immer erhält einmal Alle erforderlichen Sperren sind entweder erfüllt oder warten, sodass kein Deadlock auftritt. Aber in InnoDB werden Sperren, mit Ausnahme von Transaktionen, die aus einem einzelnen SQL bestehen, schrittweise erworben, was bestimmt, dass in InnoDB ein Deadlock möglich ist. Tabelle 20-17 zeigt ein Beispiel für einen Deadlock.

Im obigen Beispiel müssen beide Transaktionen die exklusive Sperre der anderen Partei erhalten, um mit dem Abschluss der Transaktion fortfahren zu können. Diese Art des zyklischen Wartens auf eine Sperre ist ein typischer Deadlock.

Nachdem ein Deadlock auftritt, kann InnoDB ihn normalerweise automatisch erkennen und veranlassen, dass eine Transaktion die Sperre aufhebt und zurücksetzt, während eine andere Transaktion die Sperre erhält und die Transaktion weiter abschließt. Wenn jedoch externe Sperren oder Tabellensperren beteiligt sind, kann InnoDB Deadlocks nicht vollständig automatisch erkennen. Dies muss durch Festlegen des Sperrwartezeit-Timeout-Parameters innodb_lock_wait_timeout behoben werden. Es ist zu beachten, dass dieser Parameter nicht nur zur Lösung des Deadlock-Problems verwendet wird. Wenn der gleichzeitige Zugriff relativ hoch ist und eine große Anzahl von Transaktionen ausgesetzt wird, weil die erforderlichen Sperren nicht sofort erhalten werden können, wird eine große Menge an Computerressourcen beansprucht und schwerwiegende Leistungsprobleme verursachen. Das Problem zieht sich sogar über Datenbanken hinweg. Wir können diese Situation vermeiden, indem wir einen geeigneten Schwellenwert für die Sperrwartezeit festlegen.

Im Allgemeinen sind Deadlocks ein Problem des Anwendungsdesigns, indem Geschäftsprozesse, Datenbankobjektdesign, Transaktionsgröße und SQL-Anweisungen angepasst werden, die auf die Datenbank zugreifen. Die meisten Deadlocks können vermieden werden.

Im Folgenden werden anhand von Beispielen einige gängige Methoden zur Vermeidung von Deadlocks vorgestellt.

(1) Wenn in einer Anwendung verschiedene Programme gleichzeitig auf mehrere Tabellen zugreifen, versuchen Sie, den Zugriff auf die Tabellen in derselben Reihenfolge zu vereinbaren, was die Wahrscheinlichkeit eines Deadlocks erheblich verringern kann. Da im folgenden Beispiel die beiden 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, kann ein Deadlock vermieden werden.

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

(3) Wenn Sie in einer Transaktion einen Datensatz aktualisieren möchten, sollten Sie direkt eine Sperre mit ausreichender Stufe beantragen, d. h. eine exklusive Sperre. Sie sollten nicht zuerst eine gemeinsame Sperre beantragen Beantragen Sie beim Aktualisieren eine exklusive Sperre, denn wenn der Benutzer eine exklusive Sperre beantragt Zu diesem Zeitpunkt haben möglicherweise andere Transaktionen die gemeinsame Sperre desselben Datensatzes erhalten, was zu Sperrkonflikten oder sogar Deadlocks führt. Eine konkrete Demonstration finden Sie im Beispiel in Abschnitt 20.3.3.

(4) Wie bereits erwähnt, unter der Isolationsstufe REPEATABLE-READ, wenn zwei Threads SELECT...FOR UPDATE verwenden, um gleichzeitig exklusive Sperren für Datensätze mit derselben Bedingung hinzuzufügen, wenn es keinen Datensatz gibt, der die Bedingung erfüllt , beide Threads fügen Lock erfolgreich hinzu. Das Programm erkennt, dass der Datensatz noch nicht existiert und versucht, einen neuen Datensatz einzufügen. Wenn beide Threads dies tun, kommt es zu einem Deadlock. In diesem Fall kann das Problem durch Ändern der Isolationsstufe auf READ COMMITTED vermieden werden.

(5) Wenn die Isolationsstufe READ COMMITTED ist und beide Threads zuerst SELECT...FOR UPDATE ausführen, stellen Sie fest, ob Datensätze vorhanden sind, die die Bedingungen erfüllen, und fügen Sie die Datensätze ein, wenn nicht. Zu diesem Zeitpunkt kann nur ein Thread erfolgreich eingefügt werden, und der andere Thread wartet auf eine Sperre. Wenn der erste Thread sendet, macht der zweite Thread jedoch einen Fehler erhält ein exklusives Schloss! Wenn zu diesem Zeitpunkt ein dritter Thread eine exklusive Sperre beantragt, kommt es ebenfalls zu einem Deadlock.

In diesem Fall können Sie den Einfügevorgang direkt ausführen und dann die Ausnahme beim Duplikat des Primärschlüssels abfangen oder immer ROLLBACK ausführen, um die erworbene exklusive Sperre aufzuheben, wenn ein Fehler beim Duplikat des Primärschlüssels auftritt.

Obwohl Deadlocks durch das Design und die SQL-Optimierung sowie andere oben eingeführte Maßnahmen erheblich reduziert werden können, ist es schwierig, Deadlocks vollständig zu vermeiden. Daher ist es eine gute Programmiergewohnheit, Deadlock-Ausnahmen beim Programmieren immer abzufangen und zu behandeln.

Wenn ein Deadlock auftritt, können Sie den Befehl SHOW INNODB STATUS verwenden, um die Ursache des letzten Deadlocks zu ermitteln. Die zurückgegebenen Ergebnisse umfassen detaillierte Informationen zu Transaktionen im Zusammenhang mit Deadlocks, z. B. die SQL-Anweisung, die den Deadlock verursacht hat, die Sperren, die die Transaktion erworben hat, auf welche Sperren sie wartet und die Transaktionen, die zurückgesetzt wurden. Darauf aufbauend können die Ursachen für Stillstände analysiert und Verbesserungsmaßnahmen ergriffen werden.

Das Folgende ist eine Beispielausgabe von SHOW INNODB STATUS:

mysql> show innodb status G

InnoDB-Zusammenfassung

Dieses Kapitel konzentriert sich auf die Implementierungsfunktionen von MyISAM-Sperren auf Tabellenebene und InnoDB-Sperren auf Zeilenebene in MySQL diskutiert die beiden Sperrprobleme und Lösungen, auf die Speicher-Engines häufig stoßen.

Für MyISAM-Tabellensperren werden hauptsächlich die folgenden Punkte diskutiert:

(1) Gemeinsam genutzte Lesesperren (S) sind kompatibel, aber zwischen gemeinsam genutzten Lesesperren (S) und exklusiven Schreibsperren (X) und exklusiven Schreibsperren (X ) schließen sich gegenseitig aus, was bedeutet, dass Lesen und Schreiben seriell sind.

(2) Unter bestimmten Bedingungen ermöglicht MyISAM die gleichzeitige Ausführung von Abfragen und Einfügungen. Damit können wir das Sperrkonfliktproblem für Abfragen und Einfügungen in derselben Tabelle in Anwendungen lösen.

(3) Der Standard-Sperrplanungsmechanismus von MyISAM ist die Schreibpriorität, die nicht unbedingt für alle Anwendungen geeignet ist. Benutzer können den Lese-/Schreibsperrenkonflikt anpassen, indem sie den Parameter LOW_PRIORITY_UPDATES festlegen oder die Option LOW_PRIORITY in INSERT, UPDATE und DELETE angeben Aussagen.

(4) Aufgrund der großen Sperrgranularität von Tabellensperren und der seriellen Lese- und Schreibvorgänge kann es bei der MyISAM-Tabelle zu erheblichen Sperrwartezeiten kommen. Sie können die Verwendung von InnoDB-Tabellen in Betracht ziehen, um Sperrkonflikte zu reduzieren.

Für InnoDB-Tabellen werden in diesem Kapitel hauptsächlich die folgenden Inhalte behandelt.

Die Zeilensperre von InnoDB basiert auf dem Sperrindex. Wenn auf die Daten nicht über den Index zugegriffen wird, verwendet InnoDB eine Tabellensperre.

Einführung des InnoDB-Gap-Lock-Mechanismus (Next-Key) und der Gründe, warum InnoDB Gap-Locks verwendet.

Unter verschiedenen Isolationsstufen sind der Sperrmechanismus und die konsistente Lesestrategie von InnoDB unterschiedlich.

MySQL-Wiederherstellung und -Replikation haben auch einen großen Einfluss auf den InnoDB-Sperrmechanismus und die konsistente Lesestrategie.

Sperrkonflikte und sogar Deadlocks lassen sich nur schwer vollständig vermeiden.

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

Verwenden Sie so viel wie möglich eine niedrigere Isolationsstufe;

Entwerfen Sie den Index sorgfältig und versuchen Sie es Verwenden Sie den Index, um auf Daten zuzugreifen. Machen Sie das Sperren präziser und verringern Sie so die Wahrscheinlichkeit von Sperrkonflikten.

Wählen Sie eine angemessene Transaktionsgröße, und die Wahrscheinlichkeit von Sperrkonflikten bei kleinen Transaktionen ist geringer.

Wenn Sie den Datensatz sperren, ist es am besten, auf einmal eine ausreichende Sperrstufe 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, auf jede Tabelle in der gleichen Reihenfolge zuzugreifen. Versuchen Sie bei einer Tabelle, auf die Zeilen in der Tabelle in einer festen Reihenfolge zuzugreifen. Dadurch wird die Wahrscheinlichkeit eines Deadlocks erheblich verringert.

Versuchen Sie, Gleichheitsbedingungen für den Zugriff auf Daten zu verwenden, um die Auswirkungen von Lückensperren auf das gleichzeitige Einfügen zu vermeiden.

Bewerben Sie sich nicht für eine Sperrstufe, die über den tatsächlichen Bedarf hinausgeht, und zeigen Sie bei der Abfrage keine Sperre an.

Für einige bestimmte Transaktionen können Tabellensperren verwendet werden, um die Verarbeitungsgeschwindigkeit zu erhöhen oder die Möglichkeit eines Deadlocks zu verringern.