Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. :)

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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:

START TRANSACTION;
INSERT #1;
INSERT #2;
COMMIT;

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.

share|improve this answer
    
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? –  Steve Green 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? –  Steve Green 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. –  Steve Green 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
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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