[
https://issues.apache.org/jira/browse/DERBY-4437?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13057963#comment-13057963
]
Rick Hillegas commented on DERBY-4437:
--------------------------------------
Backported the following patches from trunk to the 10.8 branch. Tests passed
cleanly for me. Committed to 10.8 branch at subversion revision 1141645:
1135226 derby-4437-01-aj-allTestsPass.diff
1135754 derby-4437-02-ac-alterTable-bulkImport-deferredInsert.diff
1137985 derby-4437-04-aa-reclaimUnusedValuesOnShutdown.diff
1138434 derby-4437-05-aa-pluggablePreallocation.diff
1141567 derby-4437-07-ad-biggerDefault_propertyCanBeInteger.diff
The following patches were NOT backported:
1136036 derby-4437-03-aa-upgradeTest.diff (10.9-specific upgrade test)
derby-4437-06-aa-selfTuning (Uncommitted, experimental patch)
In a follow-on patch, I would like to write a 10.8.2-specific upgrade test to
verify the behavior of soft-(up/down)grade between 10.8.1 and 10.8.2.
This patch touches the following files:
M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
M java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java
M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
M java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
M java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java
M java/engine/org/apache/derby/impl/sql/execute/InsertConstantAction.java
M java/engine/org/apache/derby/impl/sql/catalog/SequenceGenerator.java
M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
A + java/engine/org/apache/derby/impl/sql/catalog/SequenceRange.java
M java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java
M java/engine/org/apache/derby/impl/db/BasicDatabase.java
M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
M java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java
M java/engine/org/apache/derby/iapi/reference/Property.java
A + java/engine/org/apache/derby/catalog/SequencePreallocator.java
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java
A +
java/testing/org/apache/derbyTesting/functionTests/tests/lang/t_4437_2.dat
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceGeneratorTest.java
M tools/javadoc/publishedapi.ant
> 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,
> derby-4437-07-ac-biggerDefault_propertyCanBeInteger.diff,
> derby-4437-07-ad-biggerDefault_propertyCanBeInteger.diff, insertperf.png,
> insertperf2.png, prealloc.png, releaseNote.html
>
>
> 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
