[jira] [Commented] (DERBY-5910) Document use of Connection.close() with try-with-resources

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

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

Dag H. Wanvik commented on DERBY-5910:
--------------------------------------

Thanks, Kim!!

I'll attempt a rewording of the this Dev guide paragraph to make it clearer when/why one can't rely on connection auto-close in Derby. Feel free to improve the wording :)

"Statements, result sets, and connections extend AutoCloseable in JDK 7 and after. If you declare a connection in a try-with-resources statement, make sure that within the try block you commit or roll back any transactions that you initiate. This is particularly important if your application needs to run with any other RDBMS, because the transactional behavior of the Connection.close method is not specified by the JDBC specification. Derby raises an exception if there are uncommitted transactions or other in-flight work, but another RDBMS may behave differently."

->

"Statements, result sets, and connections extend AutoCloseable in JDK 7 and after. If you declare a connection in a try-with-resources statement *and* and there is an error the code doesn't catch, the connection will be attempted closed automatically.

Now, Derby throws an exception if one tries to close a connection with a pending transaction. This has the following implication for the use of try-with-resources: if you are running with auto-commit off *and* a transaction is pending (i.e. it has not been rolled back or committed) when an an unhandled error of *statement level severity*[1] happens, Derby will fail to auto close the connection, since the error itself doesn't cause the transaction to roll back. 

It is therefore best to always catch errors inside the try-with-resources block and roll back or commit as the case may be to ensure there is no pending transaction when leaving the try-with-resources block. This will be a good idea for portability anyway, since DBMSes differ as to their semantics when trying to close a connection with a pending transaction.

[1] See the description of property "derby.stream.error.logSeverityLevel" in the Reference Manual for information on errror severity levels."
                
> Document use of Connection.close() with try-with-resources
> ----------------------------------------------------------
>
>                 Key: DERBY-5910
>                 URL: https://issues.apache.org/jira/browse/DERBY-5910
>             Project: Derby
>          Issue Type: Improvement
>          Components: Documentation
>    Affects Versions: 10.9.1.0
>            Reporter: Kim Haase
>            Assignee: Kim Haase
>         Attachments: DERBY-5910.diff, DERBY-5910.stat, DERBY-5910.zip
>
>
> The Java SE 7 try-with-resources feature can cause problems with AutoCloseable objects like java.sql.Connection. You must be careful when writing portable code which declares Connections in the initializers of try-with-resources blocks, because Derby raises an exception if you try to close a Connection with uncommitted work in-flight. 
> Changing Derby's Connection.close() to always commit in-flight work (and not raise an exception) would create backward compatibility issues, but we do not know yet how serious these issues would be, owing to a lack of data.
> Rather than change Derby's behavior now, we should document how Derby's Connection.close() behaves and describe the portability issues involved in declaring Derby Connections in try-with-resources initializers. 

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira