[DB-SIG] MySQL Multi-Table Insert Strategy

Kolbe Kegel kolbe at kolbekegel.com
Wed Jul 6 18:26:46 CEST 2005


Hi Don,

> I'm new on the list, and just had a general question about the best
> approach for setting up a multi-table insert.  I know that MySQL
> doesn't support this outright, but would most people recommend using a
> transaction?

This question is essentially neutral to the language-specific API  
that you're using. What you're describing is *exactly* when you  
should use a transaction. Bear in mind that all tables involved in  
the transaction will need to use the InnoDB storage engine if you  
want the transaction to make any sense (i.e. work as you hope).

> In my DB app, I have a table, person, that uses foreign keys from
> other tables (3-4).  When I add someone to the person table, I want
> the data for their address to be inserted into the address table as
> well.  Other data needs to be inserted into additional tables.

BEGIN
INSERT INTO people (...) VALUES (...)
id = last_insert_id()
INSERT INTO address (person_id, ...) VALUES (id, ...)
INSERT INTO other (person_id, ...) VALUES (id, ...)
COMMIT

Check the return values of each of your statements and if anything is  
weird, ROLLBACK the transaction. Likewise, if the COMMIT fails or if  
you have to rollback, you should design your application so that it  
will retry the transaction in case the transaction could not be  
committed. This can happen because of deadlocks or other reasons, and  
a transaction-aware application should be able to deal with that.


Regards,

Kolbe


More information about the DB-SIG mailing list