MySQL lernt, über Sperren und Klassifizierung zu sprechen

Dieser Artikel hilft Ihnen, die Sperren in MySQL zu verstehen und stellt die Granularitätsklassifizierung von Sperren und die Kompatibilitätsklassifizierung von Sperren vor.

1. Datenbank-Parallelitätsszenario

In einem Szenario mit hoher Parallelität weist die Datenbank die folgenden Szenarien auf:

• Lesen: Es gibt kein Problem und es ist 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.

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

MySQL vier Hauptisolationsstufen:

Isolationsstufe	Dirty Read	Non- wiederholbare Lektüre	Phantomlesung
READ UNCOMMITTED: Uncommitted read	kann passieren	kann passieren	kann passieren
READ COMMITTED: readcommitted	aufgelöst	kann passieren	kann passieren
REPEATABLE READ: Wiederholbares Lesen	Gelöst	Gelöst	Kann passieren
SERIALIZABLE: Serialisierbar	Gelöst	Gelöst	Gelöst

Es ist ersichtlich, dass MySQL tatsächlich das Problem der Nichtwiederholbarkeit auf der Isolationsebene REPEATABLE READ löst, im Grundedas Phantom-Lese-Problem löst, aber in extremen Fällen gibt es immer noch Phantom-Lesevorgänge.

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

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

Bei Lesevorgängen wird unter MVCC auf RR-Ebene beim Starten einer Transaktion eine ReadView generiert und dann über ReadView gefunden Eine historische Version, die die Bedingungen erfüllt, und diese Version wird aus Rückgängig-Protokollen erstellt. Beim Generieren von ReadView wird tatsächlich ein Snapshot generiert, sodass die SELECT-Abfrage zu diesem Zeitpunkt „Snapshot Read“ (oder konsistentes Lesen) lautet. Das wissen wir unter RR , wird eine ReadView nur generiert, wenn eine SELECT-Operation zum ersten Mal während der Ausführung einer Transaktion ausgeführt wird. Nachfolgende SELECT-Operationen verwenden diese ReadView wieder, wodurch nicht wiederholbare Lesevorgänge vermieden werden und das Problem des 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 ist, die neueste Version des Schreibvorgangs jedoch nicht dieselbe ist Es kann zu Konflikten kommen, sodass andere Transaktionen frei Änderungen an den Datensätzen in der Tabelle vornehmen können. 2️⃣ Lese- und Schreibvorgänge sind gesperrt

Wenn einige unserer Geschäftsszenarien das Lesen der alten Version des Datensatzes nicht zulassen, aber jedes Mal die neueste Version des Datensatzes lesen müssen, z. B. bei einer Bankeinzahlungstransaktion, Sie müssen zuerst den Kontostand auslesen

, ihn dann

zum Betrag dieser Einzahlung addieren und schließlich in die Datenbank schreiben

. 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 ebenfalls in die Warteschlange gestellt und wie Schreib-/Schreibvorgänge ausgeführt werden.

Dirty Reading liegt daran, dass die aktuelle Transaktion einen Datensatz liest, der von einer anderen nicht festgeschriebenen Transaktion geschrieben wurde. Wenn jedoch eine andere Transaktion diesen Datensatz sperrt

, während der Datensatz geschrieben wird, kann die aktuelle Transaktion nicht mehr gelesen werden den Datensatz, so dass es kein Dirty-Read-Problem gibt.

Bei nicht wiederholbaren Lesevorgängen liegt dies daran, dass die aktuelle Transaktion zuerst einen Datensatz liest und nachdem eine andere Transaktion Änderungen am Datensatz vornimmt und ihn festschreibt, erhält die aktuelle Transaktion beim erneuten Lesen andere Werte Wenn der Datensatz in der aktuellen Transaktion gelesen wird, wird er gesperrt. Dann kann der Datensatz nicht durch eine andere Transaktion geändert werden, und es erfolgt natürlich kein nicht wiederholbares Lesen. Beim Phantomlesen

liegt das daran, dass die aktuelle Transaktion einen Datensatz in einem

Bereich liest und dann eine andere Transaktion einen neuen Datensatz in den Bereich einfügt. Wenn die aktuelle Transaktion den Datensatz im Bereich erneut liest, a Wenn ein neuer Datensatz gefunden wird, nennen wir die neu eingefügten Datensätze Phantomdatensätze.

Wie ist dieser Bereich zu verstehen? Wie folgt:

Angenommen, es gibt nur ein Datenelement mit id=1 in der Tabelle user. Wenn Transaktion A eine Abfrageoperation von id = 1 ausführt, können die Daten abgefragt werden, wenn es sich um eine Bereichsabfrage handelt, z. B. id in (1,2 ) code> wird nur ein Datenelement abgefragt.

Zu diesem Zeitpunkt führt Transaktion B einen neuen Vorgang mit id = 2 aus und übermittelt ihn.

• Zu diesem Zeitpunkt führt Transaktion A die Abfrage von id in(1,2) erneut aus und es werden 2 Datensätze gelesen, sodass ein Phantomlesen erfolgt. id=1的数据。

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

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

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

注：由于 RR 可重复读的原因，其实是查不出 id = 2的记录的，所以如果执行一次 update ... where id = 2

Hinweis

: Aufgrund des wiederholbaren Lesens von RR kann der Datensatz mit id=2 tatsächlich nicht gefunden werden, wenn Sie also ein Update durchführen. .. wo id = 2, Sie können es herausfinden, indem Sie den Bereich durchsuchen.

Es ist nicht einfach, das Problem des Phantomlesens durch Sperren zu lösen, da die Phantomdatensätze nicht vorhanden sind, wenn die aktuelle Transaktion die Datensätze zum ersten Mal liest. Daher ist das Sperren beim Lesen etwas mühsam, da dies nicht der Fall ist Wissen wen man sperrt.

Wie löst InnoDB das Problem? Schauen wir uns zunächst an, welche Sperren die InnoDB-Speicher-Engine hat. 2. Schlösser und Klassifizierungen in MySQL JDK-Klassifizierung durch Sperrung:

3. Klassifizierung der Sperrgranularität

Was ist die Sperrgranularität? Die sogenannte Sperrgranularität bezieht sich auf den Umfang dessen, was Sie sperren möchten.

Wenn Sie beispielsweise zu Hause auf die Toilette gehen, müssen Sie nur das Badezimmer abschließen. Sie müssen nicht das gesamte Haus abschließen, um zu verhindern, dass Familienmitglieder das Badezimmer betreten.

Was ist eine sinnvolle Sperrgranularität?

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 der Verriegelung verfeinert. Solange Sie beim Duschen die Badezimmertür schließen, können andere trotzdem hineingehen und sich die Hände waschen. Wenn die verschiedenen Funktionsbereiche bei der ursprünglichen Gestaltung des Badezimmers nicht unterteilt und isoliert werden, kann die maximale Nutzung der Badezimmerressourcen nicht erreicht 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 weist die folgenden Merkmale auf:

1. Die geringste Wahrscheinlichkeit eines Sperrenkonflikts 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. Geringer 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 Sperrkonflikts ist hoch und die Parallelität ist gering

3.3 Seitensperre

Die Sperre auf Seitenebene ist eine einzigartige Sperrstufe in MySQL, die nicht gefunden wird 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.

GroßJa

Zeilensperre

Tabellensperre

Seitensperre

Granularität sperren

Klein

Groß

Zwischen den beiden

Verriegelungseffizienz

Langsam

Schnell

Konfliktwahrscheinlichkeit zwischen den beiden Leistungsaufwand

Klein

Zwischen den zwei

Ist es ein Stillstand

Nein

Ja

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 ...

• 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 ...

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 Gemeinsame Sperren und exklusive Sperren

Geteilte Sperren

(Shared Locks) können auch als Lesesperren bezeichnet werden, abgekürzt als

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

(Exklusive Sperren), können auch als exklusive Sperren oder Schreibsperren bezeichnet werden, die als

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 der Sperre analysieren: Wenn es Transaktion A und Transaktion B gibt

Transaktion A erwirbt die S-Sperre eines Datensatzes und zu diesem Zeitpunkt möchte Transaktion B auch die S-Sperre des Datensatzes erwerben, Dann kann auch Transaktion B die Sperre erwerben, was bedeutet, dass Transaktion A und Transaktion B gleichzeitig die S-Sperre des Datensatzes halten.

• 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.

4.2 Intention Lock

Intention Shared Lock

(Intention Shared Lock), auch als

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

(Intention Exclusive Lock), auch als

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 Zeilensperren für mehrere dieser Datensätze aufweist, muss Transaktion B der Tabelle Sperren auf Tabellenebene hinzufügen. Wenn keine beabsichtigte Sperre vorhanden ist, klicken Sie auf „Suchen“. ob diese 100 Millionen Datensätze in der Tabelle gesperrt sind. 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. Wenn Transaktion B die Tabelle aktualisiert, muss sie nicht wirklich wissen, welche Zeile gesperrt ist. Sie muss lediglich wissen, dass eine Zeile ohnehin gesperrt ist. Um es ganz klar auszudrücken: Die Hauptfunktion von Absichtssperren besteht darin, den Widerspruch zwischen Zeilensperren und Tabellensperren aufzulösen. Sie können zeigen, dass

eine Transaktion eine Sperre für eine bestimmte Zeile hält oder sich darauf vorbereitet, die Sperre aufrechtzuerhalten. Kompatibilität verschiedener Schlösser auf

Tabellenebene

:

S

ISKompatibel KompatibelNicht kompatibelKompatibelKompatibelNicht kompatibelNicht KompatibelISNicht kompatibel

X

IX

S

Nicht kompatibel

IS

Nicht kompatibel

Nicht kompatibel

Nicht kompatibel

Kompatibel

Kompatibel

Nicht kompatibel

4.3 Sperren für Lesevorgänge

Für MySQL-Lesevorgänge gibt es zwei Möglichkeiten zum Sperren.

1️⃣ SELECT * FROM table LOCK IN SHARE MODE

Wenn die aktuelle Transaktion diese Anweisung ausführt, fügt sie S-Sperren zu den Datensätzen hinzu, die sie liest, sodass andere Transaktionen weiterhin S-Sperren für diese Datensätze erwerben können (Für Andere Transaktionen verwenden beispielsweise auch die Anweisung SELECT ... LOCK IN SHARE MODE, um diese Datensätze zu lesen), können jedoch die X-Sperren dieser Datensätze nicht erhalten (verwenden Sie beispielsweise die Anweisung SELECT .. . FOR UPDATE-Anweisung zum Lesen dieser Datensätze oder zum direkten Ändern dieser Datensätze). SELECT ... LOCK IN SHARE MODE 语句来读取这些记录），但是不能获取这些记录的 X 锁（比方说使用 SELECT ... FOR UPDATE 语句来读取这些记录，或者直接修改这些记录）。

如果别的事务想要获取这些记录的 X 锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的 S 锁释放掉

2️⃣ SELECT FROM table FOR UPDATE

如果当前事务执行了该语句，那么它会为读取到的记录加 X 锁，这样既不允许别的事务获取这些记录的 S 锁（比方说别的事务使用 SELECT ... LOCK IN SHARE MODE 语句来读取这些记录），也不允许获取这些记录的 X 锁（比如说使用 SELECT ... FOR UPDATEWenn andere Transaktionen die X-Sperren dieser Datensätze erwerben möchten, werden sie blockiert, bis die S-Sperren für diese Datensätze freigegeben werden, nachdem die aktuelle Transaktion festgeschrieben wurde Wenn diese Anweisung eingegeben wird, werden den gelesenen Datensätzen X-Sperren hinzugefügt. Dadurch können andere Transaktionen die S-Sperren dieser Datensätze nicht erhalten (andere Transaktionen verwenden beispielsweise SELECT... LOCK IN SHARE MODE -Anweisung, um diese Datensätze zu lesen), noch ist es zulässig, die X-Sperre dieser Datensätze zu erhalten (verwenden Sie beispielsweise die <code>SELECT ... FOR UPDATE-Anweisung, um diese Datensätze zu lesen oder direkt zu ändern diese Aufzeichnungen).

Wenn andere Transaktionen die S- oder

4.4 Sperren für Schreibvorgänge

Für MySQL-Schreibvorgänge werden am häufigsten DELETE, UPDATE und INSERT verwendet. Implizites Sperren, automatisches Sperren und Entsperren.

1️⃣ DELETE

Der Vorgang zum Durchführen einer DELETE-Operation für einen Datensatz besteht darin, zuerst den Datensatz im B+-Baum zu suchen, dann die X-Sperre des Datensatzes zu erhalten und dann den Löschmarkierungsvorgang auszuführen. Wir können diesen Prozess des Auffindens der Position des zu löschenden Datensatzes im B+-Baum auch als gesperrten Lesevorgang betrachten, um die X-Sperre zu erhalten.

2️⃣ EINFÜGEN

Normalerweise ist der Vorgang des Einfügens eines neuen Datensatzes nicht gesperrt, um diesen neu eingefügten Datensatz vor der Verwendung durch andere zu schützen, bevor der Transaktionszugriff ausgeführt wird.

3️⃣ UPDATE

Beim Durchführen einer UPDATE-Operation für einen Datensatz gibt es drei Situationen:

① Wenn der Schlüsselwert des Datensatzes nicht geändert wurde und der von der aktualisierten Spalte belegte Speicherplatz sich vorher und nachher nicht geändert hat Ändern Sie dann zuerst den Speicherort dieses Datensatzes im B + -Baum, erhalten Sie dann die X-Sperre des Datensatzes und führen Sie schließlich Änderungsvorgänge am Speicherort des ursprünglichen Datensatzes durch. Tatsächlich können wir diesen Prozess des Auffindens der Position des zu ändernden Datensatzes im B+-Baum auch als gesperrten Lesevorgang betrachten, um die X-Sperre zu erhalten.

② Wenn der Schlüsselwert des Datensatzes nicht geändert wurde und sich der von mindestens einer aktualisierten Spalte belegte Speicherplatz vor und nach der Änderung geändert hat, suchen Sie zuerst die Position des Datensatzes im B+-Baum und rufen Sie dann das X ab Sperren Sie den Datensatz, löschen Sie den Datensatz vollständig (dh verschieben Sie ihn vollständig in die Müllliste) und fügen Sie schließlich einen neuen Datensatz ein. Dieser Vorgang zum Auffinden der Position des zu ändernden Datensatzes im B+-Baum wird als gesperrter Lesevorgang zum Erhalten der X-Sperre betrachtet. Der neu eingefügte Datensatz wird durch die implizite Sperre geschützt, die durch die INSERT-Operation bereitgestellt wird.

③ Wenn der Schlüsselwert des Datensatzes geändert wird, entspricht dies dem Ausführen einer DELETE-Operation für den ursprünglichen Datensatz und der anschließenden Ausführung einer INSERT-Operation. Der Sperrvorgang muss gemäß den Regeln von DELETE und INSERT ausgeführt werden.

PS: Warum können andere Transaktionen trotzdem gelesen werden, wenn die Schreibsperre gesperrt ist?

Da InnoDB über einen MVCC-Mechanismus (Multi-Version Concurrency Control) verfügt, können Snapshot-Lesevorgänge verwendet werden, ohne blockiert zu werden.

4. Sperrgranularitätsklassifizierung

Was ist Sperrgranularität? Die sogenannte Sperrgranularität bezieht sich auf den Umfang dessen, was Sie sperren möchten.

Wenn Sie beispielsweise zu Hause auf die Toilette gehen, müssen Sie nur das Badezimmer abschließen. Sie müssen nicht das gesamte Haus abschließen, um zu verhindern, dass Familienmitglieder das Badezimmer betreten.

Was ist eine sinnvolle Sperrgranularität?

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 der Verriegelung verfeinert. Solange Sie beim Duschen die Badezimmertür schließen, können andere trotzdem hineingehen und sich die Hände waschen. Wenn die verschiedenen Funktionsbereiche bei der ursprünglichen Gestaltung des Badezimmers nicht unterteilt und isoliert werden, kann die maximale Nutzung der Badezimmerressourcen nicht erreicht werden.

In ähnlicher Weise gibt es auch in MySQL eine Sperrgranularität. Normalerweise in drei Typen unterteilt:

Zeilensperren, Tabellensperren und Seitensperren.

4.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:

• 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 gering. Je höher das Geschlecht.

• 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.

• Wird einen Deadlock erzeugen

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

4.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:

• Es hat einen geringen Overhead und schnelles Sperren

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

• 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.

• 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.

ZeilensperreTabellensperreGranularität sperrenKleinGroßZwischen den beiden VerriegelungseffizienzLangsamSchnellKonfliktwahrscheinlichkeit zwischen den beiden LeistungsaufwandGroßKlein Zwischen den zweiIst es ein StillstandJaNeinJa

5. 算法实现分类

对于上面的锁的介绍，我们实际上可以知道，主要区分就是在锁的粒度上面，而 InnoDB 中用的锁就是行锁，也叫记录锁，但是要注意，这个记录指的是通过给索引上的索引项加锁。

InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决 定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

同时当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁。

不过即使是行锁，InnoDB 里也是分成了各种类型的。换句话说即使对同一条记录加行锁，如果类型不同，起到的功效也是不同的。通常有以下几种常用的行锁类型。

5.1 Record Lock

记录锁，单条索引记录上加锁。

Record Lock 锁住的永远是索引，不包括记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

记录锁是有 S 锁和 X 锁之分的，当一个事务获取了一条记录的 S 型记录锁后，其他事务也可以继续获取该记录的 S 型记录锁，但不可以继续获取 X 型记录锁；当一个事务获取了一条记录的 X 型记录锁后，其他事务既不可以继续获取该记录的 S 型记录锁，也不可以继续获取 X 型记录锁。

5.2 Gap Locks

间隙锁，对索引前后的间隙上锁，不对索引本身上锁。

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有问题，就是事务在第一次执行读取操作时，那些幻影记录尚 不存在，我们无法给这些幻影记录加上记录锁。所以我们可以使用间隙锁对其上锁。

如存在这样一张表：

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);

如下：

开启一个事务 A：

BEGIN;

SELECT * FROM test WHERE number = 3 FOR UPDATE;

此时，会对((1,1),(5,3))和((5,3),(7,8))之间上锁。

如果此时在开启一个事务 B 进行插入数据，如下：

BEGIN;

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

结果如下：

为什么不能插入？因为记录(2,2)要 插入的话，在索引 number上，刚好落在((1,1),(5,3))和((5,3),(7,8))之间，是有锁的，所以不允许插入。 如果在范围外，当然是可以插入的，如：

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

5.3 Next-Key Locks

next-key locks 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合，包括记录本身，每个 next-key locks 是前开后闭区间，也就是说间隙锁只是锁的间隙，没有锁住记录行，next-key locks 就是间隙锁基础上锁住右边界行。

默认情况下，InnoDB 以 REPEATABLE READ 隔离级别运行。在这种情况下，InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描，这可以防止幻读的发生。

6. 乐观锁和悲观锁

乐观锁和悲观锁其实不算是具体的锁，而是一种锁的思想，不仅仅是在 MySQL 中体现，常见的 Redis 等中间件都可以应用这种思想。

6.1 乐观锁

所谓乐观锁，就是持有乐观的态度，当我们更新一条记录时，假设这段时间没有其他人来操作这条数据。

实现乐观锁常见的方式

常见的实现方式就是在表中添加 version字段，控制版本号，每次修改数据后+1。

在每次更新数据之前，先查询出该条数据的 version版本号，再执行业务操作，然后在更新数据之前在把查到的版本号和当前数据库中的版本号作对比，若相同，则说明没有其他线程修改过该数据，否则作相应的异常处理。

6.2 悲观锁

所谓悲观锁，就是持有悲观的态度，一开始就假设改数据会被别人修改。

悲观锁的实现方式有两种

共享锁（读锁）和排它锁（写锁），参考上面。

7. 死锁

是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统 处于死锁状态或系统产生了死锁。

产生的条件

• 互斥条件：一个资源每次只能被一个进程使用；
• 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；
• 不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺；
• 循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系。

MySQL 中其实也是一样的，如下还是这样一张表：

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');

按照如下顺序执行：

Seitensperre

	A	B
①	BEGIN
②		BEGIN
③	SELECT * FROM user WHERE name='a' FOR UPDATE
④		SELECT * FROM user WHERE name='b' FOR UPDATE
⑤	SELECT * FROM user WHERE name='b' FOR UPDATE
⑥		SELECT * FROM user WHERE name='a' FOR UPDATE

1、开启 A、B 两个事务；

2、首先 A 先查询name='a'的数据，然后 B 也查询name='b'的数据；

3、在 B 没释放锁的情况下，A 尝试对 name='b'的数据加锁，此时会阻塞；

4、若此时，事务 B 在没释放锁的情况下尝试对 name='a'的数据加锁，则产生死锁。

此时，MySQL 检测到了死锁，并结束了 B 中事务的执行，此时，切回事务 A，发现原本阻塞的 SQL 语句执行完成了。可通过show engine innodb status \G查看死锁。

如何避免

从上面的案例可以看出，死锁的关键在于：两个(或以上)的 Session 加锁的顺序不一致，所以我们在执行 SQL 操作的时候要让加锁顺序一致，尽可能一次性锁定所需的数据行。

【相关推荐：mysql视频教程】

Das obige ist der detaillierte Inhalt vonMySQL lernt, über Sperren und Klassifizierung zu sprechen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!