Einführung in die innoDB-Sperre in MySQL

Warum müssen Sie die Transaktion starten, bevor Sie InnoDB sperren?

Die Freigabe der Innodb-Sperre erfolgt, nachdem die Transaktion festgeschrieben/zurückgesetzt wurde automatisch freigegeben werden. Standardmäßig aktiviert innodb autocommit=1 die automatische Übermittlung

Der Unterschied zwischen Sperren, die Indizes verwenden und nicht, die Indizes für Abrufbedingungen verwenden:

Wenn Abrufbedingungen Indizes haben, werden bestimmte Zeilen aktiviert gesperrt sein.

Wenn die Suchbedingung nicht verwendet wird, wird ein vollständiger Tabellenscan durchgeführt, um alle Zeilen (einschließlich nicht vorhandener Datensätze) zu sperren.

Lesesperre:

Lesesperre ist Geteilt oder nicht blockierend. Mehrere Benutzer können gleichzeitig dieselbe Ressource lesen, ohne sich gegenseitig zu stören.

Schreibsperre:

Die Schreibsperre ist exklusiv, was bedeutet, dass eine Schreibsperre andere Schreibsperren und Lesesperren blockiert. Darüber hinaus haben Schreibsperren eine höhere Priorität als Lesesperren, sodass eine Schreibsperranforderung möglicherweise vor der Lesesperrenwarteschlange eingefügt wird, die Lesesperre jedoch nicht vor der Schreibsperre eingefügt werden darf

Tabellensperre:

InnoDB verfügt auch über zwei Tabellensperren: Intention Shared Lock (IS), Intention Exclusive Lock (IX)

Zeilensperre :

　InnoDB implementiert zwei Arten von Sperren auf Zeilenebene, gemeinsame Sperren und exklusive Sperren

Optimistisch Sperre:

Optimistische Sperre, auch optimistische Parallelitätskontrolle genannt, geht davon aus, dass sich gleichzeitige Transaktionen mehrerer Benutzer während der Verarbeitung nicht gegenseitig beeinflussen und jede Transaktion einen Teil ihrer Auswirkungen bewältigen kann, ohne Sperrdaten zu generieren. Bevor Datenaktualisierungen festgeschrieben werden, prüft jede Transaktion zunächst, ob andere Transaktionen die Daten geändert haben, nachdem die Transaktion die Daten gelesen hat. Wenn Aktualisierungen von anderen Transaktionen vorliegen, wird die aktuell festgeschriebene Transaktion zurückgesetzt.

Pessimistische Sperre:

Pessimistische Sperre, auch pessimistische Parallelitätskontrolle genannt. Wenn Transaktion A eine Sperre auf eine bestimmte Datenzeile anwendet und diese Transaktion die Sperre aufhebt, können andere Transaktionen ausgeführt werden Die Sperrkonfliktoperation, die von Transaktion A auferlegte Sperre, wird hier als pessimistische Sperre bezeichnet. Gemeinsame Sperren und exklusive Sperren (Zeilensperren, Lückensperren, Next-Key-Sperren) sind beide pessimistische Sperren

Die Implementierung pessimistischer Sperren und optimistischer Sperren:

Die Implementierung pessimistischer Sperren basiert auf der Datenbank Der bereitgestellte Sperrmechanismus ist implementiert, z. B. „select * from news with id=12“ für die Aktualisierung, während die optimistische Sperre auf der Aufzeichnung der Datenversion beruht, d. h. auf dem Hinzufügen des Versionsnummernfelds zur Tabelle als Schlüsselfaktor dafür, ob die Die Einreichung kann erfolgreich sein.

Gemeinsame Sperre (S):

Eine gemeinsame Sperre wird auch als Lesesperre bezeichnet. Eine Transaktion erwirbt eine gemeinsame Sperre einer Datenzeile, und andere Transaktionen können dies tun Erwerben Sie die gemeinsame Sperre, die der Zeile entspricht, es kann jedoch keine exklusive Sperre erhalten werden. Das heißt, wenn eine Transaktion eine Datenzeile liest, können andere Transaktionen die Datenzeile ebenfalls lesen, aber nicht hinzufügen, löschen oder ändern

Gemeinsame Sperre festlegen: SELECT .... SPERRE IM SHARE-MODUS;

Exklusive Sperre (X):

Exklusive Sperre wird auch als Schreibsperre bezeichnet Eine Datenzeile wird von anderen Transaktionen erfasst. Es können keine anderen Sperren (exklusive Sperren oder gemeinsame Sperren) für die Zeile erworben werden. Das heißt, wenn eine Transaktion eine Datenzeile liest, können andere Transaktionen die Datenzeile nicht hinzufügen, löschen, ändern oder überprüfen

Einstellung einer exklusiven Sperre: AUSWÄHLEN .... FÜR UPDATE

　Hinweis:

• Zur Auswahl Anweisungen fügt innodb keine Sperren hinzu, d. h. mehrere Auswahlvorgänge können gleichzeitig ohne Sperrkonflikte ausgeführt werden, da überhaupt keine Sperren vorhanden sind.

• Bei Einfüge-, Aktualisierungs- und Löschvorgängen fügt innodb den betreffenden Daten automatisch exklusive Sperren hinzu. Nur bei der Abfrageauswahl müssen wir die exklusive Sperre manuell festlegen.

Intention Shared Lock (IS):

Benachrichtigen Sie die Datenbank darüber, welche Sperren als nächstes angewendet werden müssen, und sperren Sie die Tabelle. Wenn Sie eine gemeinsame Sperre für Datensatz A hinzufügen müssen, findet innodb zuerst diese Tabelle, fügt der Tabelle eine gemeinsame Sperre hinzu und fügt dann eine gemeinsame Sperre für Datensatz A hinzu. Das heißt, bevor einer Datenzeile eine gemeinsame Sperre hinzugefügt wird, muss zunächst die IS-Sperre der Tabelle erhalten werden.

Absichts-Exklusivsperre (IX):

Benachrichtigen Sie die Datenbank über die Sperre Als nächstes muss die Tabellensperre angewendet und hinzugefügt werden. Wenn Sie eine exklusive Sperre für Datensatz A hinzufügen müssen, findet innodb zuerst diese Tabelle, fügt der Tabelle absichtlich eine exklusive Sperre hinzu und fügt dann eine gemeinsame Sperre für Datensatz A hinzu. Das heißt, bevor einer Datenzeile eine exklusive Sperre hinzugefügt wird, muss zunächst die IX-Sperre der Tabelle erhalten werden

Der Unterschied zwischen gemeinsam genutzter Sperre und beabsichtigter gemeinsamer Sperre, exklusiver Sperre und beabsichtigter exklusiver Sperre:

• Gemeinsame Sperren und exklusive Sperren, das System fügt unter bestimmten Bedingungen automatisch gemeinsame Sperren oder exklusive Sperren hinzu, oder Sie können gemeinsam genutzte Sperren oder exklusive Sperren manuell hinzufügen.

• Intention Shared Locks und Intention Exclusive Locks werden vom System automatisch hinzugefügt und automatisch freigegeben, und der gesamte Prozess erfordert keinen manuellen Eingriff.

• Gemeinsame Sperren und exklusive Sperren sind sowohl Zeilendatensätze von Sperren als auch beabsichtigte gemeinsame Sperren und beabsichtigte exklusive Sperren, die Tabellen sperren.

Methode zur Sperrenimplementierung:

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 auf den Primärschlüsselindex angewendet wird, sperrt MySQL den Primärschlüsselindex Sperren Sie zuerst den Nicht-Primärschlüsselindex und dann den relevanten Primärschlüsselindex .

InnoDB-Zeilensperren werden durch Sperren von Indexeinträgen implementiert. Wenn kein Index vorhanden ist, sperrt InnoDB Datensätze über einen versteckten Clustered-Index. Das heißt: Wenn die Daten nicht über Indexbedingungen abgerufen werden, sperrt InnoDB alle Daten in der Tabelle, und der tatsächliche Effekt ist der gleiche wie bei der Tabellensperre

Zeilensperren werden in drei Situationen unterteilt:

Datensatzsperre: Sperren Sie das Indexelement, d. h. sperren Sie einen Datensatz.

Lückensperre: Sperren Sie die „Lücke“ zwischen Indexelementen, die Lücke vor dem ersten Datensatz oder die Lücke nach dem letzten Datensatz, d. h. sperren Sie einen Bereich von Datensätzen, mit Ausnahme des Datensatzes selbst

Nächste Tastensperre: Sperren Sie einen Bereich von Datensätzen und schließen Sie den Datensatz selbst ein (eine Kombination aus den beiden oben genannten)

Hinweis: Die Standardstufe von InnoDB ist wiederholbares Lesen (wiederholtes Lesen) Ebene. Der ANSI/IOS-SQL-Standard definiert 4 Transaktionsisolationsstufen: nicht festgeschrieben lesen, festgeschrieben lesen, wiederholbar lesen, serialisierbar

Gap Lock und Der Unterschied zwischen Next-Key Lock:

　Next- Die Tastensperre ist eine Kombination aus Zeilensperre und Lückensperre. Wenn InnoDB den Indexdatensatz scannt, fügt es zunächst eine Zeilensperre zum ausgewählten Indexdatensatz hinzu (Datensatzsperre) und fügt dann eine Lückensperre hinzu (Lückensperre). die Lücken auf beiden Seiten des Indexdatensatzes. Wenn eine Lücke durch Transaktion T1 gesperrt ist, können andere Transaktionen keine Datensätze in diese Lücke einfügen.

Zeilensperren verhindern, dass andere Transaktionen geändert oder gelöscht werden, Lückensperren verhindern das Hinzufügen anderer Transaktionen und die durch die Kombination von Zeilensperren und GAP-Sperren gebildete Next-Key-Sperre löst gemeinsam das Problem des RR-Sektors beim Schreiben von Daten.

Wann Tabellensperren in InnoDB verwendet werden sollten:

InnoDB verwendet aufgrund von Transaktions- und Zeilensperren in den meisten Fällen Sperren auf Zeilenebene oft der Grund, warum wir uns für InnoDB entscheiden, aber in einigen Fällen erwägen wir auch die Verwendung von Sperren auf Tabellenebene

• Wenn die Transaktion die meisten Daten aktualisieren muss, wird die Die Tabelle ist relativ groß. Wenn die Standardzeilensperre verwendet wird, ist sie nicht nur ineffizient, sondern führt auch leicht dazu, dass andere Transaktionen lange warten und Sperrkonflikte verursachen.

• Transaktionen sind komplex und können Deadlocks und Rollbacks verursachen.

Unter InnoDB sollten Sie bei der Verwendung von Tabellensperren die folgenden zwei Punkte beachten.

(1) Obwohl Sie mit LOCK TALBES Sperren auf Tabellenebene zu InnoDB hinzufügen können, muss beachtet werden, dass Tabellensperren nicht von der InnoDB-Speicher-Engine-Schicht verwaltet werden , aber von Der MySQL Server der oberen Ebene ist für verantwortlich. Nur wenn autocommit = 0, innodb_table_lock = 1 (Standardeinstellung) ist, kann die InnoDB-Ebene die von MySQL hinzugefügte Tabellensperre erkennen und MySQL Server kann die von InnoDB hinzugefügte Zeilensperre erkennen In diesem Fall kann InnoDB Deadlocks mit Sperren auf Tabellenebene nur unter dieser Bedingung automatisch erkennen und verarbeiten.

(2) Wenn Sie LOCAK TABLES zum Sperren von InnoDB verwenden, achten Sie darauf, AUTOCOMMIT auf 0 zu setzen, da MySQL die Tabelle sonst nicht vor dem Ende der Transaktion sperrt. Verwenden Sie UNLOCAK TABLES nicht, um die Tabellensperre aufzuheben , da UNLOCK TABLES die Transaktion implizit festschreibt; COMMIT oder ROLLBACK kann die mit LOCAK TABLES hinzugefügte Sperre auf Tabellenebene nicht aufheben. Die richtige Methode ist wie folgt:

Zum Beispiel : Wenn Sie Tabelle t1 schreiben und

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;[do something with tables t1 and here];COMMIT;
UNLOCK TABLES;

aus Tabelle t lesen müssen Deadlock:

Wir sagten, dass es in nicht zu einem Deadlock kommen wird MyISAM, weil MyISAM immer Es geht darum, alle erforderlichen Sperren auf einmal zu erhalten, entweder um sie alle zu erfüllen oder auf sie alle zu warten. In InnoDB werden Sperren schrittweise erworben, was zu einem Deadlock führen kann.

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 Transaktion die Sperre erhält, um die Transaktion abzuschließen. Allerdings kann InnoDB Deadlocks nicht vollständig automatisch erkennen, wenn externe Sperren oder Sperren beteiligt sind. Dies muss durch Festlegen des Parameters innodb_lock_wait_timeout für die Sperrwartezeit behoben werden. Es ist zu beachten, dass dieser Parameter nicht nur zur Lösung des Deadlock-Problems verwendet wird. Wenn der gleichzeitige Zugriff relativ hoch ist und eine große Anzahl von Transaktionen ausgesetzt wird, weil sie die erforderlichen Sperren nicht sofort erhalten können, wird eine große Menge an Computerressourcen beansprucht und schwerwiegende Leistungsprobleme verursachen und sogar die Datenbank zum Absturz bringen. Wir können diese Situation vermeiden, indem wir einen geeigneten Schwellenwert für die Sperrwartezeit festlegen.

Es gibt viele Möglichkeiten, Deadlocks zu vermeiden:

1. Wenn verschiedene Programme gleichzeitig auf mehrere Tabellen zugreifen, versuchen Sie, den Zugriff auf die Tabellen in derselben Reihenfolge zu vereinbaren. Dadurch kann die Wahrscheinlichkeit eines Deadlocks erheblich verringert werden. Wenn zwei Sitzungen in unterschiedlicher Reihenfolge auf die beiden Tabellen zugreifen, ist die Wahrscheinlichkeit eines Deadlocks sehr hoch! Wenn die Zugriffe jedoch in derselben Reihenfolge erfolgen, kann ein Deadlock vermieden werden.

2. Versuchen Sie in derselben Transaktion, 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, die Sperrgranularität zu verbessern und die Möglichkeit von Deadlocks durch Sperren auf Tabellenebene zu verringern.

4. Wenn das Programm Daten stapelweise verarbeitet und die Daten im Voraus sortiert werden, um sicherzustellen, dass jeder Thread Datensätze in einer festen Reihenfolge verarbeitet, kann die Möglichkeit eines Deadlocks ebenfalls erheblich reduziert werden .

5. Wenn unter der Isolationsstufe REPEATEABLE-READ zwei Threads gleichzeitig SELECT...ROR UPDATE für denselben bedingten Datensatz verwenden, um eine exklusive Sperre hinzuzufügen, wenn die Wenn der Datensatz nicht mit dem übereinstimmt, werden beide Threads erfolgreich gesperrt. Das Programm erkennt, dass der Datensatz noch nicht existiert und versucht, einen neuen Datensatz einzufügen. Wenn beide Threads dies tun, kommt es zu einem Deadlock. In diesem Fall kann das Problem durch Ändern der Isolationsstufe auf READ COMMITTED vermieden werden.

6. Wenn die Isolationsstufe READ COMMITED ist und beide Threads SELECT...FOR UPDATE ausführen, ermitteln Sie zunächst, ob Datensätze vorhanden sind, die die Bedingungen erfüllen, und fügen Sie die Datensätze ein, wenn nicht. Zu diesem Zeitpunkt kann nur ein Thread erfolgreich eingefügt werden, und der andere Thread wartet auf die Sperre. Wenn der erste Thread sendet, macht der zweite Thread aufgrund des Primärschlüssels erneut einen Fehler, aber obwohl dieser Thread einen Fehler macht erhält ein exklusives Schloss! Wenn zu diesem Zeitpunkt ein dritter Thread eine exklusive Sperre beantragt, kommt es ebenfalls zu einem Deadlock. In diesem Fall können Sie den Einfügevorgang direkt ausführen und dann die Ausnahme beim Duplikat des Primärschlüssels abfangen oder, wenn der Fehler beim Duplikat des Primärschlüssels auftritt, immer ROLLBACK ausführen, um die erworbene exklusive Sperre freizugeben

ps: Wenn ein Deadlock auftritt, können Sie mit dem Befehl SHOW INNODB STATUS die Ursache des letzten Deadlocks ermitteln und Verbesserungsmaßnahmen ergreifen.

Zusammenfassung:

Für die InnoDB-Tabelle gibt es hauptsächlich die folgenden Punkte

(1) InnoDB-Marketing basiert auf Indizes. Wenn auf Daten ohne Index zugegriffen wird, verwendet InnoDB Tabellensperren.

(2) InnoDB-Gap-Lock-Mechanismus und der Grund, warum InnoDB Gap-Lock verwendet.

(3) Unter verschiedenen Isolationsstufen sind der Sperrmechanismus und die konsistente Lesestrategie von InnoDB unterschiedlich.

(4) MySQL-Wiederherstellung und -Replikation haben auch große Auswirkungen auf den InnoDB-Sperrmechanismus und die konsistente Lesestrategie.

(5) Sperrkonflikte und sogar Deadlocks lassen sich nur schwer vollständig vermeiden.

Nachdem Benutzer die Sperreigenschaften von InnoDB verstanden haben, können Benutzer Sperrkonflikte und Deadlocks durch Design- und SQL-Anpassungen reduzieren, einschließlich:

• Versuchen Sie, eine niedrigere Isolationsstufe zu verwenden

• Entwerfen Sie Indizes sorgfältig und versuchen Sie, Indizes für den Zugriff auf Daten zu verwenden, um das Sperren präziser zu gestalten und so das Risiko von Sperrkonflikten zu verringern.

• Wählen Sie eine angemessene Transaktionsgröße, und bei kleinen Transaktionen ist die Wahrscheinlichkeit von Sperrkonflikten geringer.

• Wenn Sie den Datensatz sperren, ist es am besten, auf einmal eine ausreichende Sperrstufe anzufordern. Wenn Sie beispielsweise Daten ändern möchten, ist es am besten, direkt eine exklusive Sperre zu beantragen, anstatt zuerst eine gemeinsame Sperre zu beantragen und dann beim Ändern eine exklusive Sperre anzufordern, was leicht zu einem Deadlock führen kann.

• Wenn verschiedene Programme auf eine Gruppe von Tabellen zugreifen, sollten sie versuchen, den Zugriff auf jede Tabelle in derselben Reihenfolge zu vereinbaren. Versuchen Sie bei einer Tabelle, in einer festen Reihenfolge auf die Zeilen in der Tabelle zuzugreifen Befehl. . Dadurch kann die Wahrscheinlichkeit eines Deadlocks erheblich verringert werden.

• Versuchen Sie, Gleichheitsbedingungen für den Zugriff auf Daten zu verwenden, um die Auswirkungen von Lückensperren auf das gleichzeitige Einfügen zu vermeiden.

• Beantragen Sie keine Sperrstufe, die über den tatsächlichen Bedarf hinausgeht, und zeigen Sie bei Abfragen keine Sperre an.

• Für einige bestimmte Transaktionen können Tabellensperren verwendet werden, um die Verarbeitungsgeschwindigkeit zu erhöhen oder die Möglichkeit eines Deadlocks zu verringern.

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