Skip navigation links
9.4.4 FOREIGN KEY 制約 »
Section Navigation      [Toggle]

9.4.3.2. 設定可能な InnoDB 自動インクリメントロック

前の節で説明したように、InnoDB は 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 から取得される行数を事前に知ることができないため、そのステートメントの処理を進める際に、自動インクリメント値を一度に 1 つずつ割り当てます。ステートメントの終了まで保持されるテーブルレベルロックが存在しているため、ある時点で実行可能な INSERT ステートメントはテーブル t1 を参照している 1 つのステートメントだけであり、複数ステートメントによって自動インクリメント番号の生成がインターリーブされることはありません。Tx1 の INSERT ... SELECT ステートメントで生成される自動インクリメント値は連続した番号となり、Tx2 の INSERT ステートメントで使用される (単一の) 自動インクリメント値は、どちらのステートメントが先に実行されるかに応じて、Tx1 で使用されるすべての値よりも小さいか大きい値になります。

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

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

MySQL 5.1.22 以降では、InnoDB は行数が事前にわかっているタイプの INSERT ステートメントでテーブルレベル AUTO-INC ロックの使用を回避できるようになりましたが、その場合でも、ステートメントベースのレプリケーションでの決定性実行と安全性を維持します。さらに、復旧やレプリケーションの一環としてバイナリログを使って SQL ステートメントの再実行を行わない場合には、テーブルレベル AUTO-INC ロックの使用を完全に排除してさらに高い並列性やパフォーマンスを得ることもできますが、その代わり、1 つのステートメントで割り当てられる自動インクリメント番号のギャップを受け入れる必要があるほか、並列実行中のステートメントによって割り当てられる番号がインターリーブされる可能性もあります。

ステートメントの処理開始時点で挿入行数がわかっているような INSERT ステートメントでは、InnoDB はロックを一切使用せずに必要な数の自動インクリメント値をすばやく割り当てますが、それが可能なのは、テーブルレベル AUTO-INC ロックをすでに保持している並列セッションが存在しない場合だけです (その別のステートメントは処理中に自動インクリメント値を 1 つずつ割り当てるため)。より正確に言えば、そのような INSERT ステートメントは相互排他ロック (軽量ロック) の制御下で自動インクリメント値を取得しますが、このロックが保持されるのはステートメントの完了までではなく、割り当て処理の間だけです。

この新しいロック方式を使うとスケーラビリティーが大幅に向上しますが、この方式では元の機構と比べて、自動インクリメント値の割り当て方法が微妙に違います。InnoDB における自動インクリメントの動作方法を説明するため、以下ではいくつかの用語を定義し、InnoDB が新しい innodb_autoinc_lock_mode 設定パラメータの各種設定を使ってどのように動作するのかについて説明します。自動インクリメントロックの動作説明のあとで、追加の注意事項について説明します。

まず、いくつかの定義を次に示します。

  • 「INSERT 様」ステートメント

    INSERT、INSERT ... SELECT、REPLACE、REPLACE ... SELECT、LOAD DATA など、テーブル内に新しい行を生成するすべてのステートメント。

  • 「単純挿入」

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

  • 「一括挿入」

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

  • 「混在モード挿入」

    これらは、新しい行の一部 (全部ではない) の自動インクリメント値を指定する 「単純挿入」 ステートメントです。次の例を示します。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 カラムに割り当てられた値は、更新フェーズで使用される可能性もされない可能性もあります。

MySQL 5.1.22 以降では、InnoDB が AUTO_INCREMENT カラムの値を生成する際にロックをどのように使用するかを制御する設定パラメータが存在しています。このパラメータを設定するには、mysqld の起動時に --innodb-autoinc-lock-mode オプションを指定します。

一般に、自動インクリメントの動作方法に関する (おそらくレプリケーション関連の) 問題が発生した場合には、ロックモードを 0 に設定することで元の動作を強制的に使用できます。

innodb_autoinc_lock_mode パラメータには、選択可能な次の 3 つの設定が存在しています。

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

    このロックモードは、innodb_autoinc_lock_mode の追加前と同じ動作を提供します。すべての 「INSERT 様」 ステートメントで特殊なテーブルレベル AUTO-INC ロックが取得され、ステートメントの終了まで保持されます。これにより、任意のステートメントで割り当てられた自動インクリメント値が連続した値になることが保証されます (ただし、自動インクリメント値を生成したトランザクションがロールバックされた場合には、「ギャップ」 がテーブル内に存在している可能性がある。これについては後述)。

    このロックモードは、下位互換性とパフォーマンステストのためだけに提供されています。「混在モード挿入」 を使用する場合に後述の重要な意味上の違いが気にならないかぎり、このロックモードを使用する理由はほとんどありません。

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

    これがデフォルトのロックモードです。このモードでは、「一括挿入」 は特殊な AUTO-INC テーブルレベルロックを使用し、そのロックをステートメントの終了まで保持します。これは、INSERT ... SELECT、REPLACE ... SELECT、LOAD DATA のすべてのステートメントに当てはまります。ある時点で実行可能なステートメントは、AUTO-INC ロックを保持している 1 つのステートメントだけです。

    このロックモードでは、「単純挿入」 (のみ) が、自動インクリメント値の割り当てのときに軽量相互排他ロックが使用される新しいロックモデルを使用します。別のトランザクションがテーブルレベル AUTO-INC ロックを保持しているのでないかぎり、AUTO-INC ロックは使用されません。別のトランザクションが AUTO-INC ロックを保持している場合、「単純挿入」 はまるで自身も 「一括挿入.」 であるかのように、AUTO-INC ロックが解放されるのを待ちます。

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

    簡単に言えば、このロックモードの重要な効果は、スケーラビリティーの大幅な向上です。このモードは、ステートメントベースのレプリケーションで使用しても安全です。さらに、「従来」 ロックモードの場合と同じく、任意のステートメントによって割り当てられた自動インクリメント番号が「連続」した値になります。このモードでは 「従来」 モードと比較して、ある重要な例外を除けば、自動インクリメントを使用する任意のステートメントでの意味上の「違い」はありません。

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

    INSERT ... ON DUPLICATE KEY UPDATE を使用する場合にも似たような状況になります。このステートメントも 「混在モード挿入」 として分類されていますが、これは、自動インクリメント値が必ずしもすべての行で生成されないからです。InnoDB は実際に挿入が試みられる前に自動インクリメント値を割り当てるため、挿入する値が既存の値と重複しているかどうかを知ることができず、したがって自身が生成する自動インクリメント値が新しい行で使用されるかどうかを知ることができません。したがって、ステートメントベースのレプリケーションを使用する場合には、INSERT ... ON DUPLICATE KEY UPDATE を使用しないようにするか、あるいは innodb_autoinc_lock_mode = 0 (「従来」 ロックモード) を使用する必要があります。

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

    このロックモードでは、テーブルレベル AUTO-INC ロックを使用する 「INSERT 様」 ステートメントは 1 つも存在せず、複数のステートメントを同時に実行できます。これはもっとも高速でスケーラビリティーの高いロックモードですが、このモードは、ステートメントベースレプリケーションを使用する場合や復旧シナリオでバイナリログから SQL ステートメントを再実行する際には「安全」ではありません。

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

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

innodb_autoinc_lock_mode によって提供される自動インクリメントロックモードには、次のように使用上の暗黙の前提がいくつかあります。

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

    ステートメントベースのレプリケーションを使用する場合には、innodb_autoinc_lock_mode を 0 または 1 に設定し、マスターとスレーブで同じ値を使用すべきです。innodb_autoinc_lock_mode = 2 (「インターリーブ」) を使用したりマスターとスレーブで同じロックモードを使用しないように設定したりした場合、マスターとスレーブの自動インクリメント値が同じになる保証はありません。

    行ベースのレプリケーションを使用する場合には、すべての自動インクリメントロックモードが安全になります。行ベースのレプリケーションは SQL ステートメントの実行順序に左右されません。

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

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

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

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

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

    innodb_autoinc_lock_mode が 0 (「従来」) に設定されている場合、4 つの新しい行は次のようになります。 

    +-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

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

    innodb_autoinc_lock_mode が 1 (「連続」) に設定されている場合も、4 つの新しい行は次のようになります。 

    +-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

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

    innodb_autoinc_lock_mode が 2 (「インターリーブ」) に設定されていると、4 つの新しい行は次のようになります。 

    +-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+

    x と y の値は一意であり、それまでに生成されたどの行よりも大きくなります。ただし、x と y の具体的な値は、並列実行中のステートメントによって生成された自動インクリメント値の個数によって変わります。

    最後に、生成された最新のシーケンス番号が値 4 だったときに次のステートメントを発行した場合を考えます。 

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

    innodb_autoinc_lock_mode の設定内容にかかわらず、このステートメントから重複キーエラー 23000 (Can't write; duplicate key in table) が生成されます。なぜなら、行 (NULL, 'b') に対して 5 が割り当てられ、行 (5, 'c') の挿入が失敗するからです。

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

    innodb_autoinc_lock_mode が 0 (「従来」) または 1 (「連続」) に設定されている場合、テーブルレベル AUTO-INC ロックがステートメントの終了まで保持され、ある時点で実行可能なステートメントはそうした 1 つのステートメントだけであるため、任意のステートメントによって生成される自動インクリメント値は、ギャップのない連続した値となります。

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