CODEWITHSUNDEEP
mysqlsql
Edwardo
Edwardo

Reputation: 643

About mysql auto increment behavior when inserting

I will be working with mysql for a project. I have some tables with primary key as an integer that auto increments (call it id).

When I insert something that cannot be inserted (for example, a unique field ip, and you insert the same ip) it does not let you insert (which is ok!) but then, id is incremented... Which is not correct because I tried to insert, but I cant.

Is this a normal behavior?

how I can assure that the id will be only incremented when "okay" values are inserted?

Thank you

Upvotes: 2

Views: 1378

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562881

Yes, this is normal behavior. Since MySQL 5.1, InnoDB releases the auto-inc lock for a table as soon as possible to allow concurrent threads to do their own inserts without waiting for your thread. This means the auto-inc lock is released before the INSERT is known to succeed. So if anything goes wrong with the INSERT, it can't "return" the auto-incremented value, because another thread may have already allocated the N+1st auto-inc value, and InnoDB doesn't track anything except the most recent auto-inc value.

This does result in some cases where a lot of auto-inc values are "lost" if you have many failed inserts. For example, I helped one site that ran all the way up to the maximum signed INT value (231-1) because they were skipping 1000-1500 values for every row that was successfully inserted. All because of a secondary UNIQUE constraint on their username column.

You can make InnoDB behave like it did in MySQL 5.0, to let auto-increment values return their incrementation on a failed INSERT by setting innodb_auto_inc_lock_mode=0 in the my.cnf config file. Read http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html for more details.

Be aware, though, that this limits concurrency, because a thread inserting will hold the auto-inc for a longer time. Other threads have to wait for the currently inserting thread to completely succeed before they can acquire the auto-inc lock. If you have a high rate of concurrent inserts against the same table, this can slow down your app.

Also, it's normal for there to be gaps in the primary key sequence, because some transactions are rolled back anyway, or else rows get deleted. Never assume your primary key values are going to be perfectly consecutive. They are intended to be unique values, not row numbers.

Upvotes: 4

Related Questions