MySQL Forums
Forum List  »  NDB clusters

found reason for failure and solution
Posted by: Patrick Chun
Date: June 09, 2005 03:25PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
found reason for failure and solution
1884
June 09, 2005 03:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.