[
https://issues.apache.org/jira/browse/DERBY-4437?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-4437:
---------------------------------
Attachment: derby-4437-04-aa-reclaimUnusedValuesOnShutdown.diff
Attaching derby-4437-04-aa-reclaimUnusedValuesOnShutdown.diff. This patch makes
orderly shutdown reclaim unused, pre-allocated ranges of sequence/identity
numbers. Tests passed cleanly for me.
My first attempt to code this involved putting the reclamation call in
DataDictionaryImpl.stop(). That turned out to be too late during orderly
shutdown--by that time the LCC could not be found. This is my second attempt.
The reclamation call is now in BasicDatabase.stop().
I also noticed that the DataDictionary was already reclaiming unused ranges
when DDL invalidated the caches. So we should not be leaking sequence numbers
when we perform DDL.
Nothing is done about unorderly shutdown. If the application fails to shutdown
the database before exiting, then the engine will still leak unused
sequence/identity numbers.
Touches the following files:
--------------
M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
Adds a new DataDictionary method: clearSequenceCaches(). This method makes the
cached sequence/identity generators reclaim unused, pre-allocated values.
--------------
M java/engine/org/apache/derby/impl/db/BasicDatabase.java
During orderly shutdown, the Database module now calls the new DataDictionary
method.
--------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceGeneratorTest.java
Updates a test case to check that values don't leak during orderly shutdown.
> 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,
> 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, 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
