Connector/J incorrectly models autocommit state

The latest version od the JDBC driver incorrectly models the autocommit
state instead of reading the value from MySQL via @@session.autocommit.

The former works as long as you use the connection setAutoCommit(0)
state only via Connection.setAutoCommit() function. If the setting is
changed via a Statement, then the modeled value is incorrect.


--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=gcdmj-java@m.gmane.org

Re: Connector/J incorrectly models autocommit state

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Eric Raymond wrote:

> The latest version od the JDBC driver incorrectly models the autocommit
> state instead of reading the value from MySQL via @@session.autocommit.
>
> The former works as long as you use the connection setAutoCommit(0)
> state only via Connection.setAutoCommit() function. If the setting is
> changed via a Statement, then the modeled value is incorrect.
>
>

The JDBC spec states that you should change autocommit via
Connection.setAutocommit() (Section 9.3). Many other APIs, besides JDBC
depend on this behavior for transaction control. Is there a reason
you're not using the connection methods to change it?

I've actually seen other JDBC drivers for other databases that will
throw a SQLException if you try and change the autoCommit value outside
of the 'normal' means.

-Mark
- --
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+uE/UtvXNTca6JD8RAjrwAKDJCCR7MBpX1Ju4rHbt0LQnJXH0PwCg mP70
l3fw6fx/UIs/e2R3D7JpMdg=
=CHPN
-----END PGP SIGNATURE-----


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Connector/J incorrectly models autocommit state

Just something we noticed as we were thinking of ways to simulate a form
of nested (BEGIN/COMMIT/ROLLBACK) transactions via the connection
object. We were looking for a way to detect that you were inside an
explicit (not auto commit) transaction (but autocommit==0 is not a proxy
for this).

It does seem safer/easier to grab the status from mysql rather than
modeling it. Might be a performance hit depending on how often it is
used (then again a cached copy should only need an update after certain
api fn's) It's not a bad idea to prevent people from setting the
setting outside the blessed method if that's the case (and this further
limit the places you need to update the cached value).

By the way, given that there is no Connection.startTransaction(), I
assume we're OK issuing that via a statement?

Mark Matthews wrote:

>The JDBC spec states that you should change autocommit via
>Connection.setAutocommit() (Section 9.3). Many other APIs, besides JDBC
>depend on this behavior for transaction control. Is there a reason
>you're not using the connection methods to change it?
>
>I've actually seen other JDBC drivers for other databases that will
>throw a SQLException if you try and change the autoCommit value outside
>of the 'normal' means.
>
> -Mark
>- --
>
>
>


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Connector/J incorrectly models autocommit state

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Eric Raymond wrote:

> Just something we noticed as we were thinking of ways to simulate a form
> of nested (BEGIN/COMMIT/ROLLBACK) transactions via the connection
> object. We were looking for a way to detect that you were inside an
> explicit (not auto commit) transaction (but autocommit==0 is not a proxy
> for this).

I'm not clear on how you can nest transactions when the underlying
database doesn't support nested transactions.

> It does seem safer/easier to grab the status from mysql rather than
> modeling it. Might be a performance hit depending on how often it is
> used (then again a cached copy should only need an update after certain
> api fn's) It's not a bad idea to prevent people from setting the
> setting outside the blessed method if that's the case (and this further
> limit the places you need to update the cached value).

It actually would be a pretty expensive. It's also is deadlock-prone
(i.e. when you're in the middle of executing a query, and attempt to
issue a getAutoCommit()), and won't work when you're using streaming
result sets, because you won't be able to get the query to detect
autocommit status executed while reading the results.

If I were to support it at all, it would be via an option that is set
via driver properties, and it would not be the default. You're the only
one that has asked for it in 4 years, so there's not a real high demand
for it. :)

> By the way, given that there is no Connection.startTransaction(), I
> assume we're OK issuing that via a statement?

The JDBC model to implicitly start a transaction is to
setAutoCommit(false), or if autoCommit == false, to call rollback(). I
don't know of any JDBC drivers that recognize other ways of doing it.

-Mark

- --
For technical support contracts, visit https://order.mysql.com/?ref=mmma

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE+uG0mtvXNTca6JD8RAhs6AJ4ogwPd2ATI04Q1uREx6DriCuKzYwCf aS0d
ekn1e4xDHAd2R13w9rt7gyM=
=cZjN
-----END PGP SIGNATURE-----


--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=gcdmj-java@m.gmane.org