I'm not sure if I'm overcomplicating this or whether I can't see the wood for the trees.

I have two tables, both have auto incrementing ID's.

Table 1:
ID - auto
field - int
other field - varchar

Table 2:
ID - auto
Table1Id - FK
field - varchar

the first table stores an article and the 2nd stores the location of the images related to it.

For my form that uploads the data all information to be stored is taken. I need to run two inserts simultaneously (which I realise is daft) using the same ID.

My plan(flawed) is to insert into table 1, get the id and then insert into table 2.

one of the problems if is a user accesses the site in the gap between the two inserts and sees a mess.

is there an easier way, I think I've made a mistake or I'm missing something obvious as it shouldn't be this difficult. :)


This is (in part) where transactions come in. Assuming your transaction isolation level is "read committed", "repeatable read" or "serializable" (MySQL defaults to "Repeatable read" and anyone setting it to lower than "read committed" is committing crimes against the laws of the universe), other transaction are blind (to some extent) to changes made within your transaction.

So you should:


The other transactions will not be able to read your first INSERTed row (though they can find trace of its existence via locks -- probably not interesting to you). They will only be able to read both your changes or none of them.

This, again, depends on the assumption that these transactions don't come from a connection with "Read committed" isolation level -- which, unfortunately -- is something anyone is able to change for their own connection.

  • I thought a transaction wouldn't be suitable because I need the ID from one insert to use in the other and the ID won't have been generated until the commit? – null Sep 17 '13 at 17:54
  • In theory I'd need to start trans, insert into table a, get ID from table a, insert into table b, commit? – null Sep 17 '13 at 17:55
  • @SteveGreen - yes, the flow you describe is correct, and will work just fine within the scope of a transaction. – Shlomi Noach Sep 17 '13 at 17:58
  • ah, then I thank you and thank you again. I thought that the ID would not be available until the commit had taken place. – null Sep 17 '13 at 17:59
  • np. best, as always, is to give it a try and see... – Shlomi Noach Sep 17 '13 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.