Eine kurze Analyse von MySQL-Sperren und -Transaktionen

MySQL selbst wurde auf Basis des Dateisystems entwickelt, das sich durch die Existenz von Sperren unterscheidet.

Als Datenbanksoftware hat MySQL zwangsläufig gleichzeitigen Zugriff auf seine gemeinsam genutzten Ressourcen. Um den gleichzeitigen Zugriff auf verschiedene Ressourcen zu koordinieren und zu verwalten, wird ein Sperrmechanismus erstellt, da die Existenz des Sperrmechanismus die Datenintegrität und -konsistenz für die Datenbank gewährleistet.

Je nach Sperrebene können Sperren in folgende Kategorien unterteilt werden: Sperren auf Zeilenebene, Sperren auf Tabellenebene und Sperren auf Seitenebene.
Je nach Art der Sperre können Sperren in gemeinsam genutzte Sperren und exklusive Sperren (exklusive Sperren) unterteilt werden.
Um Zeilensperren und Tabellensperren zu koordinieren, werden Absichtssperren (Sperren auf Tabellenebene) generiert.

Gemeinsam genutzte Sperre, die Transaktionen das Lesen von Daten ermöglicht.
Exklusive Sperre, die Transaktionen das Ändern oder Löschen von Daten ermöglicht.
Absichtliche Sperre, eine Sperre auf Tabellenebene, die automatisch hinzugefügt wird, wenn eine Sperre auf Zeilenebene erworben wird, einschließlich: Absichtliche gemeinsame Sperre und Absichtliche exklusive Sperre.

Für die MyISAM-Speicher-Engine werden nur Tabellensperren unterstützt, während die InnoDB-Speicher-Engine Zeilensperren und Tabellensperren unterstützt.

Wenn die MyISAM-Speicher-Engine Daten ändert oder löscht, wird eine exklusive Sperre generiert, die die gesamte Tabelle sperrt und die gleichzeitige Schreibleistung schlecht ist. Beim Lesen wird eine gemeinsame Sperre generiert, die die Tabelle nicht sperrt und eine bessere Leseleistung aufweist.

Wenn die InnoDB-Speicher-Engine Daten ändert oder löscht, wird eine exklusive Sperre generiert. Die Sperrung eines bestimmten Indexdatensatzes wirkt sich im Allgemeinen nicht auf andere Zeilen in der Tabelle aus und die gleichzeitige Schreibleistung ist besser. Beim Lesen wird eine gemeinsame Sperre generiert, die die Tabelle und die Zeilen nicht sperrt und die Leseleistung ist besser.

Zeilensperren sperren Indexdatensätze und nicht Datensatzzeilen. Wenn kein Index vorhanden ist, wird zum Sperren ein impliziter Index verwendet.

Wenn einige Zeilen einer Tabelle exklusive Sperren erhalten haben, wird in der Tabelle eine beabsichtigte exklusive Sperre generiert. Wenn eine Transaktion zu diesem Zeitpunkt die gesamte Tabelle sperren möchte, wird die Transaktion blockiert, wenn sie das Vorhandensein der beabsichtigten exklusiven Sperre erkennt. Die beabsichtigte Sperre kann direkt bestimmen, ob die Tabelle gesperrt werden kann, ohne jede Zeile durchlaufen zu müssen, um zu prüfen, ob eine exklusive Sperre vorhanden ist. Die beabsichtigte Sperre koordiniert effizient die Beziehung zwischen Zeilensperren und Tabellensperren.

Zeilensperren werden je nach Sperrumfang in drei Typen unterteilt:

• Datensatzsperre Eine Sperre für eine einzelne Zeile eines Datensatzes.
• Gap Lock Gap Lock sperrt einen Bereich, der den Datensatz selbst nicht enthält.
• Next-Key Lock sperrt einen Bereich, einschließlich des Datensatzes selbst, um das Phantomleseproblem zu lösen.

Natürlich haben Sperren ihre Vor- und Nachteile und es können auch Deadlocks auftreten.
Wenn zwei oder mehr Transaktionen während der Ausführung um Ressourcen konkurrieren und sich gegenseitig zum Warten zwingen, spricht man von einem Deadlock.

Schließlich bereichert die Existenz von Sperren die Funktionalität nachfolgender Transaktionen.

MySQL entwickelt einen Mechanismus, der es ermöglicht, Daten vollständig von einem Konsistenzzustand in einen anderen zu überführen. Dieser Mechanismus wird als Transaktion bezeichnet.

Transaktionen haben vier Hauptmerkmale: Atomarität (A), Konsistenz (C), Isolation (I) und Dauerhaftigkeit (D), die als ACID bezeichnet werden.

• Atomarität: Alle Vorgänge in einer Transaktion sind entweder erfolgreich oder schlagen fehl und können nicht aufgeteilt werden.
• Konsistenz: Eine Transaktion überführt eine Datenbank von einem konsistenten Zustand in einen anderen konsistenten Zustand und stellt die Datenintegrität sicher.
• Isolation: Auch als Parallelitätskontrolle bekannt. Eine Transaktion ist für andere Transaktionen unsichtbar, bevor sie festgeschrieben wird.
• Persistenz: Sobald eine Transaktion festgeschrieben ist, ist das Ergebnis dauerhaft und es gehen keine Daten aufgrund eines Datenbankfehlers verloren.

Atomarität und Dauerhaftigkeit werden durch Redo-Protokolle erreicht, Konsistenz durch Undo-Protokolle und Isolation durch Sperrmechanismen.

Im Wesentlichen dient die Atomizität der Unterstützung der Persistenz. Wenn ein Teil einer Transaktion in das Redo-Log geschrieben wird und ein Absturz oder Stromausfall auftritt, sollte die Transaktion gemäß der Atomizität wiederhergestellt werden. In diesem Fall müssen die in der Protokolldatei persistent gespeicherten Daten durch Backtracking rückgängig gemacht werden. In der InnoDB-Speicher-Engine entsprechen die Redo-Protokolle ib_logfile0 und ib_logfile1.

Wenn die Transaktion zurückgesetzt werden muss, muss dies durch Konsistenz gewährleistet sein. Um Konsistenz zu erreichen, wird das Undo-Protokoll verwendet. Das Undo-Protokoll speichert einige Informationen zu mehreren Versionen der Transaktion. Über das Undo-Protokoll kann die Transaktion auf den Zustand vor der Änderung zurückgesetzt werden.

Hier müssen wir MySQLs MVCC-Mehrversions-Parallelitätskontrolle erwähnen, die auch durch Undo-Protokolle implementiert wird.
MVCC wird erreicht, indem am Ende jeder Datenzeile zwei versteckte Felder, „Create Version“ und „Delete Version“, hinzugefügt werden und jedes Mal, wenn eine Transaktion geöffnet wird, eine Transaktions-ID initialisiert wird. Beim Hinzufügen eines neuen Datenelements entspricht der Wert der Erstellungsversion der Transaktions-ID. Beim Löschen von Daten entspricht der Wert der Löschversion der Transaktions-ID. Beim Aktualisieren von Daten werden diese zuerst gelöscht und dann hinzugefügt. Das Rückgängig-Protokoll enthält zwei Datenelemente: eine Löschversion entspricht der Transaktions-ID und der andere Wert der Erstellungsversion entspricht der Transaktions-ID.

Während der Ausführung einer Transaktion können gleichzeitig andere Transaktionen vorhanden sein. Mehrere Transaktionen müssen voneinander isoliert werden, d. h. es muss eine Parallelitätskontrolle erreicht werden. Sperren werden verwendet, um eine Isolierung zu erreichen. Zu den Transaktionsisolationsebenen von MySQL gehören: Read Uncommitted, Read Committed, Read Repeatable und Serializable. Darunter werden „Commited Read“ und „Repeatable Read“ basierend auf der MVCC-Mehrversions-Parallelitätskontrolle implementiert.

Sperren bieten Vorteile für die Transaktionsparallelitätskontrolle, bringen aber auch Nachteile mit sich, darunter Dirty Reads, nicht wiederholbare Lesevorgänge und Phantomlesevorgänge.

Dirty Read bedeutet, dass eine Transaktion den nicht festgeschriebenen Inhalt einer anderen Transaktion liest. Sobald die andere Transaktion zurückgesetzt wird, erscheinen schmutzige Daten.
Nicht wiederholbares Lesen bedeutet, dass die gleiche Transaktion mit der gleichen SQL-Anweisung mehrere Lesevorgänge durchführt und unterschiedliche Ergebnisse zurückgibt.
Phantomlesevorgänge beziehen sich auf die Situation, in der beim Hinzufügen oder Löschen von Datensätzen durch eine Transaktion plötzlich einige Datensätze auftauchen, die eigentlich nicht angezeigt werden sollten.

Um das Problem des Dirty Read zu lösen, müssen Sie die Isolationsebene mindestens auf „Read Committed“ einstellen.
Um das Problem des nicht wiederholbaren Lesens zu lösen, müssen Sie die Isolationsebene mindestens auf „Wiederholbares Lesen“ einstellen.
Um das Phantomleseproblem zu lösen, müssen Sie die Isolationsebene auf „Serialisierbar“ setzen oder „Next-Key Lock“ verwenden.

Oben finden Sie eine kurze Analyse der Details zu MySQL-Sperren und -Transaktionen. Weitere Informationen zu MySQL-Sperren und -Transaktionen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!