stored procedure with or without transactions ???

[y]

hello

 

please...

I'd like to know what's the difference between these two procedure statements :

 

one with a transaction :

begin 
start transaction; 
insert into emp(name) values(p_name); 
insert into emp_details(id,address,phone) values(last_insert_id(),p_address,p_phone);
commit; 
end

 

and the other is without the transaction :

begin 
insert into emp(name) values(p_name); 
insert into emp_details(id,address,phone) values(last_insert_id(),p_address,p_phone);
end

 

which one is better ???? and why ???

 

plz explain it simply coz i'm a beginner

 

 

[y]

sorry

i posted the topic here by mistake

please move to mysql

[mikosiko]

you eventually could pretty much answer that question yourself just doing some reading, even if you are a beginner.

 

a couple pointers:

http://dev.mysql.com/doc/refman/5.5/en/ansi-diff-transactions.html

 

http://dev.mysql.com/doc/refman/5.0/en/commit.html

[scootstah]

It is possible that the query could be run at the same time. This means that your last_insert_id() might not be for a different query. For example, if you run "insert into emp" it might create an ID of 7. If, before you run the second insert, the first query is run again you would get an ID of 8. So the second query would have an ID of 8 and not 7.

 

Using transactions you can prevent this problem. When you use transactions you sort of create temporary data. The data is only saved permanently once you commit. So the ID would always be accurate using transactions.

[y]

thank you very much

you realy explained it marvellously