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

          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open Resolved Resolved
          23h 20m 1 Rick Hillegas 22/Sep/11 19:11
          Closed Closed Reopened Reopened
          1d 1h 37m 2 Mamta A. Satoor 23/Sep/11 20:50
          Reopened Reopened Resolved Resolved
          6d 7m 2 Mamta A. Satoor 29/Sep/11 20:56
          Resolved Resolved Closed Closed
          623d 21h 25m 3 Rick Hillegas 14/Jun/13 18:21
          Gavin made changes -
          Workflow jira [ 12633923 ] Default workflow, editable Closed status [ 12802660 ]
          Rick Hillegas made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Myrna van Lunteren made changes -
          Affects Version/s 10.8.2.2 [ 12317968 ]
          Affects Version/s 10.8.2.1 [ 12317957 ]
          Mamta A. Satoor made changes -
          Status Reopened [ 4 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          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?
          Mamta A. Satoor made changes -
          Attachment DERBY4437Sequence.java [ 12496312 ]
          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
          Mamta A. Satoor made changes -
          Resolution Fixed [ 1 ]
          Status Closed [ 6 ] Reopened [ 4 ]
          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
          Rick Hillegas made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Rick Hillegas made changes -
          Status Reopened [ 4 ] Resolved [ 5 ]
          Fix Version/s 10.8.2.2 [ 12317968 ]
          Fix Version/s 10.9.0.0 [ 12316344 ]
          Resolution Fixed [ 1 ]
          Rick Hillegas made changes -
          Resolution Fixed [ 1 ]
          Status Closed [ 6 ] Reopened [ 4 ]
          Rick Hillegas made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Rick Hillegas made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Issue & fix info [Patch Available, Newcomer] [Newcomer]
          Resolution Fixed [ 1 ]
          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.
          Rick Hillegas made changes -
          Issue & fix info [Newcomer] [Newcomer, Patch Available]
          Rick Hillegas made changes -
          Attachment derby-5426-01-aa-improveError.diff [ 12496129 ]
          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
          Rick Hillegas made changes -
          Assignee Rick Hillegas [ rhillegas ]
          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...
          Myrna van Lunteren made changes -
          Urgency Blocker
          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?
          Rick Hillegas made changes -
          Link This issue relates to DERBY-712 [ DERBY-712 ]
          Rick Hillegas made changes -
          Link This issue relates to DERBY-4437 [ DERBY-4437 ]
          Rick Hillegas made changes -
          Field Original Value New Value
          Link This issue relates to DERBY-5423 [ DERBY-5423 ]
          Rick Hillegas created issue -

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development