Thread: Bug: Transaction still open after error when autocommit=true
Hi,
There is a problem with the JDBC 3 driver for PostgreSQL 9.1 (and maybe others, but the 8.3 drivers don’t show this bug):
If I send a statement with a BEGIN token and something that fails, the database connection stays in “transaction error” mode, even if autocommit is on! This should not be if I read the specs, especially because in autocommit mode doing a rollback() throws an exception!
A small demo for the bug is attached.
Thanks for your great work btw.!
Regards,
Daniel Migowski
Attachment
Daniel Migowski <dmigowski@ikoffice.de> wrote: > A small demo for the bug is attached. The javadocs for Connection: http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html say: | JDBC applications should use the appropritate Connection method | such as setAutoCommit or setTransactionIsolation. Applications | should not invoke SQL commands directly to change the connection's | configuration when there is a JDBC method available. I take that to mean that the right way to do this is to set autoCommit to false and use the connection's commit() method. If you do it that way, is there a problem? -Kevin
Clearly the exception that is thrown when you try to rollback is that autocommit is on. I don't really see this as a bug. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Thu, Dec 22, 2011 at 6:29 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Daniel Migowski <dmigowski@ikoffice.de> wrote: > >> A small demo for the bug is attached. > > The javadocs for Connection: > > http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html > > say: > > | JDBC applications should use the appropritate Connection method > | such as setAutoCommit or setTransactionIsolation. Applications > | should not invoke SQL commands directly to change the connection's > | configuration when there is a JDBC method available. > > I take that to mean that the right way to do this is to set > autoCommit to false and use the connection's commit() method. If > you do it that way, is there a problem? > > -Kevin > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
On 23 December 2011 10:09, Daniel Migowski <dmigowski@ikoffice.de> wrote: > If I send a statement with a BEGIN token Don't do that; the driver is expecting you to manage transaction demarcation by using JDBC's autocommit setting and commit/rollback, not by sending explicit SQL. (Kevin pointed out the relevant JDBC javadoc that covers this in his reply) If you must send a raw BEGIN for some reason, then you're also responsible for transaction cleanup yourself (i.e. don't expect commit() or rollback() to work; you must send appropriate SQL yourself to commit or rollback the transaction) The driver's implementation of autocommit boils down to "if autocommit is off and the connection does not have an active transaction, then send BEGIN before the next query". When autocommit is on, the driver just assumes the server's default behavior - i.e. autocommit happens without any special intervention. If you send an explicit transaction demarcation commands, you'll confuse that. Oliver