Derby
  1. Derby
  2. DERBY-5910

Document use of Connection.close() with try-with-resources

    Details

      Description

      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.

      1. DERBY-5910.diff
        5 kB
        Kim Haase
      2. DERBY-5910.stat
        0.1 kB
        Kim Haase
      3. DERBY-5910.zip
        6 kB
        Kim Haase
      4. DERBY-5910-2.diff
        6 kB
        Kim Haase
      5. DERBY-5910-2.zip
        6 kB
        Kim Haase
      6. DERBY-5910-3.diff
        6 kB
        Kim Haase
      7. DERBY-5910-3.zip
        6 kB
        Kim Haase
      8. DERBY-5910-4.diff
        7 kB
        Kim Haase
      9. DERBY-5910-4.zip
        6 kB
        Kim Haase

        Activity

        Hide
        Kim Haase added a comment -

        This work is likely to involve changes to the following topics (at least):

        "java.sql.Connection interface" (http://db.apache.org/derby/docs/dev/ref/rrefjdbc27734.html)

        "Explicitly closing Statements, ResultSets, and Connections" (http://db.apache.org/derby/docs/dev/devguide/cdevconcepts839085.html)

        Show
        Kim Haase added a comment - This work is likely to involve changes to the following topics (at least): "java.sql.Connection interface" ( http://db.apache.org/derby/docs/dev/ref/rrefjdbc27734.html ) "Explicitly closing Statements, ResultSets, and Connections" ( http://db.apache.org/derby/docs/dev/devguide/cdevconcepts839085.html )
        Hide
        Kim Haase added a comment -

        Attaching DERBY-5910.diff, DERBY-5910.stat, and DERBY-5910.zip, with these changes:

        M src/devguide/cdevconcepts839085.dita
        M src/ref/rrefjdbc27734.dita

        I am sure some fixes are needed, especially to the Developer's Guide topic. There's probably some duplication, and I'm not sure quite how to distinguish between what to do with and without try-with-resources. Also, adding whether or not auto-commit is on or off adds yet another variant to the mix, though I'm not sure it actually makes a difference. Should the auto-commit info be added to the reference topic?

        BTW, the font differences are to be somewhat consistent with the surrounding topics in the respective manuals.

        Show
        Kim Haase added a comment - Attaching DERBY-5910 .diff, DERBY-5910 .stat, and DERBY-5910 .zip, with these changes: M src/devguide/cdevconcepts839085.dita M src/ref/rrefjdbc27734.dita I am sure some fixes are needed, especially to the Developer's Guide topic. There's probably some duplication, and I'm not sure quite how to distinguish between what to do with and without try-with-resources. Also, adding whether or not auto-commit is on or off adds yet another variant to the mix, though I'm not sure it actually makes a difference. Should the auto-commit info be added to the reference topic? BTW, the font differences are to be somewhat consistent with the surrounding topics in the respective manuals.
        Hide
        Dag H. Wanvik added a comment -

        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."

        Show
        Dag H. Wanvik added a comment - 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."
        Hide
        Kim Haase added a comment -

        Thanks very much, Dag – I do appreciate the clarifications.

        I'm attaching DERBY-5910-2.diff and DERBY-5910-2.zip, which I hope make appropriate fixes in both topics.

        Thanks for any additional feedback!

        Show
        Kim Haase added a comment - Thanks very much, Dag – I do appreciate the clarifications. I'm attaching DERBY-5910 -2.diff and DERBY-5910 -2.zip, which I hope make appropriate fixes in both topics. Thanks for any additional feedback!
        Hide
        Rick Hillegas added a comment -

        Thanks for the second patch, Kim. Looks great. Some comments:

        cdevconcepts839085

        o Fourth paragraph: "Derby will attempt to close" ->
        "the JRE will attempt to close"

        o Fifth paragraph: "an an" -> "an"

        o Same paragraph. I would say "Derby will not close" rather than
        "Derby will fail to close" because the second phrase suggests to me
        that Derby is doing something wrong.

        rrefjdbc27734

        o Third paragraph: "Derby will attempt to close" ->
        "the JRE will attempt to close"

        o Fourth paragraph: "an an" -> "an"

        o Same paragraph. I would say "Derby will not close" rather than
        "Derby will fail to close" because the second phrase suggests to me
        that Derby is doing something wrong.

        Thanks,
        -Rick

        Show
        Rick Hillegas added a comment - Thanks for the second patch, Kim. Looks great. Some comments: cdevconcepts839085 o Fourth paragraph: "Derby will attempt to close" -> "the JRE will attempt to close" o Fifth paragraph: "an an" -> "an" o Same paragraph. I would say "Derby will not close" rather than "Derby will fail to close" because the second phrase suggests to me that Derby is doing something wrong. rrefjdbc27734 o Third paragraph: "Derby will attempt to close" -> "the JRE will attempt to close" o Fourth paragraph: "an an" -> "an" o Same paragraph. I would say "Derby will not close" rather than "Derby will fail to close" because the second phrase suggests to me that Derby is doing something wrong. Thanks, -Rick
        Hide
        Kim Haase added a comment -

        Thanks, Rick, for catching those items. Attaching DERBY-5910-3.diff and DERBY-5910-3.zip, with the suggested fixes.

        Just a sanity check – in one paragraph it's the JRE that's closing the try-with-resources connection, but in the next paragraph it's Derby that's not closing it, presumably because it's still within the block and handling the transaction error?

        Show
        Kim Haase added a comment - Thanks, Rick, for catching those items. Attaching DERBY-5910 -3.diff and DERBY-5910 -3.zip, with the suggested fixes. Just a sanity check – in one paragraph it's the JRE that's closing the try-with-resources connection, but in the next paragraph it's Derby that's not closing it, presumably because it's still within the block and handling the transaction error?
        Hide
        Rick Hillegas added a comment -

        Thanks for the third rev of the patch, Kim.

        > Just a sanity check...

        At the end of the try-with-resources block, the JRE calls the close() method on the Derby connection.

        If Derby encounters an error while processing a statement, Derby does the following:

        1) If the error is statement-severity, then Derby releases resources held by the statement. But there may still be in-flight transactional work left over from previous statements in the transaction. If so, then Derby will raise an exception when the JRE calls Connection.close() on exiting the try-with-resources block. This is the problem case.

        2) If the error is transaction-severity, then Derby does (1) and rolls back the transaction. The connection is now clean so Derby will not raise an exception when the JRE calls Connection.close() on exiting the try-with-resources block.

        3) If the error is session-severity, then Derby closes the connection. Nothing happens when the JRE calls Connection.close() on exiting the try-with-resources block. That is because Connection.close() is idempotent.

        4) If the error is system-severity, then Derby shuts down the database. Again, nothing happens when the JRE calls Connection.close() on exiting the try-with-resources block.

        Here are some additional suggestions:

        Remove this paragraph:

        "A session-severity or higher exception causes the connection to close and all other JDBC objects against it to be closed. System-severity exceptions cause the Derby system to shut down, which not only closes the connection but means that no new connections should be created in the current JVM."

        Reword the later paragraph:

        "Note that a transaction-severity or higher exception causes Derby to abort an in-flight transaction. But a statement-severity exception does NOT roll back the transaction. Also note that Derby throws an exception if an attempt is made to close a connection with an in-flight transaction. Suppose now that a Connection is declared in a try-with-resources statement, a transaction is in-flight, and an unhandled statement-severity error occurs inside the try-with-resources block. In this situation, Derby will raise a follow-on exception as the JRE exits the try-with-resource block. (For details on error severity levels, see derby.stream.error.logSeverityLevel.)"

        Show
        Rick Hillegas added a comment - Thanks for the third rev of the patch, Kim. > Just a sanity check... At the end of the try-with-resources block, the JRE calls the close() method on the Derby connection. If Derby encounters an error while processing a statement, Derby does the following: 1) If the error is statement-severity, then Derby releases resources held by the statement. But there may still be in-flight transactional work left over from previous statements in the transaction. If so, then Derby will raise an exception when the JRE calls Connection.close() on exiting the try-with-resources block. This is the problem case. 2) If the error is transaction-severity, then Derby does (1) and rolls back the transaction. The connection is now clean so Derby will not raise an exception when the JRE calls Connection.close() on exiting the try-with-resources block. 3) If the error is session-severity, then Derby closes the connection. Nothing happens when the JRE calls Connection.close() on exiting the try-with-resources block. That is because Connection.close() is idempotent. 4) If the error is system-severity, then Derby shuts down the database. Again, nothing happens when the JRE calls Connection.close() on exiting the try-with-resources block. Here are some additional suggestions: Remove this paragraph: "A session-severity or higher exception causes the connection to close and all other JDBC objects against it to be closed. System-severity exceptions cause the Derby system to shut down, which not only closes the connection but means that no new connections should be created in the current JVM." Reword the later paragraph: "Note that a transaction-severity or higher exception causes Derby to abort an in-flight transaction. But a statement-severity exception does NOT roll back the transaction. Also note that Derby throws an exception if an attempt is made to close a connection with an in-flight transaction. Suppose now that a Connection is declared in a try-with-resources statement, a transaction is in-flight, and an unhandled statement-severity error occurs inside the try-with-resources block. In this situation, Derby will raise a follow-on exception as the JRE exits the try-with-resource block. (For details on error severity levels, see derby.stream.error.logSeverityLevel.)"
        Hide
        Kim Haase added a comment -

        Thanks again, Rick, for your help.

        Attaching DERBY-5910-4.diff and DERBY-5910-4.zip – I hope this does the trick.

        Show
        Kim Haase added a comment - Thanks again, Rick, for your help. Attaching DERBY-5910 -4.diff and DERBY-5910 -4.zip – I hope this does the trick.
        Hide
        Rick Hillegas added a comment -

        Thanks, Kim. Looks great. +1

        Show
        Rick Hillegas added a comment - Thanks, Kim. Looks great. +1
        Hide
        Kim Haase added a comment -

        Thanks again, Rick.

        Committed patch DERBY-5910-4.diff to documentation trunk at revision 1399767.
        Merged to 10.9 doc branch at revision 1399778.
        Merged to 10.8 doc branch at revision 1399798.

        Show
        Kim Haase added a comment - Thanks again, Rick. Committed patch DERBY-5910 -4.diff to documentation trunk at revision 1399767. Merged to 10.9 doc branch at revision 1399778. Merged to 10.8 doc branch at revision 1399798.
        Hide
        Kim Haase added a comment -

        Closing, since changes have appeared in Latest Alpha Manuals.

        Show
        Kim Haase added a comment - Closing, since changes have appeared in Latest Alpha Manuals.

          People

          • Assignee:
            Kim Haase
            Reporter:
            Kim Haase
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development