Derby
  1. Derby
  2. DERBY-3115

With embedded driver and autocommit, when closing a connection, updates on updatable result set are lost, unless result set is closed

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.3.0, 10.6.1.0, 10.7.1.1
    • Fix Version/s: None
    • Component/s: JDBC
    • Urgency:
      Normal
    • Bug behavior facts:
      Deviation from standard, Embedded/Client difference

      Description

      With autocommit, if an application neglects to close the result set
      and/or the statement, the closing of the connection will lose any
      updates performed via an updatable result set.

      If autocommit is false, SQL state 25000 invalid transaction state will
      be thrown, however.

      The JDBC standard requires that statements be closed when the
      connection is closed, cf. JDBC 4, section 9.4.4: "All Statement
      objects created from a given Connection object will be closed when the
      close method for the object is called." For updatable result sets,
      closing the statement would lead to a closing of the result set, and
      hence a commit of the updates.

      For the network client it works as expected.

      1. Main.java
        6 kB
        Dag H. Wanvik

        Issue Links

          Activity

          Hide
          Kristian Waagan added a comment -

          Updated affects versions.

          Show
          Kristian Waagan added a comment - Updated affects versions.
          Hide
          Kathey Marsden added a comment -

          Triaged for 10.5.2. Setting normal urgency.

          Show
          Kathey Marsden added a comment - Triaged for 10.5.2. Setting normal urgency.
          Hide
          Daniel John Debrunner added a comment -

          > This leads me to the following implication chain:

          > closing connection => closing result set
          > => statement is completed
          > and then autocommit.

          Agreed, but that's not how the code is implemented. Ideally the implementation would follow the spec closely, so that the closing of the Connection triggers the closing of the ResultSet and that triggers the commit (if that means the statement is completed). Thus everything would follow naturally. Calling a commit explictly at close time just means that it's an extra place that auto-commit logic is handled, though for Derby it might be the easiest, as long as it can be proved it is equivalent to what the standard defines.

          > For callable statements it is more murky..
          > For a callable statement, the is no guidance as to whether a closing
          > of the connection also implicitly handles the outstanding inout and/or
          > result count, but I think it is a reasonable, symmetric expectation.

          That would make life easier, seems like a reasonable assumption. Good to get this added to the JDBC 4.x spec in section 10.1, a statement is also completed when its connection is closed (explicitly??). This would also make the commit() in close() follow the spec.

          > I have pinged Lance again!

          Thanks. Another interesting wrinkle of auto-commit mode according to the standard is that there is no application mechanism to rollback changes made through an updateable result set when in auto-commit. This seems like a hole, something that would be fixed by allowing rollback() [and commit()] to be called even when in auto-commit mode (just as Derby does).

          Show
          Daniel John Debrunner added a comment - > This leads me to the following implication chain: > closing connection => closing result set > => statement is completed > and then autocommit. Agreed, but that's not how the code is implemented. Ideally the implementation would follow the spec closely, so that the closing of the Connection triggers the closing of the ResultSet and that triggers the commit (if that means the statement is completed). Thus everything would follow naturally. Calling a commit explictly at close time just means that it's an extra place that auto-commit logic is handled, though for Derby it might be the easiest, as long as it can be proved it is equivalent to what the standard defines. > For callable statements it is more murky.. > For a callable statement, the is no guidance as to whether a closing > of the connection also implicitly handles the outstanding inout and/or > result count, but I think it is a reasonable, symmetric expectation. That would make life easier, seems like a reasonable assumption. Good to get this added to the JDBC 4.x spec in section 10.1, a statement is also completed when its connection is closed (explicitly??). This would also make the commit() in close() follow the spec. > I have pinged Lance again! Thanks. Another interesting wrinkle of auto-commit mode according to the standard is that there is no application mechanism to rollback changes made through an updateable result set when in auto-commit. This seems like a hole, something that would be fixed by allowing rollback() [and commit()] to be called even when in auto-commit mode (just as Derby does).
          Hide
          Dag H. Wanvik added a comment -

          Thanks for helping me figuring this out one, Dan!

          > To really follow the JDBC spec I think what is needed is:
          > 3) If Connection.close() leads to a statement being completed (JDBC
          > 4 section 10.1) then a commit should be triggered.

          I think the standard requires statements to be closed when connection
          is closed(9.4.4), but "closed" may not (always) imply "completed"? If
          they are not the same, then it would appear that, yes, a transaction
          can still be active an an exception should be thrown.

          Can you help me understand when that would be the case?

          I can't understand how it is possible for selects:

          Section 15.2.5 Closing a result set object says:

          A ResultSet object is explicitly closed when
          :

          • (bullet 2) The Statement or Connection object that produced the ResultSet is
            explictly closed.

          This leads me to the following implication chain:

          closing connection => closing result set
          => statement is completed

          and then autocommit.

          For callable statements it is more murky..
          For a callable statement, the is no guidance as to whether a closing
          of the connection also implicitly handles the outstanding inout and/or
          result count, but I think it is a reasonable, symmetric expectation.

          So I still think 1a) is correct, at least for SELECTs.

          > JDBC 4 seems to indicate that both those ResultSets are open and can
          > be used within a single auto-committed transaction. The transaction
          > will be committed when rs1 or rs2 closes, whichever happens first.
          > Note that Derby implements JDBC 3 in that it performs a commit (in
          > auto-commit) on any execution within the connection. I wonder if
          > JDBC 4 intended to make this change in behaviour.

          Yes, I remember this one. I think it was intentional, but I have
          pinged Lance again! If it is intentional, we could file another issue
          for it.

          Show
          Dag H. Wanvik added a comment - Thanks for helping me figuring this out one, Dan! > To really follow the JDBC spec I think what is needed is: > 3) If Connection.close() leads to a statement being completed (JDBC > 4 section 10.1) then a commit should be triggered. I think the standard requires statements to be closed when connection is closed(9.4.4), but "closed" may not (always) imply "completed"? If they are not the same, then it would appear that, yes, a transaction can still be active an an exception should be thrown. Can you help me understand when that would be the case? I can't understand how it is possible for selects: Section 15.2.5 Closing a result set object says: A ResultSet object is explicitly closed when : (bullet 2) The Statement or Connection object that produced the ResultSet is explictly closed. This leads me to the following implication chain: closing connection => closing result set => statement is completed and then autocommit. For callable statements it is more murky.. For a callable statement, the is no guidance as to whether a closing of the connection also implicitly handles the outstanding inout and/or result count, but I think it is a reasonable, symmetric expectation. So I still think 1a) is correct, at least for SELECTs. > JDBC 4 seems to indicate that both those ResultSets are open and can > be used within a single auto-committed transaction. The transaction > will be committed when rs1 or rs2 closes, whichever happens first. > Note that Derby implements JDBC 3 in that it performs a commit (in > auto-commit) on any execution within the connection. I wonder if > JDBC 4 intended to make this change in behaviour. Yes, I remember this one. I think it was intentional, but I have pinged Lance again! If it is intentional, we could file another issue for it.
          Hide
          Daniel John Debrunner added a comment -

          To really follow the JDBC spec I think what is needed is:

          3) If Connection.close() leads to a statement being completed (JDBC 4 section 10.1) then a commit should be triggered.
          If Connection.close() does not lead to a statement being completed then no commit is triggered, if a transaction is active an exception is thrown following SQL Standard 17.3 GR 6

          This approximates 1a) but is strictly not the same, and thus simply putting a commit in Connection.close() is not the same.

          This may be somewhat further confused by the rules for statement completion have changed from JDBC 3 to JDBC 4.
          JDBC 3 includes that a statement is completed when "another Statement object is executed on the same connection" (section 10.1),
          but this line was removed in JDBC 4.

          Thus this code behaves differently in JDBC 3 & 4

          ResultSet rs1 = ps1.executeQuery();
          ResultSet rs2 = ps2.executeQuery();

          JDBC 3 will commit & close rs1 when rs2 is created
          JDBC 4 seems to indicate that both those ResultSets are open and can be used within a single auto-committed transaction. The transaction will be committed when rs1 or rs2 closes, whichever happens first.

          Note that Derby implements JDBC 3 in that it performs a commit (in auto-commit) on any execution within the connection. I wonder if JDBC 4 intended to make this change in behaviour.

          Show
          Daniel John Debrunner added a comment - To really follow the JDBC spec I think what is needed is: 3) If Connection.close() leads to a statement being completed (JDBC 4 section 10.1) then a commit should be triggered. If Connection.close() does not lead to a statement being completed then no commit is triggered, if a transaction is active an exception is thrown following SQL Standard 17.3 GR 6 This approximates 1a) but is strictly not the same, and thus simply putting a commit in Connection.close() is not the same. This may be somewhat further confused by the rules for statement completion have changed from JDBC 3 to JDBC 4. JDBC 3 includes that a statement is completed when "another Statement object is executed on the same connection" (section 10.1), but this line was removed in JDBC 4. Thus this code behaves differently in JDBC 3 & 4 ResultSet rs1 = ps1.executeQuery(); ResultSet rs2 = ps2.executeQuery(); JDBC 3 will commit & close rs1 when rs2 is created JDBC 4 seems to indicate that both those ResultSets are open and can be used within a single auto-committed transaction. The transaction will be committed when rs1 or rs2 closes, whichever happens first. Note that Derby implements JDBC 3 in that it performs a commit (in auto-commit) on any execution within the connection. I wonder if JDBC 4 intended to make this change in behaviour.
          Hide
          Dag H. Wanvik added a comment -

          So what do you suggest here, Dan? I would be ok with alternative 2), but I
          wold prefer 1a. All suggested options potentially affect exiting apps, but 1b seems
          more onerous (silent loss of data).

          As for the criterion for callable statements being "completed" in the sense of the standard,
          thus triggering auto-commit, I think we could revisit that in another issue? I am not
          sure what the current behavior is...

          Show
          Dag H. Wanvik added a comment - So what do you suggest here, Dan? I would be ok with alternative 2), but I wold prefer 1a. All suggested options potentially affect exiting apps, but 1b seems more onerous (silent loss of data). As for the criterion for callable statements being "completed" in the sense of the standard, thus triggering auto-commit, I think we could revisit that in another issue? I am not sure what the current behavior is...
          Hide
          Daniel John Debrunner added a comment -

          Michelle> To end the transaction in autocommit mode, the objects must be closed.

          By "objects" I assume you mean ResultSets, the closing of the Statement is not required to auto-commit a transaction. The rules are in JDBC 4 section 10.1.

          However, closing the ResultSets is not sufficient for CallableStatements, see section 10.1 of JDBC 4.
          It indicates that the ResultSets must be closed and the update counts and output parameters must be fetched. It's unclear what happens if the update counts and output parameters are not fetched though.

          Show
          Daniel John Debrunner added a comment - Michelle> To end the transaction in autocommit mode, the objects must be closed. By "objects" I assume you mean ResultSets, the closing of the Statement is not required to auto-commit a transaction. The rules are in JDBC 4 section 10.1. However, closing the ResultSets is not sufficient for CallableStatements, see section 10.1 of JDBC 4. It indicates that the ResultSets must be closed and the update counts and output parameters must be fetched. It's unclear what happens if the update counts and output parameters are not fetched though.
          Hide
          Michelle Caisse added a comment -

          I'm looking for guidance in the JDK 6 javadoc about how to end a transaction consisting of an open ResultSet in autocommit mode. Neither ResultSet.close() nor Statement.close() doc persuades me that they are required. Connection.commit() is not an option. To end the transaction in autocommit mode, the objects must be closed. Per the JDBC spec, Connection.close() should close the objects. This should autocommit and it should not fail. +1 for 1a.

          Show
          Michelle Caisse added a comment - I'm looking for guidance in the JDK 6 javadoc about how to end a transaction consisting of an open ResultSet in autocommit mode. Neither ResultSet.close() nor Statement.close() doc persuades me that they are required. Connection.commit() is not an option. To end the transaction in autocommit mode, the objects must be closed. Per the JDBC spec, Connection.close() should close the objects. This should autocommit and it should not fail. +1 for 1a.
          Hide
          Dag H. Wanvik added a comment -

          think the leniency ("implementation defined") in Dan's quote is there
          to allow for implementations that do not heed the SQL standard; that
          roll back an uncommitted transaction when closing the connection.

          In any case, for non auto-commit, Derby throws an exception when
          closing a connection while a transaction is active (although with
          different SQL states in network end embedded drivers, cf DERBY-2065).

          For auto-commit, neither the network client, nor embedded throws an
          exception, but behave differently (commit, rollback, respectively),
          which is confusing. We have three options to harmonize behavior:

          1a) Continue to not throw an exception under auto-commit, but make
          embedded commit as the network client does.

          1b) Continue to not throw an exception under auto-commit, but make
          network client roll back changes also.

          2) Make both clients throw on close when open transaction also under
          autocommit (as per SQL Standard 17.3 GR 6).

          I think 1a) fits well with the auto-commit paradigm and is goes well
          with the JDBC4 model: close connection closes statements which in turn
          close result sets which in turn completes the SELECT statements,
          triggering auto-commit as per section 10.1 in JDBC4. Option 2) is also OK.
          I would recommend against 1b).

          What do you think?

          Show
          Dag H. Wanvik added a comment - think the leniency ("implementation defined") in Dan's quote is there to allow for implementations that do not heed the SQL standard; that roll back an uncommitted transaction when closing the connection. In any case, for non auto-commit, Derby throws an exception when closing a connection while a transaction is active (although with different SQL states in network end embedded drivers, cf DERBY-2065 ). For auto-commit, neither the network client, nor embedded throws an exception, but behave differently (commit, rollback, respectively), which is confusing. We have three options to harmonize behavior: 1a) Continue to not throw an exception under auto-commit, but make embedded commit as the network client does. 1b) Continue to not throw an exception under auto-commit, but make network client roll back changes also. 2) Make both clients throw on close when open transaction also under autocommit (as per SQL Standard 17.3 GR 6). I think 1a) fits well with the auto-commit paradigm and is goes well with the JDBC4 model: close connection closes statements which in turn close result sets which in turn completes the SELECT statements, triggering auto-commit as per section 10.1 in JDBC4. Option 2) is also OK. I would recommend against 1b). What do you think?
          Hide
          Knut Anders Hatlen added a comment -

          Connection.close() doesn't fail if auto-commit is true. I think it is reasonable to expect that the transaction is auto-committed on Connection.close() if the call doesn't fail. (Although it might be even more reasonable that the call fails.)

          Show
          Knut Anders Hatlen added a comment - Connection.close() doesn't fail if auto-commit is true. I think it is reasonable to expect that the transaction is auto-committed on Connection.close() if the call doesn't fail. (Although it might be even more reasonable that the call fails.)
          Hide
          Daniel John Debrunner added a comment -

          JDK 6 javadoc for Connection.close() also says that if a transaction is active at Connection.close() time then the results are implementation defined.

          So the current embedded implementation is valid, the close call fails because a transaction is active, thus its Statement objects are not closed.

          SQL Standard 17.3 GR 6) indicates that a disconnect should throw a invalid transaction state exception (25001 though) if the connection is active.

          Show
          Daniel John Debrunner added a comment - JDK 6 javadoc for Connection.close() also says that if a transaction is active at Connection.close() time then the results are implementation defined. So the current embedded implementation is valid, the close call fails because a transaction is active, thus its Statement objects are not closed. SQL Standard 17.3 GR 6) indicates that a disconnect should throw a invalid transaction state exception (25001 though) if the connection is active.
          Hide
          Dag H. Wanvik added a comment -

          The problem is not affected by whether this is a scrollable or
          forward-only updatable result set (repro uses scrollable).

          Show
          Dag H. Wanvik added a comment - The problem is not affected by whether this is a scrollable or forward-only updatable result set (repro uses scrollable).
          Hide
          Dag H. Wanvik added a comment -

          attaching repro

          Show
          Dag H. Wanvik added a comment - attaching repro

            People

            • Assignee:
              Unassigned
              Reporter:
              Dag H. Wanvik
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development