Re: autocommit override
Posted by: Mark Matthews
Date: April 01, 2005 10:29AM

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

Options: ReplyQuote


Subject
Written By
Posted
April 01, 2005 09:53AM
Re: autocommit override
April 01, 2005 10:29AM
April 01, 2005 11:31AM
April 01, 2005 11:47AM
April 01, 2005 11:57AM
April 01, 2005 02:20PM


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.