Vorgehensweise, wenn die Online-MySQL-Auto-Increment-ID erschöpft ist

Die Auto-Increment-IDs von MySQL definieren alle einen Anfangswert und erhöhen dann kontinuierlich die Schrittweite. Obwohl für natürliche Zahlen keine Obergrenze existiert, ist die zur Darstellung der Zahl verwendete Bytelänge definiert, sodass auch für den Computerspeicher eine Obergrenze existiert. Beispielsweise ist ein vorzeichenloser int 4 Bytes lang und seine Obergrenze beträgt 2^32 - 1 . Was passiert, wenn die Auto-Increment-ID erschöpft ist?

Tabellendefinition - automatische Inkrementierung der ID

Die Logik der Tabellendefinition, nachdem der Auto-Inkrement-Wert die Obergrenze erreicht hat, lautet: Bei der Beantragung der nächsten ID bleibt der erhaltene Wert unverändert.

mysql> Tabelle erstellen t(id int unsigned auto_increment Primärschlüssel) auto_increment=4294967295;
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

mysql> in t-Werte einfügen (null);
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)

mysql> anzeigen, Tabelle erstellen t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Tabelle | Tabelle erstellen |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | TABELLE ERSTELLEN `t` (
  `id` int unsigned NICHT NULL AUTO_INCREMENT,
  PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 Zeile im Satz (0,00 Sek.)

//Eine Zeile mit 4294967295 wurde erfolgreich eingefügt
mysql> in t-Werte einfügen (null);
FEHLER 1062 (23000): Doppelter Eintrag „4294967295“ für Schlüssel „t.PRIMARY“

Nachdem der erste Einfügevorgang erfolgreich war, beträgt der AUTO_INCREMENT-Wert der Tabelle immer noch 4294967295, was dazu führt, dass der zweite Einfügevorgang denselben Auto-Increment-ID-Wert erhält und dann versucht, die Einfügeanweisung auszuführen, was zu einem Primärschlüsselkonflikt führt.

2^32 - 1 (4294967295) ist keine besonders große Zahl, und eine Tabelle mit häufigem Einfügen und Löschen von Daten kann sie aufbrauchen. Beim Erstellen einer Tabelle müssen Sie berücksichtigen, ob Ihre Tabelle wahrscheinlich die Obergrenze erreicht. Wenn dies der Fall ist, sollte sie als 8-Byte-Bigint ohne Vorzeichen erstellt werden.

Das InnoDB-System erhöht automatisch die row_id

Wenn Sie eine InnoDB-Tabelle erstellen, ohne einen Primärschlüssel anzugeben, erstellt InnoDB automatisch eine unsichtbare 6-Byte-Zeilen -ID . InnoDB verwaltet einen globalen dict_sys->row_id -Wert

Für alle InnoDB-Tabellen ohne Primärschlüssel wird bei jedem Einfügen einer Datenzeile die aktuelle dict_sys->row_id als row_id der einzufügenden Daten verwendet und anschließend dict_sys->row_id um 1 erhöht.

Wenn der Code implementiert ist, ist row_id eine vorzeichenlose lange Ganzzahl (Bigtint unsigned) mit einer Länge von 8 Bytes. Als InnoDB jedoch entworfen wurde, war die Länge von row_id nur 6 Bytes, sodass beim Schreiben in die Datentabelle nur die letzten 6 Bytes platziert wurden. Daher hat der Wert von row_id , der in die Datentabelle geschrieben werden kann, zwei Eigenschaften:

• Der Wertebereich der in die Tabelle geschriebenen row_id reicht von 0 bis 2^48 - 1
• Wenn dict_sys.row_id = 2^48 ist und ein anderes Dateneinfügungsverhalten vorliegt, wird row_id angefordert. Nach dem Abrufen sind die letzten 6 Bytes 0.

Das heißt, die in die Tabelle geschriebene Zeilen-ID liegt zwischen 0~2^48 - 1 . Nach Erreichen der Obergrenze ist der nächste Wert 0 und dann wird der Zyklus fortgesetzt.
2^48 - 1 ist bereits sehr groß, aber wenn eine MySQL-Instanz lange existiert, ist es immer noch möglich, die Obergrenze zu erreichen.
In InnoDB wird nach der Anwendung von row_id=N diese Datenzeile in die Tabelle geschrieben. Wenn in der Tabelle bereits eine Zeile mit row_id=N vorhanden ist, überschreibt die neu geschriebene Zeile die ursprüngliche Zeile.

Überprüfen Sie diese Schlussfolgerung: Ändern Sie die automatische Inkrementierung der Row_ID des Systems über GDB. GDB wird verwendet, um die Reproduktion von Problemen zu erleichtern und kann nur in einer Testumgebung verwendet werden.

row_id ist in der Überprüfungssequenz aufgebraucht

Zeilen-ID

Überprüfung der Wirkung nach der Anwendung

Es ist ersichtlich, dass, nachdem ich gdb verwendet habe, um dict_sys.row_id auf 2^48 festzulegen, das Einfügen von a=2 in der ersten Zeile der Tabelle t erscheint, weil die row_id dieses Werts 0 ist.
Anschließend wird a=3 eingefügt. Da row_id=1 ist, wird die vorherige Zeile mit a=1 überschrieben, da die row_id von a=1 ebenfalls 1 ist.

Daher sollten Sie in der InnoDB-Tabelle aktiv einen Auto-Inkrement-Primärschlüssel erstellen: Wenn die Auto-Inkrement-ID der Tabelle die Obergrenze erreicht, wird beim Einfügen von Daten ein Primärschlüsselkonfliktfehler gemeldet.
Denn das Überschreiben von Daten führt zu Datenverlust, der wiederum die Datenzuverlässigkeit beeinträchtigt; die Meldung von Primärschlüsselkonflikten und Einfügefehlern wirkt sich auf die Verfügbarkeit aus. Im Allgemeinen ist Zuverlässigkeit der Verfügbarkeit vorzuziehen.

Xid

Das Redo-Protokoll und das Binärprotokoll verfügen über ein gemeinsames Feld „Xid“, das zur Zuordnung zu Transaktionen verwendet wird. Wie wird Xid in MySQL generiert?

MySQL verwaltet intern eine globale Variable global_query_id

Bei jeder Ausführung der Anweisung wird sie der query_id zugewiesen und anschließend die Variable um 1 erhöht:

Wenn die aktuelle Anweisung die erste von der Transaktion ausgeführte Anweisung ist, weist MySQL der Xid der Transaktion auch query_id zu:

global_query_id ist eine reine Speichervariable und wird nach dem Neustart gelöscht. Daher können die XIDs verschiedener Transaktionen in derselben DB-Instanz identisch sein.

Nach dem Neustart von MySQL wird jedoch eine neue Binlog-Datei neu generiert, die sicherstellt, dass die XID in derselben Binlog-Datei eindeutig ist.

Obwohl ein MySQL-Neustart nicht dazu führt, dass zwei identische Xids im selben Binärprotokoll erscheinen, wird die Zählung von 0 an fortgesetzt, wenn global_query_id die Obergrenze erreicht. Theoretisch kann dieselbe XID immer noch im selben Binärprotokoll erscheinen.

Da global_query_id , beträgt die Obergrenze 2^64 - 1 . Dafür müssen folgende Voraussetzungen erfüllt sein:

• Führen Sie eine Transaktion aus, vorausgesetzt, Xid ist A
• Führen Sie als Nächstes die Abfrageanweisung 2^64 Mal aus und lassen Sie global_query_id zu A zurückkehren.
• 2^64 ist zu groß und diese Möglichkeit besteht nur theoretisch.
• Starten Sie eine weitere Transaktion, die XID dieser Transaktion ist ebenfalls A

Innodb trx_id

Xid wird von der Serverebene verwaltet
InnoDB verwendet intern Xid, um InnoDB-Transaktionen mit Servern zu verknüpfen.

Aber InnoDBs eigene trx_id ist eine separat verwaltete Transaktions-ID.

InnoDB verwaltet intern eine globale Variable max_trx_id. Jedes Mal, wenn eine neue trx_id erforderlich ist, wird der aktuelle Wert von max_trx_id abgerufen und dann max_trx_id um 1 erhöht.

Die Kernidee der InnoDB-Datensichtbarkeit

Jede Datenzeile zeichnet die trx_id auf, die sie aktualisiert. Wenn eine Transaktion eine Datenzeile liest, bestimmt sie, ob die Daten sichtbar sind, indem sie die konsistente Ansicht der Transaktion mit der trx_id der Datenzeile vergleicht.

Für die ausgeführte Transaktion können Sie die trx_id der Transaktion in der Tabelle information_schema.innodb_trx sehen.

Siehe folgendes Beispiel: trx_id der Transaktion

Ausführungsprotokoll von S2:

mysql> verwende Informationsschema;
Einlesen von Tabelleninformationen zur Vervollständigung von Tabellen- und Spaltennamen
Sie können diese Funktion deaktivieren, um einen schnelleren Start mit -A zu erhalten

Datenbank geändert

mysql> wähle trx_id, trx_mysql_thread_id aus innodb_trx;
+-----------------+---------------------+
| trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
| 421972504382792 | 70 |
+-----------------+---------------------+
1 Zeile im Satz (0,00 Sek.)

mysql> wähle trx_id, trx_mysql_thread_id aus innodb_trx;
+---------+---------------------+
| trx_id | trx_mysql_thread_id |
+---------+---------------------+
| 1355623 | 70 |
+---------+---------------------+
1 Zeile im Satz (0,01 Sek.)

S2 ruft diese beiden Felder aus der Tabelle innodb_trx ab. Das zweite Feld trx_mysql_thread_id ist die Thread-ID. Die Thread-ID wird angezeigt, um zu veranschaulichen, dass die in diesen beiden Abfragen angezeigten Transaktionen der Thread-ID 5 entsprechen. Dies ist der Thread, in dem sich S1 befindet.

Die bei t2 angezeigte trx_id ist eine sehr große Zahl; die bei t4 angezeigte trx_id ist 1289, was wie eine relativ normale Zahl aussieht. Warum ist das so?
Zum Zeitpunkt t1 wurde S1 noch nicht aktualisiert und ist eine schreibgeschützte Transaktion. Für schreibgeschützte Transaktionen weist InnoDB keine trx_id zu:

• Zum Zeitpunkt t1 beträgt der Wert von trx_id 0. Diese große Zahl dient lediglich zur Anzeige.
• InnoDB weist trx_id erst dann zu, wenn S1 den Einfügevorgang bei t3 ausführt. Daher stellt S2 bei t4 fest, dass der Wert von trx_id 1289 ist.

Wenn neben den offensichtlichen Änderungsanweisungen nach der Select-Anweisung ein „for update“ hinzugefügt wird, handelt es sich nicht um eine schreibgeschützte Transaktion.

• Neben der Transaktion selbst beinhalten Update- und Delete-Anweisungen auch das Markieren und Löschen alter Daten, d. h. das Platzieren der Daten in der Löschwarteschlange, um auf die anschließende physische Löschung zu warten. Dieser Vorgang erhöht auch max_trx_id um 1, sodass in einer Transaktion mindestens 2 hinzugefügt werden.
• Die Hintergrundoperationen von InnoDB, wie etwa die Statistik der Tabellenindexinformationen, starten ebenfalls interne Transaktionen, sodass Sie möglicherweise feststellen, dass der trx_id-Wert nicht um 1 erhöht wird.

Woher stammt die große Zahl, die bei t2 gefunden wurde?
Bei jeder Abfrage führt das System eine Zwischenberechnung durch:當前事務的trx變量的指針地址轉成整數，再加上248

Dadurch wird Folgendes sichergestellt:

• Da sich die Zeigeradresse derselben schreibgeschützten Transaktion während der Ausführung nicht ändert, ist die für dieselbe schreibgeschützte Transaktion gefundene trx_id unabhängig davon, ob sie sich in der Tabelle innodb_trx oder innodb_locks befindet, dieselbe.
• Wenn parallele schreibgeschützte Transaktionen vorhanden sind, muss die Zeigeradresse der TRX-Variable jeder Transaktion unterschiedlich sein. Auf diese Weise ist die für verschiedene gleichzeitige schreibgeschützte Transaktionen gefundene trx_id unterschiedlich.

Warum 248 hinzufügen?

Stellen Sie sicher, dass der für schreibgeschützte Transaktionen angezeigte trx_id-Wert relativ groß ist, sodass er unter normalen Umständen von der ID von Lese-/Schreibtransaktionen unterschieden werden kann. Die Logik von trx_id ähnelt jedoch der von row_id und ist als 8 Bytes definiert.
Theoretisch ist es immer noch möglich, dass eine Lese-/Schreibtransaktion dieselbe trx_id anzeigt wie eine schreibgeschützte Transaktion. Die Wahrscheinlichkeit ist jedoch sehr gering und es besteht kein wirklicher Schaden, also machen Sie sich keine Sorgen.

Warum wird schreibgeschützten Transaktionen keine trx_id zugewiesen?

• Reduzieren Sie die Größe des Arrays aktiver Transaktionen in der Transaktionsansicht. Denn die aktuell laufende schreibgeschützte Transaktion hat keinen Einfluss auf die Sichtbarkeit der Daten. Daher muss InnoDB beim Erstellen einer konsistenten Ansicht einer Transaktion nur die trx_id der Lese-/Schreibtransaktion kopieren.
• Reduzieren Sie die Anzahl der trx_id-Anwendungen. Wenn InnoDB eine normale Select-Anweisung ausführt, entspricht dies auch einer schreibgeschützten Transaktion. Nachdem schreibgeschützte Transaktionen optimiert wurden, müssen normale Abfrageanweisungen daher nicht mehr auf trx_id angewendet werden. Dadurch werden die Sperrkonflikte gleichzeitiger Transaktionen, die auf trx_id angewendet werden, erheblich reduziert.

Da schreibgeschützte Transaktionen trx_id nicht zuweisen, verlangsamt sich die Wachstumsrate von trx_id offensichtlich.
max_trx_id wird jedoch persistent gespeichert und nach einem Neustart nicht auf 0 zurückgesetzt . Theoretisch kann max_trx_id, solange eine MySQL-Instanz lange genug läuft, 2^48 - 1 erreichen und dann den Zyklus bei 0 starten.

Wenn dieser Zustand erreicht ist, weist MySQL weiterhin einen Dirty-Read-Bug auf:
Ändern Sie zunächst die aktuelle max_trx_id auf 2^48 - 1. Dies ist ein wiederholbarer Lesevorgang.

Dirty Read reproduzieren

Da die max_trx_id des Systems auf 2^48 - 1 eingestellt ist, beträgt die niedrige Wassermarke der in Sitzung A gestarteten Transaktion TA 2^48 - 1.

Bei t2:

• Sitzung B führt die erste Update-事務id=2^48 - 1 aus.
• Die zweite Transaktions-ID ist 0, und die nach der Ausführung dieses Updates generierte Datenversion hat trx_id=0

Bei t3:

Sitzung A führt eine ausgewählte Sichtbarkeitsbeurteilung durch: Die trx_id (0) der Datenversion c=3 ist kleiner als die niedrige Wassermarke der Transaktion TA (2^48 - 1), daher werden die Daten als sichtbar betrachtet.

Aber das ist eine schmutzige Lektüre.
Da der Low-Water-Mark-Wert weiter ansteigt und die Transaktions-ID bei 0 zu zählen beginnt, weisen alle Abfragen im System ab diesem Zeitpunkt Dirty Reads auf.

Und max_trx_id wird beim Neustart von MySQL nicht auf 0 gelöscht, das heißt, dieser Fehler besteht nach dem Neustart von MySQL weiterhin. Existiert dieser Fehler nur in der Theorie?
Angenommen, der TPS einer MySQL-Instanz beträgt 50 W. Wenn es so weitergeht, wird diese Situation in 17,8 Jahren eintreten. Aber ich fürchte, seit MySQL wirklich populär wurde, hat keine Instanz jemals diese Grenze erreicht. Solange die Servicezeit der MySQL-Instanz jedoch lang genug ist, wird dieser Fehler unvermeidlich auftreten.

Dies kann auch zu einem tieferen Verständnis von Niedrigwassermarken und Datensichtbarkeit führen.

Thread-ID

Das System speichert eine globale Variable thread_id_counter

Jedes Mal, wenn eine neue Verbindung erstellt wird, wird thread_id_counter der Thread-Variable new_id der neuen Verbindung zugewiesen.

thread_id_counter ist als 4 Bytes definiert. Wenn er also 2^32 - 1 erreicht, wird er auf 0 zurückgesetzt und steigt weiter an.

Sie werden jedoch in show processlist nicht zwei identische thread_id sehen. Da MySQL ein eindeutiges Array verwendet

Die Logik zum Zuweisen thread_id zu einem neuen Thread lautet:

Zusammenfassen

Jede Auto-Increment-ID hat ihr eigenes Anwendungsszenario und verhält sich nach Erreichen der Obergrenze anders:

• Wenn die Auto-Inkrement-ID der Tabelle die Obergrenze erreicht, ändert sich ihr Wert bei einer erneuten Anwendung nicht, was zu einem Primärschlüsselkonfliktfehler führt, wenn mit dem Einfügen weiterer Daten fortgefahren wird.
• Wenn row_id die Obergrenze erreicht, wird es auf 0 zurückgesetzt und dann erneut erhöht. Wenn dieselbe row_id erscheint, überschreiben die später geschriebenen Daten die vorherigen Daten
• Xid muss nur doppelte Werte in derselben Binlog-Datei vermeiden. Obwohl es theoretisch doppelte Werte geben wird, ist die Wahrscheinlichkeit äußerst gering und kann ignoriert werden.
• Der max_trx_id-Inkrementwert von InnoDB wird bei jedem Neustart von MySQL gespeichert, daher ist das in unserem Artikel erwähnte Dirty Read-Beispiel ein Fehler, der zwangsläufig auftritt. Glücklicherweise haben wir noch viel Zeit.
  

Dies ist das Ende dieses Artikels darüber, was zu tun ist, wenn die Online-MySQL-Autoinkrement-ID erschöpft ist. Weitere Informationen zur Erschöpfung der MySQL-Autoinkrement-ID finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!