I have about 50k entries that I am inserting in MySQL via PHP. Table is simple with ID (autoincrementation), and few VARCHAR fields...
Since this system allows multiple users to login at same time and do the same operation of inserting data, so let's say USER1 starts the "insert" and in very same millisecond USER2 starts inserting data in same table - I am curious if MySQL will wait for USER1 to finish process and than process USER2 entries or it will do insert simultaneously ?
So following that logic USER1 insert ID's will be from 1 to 50k and USER2 from 50k-100k, or at the other hand will it shuffle them?
...and if it will do "shuffling" is there a way to prevent this ?
P.S. I know - I can add additional field with user_id so I can distinguish which user did actual "entry", but in this case I would really like to reserve space from 1-50k to be for USER1 and 50k-100k to be for USER2....
To reserve the table and thereby the auto increment ids for yourself you should LOCK the table before beginning your inserts. Otherwise "shuffling" of ids is very likely.
Note though that nothing, even locking, guarantees the continuity of autoincrement ids. I understand that it would be "a nice touch" to have a block of inserts with continuous ids, but that's not what they are for and there are no guarantees in the system to make it so. So don't rely on it or expect it in any way.