John McGowan wrote:
> I am using connector J in my application as a way
> of connecting my application server / middleware
> to mysql. (nothing suprising here) however I've
> recently exposed a bug with the middleware that
> probably wasn't even noticed before by anybody
> because it's transaction related.
>
> Basically, the middleware is setting autocommit to
> false, and that is causing problems for me because
> my application isn't seeing the latest and
> greatest data in the database even though it
> should. I received a special (unsupported) build
> of the JDBC library for the middleware that didn't
> set autocommit to false, and everything worked as
> expected... .Well almost everything...
John,
Would you mind telling us what application server? I can tell you that everyone on the JDBC-4.0 experts' group, which happens to include appserver vendors such as IBM, Oracle and BEA all agree that messing with autocommit and transaction control outside of what the JDBC API gives you (basically setAutoCommit(), commit() and rollback()) is a definite no-no (which means that issuing 'begin' is forbidden).
I _really_ don't think you want to mess with faking out the autocommit values since the driver needs them internally for doing some housekeeping, and for other reasons you'll be opening up a can of worms.
Not 'seeing' the latest and greatest data has very little to do with autocommit, and has a lot to do with isolation levels, and depending on the isolation level whether or not the connection has called commit().
If your business requirements don't require REPEATABLE-READ semantics (which is what you're getting by default), then maybe having either the appserver or MySQL set the transaction isolation to READ-COMMITTED is what you _actually_ need?
You can tell the MySQL server to always use READ-COMMITTED by default by putting the following line in your server configuration file:
transaction-isolation=READ-COMMITTED
Regards,
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html