オンラインMySQL自動増分IDが使い果たされた場合の対処方法

MySQL の自動インクリメント ID はすべて初期値を定義し、その後ステップサイズを継続的に増加します。自然数には上限がありませんが、数値を表現するのに使用されるバイト長は定義されているため、コンピュータのストレージには上限があります。たとえば、 unsigned int は 4 バイトで、その上限は2^32 - 1です。自動増分 ID がなくなるとどうなりますか?

テーブル定義の自動増分 ID

自動増分値が上限に達した後のテーブル定義のロジックは、次の ID を適用するときに、取得された値は変更されないままになります。

mysql> テーブル t を作成します (id int unsigned auto_increment 主キー) auto_increment=4294967295;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> t 値に挿入します(null);
クエリは正常、1 行が影響を受けました (0.00 秒)

mysql> show テーブル t を作成します。
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| テーブル | テーブルの作成 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | テーブル `t` を作成する (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  主キー (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
セット内の 1 行 (0.00 秒)

//4294967295 行を正常に挿入しました
mysql> t 値に挿入します(null);
エラー 1062 (23000): キー 't.PRIMARY' のエントリ '4294967295' が重複しています

最初の挿入が成功した後、テーブルのAUTO_INCREMENT は4294967295 のままであるため、2 番目の挿入で同じ自動増分 ID 値が取得され、挿入ステートメントの実行が試行され、主キーの競合が発生します。

2^32 - 1 (4294967295) は特に大きな数値ではないため、データの挿入と削除が頻繁に行われるテーブルでは、この数値が使い果たされる可能性があります。テーブルを作成するときは、テーブルが上限に達する可能性があるかどうかを考慮する必要があります。上限に達する可能性がある場合は、8 バイトの bigint unsigned として作成する必要があります。

InnoDBシステムはrow_idを自動的に増加させる

主キーを指定せずに InnoDB テーブルを作成すると、InnoDB は自動的に非表示の 6 バイトのrow_idを作成します。 InnoDBはグローバルdict_sys->row_id値を維持します

主キーのないすべての InnoDB テーブルでは、データ行が挿入されるたびに、現在のdict_sys->row_id挿入されるデータのrow_idとして使用され、その後dict_sys->row_idが 1 増加します。

コードが実装されると、 row_id は長さが 8 バイトの符号なし long 整数 (bigint unsigned) になります。しかし、InnoDB が設計された当時、 row_idの長さは 6 バイトしかなかったため、データテーブルに書き込まれる際には最後の 6 バイトのみが配置されていました。そのため、データテーブルに書き込むことができるrow_idの値には、次の 2 つの特徴があります。

テーブルに書き込まれるrow_idの値の範囲は0から2^48 - 1です。
dict_sys.row_id = 2^48 の場合、別のデータ挿入動作があるとrow_idが要求されます。取得後、最後の 6 バイトは 0 になります。

つまり、テーブルに書き込まれる row_id の範囲は0~2^48 - 1なります。上限に達した後、次の値は 0 となり、サイクルが継続されます。
2^48 - 1 はすでに非常に大きいですが、MySQL インスタンスが長期間存続すると、上限に達する可能性があります。
InnoDB では、row_id=N を適用すると、このデータ行がテーブルに書き込まれます。row_id=N の行がテーブルにすでに存在する場合、新しく書き込まれた行によって元の行が上書きされます。

この結論を検証します。gdb を通じてシステムの自動インクリメントrow_id を変更します。 GDB は問題の再現を容易にするために使用され、テスト環境でのみ使用できます。

row_idが検証シーケンスで使い果たされました

行ID

使用後の効果の検証

gdb を使用して dict_sys.row_id を 2^48 に設定すると、この値の row_id が 0 であるため、テーブル t の最初の行に a=2 が挿入されることがわかります。
次に、a=3 が挿入されます。row_id=1 なので、a=1 の前の行は上書きされます。これは、a=1 の row_id も 1 であるためです。

したがって、InnoDB テーブルに自動インクリメント主キーを積極的に作成する必要があります。テーブルの自動インクリメント ID が上限に達すると、データを挿入するときに主キーの競合エラーが報告されます。
結局のところ、データを上書きすることはデータの損失を意味し、データの信頼性に影響します。また、主キーの競合や挿入の失敗を報告すると、可用性に影響します。一般的に、可用性よりも信頼性が優先されます。

シド

REDO ログと binlog には、トランザクションに対応するために使用される共通フィールド Xid があります。 MySQL 内で Xid はどのように生成されますか?

MySQLは内部的にグローバル変数global_query_idを維持している

ステートメントが実行されるたびに、それがquery_idに割り当てられ、変数が 1 ずつ増加します。

現在のステートメントがトランザクションによって実行される最初のステートメントである場合、MySQL はトランザクションの Xid にquery_idも割り当てます。

global_query_idは純粋なメモリ変数であり、再起動後にクリアされます。したがって、同じ DB インスタンス内の異なるトランザクションの Xid は同じになる可能性があります。

ただし、MySQL を再起動すると、新しい binlog ファイルが再生成され、同じ binlog ファイル内の Xid が一意であることが保証されます。

MySQL を再起動しても同じ binlog に 2 つの同一の Xid が表示されることはありませんが、 global_query_id上限に達した場合は 0 からカウントを続けます。理論的には、同じ Xid が同じ binlog に表示される可能性があります。

global_query_idので、上限は2^64 - 1です。これを実現するには、次の条件を満たす必要があります。

XidがAであると仮定してトランザクションを実行する
次に、クエリステートメントを2^64回実行し、 global_query_idをAに戻します。
2^64は大きすぎるため、この可能性は理論上のみ存在します。
別のトランザクションを開始します。このトランザクションのXidもAです。

Innodb の trx_id

Xidはサーバー層によって管理される
InnoDB は内部的に Xid を使用して、InnoDB トランザクションをサーバーに関連付けます。

しかし、InnoDB 独自の trx_id は、別途管理されるトランザクション ID です。

InnoDB は内部的に max_trx_id グローバル変数を維持します。新しい trx_id が必要になるたびに、max_trx_id の現在の値が取得され、max_trx_id が 1 ずつ増加します。

InnoDBデータ可視性の核となる考え方

各データ行には、それを更新する trx_id が記録されます。トランザクションがデータ行を読み取ると、トランザクションの一貫性のあるビューとデータ行の trx_id を比較して、データが表示可能かどうかを判断します。

実行中のトランザクションについては、information_schema.innodb_trx テーブルからトランザクションの trx_id を確認できます。

次の例を参照してください: トランザクションのtrx_id

	S1	シーズン2
t1	始める t 制限 1 から * を選択
t2		information_schema を使用します。 innodb_trx から trx_id、trx_mysql_thread_id を選択します。
t3	t 値に挿入(null)
t3		innodb_trx から trx_id、trx_mysql_thread_id を選択します。

S2の実行記録:

mysql> information_schema を使用します。
テーブル名と列名の補完のためのテーブル情報の読み取り
-Aでこの機能をオフにすると起動が速くなります。

データベースが変更されました

mysql> innodb_trx から trx_id、trx_mysql_thread_id を選択します。
+-----------------+---------------------+
| trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
| 421972504382792 | 70 |
+-----------------+---------------------+
セット内の 1 行 (0.00 秒)

mysql> innodb_trx から trx_id、trx_mysql_thread_id を選択します。
+---------+---------------------+
| trx_id | trx_mysql_thread_id |
+---------+---------------------+
| 1355623 | 70 |
+---------+---------------------+
セット内の1行（0.01秒）

S2 は、innodb_trx テーブルからこれら 2 つのフィールドを取得します。2 番目のフィールドtrx_mysql_thread_idはスレッド ID です。スレッド ID は、これら 2 つのクエリに表示されるトランザクションが、S1 が配置されているスレッドであるスレッド ID 5 に対応していることを示すために表示されます。

t2 に表示される trx_id は非常に大きな数値ですが、t4 に表示される trx_id は 1289 で、比較的正常な数値のように見えます。これはなぜでしょうか?
t1 では、S1 はまだ更新されておらず、読み取り専用トランザクションです。読み取り専用トランザクションの場合、InnoDB は trx_id を割り当てません。

t1 では、trx_id の値は 0 です。この大きな数字は表示にのみ使用されます。
InnoDB は、S1 が t3 で挿入を実行するまで、実際には trx_id を割り当てません。したがって、t4 では、S2 は trx_id の値が 1289 であることを発見します。

明らかな変更ステートメントに加えて、select ステートメントの後に for update が追加された場合、それは読み取り専用トランザクションではありません。

トランザクション自体に加えて、更新ステートメントと削除ステートメントには、古いデータのマーク付けと削除も含まれます。つまり、データをパージキューに入れて、その後の物理的な削除を待機します。この操作により、max_trx_id も 1 増加するため、トランザクションに少なくとも 2 つが追加されます。
テーブルインデックス情報統計などの InnoDB のバックグラウンド操作でも内部トランザクションが開始されるため、trx_id 値が 1 増加しないことがあります。

t2 で見つかった大きな数字はどこから来たのでしょうか?
クエリが実行されるたびに、システムは一時的に計算を実行します。當前事務的trx變量的指針地址轉成整數，再加上248

これにより、次のことが保証されます。

同じ読み取り専用トランザクションのポインターアドレスは実行中に変更されないため、同じ読み取り専用トランザクションで見つかった trx_id は、innodb_trx テーブルでも innodb_locks テーブルでも同じになります。
並列読み取り専用トランザクションがある場合、各トランザクションの trx 変数のポインタアドレスは異なる必要があります。このように、異なる同時読み取り専用トランザクションに対して検出された trx_id は異なります。

なぜ248を追加するのですか?

読み取り専用トランザクションに表示される trx_id 値が比較的大きいことを確認して、通常の状況下で読み取り/書き込みトランザクションの ID と区別できるようにします。ただし、trx_id のロジックは row_id と似ており、8 バイトとして定義されます。
理論的には、読み取り/書き込みトランザクションが読み取り専用トランザクションと同じ trx_id を表示する可能性はまだあります。しかし、その確率は非常に低く、実害もないので心配しないでください。

読み取り専用トランザクションに trx_id が割り当てられないのはなぜですか?

トランザクションビュー内のアクティブなトランザクション配列のサイズを縮小します。現在実行中の読み取り専用トランザクションはデータの可視性に影響を与えないためです。したがって、トランザクションの一貫したビューを作成する場合、InnoDB は読み取り/書き込みトランザクションの trx_id のみをコピーする必要があります。
trx_id アプリケーションの数を減らします。 InnoDB が通常の SELECT ステートメントを実行する場合も、読み取り専用トランザクションに対応します。したがって、読み取り専用トランザクションが最適化された後は、通常のクエリステートメントで trx_id を適用する必要がなくなり、trx_id を適用する同時トランザクションのロック競合が大幅に減少します。

読み取り専用トランザクションでは trx_id が割り当てられないため、trx_id の増加率は明らかに低下します。
ただし、 max_trx_idは永続的に保存され、再起動後も 0 にリセットされません。理論的には、MySQL インスタンスが十分長く実行される限り、max_trx_id は 2^48 - 1 に達し、その後サイクルを 0 から開始する可能性があります。

この状態に達すると、MySQL ではダーティリードバグが発生し続けます。
まず、現在の max_trx_id を 2^48 - 1 に変更します。これは繰り返し読み取り可能です。

ダーティリードを再現する

システムの max_trx_id は 2^48 - 1 に設定されているため、セッション A で開始されたトランザクション TA の最低水準点は 2^48 - 1 になります。

t2時:

セッションB事務id=2^48 - 1を実行します。
2番目のトランザクションIDは0で、この更新の実行後に生成されたデータバージョンはtrx_id=0になります。

t3時:

セッション A は選択可視性判断を実行します。データバージョン c=3 の trx_id (0) はトランザクション TA の最低水準点 (2^48 - 1) より小さいため、データは可視であると見なされます。

しかし、これは汚い読み方です。
低水準値は増加し続け、トランザクション ID は 0 からカウントを開始するため、この瞬間以降、システム内のすべてのクエリにダーティリードが発生します。

また、MySQL を再起動してもmax_trx_id 0 にクリアされません。つまり、このバグは MySQL を再起動した後も存在します。このバグは理論上のみ存在するのでしょうか?
MySQLインスタンスのTPSが50Wだと仮定すると、このままだと17.8年後にこの状況が発生します。しかし、MySQL が本格的に普及した頃から現在に至るまで、この限界に達したインスタンスは残念ながら存在しません。ただし、MySQL インスタンスのサービス時間が十分に長い限り、このバグは必然的に発生します。

これにより、低水準点とデータの可視性についての理解も深まります。

スレッドID

システムはグローバル変数thread_id_counterを保存します

新しい接続が作成されるたびに、 thread_id_counter新しい接続のスレッド変数new_idに割り当てられます。

thread_id_counterは 4 バイトとして定義されているため、 2^32 - 1に達すると 0 にリセットされ、増加し続けます。

ただし、 show processlistでは 2 つの同一のthread_id表示されません。 MySQLはユニークな配列を使用するため

新しいスレッドにthread_idを割り当てるロジックは次のとおりです。

要約する

各自動インクリメント ID には独自のアプリケーションシナリオがあり、上限に達した後の動作が異なります。

テーブルの自動インクリメント ID が上限に達すると、再度適用されてもその値は変更されず、データの挿入を続行すると主キーの競合エラーが発生します。
row_idが上限に達すると0に戻り、再び増加します。同じrow_idが現れた場合、後で書き込まれたデータが前のデータを上書きします。
Xid では、同じ binlog ファイル内で値が重複しないようにするだけです。理論上は重複した値が存在することになりますが、その確率は極めて小さいため無視できます。
InnoDB の max_trx_id 増分値は MySQL が再起動されるたびに保存されるため、この記事で説明したダーティリード例は必ず発生するバグです。幸い、まだ時間はたっぷりあります。

オンラインのMySQL自動増分IDが枯渇した場合の対処法については以上です。MySQL自動増分ID枯渇に関する詳細は、123WORDPRESS.COMの過去の記事を検索するか、以下の関連記事を引き続き閲覧してください。今後とも123WORDPRESS.COMをよろしくお願いいたします。

以下もご興味があるかもしれません: