Prevent InnoDB auto increment ON DUPLICATE KEY

Question

I am currently having problems with a primary key ID which is set to auto increment. It keeps incrementing ON DUPLICATE KEY.

For Example:

ID | field1     | field2

1  | user       | value

5  | secondUser | value

86 | thirdUser  | value

From the description above, you'll notice that I have 3 inputs in that table but due to auto increment on each update, ID has 86 for the third input.

Is there anyway to avoid this ?

Here's what my mySQL query looks like:

INSERT INTO table ( field1, field2 ) VALUES (:value1, :value2)
            ON DUPLICATE KEY
            UPDATE field1 = :value1, field2 = :value2 

And here's what my table looks like;

CREATE TABLE IF NOT EXISTS `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` varchar(200) NOT NULL,
  `field2` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `field1` (`field1`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Answer 1

This behavior is easily seen below with the default setting for innodb_autoinc_lock_mode = 1 (“consecutive” lock mode). Please also reference the fine manual page entitled AUTO_INCREMENT Handling in InnoDB. Changing this value will lower concurrency and performance with the setting = 0 for “Tranditional” lock mode as it uses a table-level AUTO-INC lock.

That said, the below is with the default setting = 1.

I am about to show you four examples of how easy it is to create gaps.

Example 1:

create table x
(   id int auto_increment primary key,
    someOtherUniqueKey varchar(50) not null,
    touched int not null,
    unique key(someOtherUniqueKey)
);
insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1;
insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1;
insert x(touched,someOtherUniqueKey) values (1,'cat') on duplicate key update touched=touched+1;

select * from x; 
+----+--------------------+---------+
| id | someOtherUniqueKey | touched |
+----+--------------------+---------+
|  1 | dog                |       2 |
|  3 | cat                |       1 |
+----+--------------------+---------+

The Gap (id=2 is skipped) is due to one of a handful of operations and quirks and nervous twitches of the INNODB engine. In its default high performance mode of concurrency, it performs range gap allocations for various queries sent to it. One had better have good reasons to change this setting, because doing so impacts performance. The sorts of things later versions of MySQL delivers to you, and you turn off due to Hyper Focusing on gaps in printout sheets (and bosses that say "Why do we have gaps").

In the case of an Insert on Duplicate Key Update (IODKU), it is assuming 1 new row and allocates a slot for it. Remember, concurrency, and your peers doing the same operations, perhaps hundreds concurrently. When the IODKU turns into an Update, well, there goes the use of that abandoned and never inserted row with id=2 for your connection and anyone else.

Example 2:

The same happens during Insert ... Select From as seen in This Answer of mine. In it I purposely use MyISAM due to reporting on counts, min, max, otherwise the range gap quirk would allocate and not fill all. And the numbers would look weird as that answer dealt with actual numbers. So the older engine (MyISAM) worked fine for tight non-gaps. Note that in that answer I was trying to do something fast and safe and that table could be converted to INNODB with ALTER TABLE after the fact. Had I done that example in INNODB to begin with, there would have been plenty of gaps (in the default mode). The reason the Insert ... Select From would have creates gaps in that Answer had I used INNODB was due to the uncertainty of the count, the mechanism that the engine chooses for safe (uncertain) range allocations. The INNODB engine knows the operation naturally, knows in has to create a safe pool of AUTO_INCREMENT id's, has concurrency (other users to think about), and gaps flourish. It's a fact. Try example 2 with the INNODB engine and see what you come up with for min, max, and count. Max won't equal count.

Examples 3 and 4:

There are various situations that cause INNODB Gaps documented on the Percona website as they stumble into more and document them. For instance, it occurs during failed inserts due to Foreign Key constraints seen in this 1452 Error image. Or a Primary Key error in this 1062 Error image.

Remember that the INNODB Gaps are there as a side-effect of system performance and a safe engine. Is that something one really wants to turn-off (Performance, Higher user statisfaction, higher concurrency, lack of table locks), for the sake of tighter id ranges? Ranges that have holes on deletes anyway. I would suggest not for my implementations, and the default with Performance is just fine.

Answer 2

You could set the innodb_autoinc_lock_mode config option to "0" for "traditional" auto-increment lock mode, which guarantees that all INSERT statements will assign consecutive values for AUTO_INCREMENT columns.

That said, you shouldn't depend on the auto-increment IDs being consecutive in your application. Their purpose is to provide unique identifiers.

Answer 3

I am currently having problems with a primary key ID which is set to auto increment. It keeps incrementing ON DUPLICATE KEY

One of us must be misunderstanding the problem, or you're misrepresenting it. ON DUPLICATE KEY UPDATE never creates a new row, so it cannot be incrementing. From the docs:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

Now it's probably the case that auto-increment occurs when you insert and no duplicate key is found. If I assume that this is what's happening, my question would be: why is that a problem?

If you absolutely want to control the value of your primary key, change your table structure to remove the auto-increment flag, but keep it a required, non-null field. It will force you to provide the keys yourself, but I would bet that this will become a bigger headache for you.

I really am curious though: why do you need to plug all the holes in the ID values?