On 03.08.2012 14:50, Rick Hillegas wrote:
On 8/2/12 3:29 PM, Kristian Waagan wrote:
Hello,
Has anyone considered the case of using try-with-resources (Java SE
7) with Derby connections with auto-commit off?
I haven't studied this in detail, but from what I can see the above
configuration will cause Connection.close to throw an exception if a
transaction is active. Based on what the Java API docs say, I think
Derby is behaving in a way that's allowed [1]. Nonetheless, this may
not be what users expect.
I suppose one could work around this issue with an extra try-catch
block, where you would call rollback/commit before rethrowing the
exception, but this kind of defeats the purpose of try-with-resource.
Connection.close implements AutoClosable.close so we can't have two
different behaviors for the two [2] scenarios (try-with-resources vs
explicit close).
Have I missed something that makes this problem moot?
Regards,
--
Kristian
[1] "It is *strongly recommended* that an application explicitly
commits or rolls back an active transaction prior to calling the
|close| method. If the |close| method is called and there is an
active transaction, the results are implementation-defined."
[2] Maybe one could use stack trace inspection, but that doesn't
sound like a good solution.
Hi Kristian,
Can you post a snippet of application code which you think is
mis-behaving? The following program runs correctly for me and does not
raise any exceptions.
The issue I'm talking about arises when some kind of error happens in
the program. The error itself doesn't have to be related to JDBC/SQL.
Consider this class:
----> Program
import java.sql.*;
public class TryWithResourcesDerby {
private static Connection connect()
throws SQLException {
return DriverManager.getConnection(
"jdbc:derby:memory:db;create=true");
}
public static void main(String[] args)
throws Exception {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
try (Connection con = connect()) {
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.executeUpdate("create table t (id int)");
if (true) {
throw new Exception("uhu, something went wrong");
}
stmt.executeUpdate("insert into t values 1,2,3,4,5");
}
}
}
----> Output
$ java -cp derbyrun.jar:. TryWithResourcesDerby
Exception in thread "main" java.lang.Exception: uhu, something went wrong
at TryWithResourcesDerby.main(TryWithResourcesDerby.java:19)
Suppressed: java.sql.SQLException: Cannot close a connection
while a transaction is still active.
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.checkForTransactionInProgress(Unknown
Source)
at
org.apache.derby.impl.jdbc.EmbedConnection.close(Unknown Source)
at
TryWithResourcesDerby.main(TryWithResourcesDerby.java:22)
Caused by: java.sql.SQLException: Cannot close a connection
while a transaction is still active.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
... 8 more
I understand why this happens, but in this case my intention was to wrap
the connection creation in try-with-resources simply so I could just
forget about it :)
As Rick already mentioned, changing the behavior would have backward
compatibility issues.
We could add some documentation, but I assume most people would figure
this out and deal with it in their code (i.e. a second try-catch block,
or in some cases maybe a catch-clause in the try-with-resources block
would be sufficient). What we don't want to see is people turning
auto-commit on just to avoid this behavior :)
--
Kristian
Of course, you could see the behavior you are describing if you create
the Connection in the initializer of the try-with-resources block.
That's because Derby's Connection.close() raises an exception if the
session has an in-flight transaction. As you note, Derby's behavior is
allowed by the JDBC spec.
We could change Connection.close() so that it always commits or rolls
back in-flight work. That would have serious backward compatibility
issues so we would have to defer that kind of change to an 11.0
release. Alternatively, we could add a knob which lets applications
configure the behavior of Connection.close().
Thanks,
-Rick
import java.sql.*;
public class z
{
public static void main( String... args ) throws Exception
{
Connection conn = DriverManager.getConnection(
"jdbc:derby:memory:db;create=true" );
conn.prepareStatement( "create table t( a int )" ).execute();
conn.setAutoCommit( false );
try ( PreparedStatement ps = conn.prepareStatement( "insert
into t( a ) values ( ? )" ) )
{
for ( int i = 1; i <= 4; i++ )
{
ps.setInt( 1, i );
ps.execute();
}
}
try ( ResultSet rs = conn.prepareStatement( "select count(*)
from t" ).executeQuery() )
{
rs.next();
System.out.println( rs.getString( 1 ) );
}
}
}
