db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel John Debrunner (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2886) Close autocommit(false) connection causes exception with code 25000 when no transaction is pending
Date Sat, 30 Jun 2007 20:53:04 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2886?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12509328
] 

Daniel John Debrunner commented on DERBY-2886:
----------------------------------------------

The SQL standard defines which statements start transactions or in their terms "transaction-initiating
SQL-statements", see section 4.33.4 of SQL foundation. This includes queries.

Modifications are only part of it, the other aspect is locks. Querys (SELECT statements) obtain
locks and with some isolation levels those locks are held until the end of transaction, thus
a commit or rollback is required to inform the database server that the locks are no longer
required.



> Close autocommit(false) connection causes exception with code 25000 when no transaction
is pending
> --------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-2886
>                 URL: https://issues.apache.org/jira/browse/DERBY-2886
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.2.1.6, 10.2.2.0
>         Environment: Java 5 and Java 6
> Embedded Driver
> Win XP
>            Reporter: Bill Robertson
>
> I have a simple program to demonstrate this.  The interesting part creates a new connection,
sets autocommit(false), executes a query, and the closes the connection.  This generates an
exception with result code 25000 and a message "Invalid transaction state." every time.  According
to the docs that I could find, that means that you're trying to close a connection with a
pending transaction.  However, there is no pending transaction because no inserts/updates/deletes/creates/drops
etc. have been issued on the connection.
> So far, I have found a work around.  If I set autocommit(true) before closing, I can
close the connection without error.  I'm not sure what the possible side effects of this are
though.
> I'm not sure if I will be able to attach the test program, so here it is.  Also included
is sample output of it running under derby 10.2.1.6 and 10.2.2.0 in Java5 and Java 6.
> ----- TestBoom.java ----------------------------------------------------
> import java.sql.*;
> import org.apache.derby.jdbc.EmbeddedDriver;
> public class TestBoom {
>     public static void main(String[] args) throws Exception {
>         
> 	// register driver
> 	EmbeddedDriver registerMe = new EmbeddedDriver();
> 	// create a datbase with a table that has one row
>         Connection conn = DriverManager.getConnection("jdbc:derby:foo;create=true");
>         Statement s=conn.createStatement();
>         s.executeUpdate("create table foo(foo int)");
>         s.close();
>         s = conn.createStatement();
>         s.executeUpdate("insert into foo values (1)");
>         s.close();
>         conn.close();
> 	// create a connection, disable autocommit, issue a query and
> 	// close the connection
>         conn = DriverManager.getConnection("jdbc:derby:foo");
>         conn.setAutoCommit(false);
>         PreparedStatement ps = conn.prepareStatement("select foo from foo where foo =
1");
>         ResultSet rs = ps.executeQuery();
>         if(rs.next()) {
>             System.out.println(rs.getInt(1));
>         }
>         rs.close();
>         ps.close();
>         // conn.setAutoCommit(true); // uncomment this line and the close will not throw
>         conn.close();  //boom
>     }
> }
> -------------------------------------------------------------------------------
> ----- Output (derby 10.2.1.6 & 10.2.2.0, Java 5 & Java 6) ----
> ***** Derby 10.2.1.6, Java 6 *****
> set path=c:\jdk1.6.0_01\bin;%path%
> set classpath=.;db-derby-10.2.1.6-bin\lib\derby.jar
> javac TestBoom.java
> java TestBoom
> 1
> Exception in thread "main" java.sql.SQLException: Invalid transaction state.
>         at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedConnection.close(Unknown Source)
>         at TestBoom.main(TestBoom.java:30)
> rd /q/s foo
> ***** Derby 10.2.2.0, Java 6 *****
> set classpath=.;db-derby-10.2.2.0-bin\lib\derby.jar
> javac TestBoom.java
> java TestBoom
> 1
> Exception in thread "main" java.sql.SQLException: Invalid transaction state.
>         at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedConnection.close(Unknown Source)
>         at TestBoom.main(TestBoom.java:30)
> Caused by: java.sql.SQLException: Invalid transaction state.
>         at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
>         ... 7 more
> ***** Derby 10.2.1.6, Java 5 *****
> set path=c:\jdk1.5.0_12\bin;%path%
> set classpath=.;db-derby-10.2.1.6-bin\lib\derby.jar
> javac TestBoom.java
> rd /q/s foo
> java TestBoom
> 1
> Exception in thread "main" java.sql.SQLException: Invalid transaction state.
>         at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedConnection.close(Unknown Source)
>         at TestBoom.main(TestBoom.java:30)
> ***** Derby 10.2.2.0, Java 5 *****
> set classpath=.;db-derby-10.2.2.0-bin\lib\derby.jar
> rd /q/s foo
> javac TestBoom.java
> java TestBoom
> 1
> Exception in thread "main" java.sql.SQLException: Invalid transaction state.
>         at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedConnection.close(Unknown Source)
>         at TestBoom.main(TestBoom.java:30)
> -------------------------------------------------------------------------------

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message