MySQL Insert Race Condition

Question

I have a webapp that currently stores all of a user's searches into a search_log table. I now want to create another table called results_log that stores all the results we supply to the user. The search_log table contains a primary key called id_search and the results log table has the foreign key id_search, and one other field id_result. The id_searched field is an auto_incrementing field in both tables.

In my web app I would do the inserts in this sequential order:

  insert into search_log table
  insert into result_log table

I am worried this may cause a race condition. If user A and user B both finish the webapp and reach this part of the code at about the same time, is it possible that the order would go:

  User A -> Insert into search_log
  User B -> Insert into search_log
  User B -> Insert into result_log
  User A -> Insert into result_log

Since both tables are auto_incrementing on the id_search field, I'm worried User A and User B will have their data swapped. I also thought about querying for the id_search, but it seems like a even worse solution.

My question is: -Is there a way to fix this race condition? -Would one solution be inserting into two tables with one SQL query? Is this possible?

Answer 1

If those tables are related, then you should include the auto increment ID with when inserting. After inserting into search_log, get the last insert ID, no lookup needed. Then include that in the result_log search as another field.

Never rely on auto increment IDs being the same in different tables.