「INSERT のような」ステートメント

INSERT、INSERT ... SELECT、REPLACE、REPLACE ... SELECT、LOAD DATA など、テーブル内に新しい行を生成するすべてのステートメントです。 「simple-inserts」、「bulk-inserts」 および 「mixed-mode」 の挿入が含まれます。

「単純挿入」

挿入行数を事前に (ステートメントの初期処理時に) 決定できるステートメントです。 これには、ネストしたサブクエリーを持たない単一行および複数行の INSERT および REPLACE ステートメントが含まれますが、INSERT ... ON DUPLICATE KEY UPDATE は含まれません。

「一括挿入」

挿入行数 (および必要な自動インクリメント値の数) が事前にわからないステートメントです。 これには、INSERT ... SELECT、REPLACE ... SELECT、および LOAD DATA ステートメントが含まれますが、単純な INSERT は含まれません。 InnoDB では、各行が処理されるたびに AUTO_INCREMENT カラムに新しい値が割り当てられます。

「混在モード挿入」

これらは、新しい行の一部 (全部ではない) の自動インクリメント値を指定する 「単純挿入」 ステートメントです。 次の例を示します。c1 はテーブル t1 の AUTO_INCREMENT カラムです。

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

INSERT ... ON DUPLICATE KEY UPDATE は別のタイプの 「混在モード挿入」 で、最悪の場合には実質 INSERT のあとに UPDATE を実行することに相当しますが、AUTO_INCREMENT カラムに割り当てられた値は、更新フェーズで使用される可能性も使用されない可能性もあります。

innodb_autoinc_lock_mode = 0 (「従来」 ロックモード)

従来のロックモードでは、innodb_autoinc_lock_mode 構成パラメータが MySQL 5.1 で導入される前と同じ動作が提供されます。 従来のロックモードオプションは、セマンティックに違いがある可能性があるため、下位互換性、パフォーマンステストおよび混合モードの挿入に関する問題の回避のために提供されています。

このロックモードでは、すべての 「INSERT-like」 ステートメントは、AUTO_INCREMENT カラムを含むテーブルに挿入するための特別なテーブルレベルの AUTO-INC ロックを取得します。 通常、このロックは (トランザクションの最後ではなく) ステートメントの最後に保持され、特定の一連の INSERT ステートメントに対して予測可能で繰返し可能な順序で自動増分値が割り当てられ、特定のステートメントによって割り当てられた自動増分値が連続していることを確認します。

ステートメントベースレプリケーションの場合、これは、SQL ステートメントがレプリカサーバー上でレプリケートされるときに、ソースサーバー上と同じ値が自動インクリメントカラムに使用されることを意味します。 複数の INSERT ステートメントの実行結果は決定的で、レプリカはソースと同じデータを再現します。 複数の INSERT ステートメントによって生成された自動インクリメント値がインターリーブされた場合、2 つの同時 INSERT ステートメントの結果は非決定的になり、ステートメントベースのレプリケーションを使用してレプリカサーバーに確実に伝播できませんでした。

この点が明確になるように、次のテーブルを使用する例を考えてみましょう。

CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;

実行中のトランザクションが 2 つ存在しており、それぞれ AUTO_INCREMENT カラムを含むテーブル内に行を挿入しているものとします。 1 つのトランザクションは 1000 行を挿入する INSERT ... SELECT ステートメントを使用しており、もう 1 つのトランザクションは 1 行を挿入する単純な INSERT ステートメントを使用しています。

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

InnoDB では、Tx1 の INSERT ステートメントで SELECT から取得される行数を事前に確認できず、ステートメントの進行に応じて一度に自動増分値が割り当てられます。 ステートメントの終了まで保持されるテーブルレベルロックが存在しているため、ある時点で実行可能な INSERT ステートメントはテーブル t1 を参照している 1 つのステートメントだけであり、複数ステートメントによって自動インクリメント番号の生成がインターリーブされることはありません。 Tx1 INSERT ... SELECT ステートメントによって生成される自動インクリメント値は連続しており、Tx2 の INSERT ステートメントで使用される (単一の) 自動インクリメント値は、最初に実行されるステートメントに応じて、Tx1 で使用されるすべての値より小さくなるか大きくなります。

SQL ステートメントがバイナリログからリプレイされたときに (ステートメントベースレプリケーションを使用している場合、または復旧シナリオで) 同じ順序で実行されているかぎり、結果は Tx1 および Tx2 が最初に実行されたときと同じです。 したがって、ステートメントの終了まで保持されるテーブルレベルロックが存在することで、自動インクリメントを使用する INSERT ステートメントをステートメントベースのレプリケーションで安全に使用できるようになります。 ただし、複数のトランザクションが同時に INSERT ステートメントを実行している場合、これらのテーブルレベルのロックによって同時実行性およびスケーラビリティが制限されます。

前述の例でテーブルレベルロックが存在しなかった場合、Tx2 の INSERT で使用される自動インクリメントカラムの値は、ステートメントが実際に実行されるタイミングに応じて変更されます。 Tx1 の INSERT の (実行前や完了後ではなく) 実行中に、Tx2 の INSERT が実行された場合、その 2 つの INSERT ステートメントで割り当てられる具体的な自動インクリメント値は非決定的となり、実行するたびに値が異なる可能性があります。

consecutive ロックモードでは、InnoDB は、行数が事前にわかっている「「単純挿入」」ステートメントに対してテーブルレベルの AUTO-INC ロックを使用せずに、ステートメントベースのレプリケーションの決定論的な実行および安全性を維持できます。

バイナリログを使用して SQL ステートメントを回復またはレプリケーションの一部としてリプレイしない場合、interleaved ロックモードを使用すると、ステートメントによって割り当てられた自動インクリメント番号のギャップを許可し、同時に実行するステートメントによって割り当てられた番号を保持する可能性がありますが、並列性とパフォーマンスを向上させるために、テーブルレベルの AUTO-INC ロックのすべての使用を排除できます。

innodb_autoinc_lock_mode = 1 (「連続」 ロックモード)

このモードでは、「一括挿入」 は特殊な AUTO-INC テーブルレベルロックを使用し、そのロックをステートメントの終了まで保持します。 これは、INSERT ... SELECT、REPLACE ... SELECT、LOAD DATA のすべてのステートメントに当てはまります。 一度に実行できるステートメントは、AUTO-INC ロックを保持している 1 つのステートメントだけです。 一括挿入操作のソーステーブルがターゲットテーブルと異なる場合は、ソーステーブルから選択された最初の行で共有ロックが取得された後に、ターゲットテーブルの AUTO-INC ロックが取得されます。 バルク挿入操作のソースとターゲットが同じテーブルの場合、選択したすべての行で共有ロックが取得された後に AUTO-INC ロックが取得されます。

「「単純な挿入」」 (事前に挿入される行数がわかっている) では、ステートメントが完了するまでではなく、割当てプロセスの間のみ保持される mutex (軽量ロック) の制御下で必要な数の自動増分値を取得することで、テーブルレベルの AUTO-INC ロックを回避します。 AUTO-INC ロックが別のトランザクションによって保持されていないかぎり、テーブルレベルの AUTO-INC ロックは使用されません。 別のトランザクションが AUTO-INC ロックを保持している場合、「「単純挿入」」は「「一括挿入」」であるかのように AUTO-INC ロックを待機します。

このロックモードでは、行数が事前にわからない (したがってステートメントの処理中に自動インクリメント番号が割り当てられる) INSERT ステートメントが存在する場合には、任意の 「INSERT のような」 ステートメントによって割り当てられたすべての自動インクリメント値が必ず連続した値になるため、その処理は、ステートメントベースのレプリケーションで使用しても安全です。

このロックモードを使用すると、ステートメントベースのレプリケーションで安全に使用できるため、スケーラビリティが大幅に向上します。 さらに、「従来」ロックモードの場合と同じく、任意のステートメントによって割り当てられた自動インクリメント番号が連続的になります。 自動増分を使用するステートメントには、「「従来型」」モードと比較してセマンティクスの変更なしがありますが、重要な例外があります。

例外は「混在モード挿入」です。この挿入では、ユーザーは複数行の「単純挿入」で、明示的な値を全部ではなく、一部の行の AUTO_INCREMENT カラムに指定します。 このような挿入の場合、InnoDB は挿入される行数より多くの自動インクリメント値を割り当てます。 ただし、自動的に割り当てられる値はすべて連続的に生成されるため、直前に実行されたステートメントによって生成された自動インクリメント値よりも値が大きくなります。 「余分」な番号は失われます。

innodb_autoinc_lock_mode = 2 (「インターリーブ」 ロックモード)

このロックモードでは、テーブルレベル AUTO-INC ロックを使用する 「INSERT のような」ステートメントは 1 つも存在しないため、複数のステートメントを同時に実行できます。 これはもっとも高速で、もっとも拡張性の高いロックモードです。ただし、ステートメントベースのレプリケーションを使用する場合や、リカバリシナリオでバイナリログから SQL ステートメントを再現する際には、安全ではありません。

このロックモードでは、自動インクリメント値は一意であり、並列実行されているすべての 「INSERT のような」ステートメントにわたって単調に増加することが保証されます。 ただし、複数のステートメントが同時に番号を生成している (つまり番号の割り当てが複数のステートメント間でインターリーブされている) 可能性があるため、任意のステートメントによって挿入される行に対して生成された値が連続的でない可能性があります。

実行中のステートメントが「「単純な挿入」」のみで、挿入される行数が事前にわかっている場合、「「混合モードの挿入」」を除き、単一のステートメントに対して生成される番号にギャップはありません。 ただし、「一括挿入」が実行されると、特定のステートメントで割り当てられた自動インクリメント値にギャップが発生する可能性があります。

レプリケーションでの自動インクリメントの使用

ステートメントベースレプリケーションを使用している場合は、innodb_autoinc_lock_mode を 0 または 1 に設定し、ソースとその複製で同じ値を使用します。 innodb_autoinc_lock_mode = 2 (「「インターリーブ」」) またはソースとレプリカが同じロックモードを使用しない構成を使用する場合、レプリカとソースで自動インクリメント値が同じであることは保証されません。

行ベースレプリケーションは SQL ステートメントの実行順序に左右されない (混在形式は、ステートメントベースレプリケーションでは安全でないステートメントで行ベースレプリケーションを使用する) ため、行ベースまたは混在形式レプリケーションを使用している場合は、すべての自動インクリメントロックモードが安全です。

「失われた」自動インクリメント値とシーケンスギャップ

すべてのロックモード (0、1、および 2) では、自動インクリメント値を生成したトランザクションがロールバックされると、これらの自動インクリメント値が「失われます」。 「INSERT のような」ステートメントが完了したかどうか、およびそれを含むトランザクションがロールバックされたかどうかに関係なく、自動インクリメントカラムの値は一度生成されたら、ロールバックできません。 このような失われた値は再使用されません。 したがって、テーブルの AUTO_INCREMENT カラムに格納されている値にはギャップが存在する可能性があります。

AUTO_INCREMENT カラムに NULL または 0 を指定

すべてのロックモード (0、1 および 2) で、ユーザーが INSERT の AUTO_INCREMENT カラムに NULL または 0 を指定すると、InnoDB はその行を値が指定されていないかのように処理し、新しい値を生成します。

AUTO_INCREMENT カラムへの負の値の割当て

すべてのロックモード (0、1 および 2) では、AUTO_INCREMENT カラムに負の値を割り当てる場合、自動増分メカニズムの動作は定義されません。

AUTO_INCREMENT 値が指定された整数型の最大整数より大きい場合

すべてのロックモード (0、1、および 2) では、値が指定された整数型に格納できる最大整数を超えると、自動インクリメントメカニズムの動作は定義されません。

「一括挿入」の自動インクリメント値のギャップ

innodb_autoinc_lock_mode が 0 (「「従来型」」) または 1 (「「連続」」) に設定されている場合、テーブルレベルの AUTO-INC ロックはステートメントの最後まで保持され、そのようなステートメントのみを一度に実行できるため、特定のステートメントによって生成される自動増分値はギャップなしで連続しています。

innodb_autoinc_lock_mode が 2 (「インターリーブ」) に設定されている場合、「一括挿入」によって生成された自動インクリメント値にギャップが存在する可能性がありますが、並列実行中の 「INSERT のような」ステートメントが存在する場合に限ります。

一括挿入では、各ステートメントで必要となる自動インクリメント値の正確な数がわからず、過大評価される可能性があるため、ロックモードが 1 または 2 の場合は、連続したステートメント間でギャップが発生する可能性があります。

「混在モード挿入」によって割り当てられる自動インクリメント値

「単純挿入」が (全部ではなく) 一部の結果行の自動インクリメント値を指定する「混在モード挿入」を検討します。 このようなステートメントの動作は、ロックモード 0、1、および 2 で異なります。 たとえば、c1 はテーブル t1 の AUTO_INCREMENT カラムで、自動生成されたシーケンス番号の最新値が 100 であるとします。

mysql> CREATE TABLE t1 (
    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> c2 CHAR(1)
    -> ) ENGINE = INNODB;

ここで、次の「「混合モードの挿入」」ステートメントについて考えてみます:

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

innodb_autoinc_lock_mode が 0 (「「従来型」」) に設定されている場合、4 つの新しい行は次のとおりです:

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

次に使用可能な自動インクリメント値は 103 です。これは、自動インクリメント値が一度に 1 つずつ割り当てられ、ステートメントの実行開始時に一度に割り当てられるわけではないためです。 この結果は、並列実行中の (任意の型の) 「INSERT のような」ステートメントが存在するかどうかに左右されません。

innodb_autoinc_lock_mode を 1 (「「連続」」) に設定すると、次の 4 つの新しい行も表示されます:

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

ただし、この場合、次に使用可能な自動増分値は 103 ではなく 105 です。これは、ステートメントの処理時に 4 つの自動増分値が割り当てられるためですが、使用されるのは 2 つのみであるためです。 この結果は、並列実行中の (任意の型の) 「INSERT のような」ステートメントが存在するかどうかに左右されません。

innodb_autoinc_lock_mode がモード 2 (「「インターリーブ」」) に設定されている場合、4 つの新しい行は次のとおりです:

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+

x および y の値は一意で、以前に生成された行より大きくなります。 ただし、x および y の特定の値は、同時に実行するステートメントによって生成される自動インクリメント値の数によって異なります。

最後に、最後に生成された順序番号が 100 の場合に発行される次のステートメントについて考えてみます:

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');

innodb_autoinc_lock_mode 設定では、101 が行 (NULL, 'b') に割り当てられ、行 (101, 'c') の挿入が失敗するため、このステートメントによって重複キーエラー 23000 (Can't write; duplicate key in table) が生成されます。

一連の INSERT ステートメントの途中での AUTO_INCREMENT カラム値の変更

MySQL 5.7 以前では、一連の INSERT ステートメントの途中で AUTO_INCREMENT カラムの値を変更すると、「「重複エントリ」」エラーが発生する可能性がありました。 たとえば、AUTO_INCREMENT カラムの値を現在の最大自動増分値より大きい値に変更する UPDATE 操作を実行した場合、未使用の自動増分値を指定しなかった後続の INSERT 操作では「「重複エントリ」」エラーが発生する可能性があります。 MySQL 8.0 以降では、AUTO_INCREMENT カラムの値を現在の最大自動増分値より大きい値に変更すると、新しい値が永続化され、後続の INSERT 操作では、新しい大きい値から始まる自動増分値が割り当てられます。 この動作を次の例に示します。

mysql> CREATE TABLE t1 (
    -> c1 INT NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (c1)
    ->  ) ENGINE = InnoDB;

mysql> INSERT INTO t1 VALUES(0), (0), (3);

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+

mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
+----+

mysql> INSERT INTO t1 VALUES(0);

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
|  5 |
+----+