I need to find a way to ensure that 2 INSERT statements occur succesfully or none at all (atomic multi-operation transaction).
Specifically - I have a table called "transactions", when a user transfers funds I perform one INSERT for the withdrawl from the source account and one INSERT for the deposit to the target account. Obviously I need to ensure at 100% that either both operations work/are succesfull otherwise I want to ensure neither commit.

For example - what if the application crashes after running the first INSERT statement and never reaches the second - then we would have loss of funds (not permitted).

Is this somehow possible in MySQL?
I need these two operations (INSERT INTO source & INSERT INTO target) to be completly atomic (all or nothing).

Any help would be greatly appreciated...
Thanks,

hmm, have you checked da manual?

I was curious to know if a TRIGGER is considered to be atomic with its associated action...?
Meaning - if I perform an INSERT into one of my tables that has an associated TRIGGER AFTER INSERT ON, what happens if in the split second after the table INSERT something happens (pc dies, loose connection to db, etc...) and the TRIGGER does not get a chance to process? Will the INSERT still commit without the TRIGGER completing?

If so - is there a way to guard against this? I need these two operations (INSERT & TRIGGER) to be completly atomic (all or nothing).

Any help would be greatly appreciated...
Thanks,

MySQL looks at many SQL operations differently depending on the MySQL storage engine currently in use. A given bit of code may (and often does) behave differently when it is executed against a different storage engine.

The default configuration of MySQL (as it is delivered when first installed) uses MyISAM, which does not support the concept of transactions at all, so there is no issue of ATOMIC or otherwise. If you read the MySQL manual COMMIT section, you'll find a single sentence that gives the key tidbit that you need:Follow the link that r937 gave you, and read carefully. The information that you need is there. Read it carefully and interpret the manual very literally... The manual is almost always correct, especially when you think that it must be kidding!

-PatP