DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info.gz) InnoDB auto-increment column

Info Catalog (mysql.info.gz) Converting tables to InnoDB (mysql.info.gz) Using InnoDB tables (mysql.info.gz) InnoDB foreign key constraints
 
 15.7.3 How an `AUTO_INCREMENT' Column Works in `InnoDB'
 -------------------------------------------------------
 
 If you specify an `AUTO_INCREMENT' column for a table, the `InnoDB'
 table handle in the data dictionary will contain a special counter
 called the auto-increment counter that is used in assigning new values
 for the column. The auto-increment 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, when a user first does an insert to a
 table `T', `InnoDB' executes the equivalent of this statement:
 
      SELECT MAX(ai_col) FROM T FOR UPDATE;
 
 The value retrieved by the statement is incremented by one and assigned
 to the column and the auto-increment counter of the table. If the table
 is empty, the value `1' is assigned. If the auto-increment counter is
 not initialized and the user invokes a `SHOW TABLE STATUS' statement
 that displays output for the table `T', the counter is initialized (but
 not incremented) and stored for use by later inserts.  Note that in
 this initialization we do 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.
 
 Note that if the 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.
 
 After the auto-increment counter has been initialized, if a 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. If the user does not explicitly specify a
 value, `InnoDB' increments the counter by one and assigns the new value
 to the column.
 
 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. Two transactions cannot have the
 `AUTO-INC' lock on the same table simultaneously.
 
 Note that you may see gaps in the sequence of values assigned to the
 `AUTO_INCREMENT' column if you roll back transactions that have gotten
 numbers from the counter.
 
 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.
 
Info Catalog (mysql.info.gz) Converting tables to InnoDB (mysql.info.gz) Using InnoDB tables (mysql.info.gz) InnoDB foreign key constraints
automatically generated byinfo2html