Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 26.8Mb
PDF (A4) - 26.9Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb


MySQL 5.6 リファレンスマニュアル  /  ...  /  構成可能な InnoDB の自動インクリメントロック

14.6.5.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 ステートメントで割り当てられる具体的な自動インクリメント値は非決定的となり、実行するたびに値が異なる可能性があります。

InnoDB では、行数が事前にわかっている場合は、INSERT ステートメントのクラスに対してテーブルレベル AUTO-INC ロックが使用されることを回避できますが、ステートメントベースのレプリケーションの決定的な実行および安全性は、引き続き保持されます。さらに、リカバリまたはレプリケーションの一部として SQL ステートメントを再現する際にバイナリログを使用しない場合は、並列性およびパフォーマンスをさらに改善するために、テーブルレベル AUTO-INC ロックの使用を完全に除去できますが、ステートメントで割り当てられた自動インクリメント数のギャップが許可され、並列実行されるステートメントで割り当てられた数がインターリーブされる可能性があるという犠牲が伴います。

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

この新しいロックスキームを使用すると、拡張性を大幅に改善できますが、元のメカニズムと比べて、自動インクリメント値が割り当てられる方法にわずかな相違が散見されます。InnoDB での自動インクリメントの動作を説明するために、次の説明でいくつかの用語を定義し、サーバーの起動時に設定できる innodb_autoinc_lock_mode 構成パラメータのさまざまな設定を使用した InnoDB の動作について説明します。自動インクリメントロックの動作説明のあとで、追加の注意事項について説明します。

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

  • INSERT のようなステートメント

    INSERTINSERT ... SELECTREPLACEREPLACE ... SELECTLOAD DATA など、テーブル内に新しい行を生成するすべてのステートメントです。

  • 単純挿入

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

  • 一括挿入

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

  • 混在モード挿入

    これらは、新しい行の一部 (全部ではない) の自動インクリメント値を指定する 単純挿入 ステートメントです。次の例を示します。c1 はテーブル t1AUTO_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 パラメータには、次の 3 つの設定を指定できます。

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

    このロックモードでは、innodb_autoinc_lock_mode が存在する前と同じ動作が提供されます。すべての INSERT のようなステートメントでは、特殊なテーブルレベル AUTO-INC ロックが取得され、ステートメントの終了まで保持されます。これにより、特定のステートメントによって割り当てられた自動インクリメント値が連続的になります。

    このロックモードの用途は次のとおりです。

    • 下位互換性。

    • パフォーマンスのテスト。

    • 混在モード挿入での問題の対処 (あとで説明するセマンティクスに相違がある可能性があるため)。

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

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

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

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

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

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

  • 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 カラムに格納されている値にはギャップが存在する可能性があります。

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

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

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

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

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

    単純挿入が (全部ではなく) 一部の結果行の自動インクリメント値を指定する混在モード挿入を検討します。このようなステートメントの動作は、ロックモード 0、1、および 2 で異なります。たとえば、c1 はテーブル t1AUTO_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    |
    +-----+------+

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

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

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

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

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

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

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


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.