Einführung in den MySQL-Datenbanksperrmechanismus

Dieser Artikel bietet Ihnen eine Einführung in den MySQL-Datenbanksperrmechanismus. Ich hoffe, dass er für Freunde hilfreich ist.

Parallelitätskontrolle

• Die Aufgabe der Parallelitätskontrolle im Datenbankverwaltungssystem besteht darin, sicherzustellen, dass bei mehreren Transaktionen auf dieselben Daten in der Datenbank zugegriffen wird Gleichzeitig wird die Isolation und Einheit der Transaktionen sowie die Einheit der Datenbank nicht zerstört.

• Blockierung, Zeitstempel, optimistische Parallelitätskontrolle und pessimistische Parallelitätskontrolle sind die wichtigsten technischen Mittel zur Parallelitätskontrolle

Sperre

Wenn gleichzeitige Transaktionen gleichzeitig auf eine Ressource zugreifen, kann dies zu Dateninkonsistenzen führen. Daher ist ein Mechanismus zur Sequenzierung des Datenzugriffs erforderlich, um die Konsistenz der Datenbankdaten sicherzustellen. Sperren sind einer der Mechanismen (empfohlenes Tutorial: MySQL-Tutorial)

Klassifizierung von Sperren

• Nach Operationen unterteilt, kann es unterteilt werden in DML-Sperre, DDL-Sperre

• Unterteilt nach Sperrgranularität kann es in Sperre auf Tabellenebene, Sperre auf Zeilenebene und Sperre auf Seitenebene unterteilt werden Sperre (MySQL)

• Je nach Sperrstufe kann es in gemeinsame Sperre und exklusive Sperre

• unterteilt werden

  Je nach Sperrmethode kann es in automatische Sperre und Anzeigesperre

• unterteilt werden in optimistische Sperre und pessimistische Sperre

DML-Sperren werden zum Schutz der Datenintegrität verwendet, einschließlich Sperren auf Zeilenebene (TX-Sperren) und Sperren auf Tabellenebene ( TM-Schlösser). DDL-Sperren werden verwendet, um die Struktur von Datenbankobjekten zu schützen, beispielsweise strukturelle Definitionen von Tabellen, Indizes usw., einschließlich exklusiver DDL-Sperren, gemeinsam genutzter DDL-Sperren und unterbrechbarer Parsing-Sperren

Sperren auf Zeilenebene

• Sperre auf Zeilenebene ist die detaillierteste Sperre in MySQL, was bedeutet, dass nur die aktuell bearbeitete Zeile gesperrt ist. Sperren auf Zeilenebene können Konflikte bei Datenbankvorgängen erheblich reduzieren. Seine Sperrgranularität ist am kleinsten, aber der Sperraufwand ist auch am größten. Sperren auf Zeilenebene sind in gemeinsame Sperren und exklusive Sperren

• Merkmale: Hoher Overhead, langsame Sperrgranularität möglich; Die kleinste, die geringste Wahrscheinlichkeit eines Sperrenkonflikts und der höchste Grad an Parallelität

Sperre auf Tabellenebene

• Sperre auf Tabellenebene ist die Größte Sperrgranularität in MySQL Eine Art Sperre bedeutet, die gesamte Tabelle des aktuellen Vorgangs zu sperren. Sie ist einfach zu implementieren, verbraucht weniger Ressourcen und wird von den meisten MySQL-Engines unterstützt. Die am häufigsten verwendeten MYISAM und INNODB unterstützen das Sperren auf Tabellenebene. Die Sperre auf Tabellenebene ist unterteilt in gemeinsame Tabellenlesesperre (gemeinsame Sperre) und exklusive Tabellenschreibsperre (exklusive Sperre)

• Eigenschaften: Geringer Overhead, die Sperrung erfolgt schnell; die Sperrgranularität ist groß, die Wahrscheinlichkeit von Sperrkonflikten ist am höchsten und die Parallelität ist am niedrigsten

Sperre auf Seitenebene

• Sperre auf Seitenebene ist eine Sperre in MySQL, deren Sperrgranularität zwischen Sperre auf Zeilenebene und Sperre auf Tabellenebene liegt. Sperren auf Tabellenebene sind schnell, weisen jedoch viele Konflikte auf. Sperren auf Zeilenebene weisen wenige Konflikte auf, sind jedoch langsam. Mit der Sperre auf Seitenebene wird eine benachbarte Gruppe von Datensätzen gesperrt. BDB unterstützt Sperren auf Seitenebene

Mysqls gemeinsamer Speicher-Engine-Sperrmechanismus

• MyISAM und MEMORY verwenden Sperren auf Tabellenebene

• BDB verwendet Sperren auf Seitenebene oder Sperren auf Tabellenebene.

• InnoDB unterstützt Sperren auf Zeilenebene Standard sind Sperren auf Zeilenebene

InnoDB-Zeilensperren und Tabellensperren

Die InnoDB-Engine unterstützt sowohl Zeilensperren als auch Tabellensperren. Wann wird also die gesamte Tabelle gesperrt? und wann wird eine Reihe gesperrt? ?

• InnoDB-Zeilensperre wird durch Sperren der Indexelemente im Index erreicht. Dies unterscheidet sich von MySQL und Oracle, die die entsprechenden Datenzeilen im Datenblock sperren. Diese Zeilensperren-Implementierungsfunktion von InnoDB bedeutet: InnoDB verwendet Sperren auf Zeilenebene nur, wenn Daten über Indexbedingungen abgerufen werden, andernfalls verwendet InnoDb Tabellensperren

• Praktische Anwendung , Sie sollten auf diese Funktion der InnoDB-Zeilensperre achten, da sie sonst leicht zu einer großen Anzahl von Sperrkonflikten führt und sich somit auf die Parallelitätsleistung auswirkt

• Bei Abfragen ohne Indexbedingungen , InnoDB verwendet Es ist eine Tabellensperre, keine Zeilensperre

• Da die Zeilensperre von MySQL eine Sperre für den Index und keine Sperre für den Datensatz ist, obwohl auf Datensätze verschiedener Zeilen zugegriffen wird, Wenn die Verwendung von Schlüsseln desselben Index zu Sperrkonflikten führt.

• Wenn eine Tabelle mehrere Indizes hat, können verschiedene Transaktionen unterschiedliche Indizes verwenden, um unterschiedliche Zeilen zu sperren. Darüber hinaus ist InnoDB unabhängig davon, ob ein Primärschlüsselindex, ein eindeutiger Index oder ein normaler Index verwendet wird Wird Zeilensperren verwenden, um Daten zu sperren

• Selbst wenn das Indexfeld in der Bedingung verwendet wird, bestimmt MySQL anhand der Kosten, ob der Index zum Abrufen der Daten verwendet werden soll Verschiedene Zeilenpläne. Wenn MySQL der Meinung ist, dass ein vollständiger Tabellenscan effizienter ist, beispielsweise für einige sehr kleine Tabellen, verwendet InnoDB keine Indizes. In diesem Fall verwendet InnoDB Tabellensperren anstelle von Zeilensperren. Vergessen Sie daher bei der Analyse von Sperrkonflikten nicht, den SQL-Ausführungsplan

  zu überprüfen

Sperren und Deadlocks auf Zeilenebene

• MyISAM erzeugt keine Deadlocks, da MyISAM immer alles, was es benötigt, auf einmal erhält. Lock, entweder alles erfüllt oder alle warten. In InnoDB werden Sperren schrittweise erworben, was zu einem Deadlock führen kann

• In MySQL sperren Sperren auf Zeilenebene nicht direkt Datensätze, sondern sperren Indizes. Indizes werden in Primärschlüsselindizes und Nicht-Primärschlüsselindizes unterteilt. Wenn eine SQL-Anweisung mit dem Primärschlüsselindex arbeitet, sperrt MySQL den Primärschlüsselindex Sperren Sie dann den relevanten Primärschlüsselindex. Während Aktualisierungs- und Löschvorgängen sperrt MySQL nicht nur alle von der Where-Bedingung gescannten Indexdatensätze, sondern auch benachbarte Schlüsselwerte, die sogenannte Next-Key-Sperre

• Deadlock : Wenn zwei Transaktionen gleichzeitig ausgeführt werden, sperrt eine den Primärschlüsselindex und wartet auf andere verwandte Indizes. Der andere sperrt den Nicht-Primärschlüsselindex und wartet auf den Primärschlüsselindex. Es kommt zu einem Deadlock.

• Nachdem ein Deadlock aufgetreten ist, kann InnoDB ihn im Allgemeinen erkennen und veranlassen, dass eine Transaktion die Sperre aufhebt und zurücksetzt und eine andere die Sperre erhält, um die Transaktion abzuschließen

Deadlock vermeiden

• Wenn verschiedene Programme gleichzeitig auf mehrere Tabellen zugreifen, versuchen Sie, den Zugriff auf die Tabellen in derselben Reihenfolge zu vereinbaren, was die Wahrscheinlichkeit eines Deadlocks erheblich verringern kann

• Versuchen Sie in derselben Transaktion, alle erforderlichen Ressourcen gleichzeitig zu sperren, um die Wahrscheinlichkeit eines Deadlocks zu verringern

• Für Geschäftsteile, die sehr anfällig für Deadlocks sind, Sie können versuchen, eine verbesserte Sperrgranularität zu verwenden, um Deadlocks durch Sperren auf Tabellenebene zu reduzieren

Gemeinsame Sperren und exklusive Sperren

• Sperren auf Zeilenebene sind in MySQL Mit der feinsten Sperrgranularität können Sperren auf Zeilenebene Konflikte im Datenbankbetrieb erheblich reduzieren. Sperren auf Zeilenebene werden in gemeinsame Sperren und exklusive Sperren unterteilt

1. Gemeinsame Sperren werden auch

Lesesperren genannt

ist die durch den Lesevorgang erstellte Sperre. Andere Benutzer können die Daten gleichzeitig lesen, aber keine Transaktion kann die Daten ändern, bis alle gemeinsamen Sperren aufgehoben wurden.

• Wenn Transaktion T eine gemeinsame Sperre zu Daten A hinzufügt, können andere Transaktionen nur gemeinsame Sperren zu A und keine exklusiven Sperren hinzufügen. Transaktionen, denen gemeinsame Sperren gewährt werden, können nur Daten lesen und keine Daten ändern

• Wenn Transaktion T eine gemeinsame Sperre zu Daten A hinzufügt und dann die Daten ändert, können andere Transaktionen keine Daten abrufen die Freigabe. Wenn mehrere Transaktionen gemeinsame Sperren für dieselben Daten erwerben, kann keine Transaktion die Daten ändern

• Verwendung:

  SSELECT ... LOCK IN SHARE MODE

• Fügen Sie LOCK IN SHARE MODE

  nach der Abfrageanweisung hinzu, um jeder Zeile im Abfrageergebnis eine gemeinsame Sperre hinzuzufügen Wenn eine Zeile eine exklusive Sperre verwendet, kann sie erfolgreich eine gemeinsame Sperre beantragen, andernfalls wird sie blockiert. Andere Threads können Tabellen auch mithilfe gemeinsamer Sperren lesen, und diese Threads lesen dieselbe Datenversion

  2. Exklusive Sperre

Die exklusive Sperre wird auch als Schreibsperre bezeichnet. Wenn die Transaktion T den Daten A eine exklusive Sperre hinzufügt, können andere Transaktionen keine Blockade zu A hinzufügen. Eine Transaktion, die eine exklusive Sperre erhält, kann Daten sowohl lesen als auch ändern

• Verwendung:

  SELECT ... FOR UPDATE
. Fügen Sie
• FOR UPDATE

  nach der Abfrageanweisung hinzu, und MySQL fügt jeder Zeile im Abfrageergebnis eine exklusive Sperre hinzu. Wenn kein anderer Thread eine exklusive Sperre für eine Zeile des Abfrageergebnissatzes verwendet, können Sie die Anwendung erfolgreich durchführen für eine exklusive Sperre. Andernfalls wird sie blockiert.3. Die Absichtssperre ist eine Sperre auf Tabellenebene um Folgendes in einer Transaktion anzuzeigen: Die Art der Sperre, die für eine Zeile angefordert wird. Zwei Tabellensperren in InnoDB:

Intention Shared Lock (IS): Zeigt an, dass die Transaktion sich darauf vorbereitet, der Datenzeile eine gemeinsame Sperre hinzuzufügen, d. h. a Eine gemeinsame Sperre wird zu einer Datenzeile hinzugefügt. Die IS-Sperre der Tabelle muss zuvor erhalten werden.

• Absichtliche exklusive Sperre (IX): Zeigt an, dass die Transaktion das Hinzufügen einer exklusiven Sperre zu der Datenzeile vorbereitet Datenzeile, die angibt, dass die Transaktion zuerst eine exklusive Sperre zu einer Datenzeile hinzufügen muss

• Die Absichtssperre wird von InnoDB automatisch hinzugefügt Benutzereingriff erforderlich

• Zusammenfassung
• Beim Einfügen, Aktualisieren und Löschen fügt InnoDB den beteiligten Daten automatisch exklusive Sperren hinzu.

  Bei allgemeinen Select-Anweisungen , InnoDB fügt keine Sperren hinzu und Transaktionen können explizit über die folgenden Anweisungen hinzugefügt werden: Gemeinsame Sperre oder exklusive Sperre

Geteilte Sperre: Wählen Sie ... SPERREN IM FREIGABEMODUS

Exklusive Sperre: AUSWÄHLEN ... FÜR UPDATE

Das obige ist der detaillierte Inhalt vonEinführung in den MySQL-Datenbanksperrmechanismus. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!