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] [Updated] (DERBY-4437) Concurrent inserts into table with identity column perform poorly
Date Wed, 08 Jun 2011 18:51:03 GMT

     [ 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-01-aj-allTestsPass.diff

Attaching derby-4437-01-aj-allTestsPass.diff. This patch replaces the old identity column
management with a scheme based on the sequence generators which were introduced by DERBY-712.
Regression tests passed for me. More tests need to be written, edge cases need to be stressed,
and some dead code may need to be pruned out.

This patch does the following:

1) Introduces a new subclass of SequenceUpdater to manage identity values in SYSCOLUMNS rows.

2) Removes the old identity management and replaces it with calls to the new SequenceUpdater.

3) No persistent forms were changed so this patch should not affect the user's ability to
upgrade and soft-downgrade.

The SYSCOLUMNS SequenceUpdater behaves just like the SYSSEQUENCES one: It pre-allocates ranges
of identity values. The number of pre-allocated values is hard-coded to the same number of
pre-allocated values used for sequences (5).

Touches the following files:

-----------

M      java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java

Introduces a SequenceUpdater to manage SYSCOLUMNS. There was already a SequenceUpdater to
manage SYSSEQUENCES.

-----------

M      java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java
M      java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java

Logic which computes max/min bounds for integer types was moved into a subroutine for re-use.

-----------

M      java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
M      java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
M      java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java
M      java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java

An extra argument was added to DataDictionary.getCurrentValueAndAdvance() so that the method
can be used for both sequences and identity columns. Some obsolete methods were removed.

-----------

M      java/engine/org/apache/derby/iapi/reference/Property.java
M      java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java

Added cache management for identity SequenceUpdaters.

-----------

M      java/engine/org/apache/derby/impl/sql/execute/InsertConstantAction.java

Some unused methods were removed. The array of RowLocations was left untouched and is still
constructed by InsertNode. Leaving this array intact avoids the need to change the serialized
form of this ConstantAction. That eliminates soft-upgrade/soft-downgrade problems.

-----------

M      java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java

Replaced the old identity management with calls to the SequenceUpdaters cached in the DataDictionary.

-----------

M      java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java
M      java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java

The existing test needed some tweaking:

1) The preallocation of identity ranges changes the results of queries against SYSCOLUMNS.

2) Fewer locks are held now, changing the results of queries against the lock vti.

3) The biggest value in a BIGINT identity column used to be (Long.MAX_VALUE - 1). Now it is
Long.MAX_VALUE, as it should be. I don't understand why a wrong result was canonized in AutoIncrementTest.


> 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
>         Attachments: derby-4437-01-aj-allTestsPass.diff
>
>
> 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