Derby
  1. Derby
  2. DERBY-5423

ERROR X0Y84: Too much contention on sequence NSTESTTAB in ns system test

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 10.8.2.2
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Windows XP, with ibm 1.6 SR9 FP1
    • Bug behavior facts:
      Regression

      Description

      The nstest system test showed the following error in the test run with 10.8.2.1 (not seen with earlier versions, incl. 10.8.1.2):

      -----------------------------
      ==========> Tester2Thread 45 THREAD starting <======
      Tester2Thread 45 is getting a connection to the database...
      ->Thread Tester2Thread 45 starting with url jdbc:derby:nstestdb;create=true;bootPassword=12345678 <-
      Connection number: 52
      java.sql.SQLException: Too much contention on sequence NSTESTTAB.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
      at org.apache.derbyTesting.system.nstest.utils.DbUtil.add_one_row(DbUtil.java:201)
      at org.apache.derbyTesting.system.nstest.tester.TesterObject.doIUDOperation(TesterObject.java:148)
      at org.apache.derbyTesting.system.nstest.tester.Tester2.startTesting(Tester2.java:109)
      at org.apache.derbyTesting.system.nstest.NsTest.run(NsTest.java:555)
      Caused by: java.sql.SQLException: Too much contention on sequence NSTESTTAB.
      at java.lang.Throwable.<init>(Throwable.java:67)
      at java.sql.SQLException.<init>(SQLException.java:101)
      at org.apache.derby.impl.jdbc.EmbedSQLException.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
      ... 13 more
      Caused by: ERROR X0Y84: Too much contention on sequence NSTESTTAB.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.impl.sql.catalog.SequenceUpdater.getCurrentValueAndAdvance(Unknown Source)
      at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getCurrentValueAndAdvance(Unknown Source)
      at org.apache.derby.impl.sql.execute.InsertResultSet.getSetAutoincrementValue(Unknown Source)
      at org.apache.derby.impl.sql.execute.BaseActivation.getSetAutoincrementValue(Unknown Source)
      at org.apache.derby.exe.ac7a858e18x0132x6516x81e1x0000003123480.e0(Unknown Source)
      at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown Source)
      at org.apache.derby.impl.sql.execute.RowResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
      ... 7 more
      ---------------------------------
      After that, it happens again:
      --------------------------------
      Exception when preparing or executing insert prepared stmt
      java.sql.SQLException: Too much contention on sequence NSTESTTAB.
      Tester2Thread 25 dbUtil ----> During executing/preparing insert stmt in dbUtil, exception thrown was : java.sql.SQLException: Too much contention on sequence NSTESTTAB.
      java.sql.SQLException: Too much contention on sequence NSTESTTAB.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
      at org.apache.derbyTesting.system.nstest.utils.DbUtil.add_one_row(DbUtil.java:201)
      at org.apache.derbyTesting.system.nstest.tester.TesterObject.doIUDOperation(TesterObject.java:148)
      at org.apache.derbyTesting.system.nstest.tester.Tester2.startTesting(Tester2.java:109)
      at org.apache.derbyTesting.system.nstest.NsTest.run(NsTest.java:555)
      Caused by: java.sql.SQLException: Too much contention on sequence NSTESTTAB.
      at java.lang.Throwable.<init>(Throwable.java:67)
      at java.sql.SQLException.<init>(SQLException.java:101)
      at org.apache.derby.impl.jdbc.EmbedSQLException.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
      ... 13 more
      Caused by: ERROR X0Y84: Too much contention on sequence NSTESTTAB.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.impl.sql.catalog.SequenceUpdater.getCurrentValueAndAdvance(Unknown Source)
      at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getCurrentValueAndAdvance(Unknown Source)
      at org.apache.derby.impl.sql.execute.InsertResultSet.getSetAutoincrementValue(Unknown Source)
      at org.apache.derby.impl.sql.execute.BaseActivation.getSetAutoincrementValue(Unknown Source)
      at org.apache.derby.exe.ac7a858e18x0132x6516x81e1x0000003123480.e0(Unknown Source)
      at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown Source)
      at org.apache.derby.impl.sql.execute.RowResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(Unknown Source)
      at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
      ... 7 more
      --------------------------
      And more often.

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Hide
          Myrna van Lunteren added a comment -

          After the changes for DERBY-5426 I no longer see this error with nstest.

          Show
          Myrna van Lunteren added a comment - After the changes for DERBY-5426 I no longer see this error with nstest.
          Hide
          Myrna van Lunteren added a comment -

          With a build including the changes for DERBY-5426, I do not see ERROR X0Y84 anymore in the nstest.
          How to go on, do I close DERBY-5423? Or DERBY-5426? Or both?

          Show
          Myrna van Lunteren added a comment - With a build including the changes for DERBY-5426 , I do not see ERROR X0Y84 anymore in the nstest. How to go on, do I close DERBY-5423 ? Or DERBY-5426 ? Or both?
          Hide
          Rick Hillegas added a comment -

          I have logged DERBY-5426 to track the improved error reporting recommended by Kathey and Mike.

          Show
          Rick Hillegas added a comment - I have logged DERBY-5426 to track the improved error reporting recommended by Kathey and Mike.
          Hide
          Mike Matrigali added a comment -

          It seems like the code should throw a lock timeout error rather than X0Y84. Since the error is based on the value of lock timeout seems reasonable
          to throw the lock timeout error. Then it is natural for a user to tune his lock timeout to avoid the error.
          And if possible it could chain another error on indicating that the lock timeout is because of concurrent use of the sequence system, and possibly
          direct the tuning of the chunk property .

          The benefits of this would be:
          o it is reasonable to expect applications to code for lock timeout vs. the current error.
          o sounds like it is compatible as the previous system also could throw lock timeout errors.
          o Since the error is based on lock timeout value, throwing the error leads user to what property to tune to avoid the error.

          Show
          Mike Matrigali added a comment - It seems like the code should throw a lock timeout error rather than X0Y84. Since the error is based on the value of lock timeout seems reasonable to throw the lock timeout error. Then it is natural for a user to tune his lock timeout to avoid the error. And if possible it could chain another error on indicating that the lock timeout is because of concurrent use of the sequence system, and possibly direct the tuning of the chunk property . The benefits of this would be: o it is reasonable to expect applications to code for lock timeout vs. the current error. o sounds like it is compatible as the previous system also could throw lock timeout errors. o Since the error is based on lock timeout value, throwing the error leads user to what property to tune to avoid the error.
          Hide
          Kathey Marsden added a comment -

          I think the new implementation should behave the same way then, throw a lock timeout and ideally link the X0Y84 as the next SQLException, to help show root cause. That way existing applications should not be surprised by the lock timeouts and setup to handle them.

          .

          Show
          Kathey Marsden added a comment - I think the new implementation should behave the same way then, throw a lock timeout and ideally link the X0Y84 as the next SQLException, to help show root cause. That way existing applications should not be surprised by the lock timeouts and setup to handle them. .
          Hide
          Rick Hillegas added a comment -

          I mean spin until we obtain a sequence number but there is no guarantee when that will happen. In the previous implementation (before DERBY-4437) an INSERT could fail on a lock timeout while trying to get the next identity value.

          Show
          Rick Hillegas added a comment - I mean spin until we obtain a sequence number but there is no guarantee when that will happen. In the previous implementation (before DERBY-4437 ) an INSERT could fail on a lock timeout while trying to get the next identity value.
          Hide
          Kathey Marsden added a comment -

          By spin forever, do you mean spin until we get a sequence number? Will that always happen eventually or will it really potentially spin forever? What was the prior behavior if the wait for a sequence number exceeded the lock timeout, did we continue to wait or did we throw a lock timeout?

          Show
          Kathey Marsden added a comment - By spin forever, do you mean spin until we get a sequence number? Will that always happen eventually or will it really potentially spin forever? What was the prior behavior if the wait for a sequence number exceeded the lock timeout, did we continue to wait or did we throw a lock timeout?
          Hide
          Kathey Marsden added a comment -

          On derby-dev list, the " [VOTE] 10.8.2.1 release" thread, there has been some discussion on this issue. I am having trouble linking to the full thread, but here is the latest:
          http://mail-archives.apache.org/mod_mbox/db-derby-dev/201109.mbox/%3C4E79ED54.2080804@oracle.com%3E

          Mike asked:
          > Rick can you explain why we get the error rather than some sort of wait
          > or retry.
          The code is in SequenceUpdater.getCurrentValueAndAdvance(). We spin
          trying to get a new sequence number. If we spin longer than the lock
          timeout set by derby.locks.waitTimeout, then we raise this error.

          Another workaround would be to set derby.locks.waitTimeout to a negative
          number so that there is no lock timeout.
          >
          > Can this error occur with the other things in the system that use
          > preallocated chunks, or is the specific to sequences.
          >
          > It seems like a bug to get an error in this case, and it even if we
          > auto-tune better or set higher defaults it still seems like one could
          > get an unexpected error.
          We could spin forever. We could add another knob which controls the spin
          timeout. Other solutions?

          It might be good to move the conversation to the Jira issue for proper tracking.

          Show
          Kathey Marsden added a comment - On derby-dev list, the " [VOTE] 10.8.2.1 release" thread, there has been some discussion on this issue. I am having trouble linking to the full thread, but here is the latest: http://mail-archives.apache.org/mod_mbox/db-derby-dev/201109.mbox/%3C4E79ED54.2080804@oracle.com%3E Mike asked: > Rick can you explain why we get the error rather than some sort of wait > or retry. The code is in SequenceUpdater.getCurrentValueAndAdvance(). We spin trying to get a new sequence number. If we spin longer than the lock timeout set by derby.locks.waitTimeout, then we raise this error. Another workaround would be to set derby.locks.waitTimeout to a negative number so that there is no lock timeout. > > Can this error occur with the other things in the system that use > preallocated chunks, or is the specific to sequences. > > It seems like a bug to get an error in this case, and it even if we > auto-tune better or set higher defaults it still seems like one could > get an unexpected error. We could spin forever. We could add another knob which controls the spin timeout. Other solutions? It might be good to move the conversation to the Jira issue for proper tracking.
          Hide
          Rick Hillegas added a comment -

          Linking this issue to DERBY-4437. As a result of the work on that issue, identity columns are now managed by the same sequence generator machinery as sequences are. That means that this error can be seen if an identity column is being incremented by too many sessions. It would be interesting to know if we can tune ourselves out of this contention by increasing the number of values which are preallocated per sequence/identity. This can be done by setting derby.language.sequence.preallocator=N, where N is some largeish number. N is the number of values which are preallocated every time the sequence/identity exhausts its current cache of values. N defaults to be 20. We could try running the test with this property set to 200 and see if that helps.

          Show
          Rick Hillegas added a comment - Linking this issue to DERBY-4437 . As a result of the work on that issue, identity columns are now managed by the same sequence generator machinery as sequences are. That means that this error can be seen if an identity column is being incremented by too many sessions. It would be interesting to know if we can tune ourselves out of this contention by increasing the number of values which are preallocated per sequence/identity. This can be done by setting derby.language.sequence.preallocator=N, where N is some largeish number. N is the number of values which are preallocated every time the sequence/identity exhausts its current cache of values. N defaults to be 20. We could try running the test with this property set to 200 and see if that helps.

            People

            • Assignee:
              Unassigned
              Reporter:
              Myrna van Lunteren
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development