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

Concurrent inserts into table with identity column perform poorly

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Duplicate
    • 10.5.3.0
    • 10.9.1.0
    • SQL
    • None
    • Release Note Needed
    • Performance

    Description

      I have a multi-threaded application which is very insert-intensive. I've noticed that it sometimes can come into a state where it slows down considerably and basically becomes single-threaded. This is especially harmful on modern multi-core machines since most of the available resources are left idle.

      The problematic tables contain identity columns, and here's my understanding of what happens:

      1) Identity columns are generated from a counter that's stored in a row in SYS.SYSCOLUMNS. During normal operation, the counter is maintained in a nested transaction within the transaction that performs the insert. This allows the nested transaction to commit the changes to SYS.SYSCOLUMN separately from the main transaction, and the exclusive lock that it needs to obtain on the row holding the counter, can be releases after a relatively short time. Concurrent transactions can therefore insert into the same table at the same time, without needing to wait for the others to commit or abort.

      2) However, if the nested transaction cannot lock the row in SYS.SYSCOLUMNS immediately, it will give up and retry the operation in the main transaction. This prevents self-deadlocks in the case where the main transaction already owns a lock on SYS.SYSCOLUMNS. Unfortunately, this also increases the time the row is locked, since the exclusive lock cannot be released until the main transaction commits. So as soon as there is one lock collision, the waiting transaction changes to a locking mode that increases the chances of others having to wait, which seems to result in all insert threads having to obtain the SYSCOLUMNS locks in the main transaction. The end result is that only one of the insert threads can execute at any given time as long as the application is in this state.

      Attachments

        1. derby-4437-08-aa-10.8upgrade.diff
          11 kB
          Richard N. Hillegas
        2. derby-4437-07-ad-biggerDefault_propertyCanBeInteger.diff
          13 kB
          Richard N. Hillegas
        3. releaseNote.html
          4 kB
          Richard N. Hillegas
        4. derby-4437-07-ac-biggerDefault_propertyCanBeInteger.diff
          13 kB
          Richard N. Hillegas
        5. Experiments_4437.html
          4 kB
          Richard N. Hillegas
        6. derby-4437-06-aa-selfTuning.diff
          10 kB
          Richard N. Hillegas
        7. derby-4437-05-aa-pluggablePreallocation.diff
          24 kB
          Richard N. Hillegas
        8. derby-4437-04-aa-reclaimUnusedValuesOnShutdown.diff
          4 kB
          Richard N. Hillegas
        9. prealloc.png
          9 kB
          Knut Anders Hatlen
        10. insertperf2.png
          7 kB
          Knut Anders Hatlen
        11. D4437PerfTest2.java
          3 kB
          Knut Anders Hatlen
        12. D4437PerfTest.java
          3 kB
          Knut Anders Hatlen
        13. insertperf.png
          6 kB
          Knut Anders Hatlen
        14. derby-4437-03-aa-upgradeTest.diff
          7 kB
          Richard N. Hillegas
        15. derby-4437-02-ac-alterTable-bulkImport-deferredInsert.diff
          11 kB
          Richard N. Hillegas
        16. derby-4437-01-aj-allTestsPass.diff
          43 kB
          Richard N. Hillegas

        Issue Links

          Activity

            People

              Unassigned Unassigned
              knutanders Knut Anders Hatlen
              Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: