MySQL duplicate ID

Question

Could it somehow happen that MySQL generates the same autoincrement ID twice?

We have the following situation:

1. a bill with id=100 was created;

2. then it was deleted;

3. then another bill was created and it has the same id = 100;

The structure of the table is:

CREATE TABLE `bill` (
  `id` int(11) NOT NULL auto_increment,
  `user` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `state` int(11) NOT NULL,
  `adv` bit(1) NOT NULL default b'0',
  `weight` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FK2E2407EC768806` (`user`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

ALTER TABLE `bill`
  ADD CONSTRAINT `FK2E2407EC768806` FOREIGN KEY (`user`) REFERENCES `user` (`id`);

Could there be some race condition or does MySQL guarantee unique autoincrement ids?

UPDATE: we cannot reproduce this situation but we logged it.

Answer 1

Auto-increment is handled differently by different storage engines. For example, with MyISAM the next auto-increment value is persisted such that if you restart the MySQL server it will keep that auto-increment value.

However, InnoDB does not persist the next auto-increment value, so if you restart the MySQL server it will calculate the current max value and increment from there.

This is relevant for you, since you are using InnoDB. So if 100 was the maximum id value in your table, then you deleted that row, then restarted the MySQL server, then it would re-use 100 on the next insert.

Here's a simple example to illustrate this point:

mysql> CREATE TABLE `bill` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

mysql> -- start at 99 to force next value to 100
mysql> insert into bill values (99);
Query OK, 1 row affected (0.01 sec)

mysql> -- use auto-increment, should be 100
mysql> insert into bill values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bill;
+-----+
| id  |
+-----+
|  99 |
| 100 |
+-----+
2 rows in set (0.00 sec)

mysql> -- delete max value
mysql> delete from bill where id = 100;
Query OK, 1 row affected (0.00 sec)

mysql> -- use auto-increment, should be 101
mysql> insert into bill values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bill;
+-----+
| id  |
+-----+
|  99 |
| 101 |
+-----+
2 rows in set (0.00 sec)

mysql> -- delete max value
mysql> delete from bill where id = 101;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> /*** RESTART MYSQL ***/
mysql> 
mysql> -- use auto-increment, should be 100
mysql> insert into bill values (null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from bill;
+-----+
| id  |
+-----+
|  99 |
| 100 |
+-----+
2 rows in set (0.00 sec)