MySQL AUTO_INCREMENT does not ROLLBACK

Question

I'm using MySQL's AUTO_INCREMENT field and InnoDB to support transactions. I noticed when I rollback the transaction, the AUTO_INCREMENT field is not rollbacked? I found out that it was designed this way but are there any workarounds to this?

Solution 1

Let me point out something very important:

You should never depend on the numeric features of autogenerated keys.

That is, other than comparing them for equality (=) or unequality (<>), you should not do anything else. No relational operators (<, >), no sorting by indexes, etc. If you need to sort by "date added", have a "date added" column.

Treat them as apples and oranges: Does it make sense to ask if an apple is the same as an orange? Yes. Does it make sense to ask if an apple is larger than an orange? No. (Actually, it does, but you get my point.)

If you stick to this rule, gaps in the continuity of autogenerated indexes will not cause problems.

Solution 2

It can't work that way. Consider:

• program one, you open a transaction and insert into a table FOO which has an autoinc primary key (arbitrarily, we say it gets 557 for its key value).
• Program two starts, it opens a transaction and inserts into table FOO getting 558.
• Program two inserts into table BAR which has a column which is a foreign key to FOO. So now the 558 is located in both FOO and BAR.
• Program two now commits.
• Program three starts and generates a report from table FOO. The 558 record is printed.
• After that, program one rolls back.

How does the database reclaim the 557 value? Does it go into FOO and decrement all the other primary keys greater than 557? How does it fix BAR? How does it erase the 558 printed on the report program three output?

Oracle's sequence numbers are also independent of transactions for the same reason.

If you can solve this problem in constant time, I'm sure you can make a lot of money in the database field.

Now, if you have a requirement that your auto increment field never have gaps (for auditing purposes, say). Then you cannot rollback your transactions. Instead you need to have a status flag on your records. On first insert, the record's status is "Incomplete" then you start the transaction, do your work and update the status to "compete" (or whatever you need). Then when you commit, the record is live. If the transaction rollsback, the incomplete record is still there for auditing. This will cause you many other headaches but is one way to deal with audit trails.

Solution 3

I had a client needed the ID to rollback on a table of invoices, where the order must be consecutive

My solution in MySQL was to remove the AUTO-INCREMENT and pull the latest Id from the table, add one (+1) and then insert it manually.

If the table is named "TableA" and the Auto-increment column is "Id"

INSERT INTO TableA (Id, Col2, Col3, Col4, ...)
VALUES (
(SELECT Id FROM TableA t ORDER BY t.Id DESC LIMIT 1)+1,
Col2_Val, Col3_Val, Col4_Val, ...)

Solution 4

Why do you care if it is rolled back? AUTO_INCREMENT key fields are not supposed to have any meaning so you really shouldn't care what value is used.

If you have information you're trying to preserve, perhaps another non-key column is needed.

Solution 5

I do not know of any way to do that. According to the MySQL Documentation, this is expected behavior and will happen with all innodb_autoinc_lock_mode lock modes. The specific text is:

In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost.” Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table.

View more solutions

Comments

[J.D. Fitz.Gerald]

Just a note: But the auto_increment values will reset to the max+1 of the column after a server reset.

[Nils]

It's not mysql-specific, Postgres behaves the same way. The explanations make sense.

[jmucchiello]

This means two transactions hitting the same table at once can never successfully both commit. That can be a huge bottleneck. And if you have a lot of foreign keys, you could easily end up with deadlocks when they have many to many relationships. My answer for gap handling only hurts a little. :-)

[mr. w]

Can you qualify this a bit? I mean, why add a whole other column, along with all the overhead that that entails (index management, disk use, more IO, etc., etc.), when there's already a perfectly good value to use in the autoinc column? By definition, it offers a unique value that never repeats and always increases as records are added. The only thing it's not is continuous, but as long as you assume gaps in the sequence, I see no problems using it to do things like sorting a set of records by their insert order. In fact, I'd say it's the best way, in terms of performance and clarity.

[Joaquín L. Robles]

This is an advice, not an answer

[vinsa]

May be this would be better (SELECT MAX(id) AS maxval FROM table_name;)+1

[Ouroboros]

This won't be an atomic operation, there would definitely be small window when more than one query gets same maxID.

[Michael Corrigan]

@P.Prasad - Couldn't you use LOCK TABLES to prevent that?

[Ouroboros]

That should be put in the answer.

[Ravinder Payal]

@MichaelCorrigan why we lock a table for this very stupid thing because an application may require many tables requiring continuous auto_increments. Now if we start locking a single instance of application can be used once

[user2505915]

Does the addition of date added happens at the time of commit or at the time of insert. Could it ever happen in case of multiple threads inserting into the table a later timestamp is visible before an earlier timestamp because of the delay in execution times?

[Tebe]

It smells with troubles. Why not creating an additional field next to id like fakeId for handling this? You would manage making it consecutive

[evilReiko]

Being a developer for many years, after reading this answer, I feel like, I've been enlightened!

[Peter Chaula]

@JoaquínL.Robles the advice is so good that I ended up forgetting about the answer

[Bill Karwin]

You could use LOCK TABLES, but that means only one transaction at a time can have access to your table. In many apps, there are dozens or hundreds of concurrent sessions inserting or updating rows in the table. The auto-increment does not hold a lock until you commit your transaction, it only locks briefly, long enough to increment the id. (this is an old answer but it just popped up in my feed today)

[KumZ]

I like your solution, how will it behave with concurrent requests? Especially the case described by @Ouroboros at stackoverflow.com/questions/449346/…, does it workaround it?

[mld.oscar]

@KumZ this might help for your answer stackoverflow.com/a/42683736/4564384

[hackel]

Also note that if you do this, you should call ANALYZE TABLE tbl after, otherwise the old AUTO_INCREMENT value will still appear in information_schema.TABLES until it expires.

[Martin Zvarík]

no clue, why this was thumbed down... this solves the issue

[frangly]

Thanks Martin Zvarík for your comment