You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Kristian Waagan <kr...@oracle.com> on 2012/08/03 00:29:53 UTC
Java 7 try-with-resources (AutoClosable) on Derby connections with
auto-commit off
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.
Re: Java 7 try-with-resources (AutoClosable) on Derby connections with auto-commit off
Posted by "Dag H. Wanvik" <da...@oracle.com>.
Kristian Waagan <kr...@oracle.com> writes:
> Hello,
>
> Has anyone considered the case of using try-with-resources (Java SE 7)
> with Derby connections with auto-commit off?
Presumably, this would happen only if the app code received an
*statement level SQLException* which isn't caught (with retry or
rollback), leading to an (auto)close. If the SQLException is
transaction or connection level error, the auto-close wouldn't throw. I
agree it seems to defeat the purpose somewhat..
Re: Java 7 try-with-resources (AutoClosable) on Derby connections
with auto-commit off
Posted by Rick Hillegas <ri...@oracle.com>.
On 8/17/12 5:42 PM, dag.wanvik@oracle.com wrote:
> Knut Anders Hatlen wrote:
>
>> What I meant was that existing applications written against Derby will
>> already have been coded so that they explicitly commit or abort the
>> transaction before they close the connection. This means they are not
>> likely to be affected by a change in how we handle closing of active
>> transactions.
> Rick Hillegas<ri...@oracle.com> writes:
>
>> I remain concerned about backward compatibility and would object to
>> option (3).
> If Knut's analysis holds (I think it does), how do you see backward
> compatibility being a problem, Rick? It seems to me this could
> potentially trip up developers used to the old Derby behavior,
Hi Dag,
I don't understand the question. Backward compatibility is exactly what
you just described: a change in behavior for applications which are used
to the old behavior.
> but not
> apps developed against Derby with the existing behavior. I'd say that is
> an accentable price to pay in this case, given proper release note heads
> up.
This is the scenario which would be handled by adding a new knob.
Thanks,
-Rick
> Dag
>
Re: Java 7 try-with-resources (AutoClosable) on Derby connections with auto-commit off
Posted by "Dag H. Wanvik" <da...@oracle.com>.
Knut Anders Hatlen wrote:
> What I meant was that existing applications written against Derby will
> already have been coded so that they explicitly commit or abort the
> transaction before they close the connection. This means they are not
> likely to be affected by a change in how we handle closing of active
> transactions.
Rick Hillegas <ri...@oracle.com> writes:
> I remain concerned about backward compatibility and would object to
> option (3).
If Knut's analysis holds (I think it does), how do you see backward
compatibility being a problem, Rick? It seems to me this could
potentially trip up developers used to the old Derby behavior, but not
apps developed against Derby with the existing behavior. I'd say that is
an accentable price to pay in this case, given proper release note heads
up.
Dag
Re: Java 7 try-with-resources (AutoClosable) on Derby connections
with auto-commit off
Posted by Rick Hillegas <ri...@oracle.com>.
On 8/17/12 8:50 AM, Knut Anders Hatlen wrote:
> Rick Hillegas<ri...@oracle.com> writes:
>
>> Thanks for that clarification, Knut. The statement which I didn't
>> understand was "I wouldn't expect that changing it would cause
>> problems for existing applications".
> What I meant was that existing applications written against Derby will
> already have been coded so that they explicitly commit or abort the
> transaction before they close the connection. This means they are not
> likely to be affected by a change in how we handle closing of active
> transactions.
I think many applications may have addressed the problem that way.
However, I think other applications may put the compensatory logic in a
catch block. That may seem goofy to us, but it may make sense to someone.
> ...
>> If I understand this email thread correctly, there are a couple
>> suggestions on the table:
>>
>> 1) Re-word the JDBC spec, the AutoCloseable javadoc, or Derby
>> documentation. This suggestion may need some clarification.
>>
>> 2) Add a knob to Derby allowing applications to configure the behavior
>> of Connection.close() when there is uncommitted in-flight work. The
>> knob would let the application configure whether Connection.close()
>> committed, rolled back, or raised an exception. The default would be
>> the current behavior of raising an exception.
>>
>> 3) Change Connection.close() to always commit in-flight work or always
>> roll back in-flight work.
>>
>> Do people have other suggestions?
> I think the current behaviour is fine, as we essentially raise an error
> to tell the application it is attempting to do something that's not well
> specified and not portable.
>
> However, if someone feels strongly for option (3), I wouldn't object
> very much, as the method would still follow the JDBC spec, the change
> would be backward compatible, and the behaviour would match at least the
> two other JDBC drivers I checked (MySQL and PostgreSQL).
>
>
I remain concerned about backward compatibility and would object to
option (3).
Thanks,
-Rick
Re: Java 7 try-with-resources (AutoClosable) on Derby connections with auto-commit off
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Rick Hillegas <ri...@oracle.com> writes:
> Thanks for that clarification, Knut. The statement which I didn't
> understand was "I wouldn't expect that changing it would cause
> problems for existing applications".
What I meant was that existing applications written against Derby will
already have been coded so that they explicitly commit or abort the
transaction before they close the connection. This means they are not
likely to be affected by a change in how we handle closing of active
transactions.
>>> For some applications the correct behavior
>>> is "oops, an error occurred, I don't know what state I'm in, I'd
>>> better throw away the uncommitted work and get out." For other
>>> applications the correct behavior is "oops, an error occurred, oh
>>> well, everything up to that point was good so save that work and get
>>> out." I think the second behavior is appropriate for applications
>>> which could correctly operate in autocommit mode but which batch a lot
>>> of statements into bigger commits for performance reasons.
>>
>> Yes, there's no way to know which of the two options is what the
>> application wants, and that's why we're currently raising an exception.
>> Since the JDBC specification (since 4.0) explicitly states that the
>> results are implementation-defined, the application won't be portable
>> unless it calls rollback() or commit() before it closes the connection.
>> Making it more convenient to use Connection together with
>> try-with-resources in a portable manner, is probably more of a
>> specification task than an implementation task.
>
> By "specification" do you mean clarifications to the JDBC spec or the
> javadoc for AutoCloseable? Do you mean changes to Derby documentation?
> Something else?
I meant the specification of the try-with-resources language feature
and/or the specification of java.sql.Connection's close method.
For example, if the JDBC spec said that calling Connection.close() on an
active transaction would cause the transaction to be rolled back and the
connection to be closed, Connection could be used with
try-with-resources without nested try/finally clauses to handle cleanup.
But I guess there's probably a good reason why the JDBC spec says the
result is implementation-defined, so I'm not sure it's very likely that
it'll ever be more well-specified.
What I tried to say, was that making language features and APIs play
well together is primarily a job for those who design the language and
the APIs, and that there's not so much we, who merely implement the
APIs, can do.
> If I understand this email thread correctly, there are a couple
> suggestions on the table:
>
> 1) Re-word the JDBC spec, the AutoCloseable javadoc, or Derby
> documentation. This suggestion may need some clarification.
>
> 2) Add a knob to Derby allowing applications to configure the behavior
> of Connection.close() when there is uncommitted in-flight work. The
> knob would let the application configure whether Connection.close()
> committed, rolled back, or raised an exception. The default would be
> the current behavior of raising an exception.
>
> 3) Change Connection.close() to always commit in-flight work or always
> roll back in-flight work.
>
> Do people have other suggestions?
I think the current behaviour is fine, as we essentially raise an error
to tell the application it is attempting to do something that's not well
specified and not portable.
However, if someone feels strongly for option (3), I wouldn't object
very much, as the method would still follow the JDBC spec, the change
would be backward compatible, and the behaviour would match at least the
two other JDBC drivers I checked (MySQL and PostgreSQL).
Option (1) isn't under our control, so there's not much we can do.
(Except for the documentation part, but it looks like we already
document the current behaviour in the topic "Explicitly closing
Statements, ResultSets, and Connections" in the developer's guide.)
I'm not in favour (2), as I don't think the benefit justifies another
knob. One well-defined behaviour of close() is better than three. :)
--
Knut Anders
Re: Java 7 try-with-resources (AutoClosable) on Derby connections
with auto-commit off
Posted by Rick Hillegas <ri...@oracle.com>.
On 8/17/12 2:17 AM, Knut Anders Hatlen wrote:
> Rick Hillegas<ri...@oracle.com> writes:
>
>> On 8/16/12 2:44 AM, Knut Anders Hatlen wrote:
>>> Kristian Waagan<kr...@oracle.com> writes:
>>>
>>>> On 10.08.2012 18:29, dag.wanvik@oracle.com wrote:
>>>>> Rick Hillegas<ri...@oracle.com> writes:
>>>>>
>>>>>> Thanks for the code snippet, Kristian. I don't see this as a Derby
>>>>>> problem.
>>>>> Hmm.. IMHO I'd like a close that does what I tell it. In my mind that
>>>>> means rolling back and closing.
>>>> That's exactly what I felt after having written a simple app using
>>>> autoclosable.
>>>> Just wanted to hear if anyone else had stumbled across this behavior
>>>> and given it any thought.
>>>>
>>>>> That the current behavior doesn't play well with autoclosable is a (new)
>>>>> argument for changed behavior.
>>>> Yes, I hope this is brought up again when we're at a point where we
>>>> can change the current behavior without too much trouble.
>>> If we think changing it is the right thing to do, I'd say that now is
>>> the right time to do it on trunk. Then we'd have plenty of time to fix
>>> any fallouts before the next feature release.
>>>
>>> I wouldn't expect that changing it would cause problems for existing
>>> applications, as it will only make calls that used to fail, succeed. In
>>> fact, many users have found the current behaviour surprising.
>> I don't understand this.
> I guess they're surprised because they've used other JDBC drivers, such
> as MySQL and PostgreSQL, which silently abort the transaction when the
> connection is closed.
Thanks for that clarification, Knut. The statement which I didn't
understand was "I wouldn't expect that changing it would cause problems
for existing
applications". I should have added my comment after that sentence rather
than after the end of its paragraph.
>> For some applications the correct behavior
>> is "oops, an error occurred, I don't know what state I'm in, I'd
>> better throw away the uncommitted work and get out." For other
>> applications the correct behavior is "oops, an error occurred, oh
>> well, everything up to that point was good so save that work and get
>> out." I think the second behavior is appropriate for applications
>> which could correctly operate in autocommit mode but which batch a lot
>> of statements into bigger commits for performance reasons.
> Yes, there's no way to know which of the two options is what the
> application wants, and that's why we're currently raising an exception.
> Since the JDBC specification (since 4.0) explicitly states that the
> results are implementation-defined, the application won't be portable
> unless it calls rollback() or commit() before it closes the connection.
> Making it more convenient to use Connection together with
> try-with-resources in a portable manner, is probably more of a
> specification task than an implementation task.
By "specification" do you mean clarifications to the JDBC spec or the
javadoc for AutoCloseable? Do you mean changes to Derby documentation?
Something else?
If I understand this email thread correctly, there are a couple
suggestions on the table:
1) Re-word the JDBC spec, the AutoCloseable javadoc, or Derby
documentation. This suggestion may need some clarification.
2) Add a knob to Derby allowing applications to configure the behavior
of Connection.close() when there is uncommitted in-flight work. The knob
would let the application configure whether Connection.close()
committed, rolled back, or raised an exception. The default would be the
current behavior of raising an exception.
3) Change Connection.close() to always commit in-flight work or always
roll back in-flight work.
Do people have other suggestions?
Thanks,
-Rick
Re: Java 7 try-with-resources (AutoClosable) on Derby connections with auto-commit off
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Rick Hillegas <ri...@oracle.com> writes:
> On 8/16/12 2:44 AM, Knut Anders Hatlen wrote:
>> Kristian Waagan<kr...@oracle.com> writes:
>>
>>> On 10.08.2012 18:29, dag.wanvik@oracle.com wrote:
>>>> Rick Hillegas<ri...@oracle.com> writes:
>>>>
>>>>> Thanks for the code snippet, Kristian. I don't see this as a Derby
>>>>> problem.
>>>> Hmm.. IMHO I'd like a close that does what I tell it. In my mind that
>>>> means rolling back and closing.
>>> That's exactly what I felt after having written a simple app using
>>> autoclosable.
>>> Just wanted to hear if anyone else had stumbled across this behavior
>>> and given it any thought.
>>>
>>>> That the current behavior doesn't play well with autoclosable is a (new)
>>>> argument for changed behavior.
>>> Yes, I hope this is brought up again when we're at a point where we
>>> can change the current behavior without too much trouble.
>> If we think changing it is the right thing to do, I'd say that now is
>> the right time to do it on trunk. Then we'd have plenty of time to fix
>> any fallouts before the next feature release.
>>
>> I wouldn't expect that changing it would cause problems for existing
>> applications, as it will only make calls that used to fail, succeed. In
>> fact, many users have found the current behaviour surprising.
>
> I don't understand this.
I guess they're surprised because they've used other JDBC drivers, such
as MySQL and PostgreSQL, which silently abort the transaction when the
connection is closed.
> For some applications the correct behavior
> is "oops, an error occurred, I don't know what state I'm in, I'd
> better throw away the uncommitted work and get out." For other
> applications the correct behavior is "oops, an error occurred, oh
> well, everything up to that point was good so save that work and get
> out." I think the second behavior is appropriate for applications
> which could correctly operate in autocommit mode but which batch a lot
> of statements into bigger commits for performance reasons.
Yes, there's no way to know which of the two options is what the
application wants, and that's why we're currently raising an exception.
Since the JDBC specification (since 4.0) explicitly states that the
results are implementation-defined, the application won't be portable
unless it calls rollback() or commit() before it closes the connection.
Making it more convenient to use Connection together with
try-with-resources in a portable manner, is probably more of a
specification task than an implementation task.
--
Knut Anders
Re: Java 7 try-with-resources (AutoClosable) on Derby connections
with auto-commit off
Posted by Rick Hillegas <ri...@oracle.com>.
On 8/16/12 2:44 AM, Knut Anders Hatlen wrote:
> Kristian Waagan<kr...@oracle.com> writes:
>
>> On 10.08.2012 18:29, dag.wanvik@oracle.com wrote:
>>> Rick Hillegas<ri...@oracle.com> writes:
>>>
>>>> Thanks for the code snippet, Kristian. I don't see this as a Derby
>>>> problem.
>>> Hmm.. IMHO I'd like a close that does what I tell it. In my mind that
>>> means rolling back and closing.
>> That's exactly what I felt after having written a simple app using
>> autoclosable.
>> Just wanted to hear if anyone else had stumbled across this behavior
>> and given it any thought.
>>
>>> That the current behavior doesn't play well with autoclosable is a (new)
>>> argument for changed behavior.
>> Yes, I hope this is brought up again when we're at a point where we
>> can change the current behavior without too much trouble.
> If we think changing it is the right thing to do, I'd say that now is
> the right time to do it on trunk. Then we'd have plenty of time to fix
> any fallouts before the next feature release.
>
> I wouldn't expect that changing it would cause problems for existing
> applications, as it will only make calls that used to fail, succeed. In
> fact, many users have found the current behaviour surprising.
I don't understand this. For some applications the correct behavior is
"oops, an error occurred, I don't know what state I'm in, I'd better
throw away the uncommitted work and get out." For other applications the
correct behavior is "oops, an error occurred, oh well, everything up to
that point was good so save that work and get out." I think the second
behavior is appropriate for applications which could correctly operate
in autocommit mode but which batch a lot of statements into bigger
commits for performance reasons.
Thanks,
-Rick
Re: Java 7 try-with-resources (AutoClosable) on Derby connections with auto-commit off
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Kristian Waagan <kr...@oracle.com> writes:
> On 10.08.2012 18:29, dag.wanvik@oracle.com wrote:
>> Rick Hillegas<ri...@oracle.com> writes:
>>
>>> Thanks for the code snippet, Kristian. I don't see this as a Derby
>>> problem.
>> Hmm.. IMHO I'd like a close that does what I tell it. In my mind that
>> means rolling back and closing.
>
> That's exactly what I felt after having written a simple app using
> autoclosable.
> Just wanted to hear if anyone else had stumbled across this behavior
> and given it any thought.
>
>>
>> That the current behavior doesn't play well with autoclosable is a (new)
>> argument for changed behavior.
>
> Yes, I hope this is brought up again when we're at a point where we
> can change the current behavior without too much trouble.
If we think changing it is the right thing to do, I'd say that now is
the right time to do it on trunk. Then we'd have plenty of time to fix
any fallouts before the next feature release.
I wouldn't expect that changing it would cause problems for existing
applications, as it will only make calls that used to fail, succeed. In
fact, many users have found the current behaviour surprising.
--
Knut Anders
Re: Java 7 try-with-resources (AutoClosable) on Derby connections
with auto-commit off
Posted by Kristian Waagan <kr...@oracle.com>.
On 10.08.2012 18:29, dag.wanvik@oracle.com wrote:
> Rick Hillegas<ri...@oracle.com> writes:
>
>> Thanks for the code snippet, Kristian. I don't see this as a Derby
>> problem.
> Hmm.. IMHO I'd like a close that does what I tell it. In my mind that
> means rolling back and closing.
That's exactly what I felt after having written a simple app using
autoclosable.
Just wanted to hear if anyone else had stumbled across this behavior and
given it any thought.
>
> That the current behavior doesn't play well with autoclosable is a (new)
> argument for changed behavior.
Yes, I hope this is brought up again when we're at a point where we can
change the current behavior without too much trouble.
Regards,
--
Kristian
>
> Dag
>
>
>
>> It might make sense for the JDBC spec to point out the
>> undefined AutoClosable behavior of Connection:
>>
>> o Statements and ResultSets have well defined close() semantics and
>> can be used portably in try-with-resources blocks.
>>
>> o But the behavior of Connection.close() is undefined. This makes it
>> hard to write portable code which declares Connections in
>> try-with-resources blocks.
>>
>> Thanks,
>> -Rick
Re: Java 7 try-with-resources (AutoClosable) on Derby connections with auto-commit off
Posted by "Dag H. Wanvik" <da...@oracle.com>.
Rick Hillegas <ri...@oracle.com> writes:
> Thanks for the code snippet, Kristian. I don't see this as a Derby
> problem.
Hmm.. IMHO I'd like a close that does what I tell it. In my mind that
means rolling back and closing.
That the current behavior doesn't play well with autoclosable is a (new)
argument for changed behavior.
Dag
> It might make sense for the JDBC spec to point out the
> undefined AutoClosable behavior of Connection:
>
> o Statements and ResultSets have well defined close() semantics and
> can be used portably in try-with-resources blocks.
>
> o But the behavior of Connection.close() is undefined. This makes it
> hard to write portable code which declares Connections in
> try-with-resources blocks.
>
> Thanks,
> -Rick
Re: Java 7 try-with-resources (AutoClosable) on Derby connections
with auto-commit off
Posted by Rick Hillegas <ri...@oracle.com>.
On 8/8/12 7:32 AM, Kristian Waagan wrote:
> 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 :)
>
>
Thanks for the code snippet, Kristian. I don't see this as a Derby
problem. It might make sense for the JDBC spec to point out the
undefined AutoClosable behavior of Connection:
o Statements and ResultSets have well defined close() semantics and can
be used portably in try-with-resources blocks.
o But the behavior of Connection.close() is undefined. This makes it
hard to write portable code which declares Connections in
try-with-resources blocks.
Thanks,
-Rick
Re: Java 7 try-with-resources (AutoClosable) on Derby connections
with auto-commit off
Posted by Kristian Waagan <kr...@oracle.com>.
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 ) );
> }
> }
> }
>
Re: Java 7 try-with-resources (AutoClosable) on Derby connections
with auto-commit off
Posted by Rick Hillegas <ri...@oracle.com>.
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 ) );
}
}
}