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


14.6.5.1 従来の InnoDB の自動インクリメントロック

InnoDB の自動インクリメント処理の元の実装では、ステートメントベースレプリケーションや特定のリカバリシナリオでバイナリログを使用すると発生する問題を回避するために、次のような方針が使用されています。

InnoDB テーブルに AUTO_INCREMENT カラムを指定すると、InnoDB データディクショナリ内のテーブルハンドルに、カラムに新しい値を割り当てる際に使用される自動インクリメントカウンタと呼ばれる特別なカウンタが含まれます。このカウンタは、ディスク上には格納されず、メインメモリー内にのみ格納されます。

InnoDB では、ai_col という名前の AUTO_INCREMENT カラムを含むテーブル t に自動インクリメントカウンタを初期化するために、次のようなアルゴリズムが使用されます。サーバーの起動のあとで、テーブル t への最初の挿入をするために、InnoDB は次のステートメントと同等なものを実行します。

SELECT MAX(ai_col) FROM t FOR UPDATE;

InnoDB は、ステートメントで取得された値を増分し、それをテーブルのカラムおよび自動インクリメントカウンタに割り当てます。デフォルトでは、値が 1 ずつ増分されます。このデフォルトは、auto_increment_increment 構成の設定でオーバーライドできます。

テーブルが空の場合、InnoDB では値 1 が使用されます。このデフォルトは、auto_increment_offset 構成の設定でオーバーライドできます。

自動インクリメントカウンタが初期化される前に、SHOW TABLE STATUS ステートメントで t テーブルが調査される場合は、InnoDB によって値は初期化されますが、増分されず、後続の挿入で使用するために格納されます。この初期化では、テーブル上で通常の排他ロック読み取りが使用され、そのロックはトランザクションの最後まで存続します。

InnoDB は、新たに作成されたテーブル用に自動インクリメントカウンタを初期化するときと同じ手順に従います。

自動インクリメントカウンタが初期化されたあとに、AUTO_INCREMENT カラムの値を明示的に指定しない場合は、InnoDB によってカウンタが増分され、新しい値がカラムに割り当てられます。カラム値を明示的に指定する行を挿入するときに、その値が現在のカウンタ値よりも大きい場合は、カウンタが指定されたカラム値に設定されます。

ユーザーが INSERTAUTO_INCREMENT カラムに NULL または 0 を指定すると、InnoDB では、値が指定されなかった場合と同様にその行が処理され、新しい値が生成されます。

カラムに負の値を割り当てる場合や、値が指定された整数型に格納できる最大整数よりも大きくなる場合は、自動インクリメントメカニズムの動作が定義されません。

自動インクリメントカウンタにアクセスするときに、InnoDB では、トランザクションの最後までではなく、現在の SQL ステートメントの最後まで存続する特別なテーブルレベルの AUTO-INC ロックが使用されます。AUTO_INCREMENT カラムを含むテーブルへの挿入の並列性を改善するために、特別なロック解放方針が導入されました。それにもかかわらず、2 つのトランザクションが同時に AUTO-INC ロックを同じテーブル上で持つことはできません。これにより、AUTO-INC ロックが長時間保持されると、パフォーマンスが影響を受ける可能性があります。これは、あるテーブルから別のテーブルにすべての行を挿入する INSERT INTO t1 ... SELECT ... FROM t2 などのステートメントの場合に発生する可能性があります。

InnoDB では、サーバーが実行されていれば、インメモリーの自動インクリメントカウンタが使用されます。前に説明したように、サーバーが停止して再起動されると、テーブルへの最初の INSERT 時に、InnoDB によってテーブルごとにカウンタが再初期化されます。

サーバーが再起動されると、CREATE TABLE および ALTER TABLE ステートメントの AUTO_INCREMENT = N テーブルオプションの効果も取り消されます。このオプションを InnoDB テーブルで使用すると、初期のカウンタの値を設定したり、現在のカウンタ値を変更したりできます。

カウンタを使用して数値が生成されたトランザクションをロールバックすると、AUTO_INCREMENT カラムに割り当てられた一連の値でギャップが見つかることがあります。


User Comments
  Posted by Wagner Bianchi on June 25, 2015
If for some sort of functionality one need to update the PK's value using the LAST_INSERT_ID(), take care to run the SHOW TABLE STATUS to update the dictionary ai_col to avoid a PK violation.

mysql> truncate table tb01;
Query OK, 0 rows affected (0.22 sec)

mysql> insert into tb01 (a,b) values (0,'wbjr');
Query OK, 1 row affected (0.01 sec)

mysql> select a,b from tb01;
+---+------+
| a | b |
+---+------+
| 1 | wbjr |
+---+------+
1 row in set (0.00 sec)

mysql> update tb01 set a=LAST_INSERT_ID(a+1) where a=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> insert into tb01 (a,b) values (0,'wbjr');
ERROR 1062 (23000): Duplicate entry '2' for key 'a'

#: here it seems that the LAST_INSERT_ID() was updated with the last one
#: but not the internal ai_col

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)

mysql> show table status like 'tb01'\G
*************************** 1. row ***************************
Name: tb01
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 3
Create_time: 2015-06-25 11:47:29
Update_time: 2015-06-25 11:48:20
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

#: after running the SHOW TABLE STATUS, the ai_col get the table's MAX(ai_col)

mysql> insert into tb01 (a,b) values (0,'wbjr');
Query OK, 1 row affected (0.04 sec)

mysql> select a,b from tb01;
+---+------+
| a | b |
+---+------+
| 2 | wbjr |
| 3 | wbjr |
+---+------+
2 rows in set (0.00 sec)
Sign Up Login You must be logged in to post a comment.