Keep sequential primary key in error insert

My create table command in mysql is

CREATE TABLE `map` (
  `id`  int(4) AUTO_INCREMENT NOT NULL,
  `city`    varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE UNIQUE INDEX `map_city_idx`
  ON `map`
  (`city`);

Intial value, like:

id(1),city('Banda Aceh')
id(2),city('Medan')

The next insert is city('Medan'), so it's error because city column is unique. The next insert is city('Bengkulu'), and the final table result is

id(1), city('Banda Aceh')
id(2), city('Medan')
id(4), city('Bengkulu')

It's not id(3) but id(4) instead. So how could I keep sequential primary key eventhough there was/were insert error before?

id(1), city('Banda Aceh')
id(2), city('Medan')
id(3), 开发者_C百科city('Bengkulu')

That looks like InnoDB behaviour. InnoDB rolls back the transaction, but autoincrement key can not be restored because it might be used by someone else. This doesn't happen in MyISAM tables, but with InnoDB you have the power to prevent it using transactions. I'll write this in php, but you can repeat it in any language you want:

mysql_query('START TRANSACTION');
//query if it exists and lock the record
$r = mysql_query('SELECT id FROM map WHERE city="Medan" FOR UPDATE'); 
if(mysql_num_rows($r)>0){
  //already exists
  // there are better ways to do this, buy you got the idea.
  list($id) = mysql_fetch_row($id);
  mysql_query('ROLLBACK'); // release the lock
}else{
  // does not exists - insert it
  mysql_query('INSERT INTO map(city) VALUES("Medan")');
  $id =  mysql_insert_id();
  mysql_query('COMMIT'); //commit and release the lock
}
//... here $id will be id of 'Medan' city regardless if it is inserted now
//  or it is duplicate and there will be no autoincrement key holes