-2

I have a table in which data is inserted and ID number work as auto_increment and primary key, the problem I am facing is that when there is wrong transaction or error in insertion of table row, the ID number jumps the sequence.

I just want it should not jump the sequence and stick to sequence.

I have gone through some other question asked on same issue and I know that innodb use auto increment feature at the beginning of transaction but I want somehow this should be at the end

Dharman
  • 30,962
  • 25
  • 85
  • 135
user3421366
  • 7
  • 2
  • 4
    Why is that a problem? If you delete records there will be gaps too. It is a indicator of doing something wrong when you feel messing with the auto-increment value. It is just a unique value for the record – juergen d Jan 02 '16 at 10:14
  • 2
    Gaps aren't an issue. There is no problem. Those numbers don't have to be sequental, only unique. Please google a bit about the reasoning, it will save you a lot of time if you give up now on having sequential numbers. – N.B. Jan 02 '16 at 10:21
  • Check here http://www.timrosenblatt.com/blog/2008/03/21/insert-where-not-exists/ or http://stackoverflow.com/questions/5924762/prevent-autoincrement-on-mysql-duplicate-insert – Yogus Jan 02 '16 at 10:59

1 Answers1

0

The documentation is clear on that point.

  • “Lost” auto-increment values and sequence gaps

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.

What you want to do is not possible with InnoDB.

Richard St-Cyr
  • 970
  • 1
  • 8
  • 14