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.
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;
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
Nach dem Login kopieren
insert into student vaues(2, 2, 'A')；
insert into student values(4, 5, 'B');
Nach dem Login kopieren
select * from student where stu_code = 1 for update
Nach dem Login kopieren
insert into student vaues(2, 0, 'c')；
insert into student vaues(2, 2, 'r')；
insert into student vaues(5, 2, 'o')；
Nach dem Login kopieren
select * from student where stu_code = 7 for update
Nach dem Login kopieren
insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；
Nach dem Login kopieren
Nach dem Login kopieren
update student set stu_name = '000' where stu_code = 10
Nach dem Login kopieren
insert into student vaues(2, 2, 'm')；
insert into student vaues(20, 22, 'j')；
Nach dem Login kopieren
Nach dem Login kopieren
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
