[
https://issues.apache.org/jira/browse/DERBY-4437?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13054637#comment-13054637
]
Rick Hillegas commented on DERBY-4437:
--------------------------------------
Thanks for the quick response, Mike. A couple comments:
o I have changed the title of DERBY-5151 to indicate that it covers the issue
of leaking identity values on abnormal exit.
o Concerning the knob: A previous checkin introduced the following new Derby
property. Currently, it can be set to the name of a class which provides custom
preallocation logic. The custom preallocator can give you different range sizes
per sequence/identity. We could also let the property be set to a number. If
set to a number, then that would be the size of the preallocation range and it
would apply to all sequences and identity columns:
derby.language.sequence.preallocator
o I agree that we should not introduce an additional property per
sequence/identity.
o Additional, non-standard SQL language would be acceptable to me. Other
databases handle this issue with very simliar language--the differences seem
very slight to me. See DERBY-5151. With a little patience, I think we could
agree on some almost standard language. A nice feature of the language-based
approach is that dblook would reconstruct the knob settings.
o A database procedure would work too. However, the knob settings would be lost
when you exported/imported the database. This defect also affects the currently
implemented workaround.
o I don't want to put any effort into the procedure or the SQL language
approaches at this time. But someone else is welcome to pick this up.
> Concurrent inserts into table with identity column perform poorly
> -----------------------------------------------------------------
>
> Key: DERBY-4437
> URL: https://issues.apache.org/jira/browse/DERBY-4437
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.5.3.0
> Reporter: Knut Anders Hatlen
> Assignee: Rick Hillegas
> Attachments: D4437PerfTest.java, D4437PerfTest2.java,
> Experiments_4437.html, derby-4437-01-aj-allTestsPass.diff,
> derby-4437-02-ac-alterTable-bulkImport-deferredInsert.diff,
> derby-4437-03-aa-upgradeTest.diff,
> derby-4437-04-aa-reclaimUnusedValuesOnShutdown.diff,
> derby-4437-05-aa-pluggablePreallocation.diff,
> derby-4437-06-aa-selfTuning.diff, insertperf.png, insertperf2.png,
> prealloc.png
>
>
> 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.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
