Einführung in Sperren in MYSQL

Wie im Datenbanksperrmechanismus eingeführt, können Datenbanksperren in DBMS je nach Sperre in Sperren auf Zeilenebene (INNODB-Engine), Sperren auf Tabellenebene (MYISAM-Engine) und Sperren auf Seitenebene (BDB-Engine) unterteilt werden Granularität.

Sperre auf Zeilenebene

Sperre auf Zeilenebene ist die detaillierteste Sperre in MySQL, was bedeutet, dass nur die Zeile des aktuellen Vorgangs 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 werden in gemeinsame Sperren und exklusive Sperren unterteilt.

Funktionen

Hoher Overhead, langsame Sperrgranularität ist am geringsten, die Wahrscheinlichkeit von Sperrkonflikten ist am höchsten und die Parallelität ist am höchsten.

Sperre auf Tabellenebene

Sperre auf Tabellenebene ist die Sperre mit der größten Granularität in MySQL. Sie bedeutet, dass die gesamte Tabelle des aktuellen Vorgangs gesperrt werden muss und wird von den meisten MySQL-Engines unterstützt. Die am häufigsten verwendeten MYISAM und INNODB unterstützen das Sperren auf Tabellenebene. Sperren auf Tabellenebene werden in gemeinsame Lesesperren für Tabellen (gemeinsame Sperren) und exklusive Schreibsperren für Tabellen (exklusive Sperren) unterteilt.

Funktionen

Geringer Overhead, schnelles Sperren; große Sperrgranularität, höchste Wahrscheinlichkeit von Sperrkonflikten und geringste Parallelität.

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. Daher wurde eine kompromittierte Seitenebene eingeführt, die jeweils eine Gruppe benachbarter Datensätze sperrt. BDB unterstützt Sperren auf Seitenebene

Funktionen

Der Overhead und die Sperrzeit sind zwischen Tabellensperren und Zeilensperren begrenzt; die Sperrgranularität ist zwischen Tabellensperren und Zeilensperren begrenzt im Allgemeinen

MySQLs häufig verwendeter Speicher-Engine-Sperrmechanismus

MyISAM und MEMORY verwenden Sperren auf Tabellenebene (Sperren auf Tabellenebene)

BDB verwendet Seitensperren (Seite - Sperren auf Zeilenebene) oder Sperren auf Tabellenebene, die Standardeinstellung ist Seitensperren

InnoDB unterstützt Sperren auf Zeilenebene (Sperren auf Zeilenebene) und Sperren auf Tabellenebene, die Standardeinstellung ist Sperren auf Zeilenebene

---

Zeilensperren und Tabellensperren in Innodb

Wie bereits erwähnt, unterstützt die Innodb-Engine sowohl Zeilensperren als auch Tabellensperren, also wann wird die gesamte Datei gesperrt werden? Wann sperrt eine Tabelle nur eine Zeile?

InnoDB-Zeilensperre wird durch Sperren der Indexeinträge im Index implementiert. Dies unterscheidet sich von MySQL und Oracle, die durch Sperren der entsprechenden Datenzeilen im Datenblock implementiert werden. Die Zeilensperren-Implementierungsfunktion von InnoDB bedeutet, dass InnoDB Sperren auf Zeilenebene nur verwendet, wenn Daten über Indexbedingungen abgerufen werden. Andernfalls verwendet InnoDB Tabellensperren!

In praktischen Anwendungen sollte dieser Funktion der InnoDB-Zeilensperre besondere Aufmerksamkeit gewidmet werden, da sie sonst zu einer großen Anzahl von Sperrkonflikten führen und somit die Parallelitätsleistung beeinträchtigen kann.

• Bei Abfragen ohne Indexbedingungen verwendet InnoDB Tabellensperren anstelle von Zeilensperren.

• Da die Zeilensperre von MySQL für den Index und nicht für den Datensatz gilt, obwohl auf Datensätze verschiedener Zeilen zugegriffen wird, kommt es bei Verwendung desselben Indexschlüssels zu einem Sperrkonflikt . Bitte beachten Sie dies bei der Gestaltung Ihrer Bewerbung.

• Wenn eine Tabelle mehrere Indizes hat, können verschiedene Transaktionen unterschiedliche Indizes verwenden, um verschiedene Zeilen zu sperren. Unabhängig davon, ob ein Primärschlüsselindex, ein eindeutiger Index oder ein gewöhnlicher Index verwendet wird, verwendet InnoDB Zeilensperren um Daten zu sperren.

• Selbst wenn in der Bedingung ein Indexfeld verwendet wird, bestimmt MySQL, ob der Index zum Abrufen von Daten verwendet werden soll, indem er die Kosten verschiedener Ausführungspläne beurteilt Der Tabellenscan ist effizienter. Bei einigen sehr kleinen Tabellen werden beispielsweise keine Indizes verwendet. 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, um sicherzustellen, dass der Index tatsächlich verwendet wird.

---

Sperren und Deadlocks auf Zeilenebene

In MyISAM treten keine Deadlocks auf, da MyISAM immer alle benötigten Sperren auf einmal erhält. Entweder erfüllen sie alle oder warte auf sie alle. 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 Nicht-Primärschlüsselindex und sperren Sie ihn dann. Während UPDATE- und DELETE-Vorgängen sperrt MySQL nicht nur alle von der WHERE-Bedingung gescannten Indexdatensätze, sondern auch benachbarte Schlüsselwerte, was als sogenannte Next-Key-Sperre bezeichnet wird.

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.

---

Es gibt viele Möglichkeiten, einen Deadlock zu vermeiden.

1 Wenn verschiedene Programme gleichzeitig auf mehrere Tabellen zugreifen, sollten Sie sich auf die gleiche Reihenfolge einigen Tabelle kann das Risiko eines Deadlocks erheblich reduzieren.

2. Versuchen Sie, alle erforderlichen Ressourcen gleichzeitig zu sperren, um die Wahrscheinlichkeit eines Deadlocks zu verringern

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

Sperren auf Zeilenebene haben die feinste Sperrgranularität In MySQL kann eine Sperre auf Zeilenebene Konflikte bei Datenbankvorgängen erheblich reduzieren. Sperren auf Zeilenebene werden in zwei Typen unterteilt: gemeinsame Sperren und exklusive Sperren. In diesem Artikel werden die Konzepte, Verwendung und Vorsichtsmaßnahmen von gemeinsam genutzten Sperren und exklusiven Sperren ausführlich vorgestellt.

Gemeinsame Sperre (Share Lock)

Gemeinsame Sperre, auch Lesesperre genannt, ist eine Sperre, die durch Lesevorgänge erstellt wird. Andere Benutzer können die Daten gleichzeitig lesen, aber keine Transaktion kann die Daten ändern (eine exklusive Sperre für die Daten erwerben), 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.

Verwendung

SELECT ... LOCK IN SHARE MODE;

Fügen Sie LOCK IN SHARE MODE nach der Abfrageanweisung hinzu. MySQL gibt jede Zeile im Abfrageergebnis frei. Sperren, wenn Kein anderer Thread verwendet eine exklusive Sperre für eine Zeile im Abfrageergebnissatz. Er kann erfolgreich eine gemeinsame Sperre beantragen, andernfalls wird er blockiert. Andere Threads können Tabellen auch mithilfe gemeinsamer Sperren lesen, und diese Threads lesen dieselbe Datenversion.

Exklusive Sperre (eXclusive Sperre)

Exklusive Sperre wird auch Schreibsperre genannt. Wenn Transaktion T eine exklusive Sperre zu Daten A hinzufügt, können andere Transaktionen keine Blockade mehr zu A hinzufügen. . Transaktionen mit exklusiven Sperren können Daten sowohl lesen als auch ändern.

Verwendung

SELECT ... FOR UPDATE;

Fügen Sie nach der Abfrageanweisung eine exklusive Sperre zu jeder Zeile im Abfrageergebnis hinzu ist nein Wenn andere Threads eine exklusive Sperre für eine beliebige Zeile im Abfrageergebnissatz verwenden, können sie erfolgreich eine exklusive Sperre beantragen, andernfalls werden sie blockiert.

Absichtssperre

Absichtssperre ist eine Sperre auf Tabellenebene, die hauptsächlich dazu dient, die Art der Sperre offenzulegen, die für die nächste Zeile in einer Transaktion angefordert wird. Zwei Tabellensperren in InnoDB:

Intention shared lock (IS): Zeigt an, dass die Transaktion das Hinzufügen einer gemeinsamen Sperre zur Datenzeile vorbereitet, was bedeutet, dass vor dem Hinzufügen einer gemeinsamen Sperre zu einer Datenzeile die IS Die Sperre der Tabelle muss zuerst abgerufen werden

Intention exklusive Sperre (IX): Ähnlich wie oben bedeutet dies, dass die Transaktion sich darauf vorbereitet, der Datenzeile eine exklusive Sperre hinzuzufügen, was darauf hinweist, dass die Transaktion zuerst abgerufen werden muss die IX-Sperre der Tabelle, bevor Sie einer Datenzeile eine exklusive Sperre hinzufügen.

Absichtssperren werden von InnoDB automatisch hinzugefügt und erfordern keinen Benutzereingriff.

Beim Einfügen, Aktualisieren und Löschen fügt InnoDB den beteiligten Daten automatisch exklusive Sperren (X) hinzu. Bei allgemeinen Select-Anweisungen fügt InnoDB keine Sperren hinzu und Transaktionen können wie folgt mit gemeinsam genutzten Sperren angezeigt werden Anweisungen Oder exklusive Sperre.

Gemeinsame Sperre: AUSWÄHLEN ... IM TEILUNGSMODUS SPERREN;

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

Verwandte Empfehlungen:

Die Verbindung zwischen MySQL-Sperren und -Indizes

MySQL-Deadlock- und Protokollanalyse

Methoden zur Implementierung der MySQL-Anweisungssperre

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