I am planning on using auto-incremented user IDs on my website. As user data will be split across several tables, I was wondering, how reliable are auto-incremented values in transactions, i.e. when inserting some initial values into tables in a transaction upon registration, is it safe to just let the auto-incrementor set the IDs in all the tables or should it just insert into one table, get the inserted ID in a separate query and use it in the subsequent insertions, resulting in higher database load just for user creation?
The auto increment field gets reserved everytime you are attempting to insert something. The insert more or less proceeds in 2 steps:
1: reserve the next available auto-increment key
2: perform the insert with this reserved key
Now, if the transaction rolls back, the only thing that can never be rolled back is the auto-increment reservation, resulting in gaps in the auto-increment column. Because of this, if you are trying to predict 100% what the auto increment will be, it is impossible to do so. There is no other issue with auto_increment that i know of, and in almost all cases, it is more reliable to rely on mysql's features than to try to do something manually.