MySQL transaction conundrum
- by David Faitelson
I need to perform several inserts in a single atomic transaction. For example:
start transaction;
insert ...
insert ...
commit;
However when MySQL encounters an error it aborts only the particular statement that caused the error. For example, if there is an error in the second insert statement the commit will still take place and the first insert statement will be recorded. Thus, when errors occur a MySQL transaction is not really a transaction. To overcome this problem I have used an error exit handler where I rollback the transaction. Now the transaction is silently aborted but I don't know what was the problem.
So here is the conundrum for you:
How can I both make MySQL abort a transaction when it encounters an error, and pass the error code on to the caller?
