Lassen Sie uns über die verschiedenen Modi und Arten von Sperren in MYSQL sprechen

Dieser Artikel vermittelt Ihnen relevantes Wissen über die verschiedenen Arten und Modi von Sperren in MySQL. Ich hoffe, er wird Ihnen hilfreich sein.

Bei der täglichen Entwicklungsarbeit müssen wir uns fast jeden Tag mit der Datenbank befassen. Als SQL-JUNGE, der nur CRUD kennt, tun wir das fast nicht, außer dass wir täglich den Mybatis-Generator verwenden, um automatisch DAO-Layer-Code zu generieren Wir müssen uns um die Datenbank kümmern, aber eines Tages geriet die MYSQL-Datenbank in Panik und wir fragten uns: Ist das nicht nur eine normale Abfrage? gesperrt?

Um zu vermeiden, dass der Panikausdruck vom Vorgesetzten erfasst wird, müssen wir im Voraus wissen, welche Sperren in der Datenbank vorhanden sind.

In MySQL werden Sperren tatsächlich in zwei Kategorien unterteilt: Sperrtyp (lock_type) und Sperrmodus (lock_mode).

Der Sperrtyp beschreibt die Granularität der Sperre, das heißt, wo die Sperre speziell hinzugefügt wird, und der Sperrmodus beschreibt, welche Art von Sperre hinzugefügt wird, ob es sich um eine Lesesperre oder eine Schreibsperre handelt. Sperrmodi werden häufig in Verbindung mit Sperrtypen verwendet.

Unterteilt nach Sperrmodus

Lesesperre

Lesesperre, auch Shared Lock/S Lock/Share Locks genannt.

Eine Lesesperre ist eine Sperre, die von einer Transaktion (z. B. Transaktion A) erstellt wird, wenn eine Leseoperation ausgeführt wird (z. B. das Lesen einer Tabelle oder das Lesen einer bestimmten Zeile). Andere Transaktionen können die Daten gleichzeitig lesen (hinzugefügte Sperre), diese Daten jedoch kann nicht geändert werden (es sei denn, der Benutzer, der die Sperre hält, hat die Sperre freigegeben).

Nachdem Transaktion A den Daten eine Lesesperre hinzugefügt hat, können andere Transaktionen weiterhin Lesesperren (gemeinsam genutzt) hinzufügen, jedoch keine Schreibsperren.

Hinzufügen von Lesesperren für Datensätze

Das Sperren einer Zeile (d. h. eines Datensatzes) sperrt nicht den Datensatz, sondern fügt dem Index eine Sperre hinzu, die der Datensatzsperre entspricht . Wenn in der Where-Bedingung kein Index vorhanden ist, werden alle Datensätze gesperrt.

Die explizite Sperranweisung lautet:

Hinweis: Der hier erwähnte Lesevorgang bezieht sich auf den aktuellen Lesevorgang, und für den Snapshot-Lesevorgang ist keine Sperre erforderlich. Gewöhnliche Select-Lesevorgänge sind im Allgemeinen Snapshot-Lesevorgänge, mit Ausnahme expliziter Sperranweisungen wie „select...lock“ im Freigabemodus, die zu aktuellen Lesevorgängen werden. Unter der serialisierbaren Ebene der InnoDB-Engine werden normale Select-Lesevorgänge auch zu Snapshot-Lesevorgängen.

Darüber hinaus ist zu beachten, dass für die Analyse des Zeilensperren-Sperrprozesses die Analyse basierend auf der Transaktionsisolationsstufe, der Frage, ob ein Index verwendet werden soll (welcher Indextyp), und der Existenz des Datensatzes kombiniert werden muss und andere Faktoren, um zu bestimmen, wo die Sperre hinzugefügt wird.

Mehrere Situationen beim Hinzufügen von Lesesperren in der Innodb-Engine

Normale Abfragen fügen Datensätzen S-Sperren hinzu, wenn die Isolationsstufe serialisierbar ist. Dies hängt jedoch auch vom Szenario ab: Nicht-transaktionales Lesen (automatisches Festschreiben) erfordert keine Sperre unter der Isolationsstufe Serialisierbar.

Serialisierbare Isolationsstufe: Wenn die Abfragebedingung ein eindeutiger Index und eine eindeutige Abfrage mit gleichem Wert ist: Dies ist der Fall Fügen Sie in dieser Klausel eine S-Sperre zum Datensatz hinzu (wenn die Abfrage mehrere Datensätze scannt): Fügen Sie dem Datensatz selbst + der Lücke zwischen den Datensätzen (der Bereich der Lücke muss speziell analysiert werden) ein S hinzu lock;

select... im Share-Modus gibt Der Datensatz ist S-gesperrt, aber das Sperrverhalten ist je nach Isolationsstufe unterschiedlich:

RC Isolationsstufe: S-Lock wird dem Datensatz hinzugefügt. RR/Serialisierbare Isolationsstufe: Wenn die Abfragebedingung ein eindeutiger Index und eine eindeutige Gleichwertabfrage ist: S-Sperre wird zum Datensatz hinzugefügt (wenn die Abfrage mehrere Datensätze scannt): Datensatz selbst + Datensatzlücke (die Der Bereich der Lücke muss speziell analysiert werden), S-Sperre hinzufügen

Normalerweise ist der Einfügevorgang nicht gesperrt, aber wenn beim Einfügen oder Aktualisieren eines Datensatzes ein doppelter Schlüssel erkannt wird (oder ein doppelter Schlüssel zum Löschen markiert ist), Für normales Einfügen/Aktualisieren wird eine S-Sperre hinzugefügt, während für SQL-Anweisungen wie „Ersetzen in“ oder „Einfügen...bei Duplikat“ eine X-Sperre hinzugefügt wird.

Einfügen ... Auswählen Beim Einfügen von Daten wird den in der Auswahltabelle gescannten Daten eine S-Sperre hinzugefügt.

Fremdschlüsselprüfung: Wenn wir einen Datensatz in der übergeordneten Tabelle löschen, müssen wir prüfen, ob Referenzbeschränkungen bestehen Zu diesem Zeitpunkt werden die entsprechenden Datensätze in der Untertabelle gescannt und S-Sperren hinzugefügt.

Lesesperre für die Tabelle hinzufügen

Die Tabellensperre wird vom MySQL-Server implementiert. Unabhängig von der Speicher-Engine kann eine Tabellensperre verwendet werden. Im Allgemeinen wird beim Ausführen einer DDL-Anweisung wie ALTER TABLE die gesamte Tabelle gesperrt. Sie können eine Tabelle auch explizit sperren, wenn Sie eine SQL-Anweisung ausführen.

Die explizite Sperranweisung für die Tabelle lautet:

Bei Verwendung der MYISAM-Engine müssen wir normalerweise nicht manuell sperren, da die MYISAM-Engine unsere SQL-Anweisungen automatisch sperrt und für den gesamten Prozess kein Benutzer erforderlich ist Intervention:

• Abfrageanweisung (Auswahl): fügt automatisch Lesesperren zu den beteiligten Tabellen hinzu;

• Update-Anweisungen (Aktualisieren, Löschen, Einfügen): fügt automatisch Schreibsperren zu den beteiligten Tabellen hinzu.

Schreibsperre

Schreibsperre, exklusive Sperre/X-Sperre/exklusive Sperren. Der blockierende Charakter von Schreibsperren ist viel strenger als der von Lesesperren. Nachdem eine Transaktion den Daten eine Schreibsperre hinzugefügt hat, können andere Transaktionen die Daten weder lesen noch ändern.

Gleich dem Umfang der Lese- und Schreibsperre kann eine Schreibsperre sowohl zu Datensätzen als auch zu Tabellen hinzugefügt werden.

Fügen Sie eine Schreibsperre für den Datensatz hinzu.

Fügen Sie eine Schreibsperre für den Datensatz hinzu. Die Engine muss InnoDB verwenden.

Normalerweise werden gewöhnliche Select-Anweisungen nicht gesperrt (außer wenn die Isolationsstufe Serialisierbar ist). Wenn Sie während der Abfrage eine exklusive Sperre hinzufügen möchten, müssen Sie die folgende Anweisung verwenden:

Schreibsperre während der Abfrage hinzufügen:

und Lesesperre hinzufügen Die Sperre ist dieselbe, und die Schreibsperre wird ebenfalls zum Index hinzugefügt.

Schreibsperre beim Aktualisieren hinzufügen:

Schreibsperre für die Tabelle hinzufügen

Die Anweisung zum expliziten Hinzufügen einer Schreibsperre für die Tabelle lautet:

Wenn die Engine myisam auswählt, fügen Sie / ein update Die /delete-Anweisung fügt der Tabelle automatisch eine exklusive Sperre hinzu.

Kompatibilität mit Lese- und Schreibsperren:

• Die Lesesperre wird gemeinsam genutzt, sie blockiert keine anderen Lesesperren, aber sie blockiert andere Schreibsperren;

• Die Schreibsperre ist exklusiv, sie blockiert andere Lesesperren Und Schreibsperre;

• Zusammenfassung: Lesen und Lesen schließen sich nicht gegenseitig aus, Lesen und Schreiben schließen sich gegenseitig aus, Schreiben und Schreiben schließen sich gegenseitig aus. Absichtssperre: Absichtssperre ist eine Tabelle, die dies tut Kein Konflikt mit Sperren auf Zeilenebene. Die Stufensperre gibt den Typ der Sperre an (S-Sperre oder InnoDB unterstützt mehrere Granularitätssperren und ermöglicht so die Koexistenz von Sperren auf Zeilenebene und Sperren auf Tabellenebene.

Absichtssperren sind unterteilt in:

Absichtliche gemeinsame Sperren (IS-Sperren): IS-Sperren zeigen an, dass die aktuelle Transaktion beabsichtigt, gemeinsame Sperren für Zeilen in der Tabelle festzulegen. Wenn die folgende Anweisung ausgeführt wird, wird die IS-Sperre erworben Erstens, weil diese Operation darin besteht, S-Sperre zu erwerben: S-Sperre erwerben: Wählen Sie ... Sperre im FreigabemodusIntention exklusive Sperre (IX-Sperre): IX-Sperre zeigt an, dass die aktuelle Transaktion beabsichtigt, eine exklusive Sperre für die Zeile in festzulegen die Tabelle

Die folgende Anweisung wird zuerst erfasst, wenn eine IX-Sperre ausgeführt wird, da dieser Vorgang eine X-Sperre erhält: muss zuerst die entsprechende IS-Sperre bzw. IX-Sperre erwerben.

Was bewirkt die Absichtssperre?

Wenn eine andere Transaktion eine gemeinsame oder exklusive Sperre auf Tabellenebene versucht, wird sie durch die von der ersten Transaktion gesteuerte Absichtssperre auf Tabellenebene blockiert. Die zweite Transaktion muss vor dem Sperren der Tabelle nicht nach einzelnen Seiten- oder Zeilensperren suchen, sondern nur nach Absichtssperren für die Tabelle.

Beispiel: Tabelle test_user:

Transaktion A hat eine exklusive Sperre für eine Zeile erworben, diese jedoch nicht festgeschrieben;

Transaktion B möchte eine gemeinsame Tabellensperre für die Tabelle test_user erwerben;

Weil geteilt Sperren unterscheiden sich von exklusiven Sperren. Die Sperren schließen sich gegenseitig aus. Wenn also Transaktion B versucht, eine gemeinsame Sperre zur Tabelle test_user hinzuzufügen, muss sie Folgendes sicherstellen:

Keine andere Transaktion hält derzeit eine exklusive Sperre für die Benutzertabelle (Tabelle exklusiv). sperren).

Keine andere Transaktion hält derzeit eine exklusive Sperre (zeilenexklusive Sperre) für eine Zeile in der Benutzertabelle.

Um zu überprüfen, ob die zweite Bedingung erfüllt ist, muss Transaktion B prüfen, ob für jede Zeile in der Tabelle eine exklusive Sperre vorhanden ist, und gleichzeitig sicherstellen, dass keine exklusive Sperre für die Tabelle test_user vorhanden ist. Offensichtlich ist dies ein sehr ineffizienter Ansatz, aber mit der Absichtssperre ist die Situation anders:

Denn zu diesem Zeitpunkt hat Transaktion A zwei Sperren erworben: die absichtliche exklusive Sperre für die Benutzertabelle und die exklusive Datensperre mit der ID 28 für die Zeile.

• Transaktion B möchte die gemeinsame Sperre der Tabelle test_user erhalten:

Transaktion B muss nur erkennen, ob Transaktion A die exklusive Absichtssperre der Tabelle test_user hält, und kann dann wissen, dass Transaktion A die exklusive Sperre von halten muss Wenn einige Datenzeilen in der Tabelle gesperrt sind, wird die Sperranforderung von Transaktion B für die Tabelle test_users ausgeschlossen (blockiert), sodass nicht ermittelt werden muss, ob für jede Datenzeile in der Tabelle eine exklusive Sperre vorhanden ist.

• Transaktion C möchte auch eine exklusive Sperre für eine bestimmte Zeile in der Benutzertabelle erhalten:

Transaktion C erkennt, dass Transaktion A eine exklusive Absichtssperre für die test_user-Tabelle hält;

Absichtssperren schließen sich nicht gegenseitig aus , sodass Transaktionen C die beabsichtigte exklusive Sperre der Tabelle test_user erhalten haben.

Da es keine exklusive Sperre für die Datenzeile mit der ID 31 gibt, hat Transaktion C schließlich erfolgreich die exklusive Sperre für die Datenzeile erhalten.

• Absichtssperren schließen sich nicht gegenseitig aus, es gibt jedoch bestimmte Kompatibilitäten und gegenseitige Ausschlüsse zwischen Absichtssperren und anderen Tabellensperren, wie folgt:

  Kompatibilität und gegenseitige Ausschließlichkeit zwischen Absichtssperren:

  

  Gegenseitige Ausschließlichkeit zwischen Absichtssperren und gewöhnlichen exklusiven/gemeinsamen Sperren:

  

  Auto-Inkrement-Sperren

  Wenn wir die Tabellenstruktur entwerfen, normalerweise die Der Primärschlüssel ist auf automatische Inkrementierung eingestellt (denken Sie darüber nach, warum?).

  In der InnoDB-Speicher-Engine wird für jedes sich selbst erhöhende Feld ein selbsterhöhender Zähler festgelegt. Wir können die folgende Anweisung ausführen, um den aktuellen Wert dieses Zählers zu erhalten:

  

  Wenn wir eine Einfügeoperation ausführen, führt die Operation eine +1-Operation basierend auf dem aktuellen Wert dieses sich selbst erhöhenden Zählers aus und weist ihn zu Bei der selbsterhöhenden Spalte wird dieser Vorgang als Auto-Inkrement-Sperre bezeichnet. Bei dieser Art von Sperre wird tatsächlich ein spezieller Tabellensperrmechanismus verwendet. Wenn der Einfügevorgang in einer Transaktion erfolgt, wird die Sperre sofort aufgehoben Der Einfügevorgang wird abgeschlossen, anstatt auf das Festschreiben der Transaktion zu warten.

  Je nach Art der Sperre

  Globale Sperre

  Die sogenannte globale Sperre sperrt tatsächlich die gesamte Datenbankinstanz.

  Es gibt einen Unterschied zwischen einer Datenbankinstanz und einer Datenbank:

  Eine Datenbank ist ein Warehouse, das Daten speichert. Insbesondere in MySQL ist eine Datenbank eigentlich eine Sammlung von Datendateien (was wir normalerweise als Datenbank bezeichnen, z. B Erstellen einer Datenbankanweisung) Datenbank erstellen...).

  Datenbankinstanz bezieht sich auf die Anwendung, die auf die Datenbank zugreift. In MySQL ist es der mysqld-Prozess.

  Um es einfach zu verstehen: Eine Datenbankinstanz enthält verschiedene von Ihnen erstellte Datenbanken.

  Wenn Sie der Datenbankinstanz eine globale Sperre hinzufügen, wird die gesamte Bibliothek in einen schreibgeschützten Zustand versetzt (dies ist sehr gefährlich).

  Im Allgemeinen besteht das typische Verwendungsszenario globaler Sperren in der vollständigen Datenbanksicherung, d. h. in der Auswahl aller Tabellen in der Datenbank. Aber seien Sie vorsichtig, wenn Sie die gesamte Bibliothek im schreibgeschützten Zustand belassen, führt dies zu ernsthaften Problemen:

  • Fügen Sie der Hauptbibliothek eine globale Sperre hinzu. Während des Sperrzeitraums können keine Aktualisierungsvorgänge ausgeführt werden Unternehmen sind grundsätzlich nicht verfügbar.

  • Fügen Sie eine globale Sperre für die Slave-Bibliothek hinzu. Während des Sperrzeitraums kann keine Master-Slave-Synchronisierung durchgeführt werden, was zu einer Verzögerung der Master-Slave-Synchronisierung führt.

  Die Sperranweisung der globalen Sperre lautet:

  

  Die Methode zum Entsperren der globalen Sperre lautet:

  • Trennen Sie einfach die Sitzung, die die globale Sperre ausführt.

  • Führen Sie die entsperrende SQL-Anweisung aus : Tabellen entsperren;

  Wenn Sie eine Datenbanksicherung benötigen, können Sie das offizielle logische Sicherungstool mysqldump verwenden.

  Da wir bereits das Dump-Tool haben, warum brauchen wir FTWRL? Konsistentes Lesen ist gut, aber nur, wenn die Engine diese Isolationsstufe unterstützt. Engines wie MyISAM unterstützen beispielsweise keine Transaktionen. Zu diesem Zeitpunkt müssen wir den Befehl FTWRL verwenden.

  Wenn vor FTWRL ein Lese- oder Schreibvorgang erfolgt, wartet FTWRL vor der Ausführung, bis der Lese- und Schreibvorgang abgeschlossen ist.

  Wenn FTWRL ausgeführt wird, müssen fehlerhafte Seitendaten auf die Festplatte geleert werden. Da die Datenkonsistenz gewahrt bleiben muss, wird FTWRL ausgeführt, wenn alle Transaktionen übermittelt werden.

  Die Implementierung der globalen Sperre basiert immer noch auf der Metadatensperre.

  Metadatensperre

  Metadatensperre, auch MDL-Sperre genannt, wird zum Schutz von Metadateninformationen verwendet, die nicht aktiv gesteuert werden können. In MySQL Version 5.5 wurden MDL-Sperren eingeführt, hauptsächlich um die Metadatenkonsistenz bei gleichzeitigen Vorgängen von DDL und DML in einer gleichzeitigen Umgebung aufrechtzuerhalten. Zum Beispiel die folgende Situation:

  Isolationsstufe: RR

  

  Wenn kein Metadatensperrschutz vorhanden ist, kann Transaktion 2 DDL-Vorgänge direkt ausführen, was zu einem Fehler bei Transaktion 1 führt. Die MDL-Sperre wurde in der MYSQL5.5-Version hinzugefügt, um diese Situation zu verhindern. Da Transaktion 1 die Abfrage öffnet, erhält sie die MDL-Lesesperre. Wenn Transaktion 2 DDL ausführen möchte, muss sie die MDL-Schreibsperre erhalten muss auf die Freigabe der Transaktion 1 warten. Sie kann nur ausgeführt werden, wenn die Lesesperre aufgehoben ist.

  • Beim Hinzufügen, Löschen, Ändern und Abfragen von Datensätzen in der Tabelle (DML-Operationen) werden automatisch MDL-Lesesperren hinzugefügt.

  • Beim Ändern der Struktur der Tabelle (DDL-Operationen) werden automatisch MDL-Schreibsperren hinzugefügt hinzugefügt.

  Die Granularität von MDL-Sperren

  MDL-Sperren werden auf der MySQL-Serverebene implementiert, nicht im Speicher-Engine-Plug-in. Je nach Sperrumfang lassen sich MDL-Schlösser in folgende Kategorien einteilen:

  

  MDL-Sperrmodus

  

  Sperre auf Seitenebene

  Eine Sperre in MySQL mit einer Sperrgranularität zwischen Sperren auf Zeilenebene und Sperren auf Tabellenebene. Sperren auf Tabellenebene sind schnell, weisen jedoch viele Konflikte auf. Sperren auf Zeilenebene weisen wenige Konflikte auf, sind jedoch langsam. Also haben wir eine kompromittierte Seitenebene genommen und jeweils eine Gruppe benachbarter Datensätze gesperrt. Verschiedene Speicher-Engines unterstützen unterschiedliche Sperrmechanismen. Gemäß den verschiedenen Speicher-Engines können die Eigenschaften von Sperren in MySQL grob wie folgt zusammengefasst werden:

  

  Sperren auf Seitenebene ist eine einzigartige Sperrebene in MySQL. Die Parallelität ist durchschnittlich Merkmale der Sperrung auf Seitenebene sind: Die Sperrgranularität liegt zwischen der Sperrung auf Zeilenebene und der Sperrung auf Tabellenebene, sodass der zum Erhalten der Sperre erforderliche Ressourcenaufwand und die gleichzeitige Verarbeitungsfähigkeit, die sie bereitstellen kann, ebenfalls zwischen den beiden oben genannten liegen. Darüber hinaus führen Sperren auf Seitenebene und Sperren auf Zeilenebene zu einem Deadlock.

  Vergleich der Sperrgranularität: Sperren auf Tabellenebene > Sperren auf Seitenebene

  Sperren auf Tabellenebene

  Tabellensperren wurden oben eingeführt, verglichen mit der feinkörnigen Hinzufügung von Zeilensperren Sperren, Tabellensperren dienen zum Sperren der gesamten Tabelle. Da die gesamte Tabelle gesperrt ist, ist dies nicht so kompliziert wie das Sperren von Zeilen, sodass das Sperren schneller ist als das Sperren von Zeilen und es keinen Deadlock gibt (da die Transaktion gleichzeitig die gewünschte Tabellensperre erhält), aber Tabellensperren haben auch welche Probleme: Der Sperrbereich ist zu groß, und wenn die Parallelität relativ hoch ist, steigt die Wahrscheinlichkeit von Sperrkonflikten, was die Parallelitätsleistung erheblich verringert.

  Methode zum Sperren der Tabellensperre

  Wenn die Engine MYISAM auswählt

  Die MYISAM-Engine unterstützt nur Tabellensperren und keine Zeilensperren.

  Die Anweisung zum manuellen Hinzufügen von Sperren auf Tabellenebene lautet wie folgt:

  

  Bei Verwendung der MYISAM-Engine müssen wir normalerweise keine manuelle Sperre vornehmen, da die MYISAM-Engine unsere SQL-Anweisungen und den gesamten Prozess automatisch sperrt erfordert keinen Benutzereingriff:

  • Abfrageanweisung (Auswahl): fügt automatisch Lesesperren zu den beteiligten Tabellen hinzu;

  • Update-Anweisungen (Aktualisieren, Löschen, Einfügen): fügt automatisch Schreibsperren zu den beteiligten Tabellen hinzu

  Wenn InnoDB als Engine ausgewählt ist, unterstützt die InnoDB-Engine sowohl Sperren auf Zeilenebene als auch Sperren auf Tabellenebene, und die Standardeinstellung sind Sperren auf Zeilenebene. Sperren Sie die Tabelle manuell in der InnoDB-Engine und verwenden Sie auch die Lese-/Schreibanweisung für die Sperrtabelle {tableName}, um Lese-/Schreibsperren hinzuzufügen.

  Darüber hinaus unterstützt innodb auch eine Sperre auf Tabellenebene: Absichtssperre (bereits oben eingeführt).

  Im Allgemeinen umfassen die Sperren auf Tabellenebene der InnoDB-Engine fünf Sperrmodi:

  LOCK_IS: Zeigt lesebezogene Sperren an

  • LOCK_IX: Zeigt schreibbezogene Sperren an

  • LOCK_S: Tabellenlesesperren 🔜 handelt es sich um Sperren auf Zeilenebene (Sperren auf Tabellenebene). Aufgrund von Leistungsproblemen wird ihre Verwendung im Allgemeinen nicht empfohlen. Im Vergleich zu Sperren auf Tabellenebene haben Sperren auf Zeilenebene offensichtliche Leistungsvorteile:

  • Wenige Konflikte: Beim Zugriff auf verschiedene Datensätze in mehreren Threads gibt es nur wenige Sperrkonflikte.

  • Die Granularität der Sperre ist gering: Es kann für eine lange Zeit gesperrt werden. Eine einzelne Zeile hat keine Auswirkungen auf andere Zeilen, daher ist der Grad der Parallelität am höchsten. Wenn Sie jedoch nicht aufpassen, kann es sehr leicht zu einem Deadlock kommen ( Tabellensperren sind nicht vorhanden. Daher müssen Sie bei der Verwendung von Zeilensperren auf die Sperrreihenfolge und den Sperrbereich achten.

  Die Zeilensperren von InnoDB werden durch das Sperren von Indexelementen implementiert. Dies bedeutet, dass Zeilensperren nur beim Abfragen von Datensätzen über den Index verwendet werden. Wenn Daten ohne den Index abgefragt werden, werden Tabellensperren verwendet und die Leistung wird erheblich verringert.

  • Sie müssen bedenken: Zeilensperren werden auch Datensatzsperren genannt und Datensatzsperren werden dem Index hinzugefügt.

  wobei die Bedingung den Primärschlüsselindex angibt: Dann wird die Sperre zum Primärschlüsselindex hinzugefügt. Die Bedingung gibt den Sekundärindex an: Die Datensatzsperre wird nicht nur zu diesem Sekundärindex hinzugefügt, sondern auch zu diesem Auf dem Clustered-Index, der dem Sekundärindex entspricht;

  wobei der Index nicht verwendet werden kann: MySQL fügt Datensatzsperren zu allen Datenzeilen in der gesamten Tabelle hinzu und die Speicher-Engine-Schicht gibt alle Datensätze zum Filtern zurück MySQL-Server.

  • Datensatzsperre: LOCK_REC_NOT_GAP (nur Datensätze sperren)

  Datensatzsperre ist die einfachste Zeilensperre. Wenn beispielsweise auf der RR-Isolationsebene die Anweisung „select * from t_user where id = 1 for update“ ausgeführt wird, ist der Datensatz id = 1 (wobei id der Primärschlüssel ist) tatsächlich gesperrt (die Sperre wird dem Clustered-Index hinzugefügt). .

  Datensatzsperren werden immer zum Index hinzugefügt. Auch wenn eine Tabelle keinen Index hat, erstellt die Datenbank implizit einen Index. Wenn die in der WHERE-Bedingung angegebene Spalte ein Sekundärindex ist, wird die Datensatzsperre nicht nur zum Sekundärindex, sondern auch zum Clustered-Index hinzugefügt, der dem Sekundärindex entspricht.

  Beachten Sie, dass die SQL-Anweisung den Hauptindex verwendet, um einen vollständigen Tabellenscan zu implementieren. Zu diesem Zeitpunkt fügt MySQL allen Datenzeilen in der gesamten Tabelle Datensatzsperren hinzu.

  Wenn eine WHERE-Bedingung nicht schnell durch den Index gefiltert werden kann, sperrt die Speicher-Engine-Schicht alle Datensätze und gibt sie zurück, und dann filtert die MySQL-Server-Schicht sie. Wenn kein Index vorhanden ist, werden nicht nur viele Sperrressourcen verbraucht und der Overhead der Datenbank erhöht, sondern auch die Parallelitätsleistung der Datenbank wird erheblich verringert. Daher müssen Sie daran denken, den Index während des Aktualisierungsvorgangs zu verwenden (weil). Durch den Aktualisierungsvorgang wird eine X-Sperre hinzugefügt.

  Verschiedene Arten von Sperren auf Zeilenebene:

  Lückensperre: LOCK_GAP (sperrt nur Lücken)

  Die Lückensperre ist eine Art Intervallsperre. Die Sperre wird dem nicht vorhandenen freien Speicherplatz oder zwischen zwei Indexdatensätzen oder dem ersten Indexdatensatz oder dem Speicherplatz nach dem letzten Index hinzugefügt. Sie wird verwendet, um anzuzeigen, dass nur ein Bereich gesperrt ist (normalerweise isoliert bei der Ausführung). Bereichsabfragen) Ebene in RR oder serialisierbaren Intervallen).

  GAP-Sperren werden im Allgemeinen unter der RR-Isolationsstufe verwendet. Der Hauptzweck der Verwendung der GAP-Sperre besteht darin, Phantomlesevorgänge zu verhindern. In dem durch die GAP-Sperre gesperrten Intervall dürfen keine Daten eingefügt oder aktualisiert werden.

  Die Bedingungen zum Generieren von Lückensperren: Die Isolationsstufe von innodb ist Wiederholbares Lesen oder Serialisierbar.

  Erläuterung des Umfangs der Lückensperre:

  Isolationsstufe: RR

  

  Nehmen Sie die Student-Tabelle als Beispieldaten, id als Primärschlüssel, stu_code als Studentennummer und fügen Sie einen normalen Index hinzu.

  Lückensperrbereichsdefinition:

  Suchen Sie nach links nach dem nächstgelegenen Wert A als linkes Intervall gemäß den Suchbedingungen und suchen Sie nach rechts nach dem nächstgelegenen Wert B als rechtes Intervall. Die Lückensperre ist (A, B)

  Nach links kann der nächstgelegene Wert A nicht gefunden werden. Suchen Sie nach dem nächsten Wert B, da das rechte Intervall (unendlich, B) ist

  Finden Sie den nächsten Wert A links als linkes Intervall. Der nächste Wert B kann nicht rechts gefunden werden, was unendlich ist. Als rechtes Intervall ist die Lückensperre (A, unendlich). B) Beispiel:
  Transaktion 1:

  select * from student where stu_code = 4 for update

  Transaktion 2:

  insert into student vaues(2, 2, 'A')；
  insert into student values(4, 5, 'B');

  Gemäß der SQL-Anweisungsanalyse von Transaktion 1 ist der Bereich der Lückensperre: stu_code = 4 Datensätze sind vorhanden, daher ist das linke Intervall das aktuellste Der Indexwert ist stu_code = 3, das rechte Intervall ist der aktuellste Indexwert ist stu_code = 7, also die Lücke Der Bereich ist: (3, 7), also sind die beiden Einfügeanweisungen von Transaktion 2, eine außerhalb des Bereichs und die Andere liegen innerhalb des Bereichs und können eingefügt werden, während diejenigen innerhalb des Bereichs blockiert sind, sodass (2, 2, 'A') erfolgreich eingefügt werden kann blockiert.
  Intervall (unendlich klein, B) Beispiel:
  Transaktion 1:

  select * from student where stu_code = 1 for update

  Transaktion 2:

  insert into student vaues(2, 0, 'c')；
  insert into student vaues(2, 2, 'r')；
  insert into student vaues(5, 2, 'o')；

  Gemäß der SQL-Anweisungsanalyse von Transaktion 1 ist der Bereich der Lückensperre: stu_code = 1 vorhanden, und da Links ist kein aktueller Datensatz vorhanden, und der nächste Indexwert rechts ist stu_code = 3, sodass der Lückensperrbereich (unendlich, 3) ist. Daher ist die Ausführung der ersten und zweiten SQL-Einfügungsanweisungen von Transaktion 2 blockiert und liegt im Bereich der Lückensperre. Die dritte SQL-Einfügeanweisung kann erfolgreich ausgeführt werden und liegt nicht im Lückensperrbereich.
  Intervall (A, unendlich) Beispiel:
  Transaktion 1:

  select * from student where stu_code = 7 for update

  Transaktion 2:

  insert into student vaues(2, 2, 'm')；
  insert into student vaues(20, 22, 'j')；

  Gemäß der SQL-Anweisungsanalyse von Transaktion 1 ist der Bereich der Lückensperre: stu_code = 7 existiert, der nächste Index Der Wert auf der linken Seite ist stu_code = 4, und auf der rechten Seite gibt es keinen Indexwert, daher ist der Bereich der Lückensperre: (4, unendlich). Die erste eingefügte Anweisung kann erfolgreich ausgeführt werden und liegt nicht innerhalb des Lückenbereichs. Die Ausführung der zweiten Einfügeanweisung wird blockiert, was innerhalb des Lückensperrbereichs liegt.
  Wie kann ich die Abfrageanweisung sperren, wenn sie nicht in der Datenbank aufgezeichnet ist?
  Die obige Abfrage wird aufgezeichnet, wenn die Abfrageanweisung nicht in der Datenbank aufgezeichnet ist. Fahren wir fort:
  Transaktion 1:

  update student set stu_name = '000' where stu_code = 10

  Transaktion 2:

  insert into student vaues(2, 2, 'm')；
  insert into student vaues(20, 22, 'j')；

  Gemäß der obigen Ausführungsanweisung kann der Datensatz links (10, 7, 'Xiao Ming') nicht gefunden werden Das heißt, der Bereich der Lückensperre ist: (7, unendlich). Die erste Einfügeanweisung liegt nicht innerhalb des Bereichs und kann erfolgreich ausgeführt werden. Die zweite Einfügeanweisung ist innerhalb des Bereichs blockiert und kann nicht ausgeführt werden. Wenn die Where-Bedingung von Transaktion 1 größer als 10 ist, wird der nächste Datensatzwert auch links als linkes Intervall gefunden, sodass der Bereich der Lückensperre ebenfalls ist: (7, unendlich)
  Zusammenfassung: Die Bedingungen für Lücke Sperrgenerierung
  RR/Serialisierbare Isolationsstufe Weiter: Wenn Auswählen... Wo...Für Aktualisierung:
  Verwenden Sie nur eindeutige Indexabfragen und sperren Sie nur einen Datensatz. InnoDB verwendet Zeilensperren.
  Wenn nur eine eindeutige Indexabfrage verwendet wird, die Suchbedingung jedoch eine Bereichssuche ist oder das Suchergebnis eine eindeutige Suche ist, aber nicht existiert (versucht, nicht vorhandene Daten zu sperren), tritt die Next-Key-Sperre auf.
  Wenn Sie den normalen Indexabruf verwenden, wird unabhängig von der Art der Abfrage eine Lückensperre generiert, solange sie gesperrt ist.

  Bei gleichzeitiger Verwendung eines eindeutigen Index und eines gewöhnlichen Index kommt es auch zu Lückensperren, da die Datenzeilen zuerst nach dem gewöhnlichen Index und dann nach dem eindeutigen Index sortiert werden.

  Next-Key Lock: LOCK_ORDINARY, auch bekannt als Next-Key Lock

  Next-Key Lock ist eine Kombination aus Record Lock + Gap Lock. Wie bei Lückensperren gibt es unter der RC-Isolationsstufe keine Next-Key-Sperre (es sei denn, sie wird durch Ändern der Konfiguration zwangsweise aktiviert), sondern nur die RR/Serialisierbare Isolationsstufe.

  MySQL InnoDB arbeitet unter der wiederholbaren Leseisolationsstufe (RR) und sperrt Datenzeilen mithilfe der Next-Key-Sperre, wodurch das Auftreten von Phantomlesevorgängen wirksam verhindert werden kann. Next-Key Lock ist eine Kombination aus Zeilensperre und Lückensperre. Wenn InnoDB den Indexdatensatz scannt, fügt es zunächst eine Zeilensperre (Record Lock) zum Indexdatensatz und dann eine Lückensperre (Gap Lock) zu den Lücken hinzu auf beiden Seiten des Indexdatensatzes. Nach dem Hinzufügen der Lückensperre können andere Transaktionen keine Datensätze in dieser Lücke ändern oder einfügen.

  Wenn der abgefragte Index eindeutige Attribute enthält (eindeutiger Index, Primärschlüsselindex), optimiert die Innodb-Speicher-Engine die Next-Key-Sperre und reduziert sie auf eine Datensatzsperre, dh nur der Index selbst ist gesperrt, nicht der Bereich .

  Absichtssperre einfügen: LOCK_INSERT_INTENSION

  Absichtssperre einfügen, die beim Einfügen von Datensätzen verwendet wird. Es handelt sich um eine spezielle Lückensperre. Diese Sperre stellt die Absicht des Einfügens dar. Diese Sperre besteht nur, wenn die Einfügeanweisung ausgeführt wird.

  Angenommen, es gibt Indexdatensätze mit den Werten id = 1 bzw. id = 5 (keine Datensätze zwischen 1 und 5). Separate Transaktionen versuchen, id = 2 bzw. id = 3 einzufügen, bevor sie die exklusive Sperre für das eingefügte Objekt erhalten In der Zeile verwendet jede Transaktion eine Einfügungsabsichtssperre, um den Raum zwischen 1 und 5 zu sperren, blockiert sich jedoch nicht gegenseitig. Weil es keinen Konflikt zwischen eingefügten Absichtssperren gibt.

  Das Einfügen von Absichtssperren führt zu Konflikten mit Lückensperren oder Next-Key-Sperren: Die Funktion von Lückensperren besteht darin, das Intervall zu sperren, um zu verhindern, dass andere Transaktionen Daten einfügen und Phantom-Lesevorgänge verursachen.

  Unter der Annahme, dass Transaktion A im obigen Szenario die Lückensperre mit der ID im Intervall (1, 5) im Voraus erworben hat, versucht Transaktion B, wenn sie versucht, die ID = 2 einzufügen, zunächst, die Einfügungsabsichtssperre zu erhalten , aber aufgrund der Einfügeabsichtssperre Es kommt zu Konflikten mit der Lückensperre, was dazu führt, dass das Einfügen fehlschlägt und so das Auftreten von Phantomlesevorgängen vermieden wird.

  Fazit

  Der Sperrmechanismus von MYSQL ist sehr komplex. Bei der eigentlichen Entwicklungsarbeit müssen Sie beim Festlegen der Isolationsstufe sehr vorsichtig sein. Beispielsweise verfügt die RR-Ebene über eine Lückensperre mehr als die RC-Ebene. Dies kann zu schwerwiegenden Leistungsproblemen führen. In diesem Artikel wird die Klassifizierung von MYSQL-Sperren aus Sicht des Sperrmodus und des Sperrbereichs kurz vorgestellt. Ich hoffe, dass wir im Prozess der Datenbankentwicklung sorgfältig analysieren und untersuchen können, ob unsere SQL-Anweisungen sinnvoll sind (achten Sie insbesondere darauf, ob Deadlocks usw. auftreten). Probleme werden auftreten) )!

  Empfohlenes Lernen: MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonLassen Sie uns über die verschiedenen Modi und Arten von Sperren in MYSQL sprechen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!