(mysql.info) innodb-auto-increment-column

Info Catalog

(mysql.info) converting-tables-to-innodb

(mysql.info) using-innodb-tables

(mysql.info) innodb-foreign-key-constraints

 
 14.2.6.3 How `AUTO_INCREMENT' Columns Work in `InnoDB'
 ......................................................
 
 If you specify an `AUTO_INCREMENT' column for an `InnoDB' table, the
 table handle in the `InnoDB' data dictionary contains a special counter
 called the auto-increment counter that is used in assigning new values
 for the column. This counter is stored only in main memory, not on disk.
 
 `InnoDB' uses the following algorithm to initialize the auto-increment
 counter for a table `T' that contains an `AUTO_INCREMENT' column named
 `ai_col': After a server startup, for the first insert into a table `T',
 `InnoDB' executes the equivalent of this statement:
 
      SELECT MAX(ai_col) FROM T FOR UPDATE;
 
 `InnoDB' increments by one the value retrieved by the statement and
 assigns it to the column and to the auto-increment counter for the
 table. If the table is empty, `InnoDB' uses the value `1'.  If a user
 invokes a `SHOW TABLE STATUS' statement that displays output for the
 table `T' and the auto-increment counter has not been initialized,
 `InnoDB' initializes but does not increment the value and stores it for
 use by later inserts. Note that this initialization uses a normal
 exclusive-locking read on the table and the lock lasts to the end of
 the transaction.
 
 `InnoDB' follows the same procedure for initializing the auto-increment
 counter for a freshly created table.
 
 After the auto-increment counter has been initialized, if a user does
 not explicitly specify a value for an `AUTO_INCREMENT' column, `InnoDB'
 increments the counter by one and assigns the new value to the column.
 If the user inserts a row that explicitly specifies the column value,
 and the value is bigger than the current counter value, the counter is
 set to the specified column value.
 
 You may see gaps in the sequence of values assigned to the
 `AUTO_INCREMENT' column if you roll back transactions that have
 generated numbers using the counter.
 
 If a user specifies `NULL' or `0' for the `AUTO_INCREMENT' column in an
 `INSERT', `InnoDB' treats the row as if the value had not been
 specified and generates a new value for it.
 
 The behavior of the auto-increment mechanism is not defined if a user
 assigns a negative value to the column or if the value becomes bigger
 than the maximum integer that can be stored in the specified integer
 type.
 
 When accessing the auto-increment counter, `InnoDB' uses a special
 table-level `AUTO-INC' lock that it keeps to the end of the current SQL
 statement, not to the end of the transaction. The special lock release
 strategy was introduced to improve concurrency for inserts into a table
 containing an `AUTO_INCREMENT' column. Nevertheless, two transactions
 cannot have the `AUTO-INC' lock on the same table simultaneously, which
 can have a performance impact if the `AUTO-INC' lock is held for a long
 time. That might be the case for a statement such as `INSERT INTO t1
 ... SELECT ... FROM t2'.
 
 `InnoDB' uses the in-memory auto-increment counter as long as he server
 runs. When the server is stopped and restarted, `InnoDB' reinitializes
 the counter for each table for the first `INSERT' to the table, as
 described earlier.
 
 Beginning with MySQL 5.0.3, `InnoDB' supports the `AUTO_INCREMENT = N'
 table option in `CREATE TABLE' and `ALTER TABLE' statements, to set the
 initial counter value or alter the current counter value. The effect of
 this option is canceled by a server restart, for reasons discussed
 earlier in this section.
 

Info Catalog

(mysql.info) converting-tables-to-innodb

(mysql.info) using-innodb-tables

(mysql.info) innodb-foreign-key-constraints