Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6542

Improve the concurrency of identity columns by using SYS.SYSSEQUENCES

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.11.1.1
    • Fix Version/s: 10.11.1.1
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      Release Note Needed
    • Bug behavior facts:
      Performance

      Description

      This is another attempt to improve the concurrency of identity columns. The previous attempt was tracked by DERBY-4437.

      This new attempt will try out Mike's last suggestion: use system-created sequences managed by SYS.SYSSEQUENCES. This should reduce the contention on the core catalogs.

      I'm hopeful about this approach because of the experiments tracked by DERBY-6533. There we are not seeing any problems related to sequence generators but we are seeing lots of identity-related lock timeouts.

      Here is the general shape of this approach:

      1) When adding an identity column to a table, Derby will create a sequence generator for the column. The sequence generator will live in the SYS schema and its name will be the table's UUID.

      2) DROP SEQUENCE will not operate on system-created sequences. System-created sequences will be dropped/modified by DROP/ALTER TABLE commands.

      3) We will add a new system function for inspecting the current, in-memory value of an identity generator without getting a lock on SYS.SYSSEQUENCES: SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY( tableSchemaName, tableName ).

      4) Derby will create a sequence for every legacy identity column after hard-upgrade.

      5) These changes will take place only after hard-upgrade. Soft-upgrade will not change the behavior of identity columns.

      Comments on this proposal are welcome. Thanks.

        Attachments

        1. releaseNote.html
          3 kB
          Richard N. Hillegas
        2. releaseNote.html
          4 kB
          Richard N. Hillegas
        3. derby-6542-03-aa-lockTimeoutForIdentityContention.diff
          3 kB
          Richard N. Hillegas
        4. derby-6542-02-af-useNewSequenceGenerator.diff
          73 kB
          Richard N. Hillegas
        5. derby-6542-02-ac-useNewSequenceGenerator.diff
          42 kB
          Richard N. Hillegas
        6. derby-6542-02-ab-useNewSequenceGenerator.diff
          42 kB
          Richard N. Hillegas
        7. derby-6542-01-ab-catalog.diff
          44 kB
          Richard N. Hillegas

        Issue Links

          Activity

            People

            • Assignee:
              rhillegas Richard N. Hillegas
              Reporter:
              rhillegas Richard N. Hillegas

              Dates

              • Created:
                Updated:
                Resolved:

                Issue deployment