So verwenden Sie MySQL-Tabellensperren, Zeilensperren, exklusive Sperren und gemeinsame Sperren

1. Auswahl des Transaktionsisolationsmechanismus

• Wenn es uns egal ist und wir den Transaktionsisolationsmechanismus von uncommitted read verwenden und diesen Threads erlauben, die Datenbank gleichzeitig zu betreiben, werden Dirty Reads (read Uncommitted) ausgeführt Daten), nicht wiederholbares Lesen (zwei Abfragewerte sind unterschiedlich), Phantomlesen (zwei Abfragedatenvolumina sind unterschiedlich) und andere Probleme, Die Datensicherheit ist am niedrigsten, der Vorteil besteht darin, dass die Parallelitätseffizienz sehr hoch ist. im Allgemeinen nicht verwendet

• Wenn wir alle Transaktionen serialisieren (durch Sperren implementieren) und über Sperren ordnen, ist die Parallelitätseffizienz zwar zu gering, obwohl die Datensicherheit verbessert wird, und wird im Allgemeinen nicht verwendet

  Daher verwenden wir im Allgemeinen die beiden Isolationsstufen „festgeschriebenes Lesen und wiederholbares Lesen“, die die Sicherheit, Konsistenz und Parallelitätseffizienz von Daten ausgleichen und durch die MVCC-Mehrversions-Parallelitätskontrolle (MVCC) implementiert werden. Es ist das Prinzip des festgeschriebenen Lesens und wiederholbares Lesen, und die Sperre ist das Prinzip der Serialisierung)

• 2. Sperre auf Tabellenebene und Sperre auf Zeilenebene

  Sperre auf Tabellenebene: Sperren Sie die gesamte Tabelle. Der Overhead ist gering (da Sie den Datensatz einer bestimmten Zeile in der Tabelle nicht finden müssen, um ihn zu sperren. Wenn Sie diese Tabelle ändern möchten, beantragen Sie direkt die Sperre dieser Tabelle), die Sperre ist schnell. und es wird keinen Deadlock geben; die Sperrgranularität ist groß und es treten Sperrkonflikte mit hoher Wahrscheinlichkeit und geringer Parallelität auf.

Sperre auf Zeilenebene: Sperren Sie eine Zeile mit Datensätzen. Es ist teuer (es muss der entsprechende Datensatz in der Tabelle gefunden werden, und es gibt einen Prozess zum Durchsuchen der Tabelle und des Index), das Sperren ist langsam und die Sperrgranularität ist am geringsten, und die Wahrscheinlichkeit eines Sperrkonflikts ist gering am niedrigsten und die Parallelität ist hoch

InnoDB-Speicher Die Engine unterstützt die Transaktionsverarbeitung, die Tabelle unterstützt Sperren auf Zeilenebene und die Parallelitätsfähigkeit ist besser

InnoDB-Zeilensperre wird durch Sperren der Indexelemente erreicht Der Index, anstatt die Zeilendatensätze der Tabelle zu sperren. Dies bedeutet, dass InnoDB nur Sperren auf Zeilenebene verwendet, wenn Daten über Indexbedingungen abgerufen werden. Andernfalls verwendet InnoDB Tabellensperren. Da die Zeilensperrimplementierung von InnoDB so ist Eine Sperre wird dem Indexfeld hinzugefügt, keine Sperre des Zeilendatensatzes. Obwohl auf verschiedene Zeilen der Tabelle unter der InnoDB-Engine zugegriffen wird, tritt daher dennoch ein Sperrenkonflikt auf, wenn dasselbe Indexfeld als Filterbedingung verwendet wird. Dies kann nur seriell und nicht gleichzeitig erfolgen

1. Auch wenn ein Index in SQL verwendet wird, wird der MySQL-Optimierer jedoch aufgeben, wenn davon ausgegangen wird, dass ein vollständiger Tabellenscan effizienter ist als die Verwendung eines Index Verwenden Sie zu diesem Zeitpunkt den Index, sodass keine Zeilensperren, sondern Tabellensperren verwendet werden. Beispielsweise verwendet MySQL für einige kleine Tabellen keine Indizes. 3. Exklusive Sperren (Exklusiv) und gemeinsam genutzt Sperren (gemeinsam genutzt)

Exklusive Sperren, auch bekannt als Schreiben (SX, SX) und Schreiben und Schreiben (XX) schließen sich gegenseitig aus

2. Paar Es besteht die folgende Beziehung zwischen Transaktionen, die X- und S-Sperren hinzufügen:

3. Eine Transaktion fügt dem Datenobjekt A eine S-Sperre hinzu. Sie kann Lesevorgänge ausführen Während des Sperrzeitraums können andere Transaktionen S-Sperren zu A hinzufügen, aber keine X-Sperren hinzufügen. Eine Transaktion fügt eine :select …-Sperre im Freigabemodus hinzu, um eine gemeinsame Sperre zu erzwingen Erwerb, wählen Sie … für die Aktualisierung aus, um die exklusive Sperre zu erwerben1. Testen Sie die Kompatibilität von exklusiven Sperren und gemeinsam genutzten Sperren zwischen verschiedenen Transaktionen.
   Überprüfen Sie zunächst die SQL-Anweisung und den Inhalt der Tabelle. Sehen Sie sich die Isolationsstufe an:

Öffnen Sie zuerst eine Transaktion und fügen Sie den Daten eine exklusive Sperre mit der ID = 7 hinzu.

• Öffnen Sie dann die Transaktion mit einem anderen Client.
Wir verwenden den Service-Thread einer anderen Transaktion, der eine exklusive Sperre hinzugefügt hat auf die Daten mit der ID=7, was sie blockierte

Wir haben versucht, den Daten mit der ID=7 eine gemeinsame Sperre hinzuzufügen, aber sie blockierte immer noch

• Zusammenfassung: Zwischen verschiedenen Transaktionen Für Datensperren können nur SS-Sperren koexistieren. XX, SX und

  

  Verwenden Sie das nicht indizierte Feld der Tabelle als Filterbedingung

  

  Transaktion 2 möchte nun auch die exklusive Sperre dieses Datensatzes erhalten, schlägt jedoch vorhersehbar fehl; dann erhält Transaktion 2 nun Chenweis Datensatz „Exklusiv“. Versuchen Sie zu sehen, ob die Sperre erfolgreich sein kann. InnoDB unterstützt Zeilensperren. Wenn die Primärschlüssel-ID gerade als Filterbedingung verwendet wurde, können Transaktion 1 und Transaktion 2 erfolgreich Sperren für verschiedene Zeilen erwerben. Jetzt stellen wir jedoch fest, dass wir die exklusive Sperre namens Chenwei nicht erhalten können. Lassen Sie uns erklären:

  Die Zeilensperre von InnoDB wird durch das Sperren von Indexelementen und nicht durch das Sperren von Tabellenzeilendatensätzen implementiert.

  Und wir verwenden den Namen als Filterbedingung, ohne den Index zu verwenden, daher werden natürlich keine Zeilensperren verwendet, Tabellensperren jedoch schon gebraucht. Dies bedeutet, dass InnoDB beim Abrufen von Daten über Indizes nur Sperren auf Zeilenebene verwendet, andernfalls verwendet InnoDB Tabellensperren!!! Das Namensfeld Nach dem Hinzufügen des Index können die beiden Transaktionen exklusive Sperren (zur Aktualisierung) für verschiedene Zeilen erhalten, was erneut beweist, dass die Zeilensperre von InnoDB zum Indexelement hinzugefügt wurde

  denn jetzt befindet sich der Name im Index Durch Zhangsan wurde festgestellt, dass die ID des Zeilendatensatzes, der sich im Hilfsindexbaum befand, 7 war, und dann ging ich zum Primärschlüsselindexbaum, um die exklusive Sperre des entsprechenden Zeilendatensatzes zu erhalten

  (Meine persönliche Vermutung ist, dass die entsprechende Datensätze im Hilfsindexbaum und im Primärschlüsselindexbaum wurden hinzugefügt (Sperre)
  4. Serialisierungsisolationsstufentest

  (Alle Transaktionen verwenden exklusive Sperren oder gemeinsame Sperren, es ist kein Benutzer erforderlich, Sperren manuell hinzuzufügen)

  Setzen Sie die Serialisierungsisolationsstufe

  Zwei Transaktionen können gleichzeitig gemeinsam genutzte Sperren erhalten (SS-Koexistenz)

  Lassen Sie nun Transaktion 2 Daten einfügen

  Zu diesem Zeitpunkt muss eine exklusive Sperre vorhanden sein Aufgrund des Einfügens hinzugefügt, aber da Transaktion 1 bereits eine gemeinsame Sperre für die gesamte Tabelle hinzugefügt hat, kann Transaktion 2 die Tabelle nicht mehr erfolgreich sperren (SX existiert nicht)

  Rollback

  Weil wir dem Namen einen Index hinzugefügt haben , die obige Auswahl entspricht dem Hinzufügen einer gemeinsamen Zeilensperre zu den Daten mit dem Namen zhangsan

  Aktualisierung von Transaktion 2

  
  

  Transaktion 2 kann nicht aktualisiert werden, da die gesamte Tabelle durch die gemeinsame Sperre von Transaktion 1 gesperrt wurde

  Transaktion 2 sieht aus Findet für Zhangsan im Hilfsindexbaum den entsprechenden Primärschlüsselwert und geht dann weiter. Der Primärschlüsselindexbaum hat den entsprechenden Datensatz gefunden, aber festgestellt, dass diese Datensatzzeile durch eine gemeinsame Sperre gesperrt wurde. Transaktion 2 kann die gemeinsame Sperre erhalten , kann aber die exklusive Sperre nicht erhalten

  Versuchen wir zu sehen, ob es mit der Primärschlüsselindex-ID aktualisiert werden kann

  Das Feld dahinter, in dem wir jetzt die ID anstelle des Namens verwenden, ist immer noch blockiert Findet auch den entsprechenden Primärschlüssel über den Hilfsindexbaum und findet dann den entsprechenden Datensatz im Primärschlüsselindexbaum (Indexbaum und Primärschlüsselindexbaum sind gesperrt)

  Wir haben die Daten mit der ID=8 aktualisiert und es war erfolgreich. Den Zeilendaten mit der ID 7 werden nur Zeilensperren hinzugefügt, sodass wir die Daten mit der ID 8 erfolgreich verarbeiten können.

  Wenn ein Index vorhanden ist, verwenden Sie Zeilensperren. Wenn kein Index vorhanden ist, verwenden Sie Tabellensperren.

  Sperren auf Tabellenebene oder Sperren auf Zeilenebene beziehen sich auf die Granularität der Sperre, und exklusive Sperren beziehen sich auf die Art der Sperre. Es gibt einen Unterschied Gemeinsame Schlösser und exklusive Schlösser#🎜🎜 #

Das obige ist der detaillierte Inhalt vonSo verwenden Sie MySQL-Tabellensperren, Zeilensperren, exklusive Sperren und gemeinsame Sperren. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!