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.
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 ) );
}
}
}
