Derby
  1. Derby
  2. DERBY-3687

Make client driver allow nested savepoints

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Network Client
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Embedded/Client difference, Seen in production

      Description

      Currently, only the embedded driver allows nested savepoints, cf. the attached repro.
      In the interest of harmonizing the drivers it would be nice to make the client driver allow this as well.

      1. Main.java
        6 kB
        Dag H. Wanvik

        Issue Links

          Activity

          Hide
          Dag H. Wanvik added a comment -

          In the repro, the client driver gives an error:

          3B002: java.sql.SQLException: The maximum number of savepoints has been reached.

          Show
          Dag H. Wanvik added a comment - In the repro, the client driver gives an error: 3B002: java.sql.SQLException: The maximum number of savepoints has been reached.
          Hide
          Dag H. Wanvik added a comment -

          Found this comment in Xact.java:

          // SQL savepoint can't be nested inside other user defined savepoints. To
          // enforce this, we check if there are already user savepoint(SQL/JDBC)
          // defined in the transaction. If yes, then throw an exception

          So, does the client use the SQL version of savepoints (not documented), and that's
          why they can't be nested? And if so, why?

          Show
          Dag H. Wanvik added a comment - Found this comment in Xact.java: // SQL savepoint can't be nested inside other user defined savepoints. To // enforce this, we check if there are already user savepoint(SQL/JDBC) // defined in the transaction. If yes, then throw an exception So, does the client use the SQL version of savepoints (not documented), and that's why they can't be nested? And if so, why?
          Hide
          Kathey Marsden added a comment -

          It does appear that client uses SQL savepoints from
          am.Connection.java:setSavepointX ...

          String sql = "SAVEPOINT \"" + savepointName + "\" ON ROLLBACK RETAIN CURSORS";
          stmt.executeX(sql);
          }

          Show
          Kathey Marsden added a comment - It does appear that client uses SQL savepoints from am.Connection.java:setSavepointX ... String sql = "SAVEPOINT \"" + savepointName + "\" ON ROLLBACK RETAIN CURSORS"; stmt.executeX(sql); }
          Hide
          Dag H. Wanvik added a comment -

          I checked the standard, and SQL savepoints do allow nesting, cf. section 4.35.2 in ISO/IEC 9075-2:2003:

          "An SQL-transaction has one or more savepoint levels, exactly one of which is the current savepoint level. The
          savepoint levels of an SQL-transaction are nested, such that when a new savepoint level NSL is established,
          the current savepoint level CSL ceases to be current and NSL becomes current. When NSL is destroyed, CSL
          becomes current again."

          So the standard is not prohibiting this functionality in SQL. Then what is the reason?

          Show
          Dag H. Wanvik added a comment - I checked the standard, and SQL savepoints do allow nesting, cf. section 4.35.2 in ISO/IEC 9075-2:2003: "An SQL-transaction has one or more savepoint levels, exactly one of which is the current savepoint level. The savepoint levels of an SQL-transaction are nested, such that when a new savepoint level NSL is established, the current savepoint level CSL ceases to be current and NSL becomes current. When NSL is destroyed, CSL becomes current again." So the standard is not prohibiting this functionality in SQL. Then what is the reason?
          Hide
          Dag H. Wanvik added a comment -

          uploading repro.

          Show
          Dag H. Wanvik added a comment - uploading repro.
          Hide
          Dag H. Wanvik added a comment -

          I tried to comment out the check for this in Xact.java, and voila, the repro worked!
          (I commented out the call to throwExceptionIfSQLSavepointNotAllowed(kindOfSavepoint)).

          I don't know about any side effect of doing this, though, but I add this observation just as
          a data point for now.

          Show
          Dag H. Wanvik added a comment - I tried to comment out the check for this in Xact.java, and voila, the repro worked! (I commented out the call to throwExceptionIfSQLSavepointNotAllowed(kindOfSavepoint)). I don't know about any side effect of doing this, though, but I add this observation just as a data point for now.
          Hide
          Dag H. Wanvik added a comment -

          I see there are tests in SavepointJDBC30Test.java, cases #17, #18 and #19 and #48, which
          test various (illegal) nesting scenarios:

          • testNoNestedSavepointsWhenUsingSQL (SQL inside SQL)
          • testNoNestedSavepointsInsideJdbcSavepoint (JDBC inside SQL)
          • testNoNestedSavepointsInsideSqlSavepoint (SQL inside SQL)
          • xtestNestedSavepoints (SQL inside JDBC; not enabled, cf. x in fixture name ?)

          I can understand why it is wise to avoid mixing the two savepoint kinds, but can someone enlighten me as to the rationale for not allowing nesting of SQL savepoints? (keeping in mind that the standard doesn't dictate this; is this some kind of compatibility decision?)

          Show
          Dag H. Wanvik added a comment - I see there are tests in SavepointJDBC30Test.java, cases #17, #18 and #19 and #48, which test various (illegal) nesting scenarios: testNoNestedSavepointsWhenUsingSQL (SQL inside SQL) testNoNestedSavepointsInsideJdbcSavepoint (JDBC inside SQL) testNoNestedSavepointsInsideSqlSavepoint (SQL inside SQL) xtestNestedSavepoints (SQL inside JDBC; not enabled, cf. x in fixture name ?) I can understand why it is wise to avoid mixing the two savepoint kinds, but can someone enlighten me as to the rationale for not allowing nesting of SQL savepoints? (keeping in mind that the standard doesn't dictate this; is this some kind of compatibility decision?)
          Hide
          Rami Ojares added a comment -

          What is this talk about "nested" savepoints?
          If I have a transaction and I set a savepoint, then execute some statements and set another savepoint.
          Would you call the second savepoint nested?
          And if so, why?
          To me the transaction seems to have a list of statements and at some points in that list there are svapoints.
          They don't seem nested to me.

          Further according to my experience derby seems to allow only one savepoint.
          I don't know why.
          Clarification would be greatly appreciated.

          Show
          Rami Ojares added a comment - What is this talk about "nested" savepoints? If I have a transaction and I set a savepoint, then execute some statements and set another savepoint. Would you call the second savepoint nested? And if so, why? To me the transaction seems to have a list of statements and at some points in that list there are svapoints. They don't seem nested to me. Further according to my experience derby seems to allow only one savepoint. I don't know why. Clarification would be greatly appreciated.
          Hide
          Dag H. Wanvik added a comment -

          For explanation of nested savepoints, cf. the quote from the SQL standard reproduced above (section 4.35.2 in ISO/IEC 9075-2:2003).
          https://issues.apache.org/jira/browse/DERBY-3687?focusedCommentId=12599237&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_12599237

          Show
          Dag H. Wanvik added a comment - For explanation of nested savepoints, cf. the quote from the SQL standard reproduced above (section 4.35.2 in ISO/IEC 9075-2:2003). https://issues.apache.org/jira/browse/DERBY-3687?focusedCommentId=12599237&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_12599237
          Hide
          Kathey Marsden added a comment -

          Triage for 10.9. Mark repro attached

          Show
          Kathey Marsden added a comment - Triage for 10.9. Mark repro attached
          Hide
          Sean van Buggenum added a comment - - edited

          To clarify, for others coming across this problem and reading this bug report: t is a DB-wide restriction (of 1 Savepoint).
          Not one save-point per connection; just, one save point (per running database).
          i.e., even if you attempt to have a 2nd Savepoint on a different connection than the 1st Savepoint was created,
          you hit this exception: java.sql.SQLException: The maximum number of savepoints has been reached.
          Looking forward to the fix! (10.9 ? What happened to that? )

          Show
          Sean van Buggenum added a comment - - edited To clarify, for others coming across this problem and reading this bug report: t is a DB-wide restriction (of 1 Savepoint). Not one save-point per connection; just, one save point (per running database). i.e., even if you attempt to have a 2nd Savepoint on a different connection than the 1st Savepoint was created, you hit this exception: java.sql.SQLException: The maximum number of savepoints has been reached. Looking forward to the fix! (10.9 ? What happened to that? )
          Hide
          Dag H. Wanvik added a comment -

          The way I read the code, the restriction is per connection, i.e. you cannot nest (user level) save points within the same connection. Do you have a repro for this behavior, Sean? If so, I think it must be some bug.

          Show
          Dag H. Wanvik added a comment - The way I read the code, the restriction is per connection, i.e. you cannot nest (user level) save points within the same connection. Do you have a repro for this behavior, Sean? If so, I think it must be some bug.
          Hide
          Sean van Buggenum added a comment -

          Ok, very sorry for the delayed reply.

          Just tried to reproduce in a simple test case. But couldn't ( how embarrassing!)

          I was quite sure I saw this, but perhaps something else was going on, due to the complexity of the situation.
          I will try to investigate this more fully, and If I find more evidence to support the above claim (or something near it) i'll post.
          In the meanwhile, I guess you can consider the above a mistake!

          Show
          Sean van Buggenum added a comment - Ok, very sorry for the delayed reply. Just tried to reproduce in a simple test case. But couldn't ( how embarrassing!) I was quite sure I saw this, but perhaps something else was going on, due to the complexity of the situation. I will try to investigate this more fully, and If I find more evidence to support the above claim (or something near it) i'll post. In the meanwhile, I guess you can consider the above a mistake!

            People

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

              Dates

              • Created:
                Updated:

                Development