I am using InnoDB and all default values, 4.0.23_Debian-1-log, and am writing a C++ app using the mysql api. I use 1 common function to pass all sql commands to mysql, so I can enable logging and see everything. I have compared this to the mysql log and confirmed the following. In my code, I do:
START TRANSACTION
UPDATE [whatever]
Now, I'm running in a debugger. I abort the program with the debugger, without doing a rollback or a commit. Now I restart the app (creating a new connection) and this time:
START TRANSACTION
[some other stutff]
COMMIT
I would expect that the first query: UPDATE [whatever] would never have been committed because that connection closed without a rollback. However, it is.
In the docs:
http://dev.mysql.com/doc/mysql/en/commit.html there appears to be a lot of confusion about this. The comment by Tod Harter indicates that if the app terminates unexpectedly, like I did, and implicit COMMIT is done. This sounds wrong--it defeats the whole purpose of transactions. A later post by Marc Waeckerlin says that a terminating exception does an implicit ROLLBACK. That sounds correct to me, but it's not what's happening.
Can that doc page be cleaned up, since the comments conflict? And can the correct behavior be clarified? Since I can see that mysql is presently doing an implicit COMMIT, how can I change it to an implicit ROLLBACK on app termination? The idea behind a transaction is that the entire set of sql statements will always be all executed, or not at all, preventing 'half done' transactions. But with the current behavior of committing on app termination, this means if there is a bug in the program and it seg faults, or the client pc crashes, you will end up with a half transaction anyway.