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

["Mike Matrigali (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6542?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13968444#comment-13968444 ] 

Mike Matrigali commented on DERBY-6542:
---------------------------------------

it has been so many years since this discussion.  Can you remind me what the 
architectural difference is between sequences and identity columns currently.  Or
if it is written up in one place point me there.  It is hard working through the 
various old issues to tell what still applies or not after all the fixes have gone in.

Scanning the old topics on this issue there seemed to be the following issues, could you comment
how your proposal affects identity column behavior for these:
1) correctness issue DERBY-5493 (i think the answer is that sequences behave better than identity)
2) is there a worst case where the change will reduce current identity performance if sequences are used, 
    likely some case with only preallocation=1.  There was some discussion about performance difference of identity and sequences,
could you note if there is any downside to switching to sequences.  I think the
areas of concern were performance with preallocation 1 being the worst case.  There
were changes both in sequences and identity columns since that discussion so not
clear what the current state is.  
3) is there a behavior change for "lost" values by changing to sequences?  

> Improve the concurrency of identity columns by using SYS.SYSSEQUENCES
> ---------------------------------------------------------------------
>
>                 Key: DERBY-6542
>                 URL: https://issues.apache.org/jira/browse/DERBY-6542
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>
> 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.



--
This message was sent by Atlassian JIRA
(v6.2#6252)