Skip to main content
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Dana Lawson
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: Dana Lawson

Key Conflicts On INSERT Still Increment AUTO_INCREMENT Value In MySQL

By on
Tags: ,

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:

Auto-increment values show increases even on no-op (key conflict) statements in MySQL.

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

15,261 Comments

Apparently this AUTO_INCREMENT behavior 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 one INSERT can 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:

innodb_autoinc_lock_mode = 1 ("consecutive" lock mode)

.... "Simple inserts" (for which the number of rows to be inserted is known in advance) avoid table-level AUTO-INC locks by obtaining the required number of auto-increment values under the control of a mutex (a light-weight lock) that is only held for the duration of the allocation process, not until the statement completes. No table-level AUTO-INC lock is used unless an AUTO-INC lock is held by another transaction. If another transaction holds an AUTO-INC lock, a "simple insert" waits for the AUTO-INC lock, as if it were a "bulk insert".

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.

55 Comments

Minor correction, createUniqueId() isn't per request, it's unique since the Lucee instance (context?) was started

15,261 Comments

@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!

Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.