[DERBY-4437] Concurrent inserts into table with identity column perform poorly

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 10.5.3.0
• Fix Version/s: 10.9.1.0
• Component/s: SQL
• Issue & fix info: Release Note Needed
• Bug behavior facts: Performance
• Assignee: Unassigned
• Reporter: Knut Anders Hatlen
• Created: 06/Nov/09 10:53
• Updated: 14/Apr/14 14:48
• Resolved: 14/Apr/14 14:48

Description

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.

Issue Links

• incorporates: DERBY-5408 parameters for message 2200H - sequence generator does not cycle - is out of sync in non-English messages
• incorporates: DERBY-5307 Document the behavior of the derby.language.sequence.preallocator property.
• is related to: DERBY-5423 ERROR X0Y84: Too much contention on sequence NSTESTTAB in ns system test
• is related to: DERBY-5428 ns test error: ERROR 38000: The exception 'org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED Identity being changed on a live cacheable. Old uuidString = 846c00a0-0132-88d8-fec0-000000080920' was thrown while evaluating an expression
• is related to: DERBY-5448 In the 10.8 branch, back out the concurrency improvements introduced by using SequenceUpdaters to allocate identity values.
• is related to: DERBY-5687 Back out the concurrency improvements for identity columns introduced by derby-4437
• is related to: DERBY-5151 Don't leak unused identity/sequence values on abnormal exit.
• is related to: DERBY-5295 Make Derby self-tune the preallocated ranges for identity columns and sequences.
• is related to: DERBY-5426 Improve the error raised by too much contention on a sequence/identity.
• is related to: DERBY-6542 Improve the concurrency of identity columns by using SYS.SYSSEQUENCES
• is related to: DERBY-4565 Create concurrency test to stress sequence generators.
• is related to: DERBY-5445 Enhance existing concurrency test to stress sequence generators to also stress identity columns
• relates to: DERBY-5422 IndexOutOfBoundsException followed by NullPointerException in cleanup during system.nstest
• relates to: DERBY-5493 Same value returned by successive calls to a sequence generator.
• relates to: DERBY-712 Support for sequences