Dieser Artikel beschreibt anhand von Beispielen MySQL-Transaktionen, Isolationsebenen und die Verwendung von Sperren. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Eine Transaktion ist eine Gruppe von SQL-Anweisungen, die gemeinsam erfolgreich sind oder fehlschlagen. Transaktionen sollten außerdem Atomarität, Konsistenz, Isolation und Dauerhaftigkeit aufweisen.

1. Grundelemente von Transaktionen (ACID)

1. Atomarität: Nach dem Start der Transaktion sind alle Vorgänge entweder vollständig erfolgreich oder vollständig fehlgeschlagen. Es besteht keine Möglichkeit eines Zwischenzustands. Die Transaktion ist ein unteilbares Ganzes, genau wie ein Atom.

2. Konsistenz: Vor und nach Beginn und Ende der Transaktion werden die Integritätsbeschränkungen der Datenbank nicht verletzt. A überweist Geld an B, A zieht das Geld ab, aber B erhält es nicht.

3. Isolation: Gleichzeitig auftretende Transaktionen (gleichzeitige Transaktionen) sollten nicht dazu führen, dass die Datenbank in einen inkonsistenten Zustand gerät. Jede Transaktion wird unabhängig ausgeführt und hat keinen Einfluss auf die Existenz anderer Transaktionen.

4. Persistenz: Alle durch Transaktionen an der Datenbank vorgenommenen Änderungen werden auf der Festplatte gespeichert und gehen nicht verloren.

2. Parallelitätsprobleme bei Transaktionen

1. Dirty Read: Transaktion A liest die nicht festgeschriebenen geschriebenen Daten von Transaktion B. Die gelesenen Daten werden als Dirty Data bezeichnet.

2. Nicht wiederholbares Lesen: Transaktion A liest dieselben Daten mehrmals, aber während des Lesevorgangs ändert Transaktion B die Daten und übergibt sie. Dies führt zu unterschiedlichen Ergebnissen, wenn dieselben Daten mehrmals gelesen werden.

3. Phantomlesen: Transaktion A ändert alle Datenzeilen in der Tabelle, beispielsweise indem sie den Status auf 1 setzt, gleichzeitig fügt Transaktion B jedoch eine neue Datenzeile mit dem Status 0 in die Tabelle ein. Für den Benutzer, der Transaktion A ausführt, gibt es in der Tabelle immer noch einen Datensatz, der nicht geändert wurde, wie eine Illusion.

3. Vier Ebenen der Transaktionsisolierung

4. Datenbankisolationsebene abrufen und festlegen

VARIABLEN WIE „%isolation%“ ANZEIGEN;
GLOBALE VARIABLEN WIE „%isolation%“ ANZEIGEN;

Verwenden von Systemvariablen zum Abfragen

WÄHLEN SIE @@GLOBAL.tx_isolation;
Wählen Sie @@SESSION.tx_isolation;
WÄHLEN SIE @@tx_isolation;

Für mysql8 verwenden Sie die folgenden Variablen zur Abfrage

Wählen Sie @@GLOBAL.transaction_isolation;
Wählen Sie @@SESSION.transaction_isolation;
Wählen Sie @@transaction_isolation;

Festlegen der Isolationsebene

SET GLOBAL tx_isolation = 'Isolationsstufe';
SET SESSION tx_isolation = 'Isolationsstufe';
SET @@tx_isolation = 'Isolationsstufe';

Für mysql8 verwenden Sie die folgende Anweisung, um

SET GLOBAL transaction_isolation = 'Isolationsebene';
SET SESSION transaction_isolation = 'Isolationsebene';
SET @@transaction_isolation = 'Isolationsebene';

5. Erklären Sie die Isolationsebenen anhand von Beispielen

Bereiten Sie zunächst eine Tabelle und einige Daten vor.

CREATE TABLE `Konto` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT KOMMENTAR 'ID',
 `name` varchar(32) STANDARD '' KOMMENTAR 'name',
 `Geld` Dezimalzahl (11,2) STANDARD '0,00' KOMMENTAR 'Geld',
 PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8mb4;

INSERT INTO `Konto` (`Name`, `Geld`) VALUES ('A', '500,00');
INSERT INTO `Konto` (`Name`, `Geld`) VALUES ('B', '100,00');

1. Lesen Sie ungebunden

Setzen Sie die Transaktionsisolation auf „READ-UNCOMMITTED“.
setze Autocommit = 0;

Transaktion B hat die Daten in der Tabelle geändert, aber nicht festgeschrieben. Transaktion A hat die geänderten Daten gelesen. Wenn für Transaktion B aus irgendeinem Grund ein Rollback durchgeführt wird, handelt es sich bei den von Transaktion A gelesenen Daten um fehlerhafte Daten.

2. Eingereichte Beiträge lesen

Setzen Sie die Transaktionsisolation auf „READ-COMMITTED“.
setze Autocommit = 0;

Wenn Transaktion B die Daten ändert, aber nicht festschreibt, erhält Transaktion A trotzdem die Originaldaten, wodurch das Dirty-Read-Problem gelöst wird.

Wenn jedoch Transaktion B festgeschrieben wird, führt Transaktion A die letzte Abfrage aus und das Ergebnis ist nicht mit der vorherigen Abfrage konsistent, was das Problem des nicht wiederholbaren Lesevorgangs verursacht.

3. Wiederholbares Lesen

setze transaction_isolation = 'WIEDERHOLBARES LESEN';
setze Autocommit = 0;

Transaktion B hat die Daten geändert und festgeschrieben. Die Ergebnisse der beiden Abfragen von Transaktion A waren konsistent, wodurch das Problem nicht wiederholbarer Lesevorgänge gelöst wurde.

Zu diesem Zeitpunkt ändert Transaktion A die Gelddaten mit dem Namen A.

Der Betrag der Datei mit dem Namen A beträgt 350 statt 400. Wiederholbares Lesen stellt Datenkonsistenz sicher.

Wir ändern den Betrag aller Konten in Transaktion A auf 200 und fügen in Transaktion B neue Daten ein.

Transaktion A erhält immer noch zwei Daten, wodurch das Phantomleseproblem gelöst wird, das in Transaktion A auftritt, wenn neue Daten hinzugefügt werden.

4. Serialisierung

Setzen Sie die Transaktionsisolation auf „SERIALIZABLE“.
setze Autocommit = 0;

Transaktion A fragt die Tabelle ab. Wenn sie nicht festgeschrieben wird, wartet die Einfügeanweisung von Transaktion B dort, bis sie abläuft oder Transaktion A festgeschrieben wird.

Wenn im Gegenteil Transaktion B nach dem Einfügen der Daten in die Tabelle nicht festgeschrieben wird, wartet Transaktion A auf die Abfrage der Tabelle, bis Transaktion B festgeschrieben wird.

Zu diesem Zeitpunkt wird die Tabelle sowohl beim Lesen als auch beim Schreiben gesperrt, was natürlich größere Auswirkungen auf die gleichzeitige Leistung hat.

Je höher die Isolationsstufe, desto besser kann die Datenintegrität und -konsistenz gewährleistet werden.

6. MySQL-Sperre

Es gibt zwei Arten von Schlössern:

Interne Sperren: Interne Sperren, die intern vom MySQL-Server ausgeführt werden, um den Konflikt um Tabelleninhalte durch mehrere Sitzungen zu verwalten.

Externe Sperren: MySQL bietet Client-Sitzungen die Möglichkeit, explizit Tabellensperren zu erwerben, um zu verhindern, dass andere Sitzungen auf die Tabelle zugreifen.

Es gibt zwei Arten von internen Schlössern:

1. Sperren auf Zeilenebene: Sperren auf Zeilenebene sind feinkörnig und sperren nur die Zeilen, auf die zugegriffen wird. Dadurch können mehrere Sitzungen gleichzeitig Schreibzugriffe ausführen.

2. Sperren auf Tabellenebene: MySQL verwendet Sperren auf Tabellenebene für MyISAM-, Memory- und Merge-Tabellen, sodass die Tabelle jeweils nur in einer Sitzung aktualisiert werden kann. Dadurch sind diese Speicher-Engines besser für lesebasierte Vorgänge geeignet.

Externe Sperren: Die Sperrung kann über LOCK TABLE und UNLOCK TABLE gesteuert werden.

LESEN (gemeinsame Sperre): Mehrere Sitzungen können Daten aus einer Tabelle lesen, ohne eine Sperre zu erhalten. Darüber hinaus können mehrere Sitzungen eine Sperre für dieselbe Tabelle erhalten. Wenn eine LESEN-Sperre aufrechterhalten wird, kann keine Sitzung Daten in die Tabelle schreiben. Alle Schreibvorgänge warten, bis die Lesesperre aufgehoben wird.

SCHREIBEN (exklusive Sperre): Wenn eine Tabelle durch SCHREIBEN gesperrt ist, kann keine andere Sitzung als die Sitzung, die die Sperre hält, Daten lesen oder schreiben, sofern die SCHREIBEN-Sperre nicht aufgehoben wird.

Anweisung zur Tabellensperre:

LOCK TABLES Tabellenname [LESEN | SCHREIBEN];

Anweisung zum Entsperren der Tabelle:

TABELLEN ENTSPERREN;

Alle Tabellen in der Datenbank sperren:

FLUSH-TABELLEN MIT LESE-SPERRE;

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „Zusammenfassung der Kenntnisse im Bereich MySQL-Indexoperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Allgemeinfunktionen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Protokolloperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Transaktionsoperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-gespeicherte Prozeduren“ und „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-Datenbanksperren“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.