Key Conflicts On INSERT Still Increment AUTO_INCREMENT Value In MySQL
When it comes to database schema design, picking the right indexes is a critical part of how you architect your ColdFusion applications. Not only do indexes lead to greatly improved performance, they can also be used to enforce data integrity and drive idempotent workflows. Earlier this year, I looked at some of the techniques that MySQL provides for gracefully reacting to key-conflicts; but, one thing that I completely missed in that exploration was the fact that key-conflict errors still increment the table's underlying AUTO_INCREMENT value.
To see this in action, let's create a simple table with a UNIQUE KEY constraint:
CREATE TABLE `token` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`value` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `IX_byToken` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Here, our token table has a single column, value, which must be unique across all rows. To see how a key-conflict in MySQL interacts with the AUTO_INCREMENT value, let's try to insert the same token value several times in a row, followed by a new value. We can then compare the resultant id of the two inserted rows.
ASIDE: In Lucee CFML, the
createUniqueId()function returns a small value that is unique to the current request.
Note that our INSERT query below is using INSERT INGORE INTO. This means that when we try to insert the same value multiple times, MySQL will simply ignore the insert rather than throwing a key-conflict error.
<cfscript>
// Let's clear the tokens table and reset the AUTO_INCREMENT value.
truncateTokens()
// Create our first token - we know this will succeed since we just cleared the table.
value = createUniqueId();
id1 = createTokenOrIgnore( value );
dump( id1 );
// These will all be no-ops, since we're trying to insert the same token over and
// over. As such, ZERO will be returned.
dump( createTokenOrIgnore( value ) );
dump( createTokenOrIgnore( value ) );
dump( createTokenOrIgnore( value ) );
dump( createTokenOrIgnore( value ) );
// Now, let's try to insert a new token, which will result in a new AUTO_INCREMENT ID.
id2 = createTokenOrIgnore( value & "new" );
dump( id2 );
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
/**
* I insert the given token value and return the associated ID. If the token is already
* in the table, ZERO is returned.
*/
public numeric function createTokenOrIgnore( required string value ) {
```
<cfquery name="local.results" result="local.metaResults">
INSERT IGNORE INTO
token
SET
value = <cfqueryparam value="#value#" sqltype="varchar" />
;
</cfquery>
```
return( val( metaResults?.generatedKey ) );
}
/**
* I truncate the tokens table, resetting the AUTO_INCREMENT value.
*/
public void function truncateTokens() {
```
<cfquery name="local.results" result="local.metaResults">
TRUNCATE TABLE
token
;
</cfquery>
```
}
</cfscript>
Now, when we run this ColdFusion code, we get the following output:
As you can see, even when our INSERT INGORE INTO SQL statement resulted in no new row being inserted, the underlying AUTO_INCREMENT value on the InnoDB table was still increased. This is why we are seeing a gap between the two primary-keys despite the fact that our ColdFusion demo only inserted two rows.
This MySQL behavior doesn't bother me; but, it's good to know that it works this way so that I can better understand the data that I see showing up in the table. I assume that MySQL is using this approach for performance reasons (to increase concurrent operations while still enforcing a predictable state).
I should finally note that while I am demonstrating this using INSERT IGNORE INTO, the same behavior appears to hold true for any key conflict. So, for example, if I were to also have an ON DUPLICATE KEY UPDATE statement, the key-conflict logic would also increment the AUTO_INCREMENT value.
Want to use code from this post? Check out the license.
Reader Comments
Apparently this
AUTO_INCREMENTbehavior is actually a setting on the InnoDB database. In the "olden days", all auto-incrementing used to use a full-table lock, which is inherently not great for performance since only oneINSERTcan run at a time on the table. However, it looks like MySQL changed this to reduce the degree of locking; but, as a result, gave up a little of the cleanliness of the incrementing value.Check out 14.6.1.6 AUTO_INCREMENT Handling in InnoDB:
I have to remind myself that the having a Key-conflict is the edge-case, not the normal case. As such, even if I am losing some key-space, it's not going to happen very often; and, is only there to help ensure the integrity of the overall data structure.
Special thanks to Matej Dunik for point the above out to me 💪
Minor correction,
createUniqueId()isn't per request, it's unique since the Lucee instance (context?) was started@Zac,
Ahh, thank you! If had taken a moment to stop and think about that, it would have been obvious since I can refresh a page (that has
createUniqueId()) and see the value increments on each request. It looks like a base-32 counter or something. Great catch!Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →