Si vous spécifiez une colonne AUTO_INCREMENT dans une table, la table InnoDB va ajouter dans le dictionnaire de données un compteur spécial appelé le compteur auto-incrément, qui est utilisé pour assigner les nouvelles valeurs de la colonne. Le compteur est stocké uniquement en mémoire, et non pas sur le disque.

InnoDB utilise l'algorithme suivant pour initialiser le compteur auto-incrément pour la table T qui contient la colonne de type AUTO_INCREMENT appelée ai_col : après le démarrage du serveur, lorsqu'un utilisateur fait une insertion dans la table T, InnoDB exécute la commande suivante :

SELECT MAX(ai_col) FROM T FOR UPDATE;

La valeur lue par la commande est incrémenté d'une unité, et assignée à la colonne auto-incrément et au compteur de table. Si la table est vide, la valeur de 1 est assignée. Si le compteur n'est pas initialisé et que l'utilisateur appelle la commande SHOW TABLE STATUS qui affiche les informations de la table T, le compteur est initialisé mais pas incrémenté, et stocké pour être utilisé ultérieurement. Notez que dant cette initialisation, nous posons un verrou exclusif en lecture sur la table, et le verrou durera jusqu'à la fin de la transaction.

InnoDB suit la même procédure pour initialiser un compteur auto-increment avec une table fraîchement créée.

Notez que si l'utilisateur spécifie la valeur NULL ou 0 pour la colonne AUTO_INCREMENT dans la commande INSERT, InnoDB traitera la ligne comme si aucune valeur n'avait été spécifiée, et générera une nouvelle valeur.

Après l'initialisation du compteur d'auto-incrémentation, si un utilisateur insère une ligne qui définit explicitement la valeur de la colonne, et que cette valeur est plus grande que la valeur courante du compteur, le compteur prend alors cette valeur. Si l'utilisateur ne spécifie pas de valeur, InnoDB incrémente le compteur d'une unité, et assigne une nouvelle valeur à cette colonne.

Lorsque vous accédez au compteur d'auto-incrémentation, InnoDB utilise un verrou de table spécial, AUTO-INC, qui reste actif jusqu'à la fin de la commande SQL, et non pas la fin de la transaction. Le verrou spécial a été créé pour éviter les accès concurrents dans la table qui contient la colonne AUTO_INCREMENT. Deux transactions ne peuvent pas avoir le même verrou AUTO-INC simultanément.

Notez que vous pourriez voir des trous dans la séquence de valeur générée par AUTO_INCREMENT si vous annulez des transactions après avoir obtenu une valeur automatiquement.

Le comportement du mécanisme auto-increment n'est pas défini si vous assignez une valeur négative à la colonne, ou si cette dernière dépasse la capacité de la colonne.