found reason for failure and solution
After many hours of our team doing investigation, we found that the reason why some of the tables are changed was because of our new middleware's persistent database connection. It wasn't that the START TRANSACTION / COMMIT function was broken or misconfigured. It was because no explicit ROLLBACK was called. The situation was this:
get connection to db
START TRANSACTION;
doing INSERTs to various tables
doing UPDATEs to various tables
COMMIT;
...
error handling code here
...
finally, error or no error, close connection to db
On our old platform, the middleware (Tomcat 3) db connection seems to allow for automatic rollback in case of an error (by physically disconnecting to the db, thus the db does an implicit, automatic rollback).
But in our new platform with a upgraded middleware of Tomcat 5, the db connection pooling most probably, for performance, does not really disconnect from MySQL inspite of our explicit closing of connection in our code as above. And thus MySQL has no way of knowing it should do an implicit, automatic rollback.
(It should be noted that this behaviour has nothing to do with whether it is another db or MySQL. It has everything to do with "persistent" db connection of the new middleware.)
Because of no rollback caused by persistent db connection pooling, our application will continue to see a version of the db where data are modified. It (and the db) still thinks of itself inside a transaction that has not been committed or rollbacked yet!
Of course, a transaction that has not been committed will not be seen by others. Thus, other applicaton hooked up via another db connection will not see such new data.
To fix this, we had modified the code in our application to:
get connection to db
START TRANSACTION;
doing INSERTs to various tables
doing UPDATEs to various tables
COMMIT;
...
error handling code here
...
finally, if transaction failed
ROLLBACK;
error or no error, close connection to db
That is, we explicitly issue a rollback. This seemed to have eliminated the problem.
Patrick
Greenwood Canada