This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
I need to get an ID of a new record in a mysql table to be used to insert another record into another table. I am using last_insert_id() to get it. Since tomcat is multi client/ threaded it feels as if it is not safe even though mysql states
"For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client."
I think that tomcat is the client in this case so this is not safe as many clients connect to tomcat. Does anyone have any suggestions on how to accomplish this in a safe way if in fact this way is not safe as i suspect.
Tomcat maintains a connection pool (or rather, you can configure Tomcat to do so). If you ask for a connection from the pool then that connection is yours until you release it. Thus you can use LAST_INSERT_ID() to obtain the id. Example work flow:
insert first data record
insert second data record containing id from first record