Forums | MacLife

Alphacow wrote:

I'm writing an intranet for a medical company, and the code I'm working on now requires nine separate inserts to different tables in order to execute a single action; register a patient. I'm trying to figure out what transactions do and whether it would be beneficial to wrap all the commands in a "begin... commit". I have two questions:

863) if I have a machine-level problem while a transaction (power outage, etc.) is executing, does it automatically roll back, or do I have to worry about that sort of thing myself?
864) if I have a mysql-level problem within a transaction which would cause mysql to throw a error (syntax error, illegal null values,  etc.), will the transaction automatically roll back, or do I have to do it myself?

Thanks!

If I were you and worried about something going squirely before all inserts were done, I'd create a new temporary table as a staging area (by temporary I mean not for the purpose of long term record storage) and do a single insert. Upon success, then insert into the existing table structure, and upon success, delete the record from the temporary table.

That way if something breaks during the process, your temporary table has all the information needed to do the complete registration of the patient.

You can also insert into multiple tables at the same time, at least with some SQL implementations, but I'm not sure that would be safe - still may end up with some partial data.

Last edited by resedit (2009-04-18 3:01 pm)

Basseq wrote:

My gut feeling is that nothing would be committed until the entire command is finished, so in either case rollback would occur without your intervention. Don't take my word for it though, as I'm a little rusty on the details of MySQL.

Your gut feeling is correct. Although it depends on which of the machines unexpectedly shuts down (if the database server loses power, there's really no telling what would happen, really), and I couldn't tell you what MySQL's default commit points are.

So insert explicit commit points into your code at strategic, logical locations.

.tsooJ

Thanks for the replies. (Sorry for the delay in my response; this is a once-a-week job.) I was hoping to avoid needing to learn how to do "error checking" to make sure the inserts were completed correctly, but I guess that'll be a necessity here.