Was sind MySQL-Sperren und -Klassifizierungen?

1. Datenbank-Parallelitätsszenario

In Szenarien mit hoher Parallelität weist die Datenbank ohne Berücksichtigung anderer Middleware die folgenden Szenarien auf:

• Lesen: Es gibt kein Problem und es ist auch keine Parallelitätskontrolle erforderlich.

• Lesen und Schreiben: Es gibt Thread-Sicherheitsprobleme, die zu Problemen bei der Transaktionsisolation führen können und zu Dirty Reads, Phantom Reads und nicht wiederholbaren Reads führen können.

• Geschrieben: Es gibt Thread-Sicherheitsprobleme und es kann zu Problemen mit Update-Verlusten kommen, z. B. dass der erste Update-Typ verloren geht und der zweite Update-Typ verloren geht.

Angesichts der oben genannten Probleme legt der SQL-Standard fest, dass die Probleme, die unter verschiedenen Isolationsstufen auftreten können, unterschiedlich sind:

MySQL vier Hauptisolationsstufen:

Es ist ersichtlich, dass MySQL tatsächlich das Problem der Nichtwiederholbarkeit auf der Isolationsebene REPEATABLE READ löst. Grundsätzlich löst das Phänomen des Phantomlesens in extremen Fällen immer noch.

Was ist also die Lösung? Generell gibt es zwei Lösungen:

1️⃣ MVCC für Lesevorgänge und Sperren für Schreibvorgänge

Für Lesevorgänge#🎜 🎜 #, unter RR-Ebene MVCC wird beim Starten einer Transaktion eine ReadView generiert, und dann wird die qualifizierte historische Version über ReadView gefunden. Diese Version wird aus dem Rückgängig-Protokoll erstellt, und wenn die ReadView generiert wird, ist dies der Fall Tatsächlich wird ein Snapshot generiert, daher ist die SELECT-Abfrage zu diesem Zeitpunkt Snapshot read (oder konsistenter Lesevorgang). Unter RR wird während der Ausführung einer Transaktion nur der erste SELECT-Vorgang ausgeführt. Erzeugt eine ReadView und nachfolgende SELECT-Operationen verwenden diese ReadView wieder, wodurch das Problem des nicht wiederholbaren Lesens und Phantomlesens weitgehend vermieden wird.

Zum Schreiben , da beim Snapshot-Lesen oder konsistenten Lesen kein Sperrvorgang für einen Datensatz in der Tabelle ausgeführt wird und die Transaktion von ReadView eine historische Version und die neueste ist Versionen der Schreibvorgänge stehen nicht in Konflikt miteinander, sodass andere Transaktionen frei Änderungen an den Datensätzen in der Tabelle vornehmen können.

2️⃣ Lese- und Schreibvorgänge sind gesperrt

zuerst den Kontostand auslesen und ihn dann hinzufügen Betrag dieser Anzahlung , und schließlich in die Datenbank geschrieben . Nachdem Sie den Kontostand gelesen haben, möchten Sie nicht, dass andere Transaktionen auf den Kontostand zugreifen. Nur bis die aktuelle Einzahlungstransaktion abgeschlossen ist, können andere Transaktionen auf den Kontostand zugreifen. Auf diese Weise muss der Datensatz beim Lesen gesperrt werden, was bedeutet, dass Lesevorgänge und Schreibvorgänge ebenso wie Schreib-/Schreibvorgänge in die Warteschlange gestellt werden.

Bei Dirty Reads liegt das daran, dass die aktuelle Transaktion einen Datensatz liest, der von einer anderen nicht festgeschriebenen Transaktion geschrieben wurde, aber wenn eine andere Transaktion #🎜🎜 # Sperren Sie den Datensatz beim Schreiben des Datensatzes , dann kann die aktuelle Transaktion den Datensatz nicht weiter lesen, sodass kein Dirty-Read-Problem auftritt.

liegt dies daran, dass die aktuelle Transaktion zuerst einen Datensatz liest und nachdem eine andere Transaktion Änderungen am Datensatz vorgenommen und ihn festgeschrieben hat, liest die aktuelle Transaktion Es werden erneut unterschiedliche Werte erhalten, wenn Der Datensatz gesperrt ist Wenn die aktuelle Transaktion den Datensatz liest, kann eine andere Transaktion den Datensatz nicht ändern und es erfolgt natürlich kein nicht wiederholbares Lesen.

liegt es daran, dass die aktuelle Transaktion einen Datensatz in einem Bereich liest und dann eine andere Transaktion liest dass ein neuer Datensatz in den Bereich eingefügt wurde. Als die aktuelle Transaktion die Datensätze im Bereich erneut las, wurden die neu eingefügten Datensätze als Phantomdatensätze bezeichnet. Wie ist dieser Bereich zu verstehen? Wie folgt:

Angenommen, es gibt nur ein Datenelement mit id=1 in der Tabelle user.

• id=1的数据。

• 当事务 A 执行一个id = 1的查询操作，能查询出来数据，如果是一个范围查询，如 id in(1,2)，必然只会查询出来一条数据。

• 此时事务 B 执行一个id = 2的新增操作，并且提交。

• 此时事务 A 再次执行id in(1,2)的查询，就会读取出 2 条记录，因此产生了幻读。

注：由于 RR 可重复读的原因，其实是查不出 id = 2的记录的，所以如果执行一次 update ... where id = 2Wenn Transaktion A eine Abfrageoperation von id = 1 ausführt, können die Daten abgefragt werden, wenn es sich um eine Bereichsabfrage handelt, z id in(1,2), es wird nur ein Datenelement abgefragt.

Zu diesem Zeitpunkt führt Transaktion A die Abfrage von id in(1,2) erneut aus und es werden 2 Datensätze gelesen Es wird eine Phantomlesung geben.

: Aufgrund des wiederholbaren Lesens von RR ist es tatsächlich unmöglich, id = 2 zu erkennen Es wird aufgezeichnet. Wenn Sie also einmal update ... where id = 2 ausführen, können Sie es herausfinden, indem Sie eine Bereichsabfrage durchführen.

2. Sperren und Klassifizierungen in MySQL

In der offiziellen MySQL-Dokumentation führt die InnoDB-Speicher-Engine die folgenden Arten von Sperren ein:

#🎜 🎜#

Ähnlich sieht es immer noch verwirrt aus, aber wir können es nach der Art und Weise klassifizieren, wie JDK-Sperren gelernt werden:

Tatsächlich wird das Badezimmer nicht nur zum Toilettengang genutzt, sondern auch zum Duschen und Händewaschen. Dabei geht es um die Optimierung der Sperrgranularität.

Wenn Sie im Badezimmer duschen, können andere tatsächlich gleichzeitig hineingehen und sich die Hände waschen, solange sie isoliert sind, wenn Toilette, Badewanne und Waschbecken alle getrennt und relativ unabhängig (nass und trocken) sind sind getrennt), tatsächlich kann das Badezimmer von drei Personen gleichzeitig genutzt werden, aber natürlich können die drei Personen nicht dasselbe tun. Dadurch wird die Granularität des Schlosses verfeinert. Sie müssen die Badezimmertür nur dann schließen, wenn Sie duschen, und andere können trotzdem hineingehen und sich die Hände waschen. Wenn bei der Gestaltung eines Badezimmers verschiedene Funktionsbereiche nicht getrennt werden, können die Badezimmerressourcen nicht maximiert werden.

In ähnlicher Weise gibt es auch in MySQL eine Sperrgranularität. Normalerweise in drei Typen unterteilt: Zeilensperren, Tabellensperren und Seitensperren.

3.1 Zeilensperre

Bei der Einführung gemeinsamer Sperren und exklusiver Sperren werden diese tatsächlich für eine bestimmte Zeile aufgezeichnet, sodass sie auch als Zeilensperren bezeichnet werden können.

Das Sperren eines Datensatzes betrifft nur diesen Datensatz, daher ist die Sperrgranularität von Zeilensperren die feinste in MySQL. Die Standardsperre der InnoDB-Speicher-Engine ist die Zeilensperre.

Es hat die folgenden Eigenschaften:

1. Die geringste Wahrscheinlichkeit eines Sperrkonflikts und eine hohe Parallelität

   Da die Granularität von Zeilensperren gering ist, ist auch die Wahrscheinlichkeit eines Sperrressourcenkonflikts am geringsten, also die Wahrscheinlichkeit einer Sperre Der Konflikt ist gering und die Parallelität ist umso höher, je höher das Geschlecht.

2. Hoher Overhead und langsames Sperren

   Sperren sind sehr leistungsintensiv. Wenn Sie mehrere Daten in der Datenbank sperren, werden zwangsläufig viele Ressourcen beansprucht, und Sie müssen auf die vorherige Sperre warten Sperre aufgehoben werden.

3. Wird einen Deadlock erzeugen

   Was ein Deadlock ist, können Sie unten lesen.

3.2 Tabellensperre

Die Sperre auf Tabellenebene ist eine Sperre auf Tabellenebene, die die gesamte Tabelle sperrt. Sie kann Deadlocks sehr gut vermeiden und ist außerdem der größte granulare Sperrmechanismus in MySQL.

Die Standardsperre der MyISAM-Speicher-Engine ist die Tabellensperre.

Es hat die folgenden Eigenschaften:

1. Es hat einen geringen Overhead und schnelles Sperren

   Da es die gesamte Tabelle sperrt, muss es schneller sein als das Sperren einzelner Daten.

2. Es tritt kein Deadlock auf.

   Die gesamte Tabelle ist gesperrt. Andere Transaktionen können die Sperre überhaupt nicht erhalten, und natürlich tritt kein Deadlock auf.

3. Die Sperrgranularität ist groß, die Wahrscheinlichkeit eines Sperrenkonflikts ist hoch und die Parallelität ist gering in anderer Datenbankverwaltungssoftware üblich.

   Die Granularität von Sperren auf Seitenebene liegt zwischen Sperren auf Zeilenebene und Sperren auf Tabellenebene, sodass der zum Erhalten von Sperren erforderliche Ressourcenaufwand und die gleichzeitigen Verarbeitungsfunktionen, die sie bereitstellen können, ebenfalls zwischen den beiden oben genannten liegen. Darüber hinaus können Sperren auf Seitenebene ebenso wie Sperren auf Zeilenebene Deadlocks verursachen.

4. Klassifizierung der Sperrkompatibilität

In MySQL wird das Lesen von Daten hauptsächlich in aktuelles Lesen und Snapshot-Lesen unterteilt:

• Snapshot-Lesen

  Snapshot-Lesen, es werden Snapshot-Daten gelesen, ohne dass gewöhnliche SELECTs hinzugefügt werden Sperren sind Snapshot-Lesevorgänge.

  SELECT * FROM table WHERE ...

  Nach dem Login kopieren

• Aktueller Messwert

  Aktueller Messwert bedeutet, dass die neuesten Daten gelesen werden, nicht die historischen Daten, oder das Hinzufügen, Löschen und Ändern von Daten führt zum aktuellen Messwert.

  SELECT * FROM table LOCK IN SHARE MODE;
  SELECT FROM table FOR UPDATE;
  INSERT INTO table values ...
  DELETE FROM table WHERE ...
  UPDATE table SET ...

  Nach dem Login kopieren

In den meisten Fällen betreiben wir die Datenbank basierend auf aktuellen Lesevorgängen, und in gleichzeitigen Szenarien müssen wir nicht nur zulassen, dass Lesen-Lesen-Situationen nicht beeinträchtigt werden, sondern auch Schreiben-Schreiben und Lesen ermöglichen Da sich Schreib- oder Schreib-Lese-Vorgänge gegenseitig blockieren, müssen Sie in MySQL „gemeinsame Sperren“ und „exklusive Sperren“ verwenden. 4.1 Shared Locks und Exclusive Locks

Shared Locks

S Locks. Daten können gleichzeitig gelesen werden, aber keine Transaktion kann die Daten ändern. Exklusive Sperren

X-Sperren bezeichnet werden. Wenn etwas eine exklusive Sperre zu einer Zeile hinzufügt, kann nur diese Transaktion sie lesen und schreiben. Andere Transaktionen können keine Sperren hinzufügen, aber sie müssen darauf warten freigeben. Lassen Sie uns die Situation beim Erwerb von Sperren analysieren: Wenn es Transaktion A und Transaktion B gibt

• Wenn Transaktion B die X-Sperre des Datensatzes erwerben möchte, wird dieser Vorgang blockiert, bis die S-Sperre freigegeben wird, nachdem Transaktion A festgeschrieben wurde.

• Wenn Transaktion A zuerst die X-Sperre erhält, unabhängig davon, ob Transaktion B die S-Sperre oder die X-Sperre des Datensatzes erwerben möchte, wird sie blockiert, bis Transaktion A festgeschrieben wird.

• Daher können wir sagen, dass S-Lock und S-Lock kompatibel sind, S-Lock und X-Lock inkompatibel sind und X-Lock und X-Lock ebenfalls inkompatibel sind.

Intention Shared Lock

IS Lock bezeichnet. Wenn eine Transaktion einem Datensatz eine S-Sperre hinzufügen möchte, muss sie zunächst eine IS-Sperre auf Tabellenebene hinzufügen. Intention Exclusive Lock

IX Lock bezeichnet. Wenn eine Transaktion einem Datensatz eine X-Sperre hinzufügen möchte, muss sie zunächst eine IX-Sperre auf Tabellenebene hinzufügen. Absichtssperren sind Sperren auf Tabellenebene. Sie werden nur vorgeschlagen, um schnell zu beurteilen, ob die Datensätze in der Tabelle gesperrt sind, wenn S-Sperren auf Tabellenebene hinzugefügt werden. Es gibt keine gesperrten Datensätze in der Tabelle. Das heißt, die IS-Sperre ist mit der IS-Sperre kompatibel und die IX-Sperre ist mit der IX-Sperre kompatibel.

Warum brauchen Sie eine Absichtssperre? Die Absichtssperre von InnoDB wird hauptsächlich verwendet, wenn mehrere granulare Sperren nebeneinander existieren. Beispielsweise möchte Transaktion A einer Tabelle eine S-Sperre hinzufügen. Wenn eine Zeile in der Tabelle durch Transaktion B zu einer X-Sperre hinzugefügt wurde, sollte auch die Anwendung für die Sperre blockiert werden. Wenn die Tabelle viele Daten enthält, ist der Aufwand für die zeilenweise Überprüfung des Sperrflags sehr groß und die Leistung des Systems wird beeinträchtigt.

Wenn die Tabelle beispielsweise 100 Millionen Datensätze enthält und Transaktion A Zeilen für mehrere Datensätze sperrt, muss Transaktion B der Tabelle Sperren auf Tabellenebene hinzufügen. Wenn keine beabsichtigte Sperre vorhanden ist, ermitteln Sie, ob Diese 100 Millionen Datensätze sind in der Tabelle gesperrt. Wenn eine Absichtssperre vorliegt und Transaktion A vor der Aktualisierung eines Datensatzes eine Absichtssperre und dann eine Wenn es einen Konflikt gibt, warten Sie, bis Transaktion A freigegeben wird, ohne jeden Datensatz zu überprüfen. Transaktion B muss beim Aktualisieren der Tabelle nicht genau wissen, welche Zeile gesperrt ist, sie muss lediglich wissen, dass mindestens eine Zeile gesperrt wurde. Um es ganz klar auszudrücken: Die Hauptfunktion von Absichtssperren besteht darin, mit dem Widerspruch zwischen Zeilensperren und Tabellensperren umzugehen. Sie können zeigen,

Tabellenebene

S

CREATE TABLE test (
    id INT (1) NOT NULL AUTO_INCREMENT,
    number INT (1) NOT NULL COMMENT '数字',
    PRIMARY KEY (id),
    KEY number (number) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

# 插入以下数据
INSERT INTO test VALUES (1, 1); 
INSERT INTO test VALUES (5, 3); 
INSERT INTO test VALUES (7, 8); 
INSERT INTO test VALUES (11, 12);

BEGIN;

SELECT * FROM test WHERE number = 3 FOR UPDATE;

BEGIN;

# 阻塞
INSERT INTO test (id, number) VALUES (2,2);

INSERT INTO test (id, number) VALUES (8,8);

CREATE TABLE `user` (
  `id` bigint NOT NULL COMMENT '主键',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `age` varchar(10) DEFAULT NULL COMMENT '年龄',
  `url` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `suf_index_url` (`name`(3)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

# 数据
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('1', 'a', '1', '18', 'https://javatv.net');
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('2', 'b', '1', '18', 'https://javatv.net');

Das obige ist der detaillierte Inhalt vonWas sind MySQL-Sperren und -Klassifizierungen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!