Wie implementiert MySQL ACID-Transaktionen?

Vorwort

Kürzlich wurde ich in einem Interview gefragt, wie die InnoDB-Engine von MySQL Transaktionen implementiert oder wie sie die ACID-Funktionen implementiert. Ich konnte damals keine gute Antwort geben, also habe ich es selbst zusammengefasst und aufgeschrieben.

Die vier Merkmale von ACID-Transaktionen

Die vier Hauptmerkmale von Transaktionen sind ACID, A-Atomizität, C-Konsistenz, I-Isolation und D-Haltbarkeit. Konsistenz ist das ultimative Ziel und Atomarität, Isolation und Persistenz sind Maßnahmen, die ergriffen werden, um Konsistenz sicherzustellen. Die Reihenfolge, die ich geschrieben habe, basiert also nicht auf ACID. Ich habe Konsistenz ans Ende gesetzt und die Reihenfolge wird zu ADIC.

Atomarität (A)

Atomarität bedeutet, dass eine Transaktion eine unteilbare Arbeitseinheit ist. Entweder wird sie vollständig erfolgreich ausgeführt oder sie schlägt fehl, ohne dass ein Zwischenzustand auftritt oder nur ein Teil davon ausgeführt wird.

Die InnoDB-Engine von MySQL wird durch ein Undo-Log (Rollback-Log) implementiert, das sicherstellen kann, dass alle erfolgreich ausgeführten SQL-Anweisungen rückgängig gemacht werden, wenn ein Rollback einer Transaktion durchgeführt wird.

Das Undo-Protokoll ist ein logisches Protokoll, das Informationen zur SQL-Ausführung aufzeichnet. Wenn eine Transaktion die Datenbank ändert, generiert InnoDB ein entsprechendes Undo-Protokoll. Wenn die Ausführung der Transaktion fehlschlägt oder ein Rollback aufgerufen wird, wodurch die Transaktion zurückgesetzt wird, setzt die InnoDB-Engine die Daten basierend auf den Datensätzen im Undo-Protokoll auf ihren vorherigen Zustand zurück.
Wenn beispielsweise eine Einfügeanweisung ausgeführt wird, wird ein Undo-Protokoll der zugehörigen Löschanweisung generiert. Umgekehrt wird durch die Ausführung einer Löschanweisung auch ein Undo-Protokoll für die zugehörige Einfügeanweisung generiert. Dasselbe gilt beim Ausführen einer Aktualisierungsanweisung, allerdings kann die Aktualisierungsanweisung beim Ausführen des Undo-Log-Rollbacks MVCC beinhalten. Dies dient vor allem dazu, sicherzustellen, welche Version der Daten bei der Auswahl beim Ausführen des Undo-Logs sichtbar ist.

Beharrlichkeit (D)

Persistenz bedeutet, dass, sobald eine Transaktion festgeschrieben ist, der Vorgang in der Datenbank dauerhaft ist und nachfolgende andere Vorgänge und ungewöhnliche Fehler keine Auswirkungen darauf haben sollten.
Wir alle wissen, dass MySQL-Daten letztendlich auf der Festplatte gespeichert werden, sodass die Größe der Festplatte die Größe der Datenkapazität bestimmt. Wenn jedoch alle MySQL-Vorgänge durch Lesen und Schreiben auf der Festplatte ausgeführt werden, reicht allein der Festplatten-E/A aus, um die Effizienz erheblich zu reduzieren.

Daher stellt InnoDB einen Pufferpool für MySQL bereit, der die Zuordnung einiger Datenseiten auf der Festplatte enthält.
Beim Lesen von Daten aus der Datenbank werden die Daten zuerst aus dem Pufferpool gelesen. Wenn sich die Daten nicht im Pufferpool befinden, werden sie von der Festplatte gelesen und im Pufferpool abgelegt.
Wenn Daten in die Datenbank geschrieben werden, werden sie zuerst in den Pufferpool geschrieben. Die aktualisierten Daten im Pufferpool werden regelmäßig auf die Festplatte übertragen (dieser Vorgang wird als Leeren bezeichnet).

Obwohl der Pufferpool die Effizienz des Lesens und Schreibens von MySQL verbessert, bringt er auch neue Probleme mit sich. Das heißt, wenn MySQL plötzlich abstürzt, während die Daten gerade im Pufferpool aktualisiert und noch nicht auf der Festplatte aktualisiert wurden, führt dies zu Datenverlust und die Dauerhaftigkeit der Transaktion kann nicht garantiert werden.
Um dieses Cache-Konsistenzproblem zu lösen, wurde ein Redo-Log angezeigt. Beim Ändern von Daten im Pufferpool wird der Vorgang im Redo-Protokoll aufgezeichnet. Wenn die Transaktion festgeschrieben wird, wird das Redo-Protokoll über die fsync-Schnittstelle auf die Festplatte geschrieben.

Da das Redo-Protokoll beim Festschreiben der Transaktion mit der Festplatte synchronisiert wird, kann bei einem MySQL-Absturz das Redo-Protokoll von der Festplatte gelesen werden, um die Daten wiederherzustellen. Dadurch wird die Persistenz der Transaktion sichergestellt.

Das Redo-Protokoll verwendet eine Vorschreibmethode zum Aufzeichnen von Protokollen, d. h., das Protokoll wird zuerst aufgezeichnet und dann der Pufferpool aktualisiert. Dadurch wird sichergestellt, dass die Daten, solange sie im Redo-Protokoll gespeichert sind, auf der Festplatte gespeichert bleiben.

Dies bedarf einiger Erklärungen. Das Redo-Protokoll wird ebenfalls auf die Festplatte geschrieben, und auch das Leeren wird auf die Festplatte geschrieben. Warum müssen wir zuerst das Redo-Protokoll aufzeichnen, anstatt es direkt zu leeren?

Der Hauptgrund besteht darin, dass das Redo-Protokoll viel schneller ist als das Leeren.

Der erste Punkt ist, dass das Redo-Protokoll ein Anfügevorgangsprotokoll ist, also ein sequentieller IO-Vorgang, während Dirty Flushing ein zufälliger IO-Vorgang ist, da die jedes Mal aktualisierten Daten nicht unbedingt benachbart, also zufällig sind.

Der zweite Punkt ist, dass das Dirty Flushing in Einheiten von Datenseiten erfolgt (d. h. jedes Mal wird mindestens eine Datenseite von der Festplatte in den Speicher gelesen oder mindestens eine Datenseite auf die Festplatte geschrieben). Die Standardseitengröße von MySQL beträgt 16 KB. Bei jeder Änderung einer Seite muss die gesamte Seite auf die Festplatte geschrieben werden. Das Redo-Protokoll enthält nur die Vorgangsprotokolle, die tatsächlich auf die Festplatte geschrieben werden müssen.

MySQL verfügt auch über ein Protokoll namens Binlog, das Vorgänge aufzeichnet. Was ist also der Unterschied zwischen Redo-Log und Binlog?

• Der erste Funktionsunterschied:
  

Das Redo-Protokoll wird zum Aufzeichnen des Aktualisierungscache verwendet, um sicherzustellen, dass die Persistenz der Transaktionen auch bei einem MySQL-Absturz nicht beeinträchtigt wird. Das Binärprotokoll wird zum Aufzeichnen verwendet, welche Vorgänge wann ausgeführt wurden, hauptsächlich mit Zeitpunkten, um sicherzustellen, dass die Daten zu einem bestimmten Zeitpunkt wiederhergestellt werden können. Es wird auch für die Master-Slave-Synchronisierung von Daten verwendet.

• Der zweite Unterschied liegt auf der Ebene:
  

Das Redo-Log wird von der Speicher-Engine InnoDB implementiert (MyISAM hat kein Redo-Log), während das Binlog auch in jeder anderen Speicher-Engine auf MySQL-Serverebene verfügbar ist.
In Bezug auf den Speicherinhalt ist das Redo-Log ein physisches Protokoll, das auf Datenseiten der Festplatte basiert, und das Binlog ist ein logisches Protokoll, das eine ausgeführte SQL-Anweisung speichert.

• Der dritte Punkt ist der Unterschied im Schreibzeitpunkt:
  

Standardmäßig wird das Redo-Protokoll auf die Festplatte geschrieben, wenn die Transaktion festgeschrieben wird. Die Richtlinie kann über den Parameter innodb_flush_log_at_trx_commit geändert werden, sodass das Redo-Protokoll nicht warten muss, bis die Transaktion festgeschrieben ist, bevor es auf die Festplatte geschrieben wird.
Beispiel: Es kann so eingestellt werden, dass einmal pro Sekunde eine Übermittlung erfolgt.
Das Binärprotokoll wird geschrieben, wenn eine Transaktion festgeschrieben wird.

Isolierung (I)

Atomarität und Dauerhaftigkeit sind beides Maße, die auf einer einzelnen Transaktion basieren, während Isolation die Eigenschaft bezeichnet, dass mehrere Transaktionen voneinander isoliert sind und sich nicht gegenseitig beeinflussen.
Wir alle wissen, dass die strengste Isolationsstufe für Transaktionen die Serialisierbarkeit ist. Allerdings sinkt die Leistung mit zunehmender Isolation. Daher wird die serialisierbare Isolationsstufe im Allgemeinen nicht verwendet.
Zur Isolierung werden wir zwei Situationen diskutieren:

• Die Auswirkungen von Schreibvorgängen in einer Transaktion auf Schreibvorgänge in einer anderen Transaktion;
• Die Auswirkungen eines Schreibvorgangs in einer Transaktion auf einen Lesevorgang in einer anderen Transaktion;

Zunächst werden die Schreibvorgänge zwischen Transaktionen tatsächlich durch den MySQL-Sperrmechanismus isoliert, während die Schreib- und Lesevorgänge zwischen Transaktionen durch den MVCC-Mechanismus implementiert werden.

Verriegelungsmechanismus

Die Sperren in MySQL sind hauptsächlich

Je nach Funktion: Lesesperre und Schreibsperre, je nach Aktionsbereich: Sperre auf Tabellenebene und Sperre auf Zeilenebene,
Es gibt auch Absichtssperren, Lückensperren usw.

Lesesperre: auch als „gemeinsame Sperre“ bekannt, bedeutet, dass mehrere Transaktionen eine Sperre gemeinsam nutzen können und nur auf Daten zugreifen, diese aber nicht ändern können.

Schreibsperre: auch als „exklusive Sperre“ bekannt, kann Daten nicht mit anderen Transaktionen teilen. Wenn eine Transaktion eine exklusive Sperre für ein Datenelement erhält, können andere Transaktionen keine anderen Sperren für die Zeile erhalten, einschließlich gemeinsam genutzter Sperren und exklusiver Sperren.

Sperre auf Tabellenebene: bedeutet, dass die gesamte Tabelle gesperrt wird, was zu schlechter Leistung führt. Verschiedene Speicher-Engines unterstützen unterschiedliche Sperrgranularitäten. Die MyISAM-Engine unterstützt Sperren auf Tabellenebene und die InnoDB-Engine unterstützt sowohl Sperren auf Tabellenebene als auch auf Zeilenebene.

Sperre auf Zeilenebene: Die entsprechenden Zeilen, die bedient werden müssen, werden mit guter Leistung gesperrt.

Absichtssperre: Eine Absichtssperre ist eine Sperre auf Tabellenebene. Wenn eine Transaktion bereits eine exklusive Sperre oder eine gemeinsame Sperre für bestimmte Daten in einer Tabelle hinzugefügt hat, kann eine Absichtssperre hinzugefügt werden. Auf diese Weise wird die Tabelle zuerst blockiert, wenn die nächste Transaktion die Tabelle sperren will und feststellt, dass die Absichtssperre bereits vorhanden ist. Wenn die Absichtssperre nicht hinzugefügt wurde, muss die zweite Transaktion, wenn sie die Tabelle sperren will, jeweils eine Zeile durchlaufen, um zu prüfen, ob gesperrte Daten vorhanden sind.

Lückensperre: Die Lückensperre ist eine Sperre, die hinzugefügt wird, um Phantom-Lesevorgänge zu verhindern. Sie wird zu nicht vorhandenem freien Speicherplatz hinzugefügt, der zwischen zwei Indexdatensätzen oder dem Speicherplatz vor dem ersten Indexdatensatz oder nach dem letzten Indexdatensatz liegen kann (schließt jedoch nicht den aktuellen Datensatz ein). Dadurch wird sichergestellt, dass beim Ausführen der Lückensperre die neu hinzugefügten Daten blockiert werden, und es wird sichergestellt, dass die Anzahl der durch zwei Abfragen in einer Transaktion abgerufenen Datensätze konsistent ist.

Next-Key-Lock: Next-Key-Lock ist eine Kombination aus Zeilensperre und Lückensperre, da die Lückensperre den aktuellen Datensatz nicht sperrt, während Next-Key-Lock auch den aktuellen Datensatz sperrt.

Wenn eine Tabelle beispielsweise drei Datensätze enthält:

Wenn Sie dann SQL ausführen: select * from table where number = 17 for update, wird die Lückensperre gesperrt. Der Zahlenbereich ist (16, 17), (17, 20), aber die Next-Key-Sperre sperrt:
Die Intervalle 16, 17, (17, 20) sind mit Lücken gesperrt, und Nummer=17 ist mit einem Datensatz gesperrt.

Der Sperrmechanismus gewährleistet die Isolierung von Schreibvorgängen zwischen mehreren Transaktionen, während die Garantie von Lese- und Schreibvorgängen zwischen mehreren Transaktionen durch den MVCC-Mechanismus gewährleistet werden muss.

MVCC-Mechanismus

Der vollständige Name von MVCC lautet [Multi-Version ConCurrency Control], es handelt sich dabei um ein Multiversion-Control-Protokoll.

MVCC wird hauptsächlich implementiert, indem jeder Datensatzzeile versteckte Spalten hinzugefügt und Undo-Protokolle verwendet werden. Die versteckten Spalten enthalten hauptsächlich die von der Datenzeile erstellte Versionsnummer (inkrementell), den Löschzeitpunkt, einen Zeiger auf das Undo-Protokoll usw.

Wie stellt MVCC also die Lese-/Schreibisolation sicher? Es werden hauptsächlich zwei Vorgänge verwendet: Snapshot-Lesen und aktuelles Lesen.

• Momentaufnahme gelesen:
  

Um die Effizienz der Parallelität zu gewährleisten, sperrt MVCC beim Lesen von Daten nicht. Beim Ausführen von select (normales select ohne Sperre) wird zuerst die Versionsnummer der aktuellen Daten gelesen. Wenn eine Transaktion diese Datenzeile ändert, bevor select ein Ergebnis zurückgibt, ist die Versionsnummer größer als zum Zeitpunkt der Ausführung von select. Um die Konsistenz der von select gelesenen Daten sicherzustellen, werden daher nur Daten gelesen, die kleiner oder gleich der aktuellen Version sind. Diese historische Version der Daten wird aus dem Undo-Protokoll abgerufen.

• Derzeit lese ich:

Beim Ausführen von Einfügen, Aktualisieren oder Löschen werden die Daten der neuesten Version gelesen und der aktuelle Datensatz gesperrt, um sicherzustellen, dass die Versionsnummer während des Vorgangs nicht durch andere Transaktionen geändert wird.

Beispielsweise handelt es sich bei der normalen Auswahl um einen Snapshot-Lesevorgang. Dies bedeutet, dass es sich bei dem Lesevorgang möglicherweise um die historische Version der Daten handelt.

Einfügen, Aktualisieren, Löschen, Auswählen ... Sperren im Freigabemodus, und Auswählen ... zum Aktualisieren lesen die aktuellen Daten, das heißt, sie lesen die neueste Version der Daten.

Tatsächlich kann durch Festlegen der Isolationsstufe auf Serializable auch eine Lese-/Schreibisolation erreicht werden, die Parallelitätseffizienz ist jedoch viel geringer und wird daher im Allgemeinen selten verwendet. MVCC sperrt jedoch nicht beim Lesen, sondern nur beim Schreiben, wodurch die Parallelitätseffizienz verbessert wird.

Der MVCC-Mechanismus gewährleistet die Lese-/Schreibisolation zwischen mehreren Transaktionen und erreicht dadurch eine Transaktionsisolation.

Konsistenz (C)

Konsistenz bezieht sich auf die Konsistenz der Daten vor und nach der Ausführung der Transaktion. Die Datenintegrität vor und nach der Transaktion wird nicht zerstört und der Datenstatus ist legal.

• Zu den Konsistenzindikatoren gehören:
  

Die Integrität des Index (eindeutiger Index, keine Duplikate usw.), die Vervollständigung der Datenspalten (Feldtyp, Länge, Größe erfüllen die Anforderungen), Fremdschlüsseleinschränkungen usw.

• Maßnahmen zur Herstellung der Konsistenz:
  

Garantieren Sie Atomizität, Persistenz und Isolation. Wenn diese Eigenschaften nicht garantiert werden können, kann auch die Konsistenz nicht garantiert werden. Auf Datenbankebene gibt es zusätzlich zu den Garantien der oben genannten Funktionen Maßnahmen, um die Konsistenz der Felder sicherzustellen. Beispielsweise können keine Ganzzahlen übergeben werden und die Länge von Zeichenfolgen, Zeitformaten usw. darf die Spaltenbegrenzung nicht überschreiten. Allerdings müssen die Entwickler dies selbst auf Anwendungsebene sicherstellen.
Beispiel: Wird ein Teilbetrag von A nach B überwiesen, so muss sichergestellt sein, dass der von A abgezogene Betrag mit dem Betrag übereinstimmt, der bei B hinzugerechnet wird. Wird nur der Betrag von A abgezogen, ohne den Betrag von B hinzuzurechnen, kann die Konsistenz nicht gewährleistet werden.

Darüber hinaus stellt MySQL durch ein zweiphasiges Transaktions-Commit auch die Datenkonsistenz zwischen Redo-Log und Binärlog sicher.

Bei der Einführung in die Persistenz haben wir oben den Unterschied zwischen Redo-Log und Binlog erläutert. Der dritte Unterschied besteht darin, dass beim Festschreiben einer Transaktion standardmäßig sowohl das Redo-Log als auch das Binlog geschrieben werden. Wie koordinieren sie also die Konsistenz? Welches Protokoll wird geschrieben, um festzustellen, ob eine Transaktion erfolgreich abgeschlossen wurde?
MySQL stellt die Datenkonsistenz der beiden Protokolle durch zweiphasiges Commit sicher.

• Einreichung der ersten Stufe,

Senden Sie das Redo-Protokoll an die Festplatte und ändern Sie den Status in den Vorbereitungsstatus. Führen Sie keine Operationen am Binärprotokoll aus.

• Einreichung der zweiten Stufe,

1. Generieren Sie ein Binärprotokoll der Transaktionsvorgänge und schreiben Sie das Binärprotokoll auf die Festplatte.

2. Rufen Sie die Transaktions-Commit-Schnittstelle der Engine auf, um den Redo-Log-Status von „Vorbereiten“ auf „Commit“ zu ändern. Anschließend wird die Transaktion festgeschrieben.
Durch die oben beschriebene zweiphasige Übermittlung wird die Konsistenz der Transaktionsdaten sichergestellt.
Wenn sich das Redo-Protokoll beim Festschreiben der Transaktion in der Vorbereitungsphase befindet und MySQL abstürzt oder abstürzt, wird die Transaktion zurückgesetzt.
Wenn die Transaktion das Redo-Log festlegt und sich in der Commit-Phase befindet, wird bei einem Absturz eine Transaktionswiederherstellung durchgeführt. Die lokale Transaktion wird über das Redo-Log wiederhergestellt. Wenn es sich um eine Master-Slave-Datenbank handelt, werden die Daten während der Commit-Phase basierend auf dem Binlog aus der Slave-Datenbank wiederhergestellt.
Dies ist die Grundlage für die erfolgreiche Übermittlung einer Transaktion basierend auf dem erfolgreichen Schreiben des Binlogs. Weil Binlong normalerweise zur Wiederherstellung während der Absturzwiederherstellung verwendet wird. Wenn kein Binlog generiert wurde, wird nur das Redo-Protokoll geschrieben. Während der Wiederherstellung stellt das Redo-Protokoll eine Version der Daten wieder her, während die über das Bin-Protokoll wiederhergestellten Slave-Daten die Binlog-Version der Daten zu einem früheren Zeitpunkt darstellen, was zu Dateninkonsistenzen führt.

Zusammenfassen

ACID und Konsistenz von MySQL-Transaktionen sind die ultimativen Ziele.
Zu den Maßnahmen zur Gewährleistung der Konsistenz gehören:

• AAtomicity: garantiert durch Undo-Log (Rollback nach Ausnahme oder Ausführungsfehler).
• D Persistenz: Garantiert durch Redo-Log (stellt sicher, dass bei einem MySQL-Absturz oder einem Stromausfall die Daten schließlich durch das Redo-Log auf der Festplatte gespeichert werden können).
• Isolierung: Die Lese- und Schreibvorgänge zwischen Transaktionen werden durch den Sperrmechanismus von MySQL isoliert, und die Schreibvorgänge zwischen Transaktionen werden durch den MVCC-Mechanismus (Snapshot-Lesen, aktuelles Lesen) isoliert.
• C-Konsistenz: Das ultimative Ziel einer Transaktion besteht darin, Konsistenz sowohl auf Datenbank- als auch auf Anwendungsebene sicherzustellen. Darüber hinaus verwendet MySQL eine zweiphasige Commit-Transaktion, um die Transaktionskonsistenz während der Persistenz sicherzustellen.

Oben finden Sie Einzelheiten dazu, wie MySQL ACID von Transaktionen implementiert. Weitere Informationen dazu, wie MySQL ACID von Transaktionen implementiert, finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!