MySQL 自動インクリメント ID 枯渇の例

ディスプレイ定義ID

テーブルに定義された自動増分IDが上限に達した場合、次のIDを申請する際に得られる値は変更されません

-- (2^32-1) = 4,294,967,295
-- BIGINT UNSIGNEDの使用をお勧めします
テーブル t を作成します (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=4294967295;
t VALUES (null) に挿入します。

-- AUTO_INCREMENT は変更されていません mysql> SHOW CREATE TABLE t;
+-------+------------------------------------------------------+
| テーブル | テーブルの作成 |
+-------+------------------------------------------------------+
| t | テーブル `t` を作成する (
 `id` int(10) 符号なし NOT NULL AUTO_INCREMENT,
 主キー (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 デフォルト CHARSET=utf8 |
+-------+------------------------------------------------------+

mysql> t VALUES (null) に INSERT INTO します。
エラー 1062 (23000): キー 'PRIMARY' のエントリ '4294967295' が重複しています

InnoDB 行ID

1. 作成された InnoDB テーブルで主キーが指定されていない場合、InnoDB は 6 バイトの長さの非表示の row_id を作成します。

2. InnoDB は、主キーのないすべての InnoDB テーブルに対してグローバル dict_sys.row_id 値を維持します。

• 現在のdict_sys.row_idの値は、データを挿入するためのrow_idとして使用され、その後dict_sys.row_id + 1の値が挿入されます。

3. コード実装では、row_idは8バイトのBIGINT UNSIGNEDです。

• ただし、InnoDB が設計されたとき、row_id 用に予約されたスペースは 6 バイトのみであり、データ テーブルに書き込むときには最後の 6 バイトのみが格納されていました。
• row_idの値の範囲は0〜2^48-1です。
• 上限に達した後の次の値は0です

4. InnoDBでは、row_id=Nを適用した後、このデータ行をテーブルに書き込みます。

• テーブル内に row_id=N の行がすでに存在する場合、新しく書き込まれた行によって元の行が上書きされます。

5. 自動増分主キーを作成することをお勧めします

• テーブルの自動インクリメント ID が上限に達すると、データの挿入時に主キーの競合エラーが報告され、可用性に影響します。
• データを上書きするとデータが失われ、信頼性に影響します。
• 一般的に言えば、信頼性は可用性よりも優れている

XID

1. redologとbinlogを一緒に使用すると、トランザクションに対応する共通フィールドXIDが存在する。

2. ロジックを生成する

• MySQLは内部的にグローバル変数global_query_idを維持している
• ステートメントが実行されるたびに、global_query_idがQuery_idに割り当てられ、次にglobal_query_id+1が割り当てられます。
• 現在のステートメントがこのトランザクションによって実行される最初のステートメントである場合、Query_id をこのトランザクションの XID に割り当てます。

3. global_query_idは純粋なメモリ変数であり、再起動後にクリアされます。

• したがって、同じデータベース インスタンス内で、異なるトランザクションの XID が同じになる可能性があります。
• MySQLが再起動すると、新しいbinlogが再生成されます。
  • 保証: XID は同じ binlog ファイル内で一意です
• global_query_idが上限に達すると、0からカウントを続けます。
  • したがって、理論上は同じ XID が同じバイナリログに表示されますが、その確率は極めて低くなります。

4. global_query_idは8バイトで、上限は2^64-1です。

• XIDがAであると仮定してトランザクションを実行する
• 次に、クエリステートメントを2^64回実行し、global_query_idをAに戻します。
• 別のトランザクションを開始します。このトランザクションのXIDもAです。

InnoDB の trx_id

1. XIDはサーバー層によって管理される

2. InnoDB は内部的に trx_id を使用して、InnoDB トランザクションをサーバー層に関連付けます。

3. InnoDBは内部的にグローバル変数max_trx_idを維持している

• 新しいtrx_idを申請する必要があるたびに、max_trx_idの現在の値を取得し、次にmax_trx_id+1を取得します。

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

• 各データ行には、それを更新したtrx_idが記録されます。
• トランザクションがデータ行を読み取るとき、データの可視性を決定する方法
  • トランザクションの一貫性のあるビューをこのデータ行のtrx_idと比較します

5. 実行中のトランザクションについては、information_schema.innodb_trx を通じてトランザクションの trx_id を確認できます。

操作手順

-- T2 では、mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
+-----------------+---------------------+
| trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
| 281479812572992 | 30 |
+-----------------+---------------------+

-- T4 で、mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
+-----------------+---------------------+
| trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
| 7417540 | 30 |
+-----------------+---------------------+

mysql> プロセスリストを表示します。
+----+-----------------+-----------+--------------------+---------+------------------------+------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+----+-----------------+-----------+--------------------+---------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | デーモン | 344051 | 空のキューを待機中 | NULL |
| 30 | ルート | ローカルホスト | テスト | スリープ | 274 | | NULL |
| 31 | root | localhost | information_schema | クエリ | 0 | 開始 | SHOW PROCESSLIST |
+----+-----------------+-----------+--------------------+---------+------------------------+------------------+

1. trx_mysql_thread_id=30 はスレッドID、つまりセッションAが配置されているスレッドです。

2. T1では、trx_idの値は実際には0であり、大きな値は表示用のみです（通常の読み取りおよび書き込みトランザクションとは異なります）。

3. T2 の時点では、trx_id は大きな数値になっています。これは、T1 の時点でセッション A に更新操作が含まれず、読み取り専用トランザクションであったためです。

• 読み取り専用トランザクションの場合、InnoDBはtrx_idを割り当てません。

4. セッションAがT3の時点でINSERT文を実行すると、InnoDBは実際にtrx_idを割り当てる。

読み取り専用トランザクション

1. 上記の時間T2で、大きなtrx_idがシステムによって一時的に計算されます。

• 現在のトランザクションのtrx変数のポインタアドレスを整数に変換し、2^48を加算します。

2. 同じ読み取り専用トランザクションの実行中、そのポインタアドレスは変更されません。

• innodb_trxテーブルでもinnodb_locksテーブルでも、同じ読み取り専用トランザクションで見つかったtrx_idは同じです。

3. 複数の並列読み取り専用トランザクションがある場合、各トランザクションのtrx変数のポインタアドレスは異なる必要があります。

• 同時実行されている読み取り専用トランザクションごとに trx_id が異なります。

4. 2^48 を追加する目的は、読み取り専用トランザクションによって表示される trx_id 値が比較的大きくなり、通常の読み取り/書き込みトランザクションを区別するために使用されるようにすることです。

5. trx_idのロジックはrow_idに似ており、長さは8バイトと定義されています。

• 理論的には、読み取り/書き込みトランザクションで読み取り専用トランザクションと同じ trx_id を表示することが可能です。
• しかし、その可能性は極めて低く、実害はない。

6. 読み取り専用トランザクションにtrx_idを割り当てないことの利点

• トランザクションビューでアクティブ配列のサイズを縮小できます
  • 現在実行中の読み取り専用トランザクションは、データの可視性に影響を与えません。
  • したがって、トランザクションの一貫したビューを作成するときは、読み取りおよび書き込みトランザクションの trx_id をコピーするだけで済みます。
• trx_idアプリケーションの数を減らすことができます
  • InnoDB では、通常の SELECT 文を 1 つだけ実行した場合でも、実行プロセス中は読み取り専用トランザクションに該当します。
  • 通常のクエリ ステートメントが trx_id に適用されない場合は、trx_id を適用する同時トランザクションによって発生するロックの競合を大幅に削減できます。
  • 読み取り専用トランザクションはtrx_idを割り当てないため、trx_idの増加率は遅くなります。

7. max_trx_id は永続的に保存され、再起動後も 0 にリセットされません。上限の 2^48-1 に達した後にのみ 0 にリセットされます。

スレッドID

1. SHOW PROCESSLISTの最初の列はthread_idです

2. システムは環境変数thread_id_counterを保存します。

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

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

• しかし、SHOW PROCESSLISTでは2つの同一のthread_idは表示されません。
• MySQLは新しいスレッドにthread_idを割り当てるための独自の配列ロジックを設計しているため、ロジックコードは次のようになります。

する {
  新しいID = スレッドIDカウンタ++;
} while (!thread_ids.insert_unique(new_id).second);

参考文献

「MySQL実践45講義」

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。