Ids from mysql massive insert from simultaneous sources

Question

I've got an application in php & mysql where the users writes and reads from a particular table. One of the write modes is in a batch, doing only one query with the multiple values. The table has an ID which auto-increments. The idea is that for each row in the table that is inserted, a copy is inserted in a separate table, as a history log, including the ID that was generated.

The problem is that multiple users can do this at once, and I need to be sure that the ID loaded is the correct.

Can I be sure that if I do for example:

INSERT INTO table1 VALUES ('','test1'),('','test2')

that the ids generated are sequential?

How can I get the Id's that were just loaded, and be sure that those are the ones that were just loaded?

I've thinked of the LOCK TABLE, but the users shouldn't note this.

Hope I made myself clear...

Answer 1

based on the current implementation of myisam and innodb, yes. however, this is not guaranteed to be so in the future, so i would not rely on it.

Answer 2

Building an application that requires generated IDs to be sequential usually means you're taking a wrong approach - what happens when you have to delete a value some day, are you going to re-sequence the entire table? Much better to just let the values fall as they may, using a primary key to prevent duplication.