Derby
  1. Derby
  2. DERBY-5426

Improve the error raised by too much contention on a sequence/identity.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.8.2.2, 10.9.1.0
    • Fix Version/s: 10.8.2.2, 10.9.1.0
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Blocker
    • Issue & fix info:
      Newcomer

      Description

      Currently, when there is too much contention on a sequence/identity, Derby raises an error saying so. There are two properties which the user can adjust in order to reduce the risk of this error:

      derby.locks.waitTimeout
      derby.language.sequence.preallocator

      It would be good to point the user at these knobs. The following change would improve this error reporting:

      1) Raise a lock timeout SQLException

      2) Chain a "too much contention" SQLException (X0Y84) to the lock timeout.

      3) Make the "too much contention" exception suggest that the user adjust the properties mentioned above.

      To make the code easier to understand, the exception raising could replace the loop exit inside the following "if" block in SequenceUpdater.getCurrentValueAndAdvance():

      if (
      (_lockTimeoutInMillis >= 0L) &&
      ( (System.currentTimeMillis() - startTime.longValue()) > _lockTimeoutInMillis )
      )

      { break; }

      This approach was recommended by the discussion on DERBY-5423.

      1. DERBY4437Sequence.java
        2 kB
        Mamta A. Satoor
      2. derby-5426-01-aa-improveError.diff
        4 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Mamta A. Satoor added a comment -

          I will go ahead and create a new jira for a possible test case. Thanks

          Show
          Mamta A. Satoor added a comment - I will go ahead and create a new jira for a possible test case. Thanks
          Hide
          Rick Hillegas added a comment -

          Hi Myrna,

          I believe that both DERBY-5426 and DERBY-5423 are resolved. I resolved this issue but Mamta re-opened it. Perhaps Mamta's additional investigation could happen in another JIRA. Thanks.

          Show
          Rick Hillegas added a comment - Hi Myrna, I believe that both DERBY-5426 and DERBY-5423 are resolved. I resolved this issue but Mamta re-opened it. Perhaps Mamta's additional investigation could happen in another JIRA. Thanks.
          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
          Mamta A. Satoor added a comment -

          The attached program takes two parameters. First on determines how many threads will be spawned. Second one determines how many insert will be performed by each one of those threads. The threads issue a commit after every 1000 rows. I could add another parameter to the program which will determine how often commit should be performed(rather than every 1000 rows that I hard coded in the program right now). I have run this program with various paramter values and have not run into sequence contention problem yet. The latest run which has not finished yet is as follows
          java -Dderby.locks.waitTimeout=0 DERBY4437Sequence 10 1000000000

          Not sure if value '0' means for derby.locks.waitTimeout.But I have tried value 1 as well with fewer insert rows as shown below and that didn't result in problem.
          java -Dderby.locks.waitTimeout=1 DERBY4437Sequence 10 10000000

          The ouptut from the program is pretty verbose so when I run it, I direct the output to a text file. If there was an exception thrown, the output will have string 'error' in it but I haven't seen that yet in my few runs of the program.
          $ time java -Dderby.locks.waitTimeout=1 DERBY4437Sequence 10 10000000 > dellater.txt

          I will appreciate any ideas on how to make this program run into sequence contention exception. Thanks

          Show
          Mamta A. Satoor added a comment - The attached program takes two parameters. First on determines how many threads will be spawned. Second one determines how many insert will be performed by each one of those threads. The threads issue a commit after every 1000 rows. I could add another parameter to the program which will determine how often commit should be performed(rather than every 1000 rows that I hard coded in the program right now). I have run this program with various paramter values and have not run into sequence contention problem yet. The latest run which has not finished yet is as follows java -Dderby.locks.waitTimeout=0 DERBY4437Sequence 10 1000000000 Not sure if value '0' means for derby.locks.waitTimeout.But I have tried value 1 as well with fewer insert rows as shown below and that didn't result in problem. java -Dderby.locks.waitTimeout=1 DERBY4437Sequence 10 10000000 The ouptut from the program is pretty verbose so when I run it, I direct the output to a text file. If there was an exception thrown, the output will have string 'error' in it but I haven't seen that yet in my few runs of the program. $ time java -Dderby.locks.waitTimeout=1 DERBY4437Sequence 10 10000000 > dellater.txt I will appreciate any ideas on how to make this program run into sequence contention exception. Thanks
          Hide
          Mamta A. Satoor added a comment -

          I just wanted to see if we could have a standalone program to reproduce the sequence contention error. If that program works then may be we can add it to our junit suite.

          I am trying to work on a repo but what I wrote does not run into sequence contention. I was hoping I could get some tips on how I could tune the program to make it run into problem(I understand it may not reproduce everytime). I will attach the program soon with instruction on how to run it and what parameters I have tried. Thanks

          Show
          Mamta A. Satoor added a comment - I just wanted to see if we could have a standalone program to reproduce the sequence contention error. If that program works then may be we can add it to our junit suite. I am trying to work on a repo but what I wrote does not run into sequence contention. I was hoping I could get some tips on how I could tune the program to make it run into problem(I understand it may not reproduce everytime). I will attach the program soon with instruction on how to run it and what parameters I have tried. Thanks
          Hide
          Rick Hillegas added a comment -

          Ported 1174290 from trunk to 10.8 branch at subversion revision 1174297.

          Show
          Rick Hillegas added a comment - Ported 1174290 from trunk to 10.8 branch at subversion revision 1174297.
          Hide
          Rick Hillegas added a comment -

          Regression tests passed cleanly for me. Committed derby-5426-01-aa-improveError.diff at subversion revision 1174290.

          Show
          Rick Hillegas added a comment - Regression tests passed cleanly for me. Committed derby-5426-01-aa-improveError.diff at subversion revision 1174290.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-5426-01-aa-improveError.diff. This patch improves error reporting for high contention sequences/identities. I am running regression tests now.

          This patch makes the following changes:

          1) The "too much contention" message (X0Y84) now recommends that the user try adjusting the two properties.

          2) To clarify the code, error raising has been moved inside the retry loop in SequenceUpdater.getCurrentValueAndAdvance().

          3) The two SequenceUpdaters now raise different exceptions when they have looped too long trying to get the next value from the sequence generator. The sequence SequenceUpdater continues to raise a "too much contention" exception just as it used to. The identity SequenceUpdater now raises a "lock timeout" exception (40XL1) which wraps a "too much contention" exception.

          Touches the following files:

          M java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java
          M java/engine/org/apache/derby/loc/messages.xml

          Show
          Rick Hillegas added a comment - Attaching derby-5426-01-aa-improveError.diff. This patch improves error reporting for high contention sequences/identities. I am running regression tests now. This patch makes the following changes: 1) The "too much contention" message (X0Y84) now recommends that the user try adjusting the two properties. 2) To clarify the code, error raising has been moved inside the retry loop in SequenceUpdater.getCurrentValueAndAdvance(). 3) The two SequenceUpdaters now raise different exceptions when they have looped too long trying to get the next value from the sequence generator. The sequence SequenceUpdater continues to raise a "too much contention" exception just as it used to. The identity SequenceUpdater now raises a "lock timeout" exception (40XL1) which wraps a "too much contention" exception. Touches the following files: M java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java M java/engine/org/apache/derby/loc/messages.xml
          Hide
          Rick Hillegas added a comment -

          I can make this change and run regression tests. Stand by...

          Show
          Rick Hillegas added a comment - I can make this change and run regression tests. Stand by...
          Hide
          Myrna van Lunteren added a comment -

          I think this has the chance to break/prevent upgrade for applications that have programmed a check and re-try on time-out, so I think this is a blocker for 10.8.2. One shouldn't need to change anything in a minor release upgrade...

          Rick, do you plan/have time to work on this?

          Show
          Myrna van Lunteren added a comment - I think this has the chance to break/prevent upgrade for applications that have programmed a check and re-try on time-out, so I think this is a blocker for 10.8.2. One shouldn't need to change anything in a minor release upgrade... Rick, do you plan/have time to work on this?

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development