Get all inserted IDs when inserting multiple rows using a single query

Question

I've already looked at other answers and I still feel that my question is relevant and deserves a separate entry.

I have a table named settings(which stores user settings) and I have to insert multiple settings for each user. Initially, I had executed a separate insert statement for each setting, but having felt this wasn't a particularly good way to do it, I thought of inserting multiple rows by the same insert statement. My only problem is that I want the auto_incremented IDs of each of the newly inserted rows.

I've read answers that say this isn't possible/scalable etc, but I feel that I have hit upon the solution. I want feedback whether my way is correct or not and hence this question.

What I've done is simple. After inserting the multiple rows, I call last_insert_id() to get the ID of the first row of the simultaneously inserted rows. I already have the count of the number of rows inserted, so I simply create a new array and populate it with IDs starting at last_insert_id() and ending at last_insert_id()+n-1 (where n is the number of rows inserted).

I feel this will work because of the following reasons:

1.) MYSQL documentation states that last_insert_id() is connection dependent and if another client/connection inserts new records, then that won't affect other client's last_insert_id().

2.) I feel that as the insert is being done by a single SQL statement, the whole insertion should be treated as a single transaction. If that is true, then ACID rules should apply and the auto_incremented values should be sequential. I'm not sure about this one.

Those are my reasons why I feel the logic should work. So my question is, will the above logic work for ALL conditions? Can I rely on it to work correctly in all situations? I know it is working for me currently.

Answer 1

If you like to gamble - then do this :)

To be 99% sure you would have to lock the table for writing. You are not sure that the (in future) two transactions  will not be able to intertwine.

To be 100% sure you read these values. (Or analyze the source MySQL) The best solution would be to add the date to tablei edit settings and read the latest. If you do not want to change the structure, you can use triggers http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html.

Good solution will be refresh all your settings or only pairs: key - setting name