db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-4437) Concurrent inserts into table with identity column perform poorly
Date Fri, 24 Jun 2011 19:21:47 GMT

    [ 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

        

Mime
View raw message