在插入时获取一个表的auto_increment值的最佳方法

Question

in my database there are three tables,they are

posts{ post_id , title , description ,etc ,etc}
tags{ tag_id , tag_name , tag_type }
tags_for_post{ tag_ref_id , post_ref_id}

so as you can see once i insert a row in posts table then i will need that row's post_id value to update my tags_for_posts table. that is, which tags are tagged in which post are stored in tags_for_posts table and to do it i will need the corresponding posts post_id value.

so my question is how will i get the auto incremented value(i.e. post_id) at the time of insertion??? but there is a problem

it will run on a server so multiple number of clients will access it at a same time.so it may happen that two clients accessing the posts table at the same time. so how can i assure that the last_insert_id() will give the corresponding posts post_id.

so i want do it in three steps like:

1. enter few column's data in posts table and get the value of auto_incremented post_id value of this row
2. now use that row's post_id value for corresponding tag insertion in tag_for_post table.
3. again come to posts table complete rest calculations and updates.

as i said earlier the main problem which i encountered is how can i assure that i will always get the corresponding row's post_id value even if multiple clients accessing it at same time. i dont think mysql_insert_id or LAST_INSERT_ID() will work for this purpose because multiple clients can try to insert at same time.so how can i assure that each time i will get the id of corresponding row

now before marking it as duplicate thread, i want say i have searched a lot in google and stackoverflow and most of them advising to use mysql_insert_id or LAST_INSERT_ID() and as i said i am not happy with this because i think it wont give the desired result for multiple access at same time.

i have found two solutions which is likely to solve my problem using procedure and using transaction:stackoverflow

in stackoverflow solution they are saying to write the code as ::

begin transaction
insert into your table (half empty values);
$id = get last autoincrement id
do calculations
update set data = full data where id = $id
commit transaction

now my questions are ::

1. will the above code solve my problem properly??
2. will i need to write additional code for implementing the above code??
3. if the problem cant be solved using above code then how can i solve??
4. does the above code ensures that it will always return the corresponding post_id even in case of multiple insertion at same time.
5. for example, assume that one client has issued an inseret command and before that client issues $id = get last autoincrement id another client has already issued insertcommand.now will the first client get the id of the row which he inserted using the above code??

Answer 1

The last insert ID is unique to each client connected, it points to the last ID inserted by that client only, and not other IDs inserted by other clients.

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

In other words you do not need to perform any kind of locking for this if you have multithreading in mind.