[DERBY-6542] Improve the concurrency of identity columns by using SYS.SYSSEQUENCES

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.11.1.1
• Fix Version/s: 10.11.1.1
• Component/s: SQL
• Urgency: Normal
• Issue & fix info: Release Note Needed
• Bug behavior facts: Performance
• Assignee: Richard N. Hillegas
• Reporter: Richard N. Hillegas
• Created: 14/Apr/14 14:39
• Updated: 09/Sep/16 09:53
• Resolved: 30/May/14 13:51

Description

This is another attempt to improve the concurrency of identity columns. The previous attempt was tracked by DERBY-4437.

This new attempt will try out Mike's last suggestion: use system-created sequences managed by SYS.SYSSEQUENCES. This should reduce the contention on the core catalogs.

I'm hopeful about this approach because of the experiments tracked by DERBY-6533. There we are not seeing any problems related to sequence generators but we are seeing lots of identity-related lock timeouts.

Here is the general shape of this approach:

1) When adding an identity column to a table, Derby will create a sequence generator for the column. The sequence generator will live in the SYS schema and its name will be the table's UUID.

2) DROP SEQUENCE will not operate on system-created sequences. System-created sequences will be dropped/modified by DROP/ALTER TABLE commands.

3) We will add a new system function for inspecting the current, in-memory value of an identity generator without getting a lock on SYS.SYSSEQUENCES: SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY( tableSchemaName, tableName ).

4) Derby will create a sequence for every legacy identity column after hard-upgrade.

5) These changes will take place only after hard-upgrade. Soft-upgrade will not change the behavior of identity columns.

Comments on this proposal are welcome. Thanks.

Issue Links

• is blocked by: DERBY-6554 Too much contention followed by assert failure when accessing sequence in transaction that created it
• is related to: DERBY-6550 Bulk-insert causes identity columns to cycle when they shouldn't
• is related to: DERBY-6595 CheckToursDBTest failed while updating sequence value on disk
• is related to: DERBY-6579 Changing the step value for an identity column incorrectly changes its current value.
• is related to: DERBY-6852 Allow identity columns to cycle (as defined in SQL:2003)
• is related to: DERBY-6580 Document the new SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY function
• is related to: DERBY-3888 ALTER TABLE ... ADD COLUMN cannot add identity columns
• relates to: DERBY-4437 Concurrent inserts into table with identity column perform poorly
• relates to: DERBY-6533 Add a quiet mode to NsTest