Lost Numbers With AUTO_INCREMENT

[AdilHousni]

I am working on a project using mysql. The problem is that we have a table with auto_increment column and I noticed that the system skip some values in the auto increment column.

Example :
: Auto_Increment_Column Column2 Column3
Record 1 : 1 A B
Record 2 : 2 C D
Record 3 : 3 E F
Record 4 : 6 Y U

I don't know why the system skip some values (4 and 5)?
I didn't delete any record from the table.

Thank you for your help.

Adil

 

[sleipnir214]

There are two reasons why an AUTO_INCREMENT column would be missing numbers.

The first, as you have pointed out, is when records are deleted. An AUTO_INCREMENT column does not go back and fill in.

The second is when you explicitly assign a value to an AUTO_INCREMENT column. If there are numbers between the last-generated number and the value you have inserted, the AUTO_INCREMENT column will skip those numbers. The next number it generates will also be one number higher than the one you inserted.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[AdilHousni]

The first and the second reason are true but it's not my case. The database is accessed by about 10 users using java client. In the java client I use SELECT LAST_INSERT_ID to catch the values gived by the autoincrement column. Perhaps the problem comes from the fact that multiples inserts (Launched by java clients) can occur in the same time in the auto_increment column ???

Adil

 

[sleipnir214]

No. By definition, the last-generated auto_increment value is maintained on a per-connection basis. Each of these Java clients will, after inserting a record into a table with an auto_increment column, get back the value that applied to that connection's insertion.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[ericbrunson]

Well, if there were two contending inserts and one failed and rolled back, then that insert's auto_increment would be used up, but not in the database.