Verstehen Sie das Sperrproblem der MySQL-Einfügung in ... select

MySQL-TutorialIn dieser Spalte wird das Sperrproblem beim Einfügen von MySQL in ... select vorgestellt.

Zitat:

Ich bin kürzlich auf ein Datenbank-Deadlock-Problem gestoßen. Hier ist eine Aufzeichnung des Lösungsprozesses.

Das Problem entsteht:

Es gibt mehrere Ereignisse in MySQL im System, die alle paar Minuten für Funktionen wie statistische Daten ausgeführt werden, und dann schreibt dieses Ereignis Daten in eine Tabelle. Allgemeiner Inhalt: in a ersetzen aus Pflichtfelder aus b auswählen; Die allgemeine Struktur ist wie folgt: Das von „select from b“ benötigte Feld ist tatsächlich sehr kompliziert und umfasst viele Tabellenverknüpfungsoperationen. Dann wird dieses Ereignis einmal pro Minute ausgeführt, wenn die Datenmenge groß ist Es kann sein, dass es nicht in einer Minute abgeschlossen ist. Dann müssen wir verschiedene andere Einfüge- und Aktualisierungsvorgänge für Tabelle b durchführen. Zu diesem Zeitpunkt werden Sie feststellen, dass in den Backend-Protokollen häufig Deadlock- und Wait-Lock-Timeout-Fehler auftreten. Der letzte Test ergab, dass das Problem durch das Ausschalten des Ereignisses behoben wurde, und es wurde im Wesentlichen bestätigt, dass das Problem bei diesem Ereignis lag.

Problemanalyse:

Tatsächlich war es am zeitaufwändigsten, herauszufinden, dass es sich um ein Ereignisproblem handelte. Es dauerte nicht lange, die Daten abzufragen und das Problem zu lösen. 1. Finden Sie zunächst anhand der Fehlerinformationen im Back-End-Protokoll heraus, welche Tabelle den Deadlock verursacht hat und welche Tabelle auf die Zeitüberschreitung der Sperre gewartet hat. 2. Anschließend haben wir anhand dieser Tabellennamen und der gedruckten SQL herausgefunden, wo Das Problem könnte sein, es wurde grob bestätigt, dass es durch die SQL im Ereignis verursacht wurde. Nach dem Ausschalten des Ereignisses stellten wir fest, dass es kein Sperrproblem im Protokoll gab In der Anweisung haben wir festgestellt, dass es sich wahrscheinlich um ein Feld aus b handelt;

Der Hauptgrund hierfür ist, dass nicht klar ist, in welchen Situationen MySQL gesperrt wird Wenden Sie nur eine gemeinsame Sperre an, und für Vorgänge wie das Einfügen und Aktualisieren der B-Tabelle wird eine exklusive Sperre angewendet. Diese beiden sind kompatibel, einer liest und einer schreibt, und es gibt keinen Konflikt. Aber dem Timeout-Phänomen nach zu urteilen, scheint es, dass die für select from b erforderlichen Felder auch die b-Tabelle sperren. Sowohl Einfügungen als auch Aktualisierungen warten also auf die Sperre.

Endlich habe ich in Stack Overflow einige interessante Informationen und Linkadressen gefunden. Hier heißt es, dass es auf die Ebene „read-committed“ eingestellt werden muss. Dann wird auch ein MySQL-Konfigurationsparameter eingeführt: innodb_locks_unsafe_for_binlog.

Also sind wir diesen Informationen gefolgt, um sie auf der offiziellen Website zu überprüfen, und haben diesen Absatz gefunden:

INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: During roll-forward recovery using a statement-based binary log, every SQL statement must be executed in exactly the same way it was done originally.复制代码

Das bedeutet, dass für INSERT INTO T SELECT ... FROM S WHERE ... In diesem Fall zunächst einmal wer ist Typ auf dem T-Tisch? Datensatzsperren (Sperren auf Zeilenebene) ohne Lückensperren. Für Tabelle S gibt es zwei Situationen, in denen keine Sperre gesperrt wird:

1. Wenn die Transaktionsisolationsstufe READ COMMITTED ist.

2. Oder innodb_locks_unsafe_for_binlog ist aktiviert und die Transaktionsisolationsstufe ist nicht SERIALIZABLE Reihe von S-Next-Taste. Wenn Sie sich bei next-key nicht sicher sind, können Sie diese Einführung und die Linkadresse auf der offiziellen Website lesen.

Die Art und Weise, wie wir das Warte-Timeout lösen wollen, ist relativ klar, nämlich zu verhindern, dass die S-Tabelle gesperrt wird. Um jedoch eine Sperre zu vermeiden, können wir die beiden auf der offiziellen Website genannten Methoden verwenden. Beides ist möglich, aber gemäß der Einführung des Parameters innodb_locks_unsafe_for_binlog ist es am besten, Methode 1 zu verwenden und die Transaktionsisolationsstufe auf „read-committed“ zu setzen.

Die Gründe sind wie folgt:

1. Der Parameter innodb_locks_unsafe_for_binlog ist statisch. Sie müssen eine Zeile innodb_locks_unsafe_for_binlog = 1 zu my.cnf hinzufügen und dann die Datenbank neu starten. Geben Sie den Befehl in MySQL ein:

show variables like "%innodb_locks_unsafe_for_binlog%"复制代码

Wenn festgestellt wird, dass es eingeschaltet ist, wurde es erfolgreich aktiviert.

2. Die Isolationsstufe einer Transaktion ist relativ feinkörnig und kann für eine bestimmte Sitzung festgelegt werden. Verschiedene Sitzungen können unterschiedliche Isolationsstufen verwenden. Dieser Parameter ist dynamisch und kann direkt in der MySQL-Befehlszeile geändert werden.

3. Die Parametereinführung von mysql5.7 besagt, dass der Parameter innodb_locks_unsafe_for_binlog in nachfolgenden MySQL-Versionen aufgegeben wird. Das stimmt. Ich habe die detaillierte Parametererklärung von mysql8.0 überprüft und festgestellt, dass dieser Parameter nicht mehr existiert.

Es wird daher empfohlen, zur Kontrolle die Transaktionsisolationsstufe zu verwenden.

Weitere verwandte kostenlose Lernempfehlungen:

MySQL-Tutorial

(Video)

Das obige ist der detaillierte Inhalt vonVerstehen Sie das Sperrproblem der MySQL-Einfügung in ... select. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!