db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (Commented) (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5493) Same value returned by successive calls to a sequence generator.
Date Fri, 30 Mar 2012 18:00:28 GMT

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

Rick Hillegas commented on DERBY-5493:

Thanks for continuing the discussion, Mike. I think we may be converging on a simpler solution.
I need to think some more about this but I agree that a phased approach is probably safest.
I also agree that a common approach which handles both sequences and identity columns is desirable.
Some quick responses:

Here's a modification of your proposal:

1) We eliminate the ghost catalog.

2) But we dedicate a special transaction controller per sequence. All operations (except ddl)
on a SYSSEQUENCES tuple happen in the transaction controller which is dedicated to that row.
We could also experiment with a single sequence-controlling transaction if people think that
the cost of multiple transaction controllers is too high.

3) We will tell users to stop trying to query SYSSEQUENCES and to, instead, use the new syscs_peek_at_sequence()
function. That function will do its work in the transaction controller which is dedicated
to the  corresponding SYSSEQUENCES tuple.

4) The NEXT VALUE FOR invocation will, if necessary, pre-allocate a new range in the SYSSEQUENCE
row by using the row's dedicated transaction controller. Access to this transaction controller
will be single-threaded so syscs_peek_at_sequence() and NEXT VALUE FOR should not block each
other. Attempts to pre-allocate a range should always succeed unless DDL is happening on the
sequence or the user is directly querying the row--we will tell users they shouldn't do that
but should instead use syscs_peek_at_sequence().

I don't understand your proposal about how to tackle identity columns. You may be suggesting
that we create a sequence (backed by a row in SYSSEQUENCES) for every identity column. I think
that is a good approach and it aligns us with the behavior of the SQL Standard, which says
that an identity column should behave as though it is implemented with an internal sequence.

> Same value returned by successive calls to a sequence generator.
> ----------------------------------------------------------------
>                 Key: DERBY-5493
>                 URL: https://issues.apache.org/jira/browse/DERBY-5493
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions:,,,,,
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>              Labels: derby_triage10_9
>         Attachments: derby-5493-01-aa-correctnessPlusPeekerPlusTest.diff
> The following script shows the same value being returned from a sequence generator by
two successive NEXT VALUE FOR calls. Thanks to Knut for finding this:
> connect 'jdbc:derby:memory:db;create=true';
> create table t (x int);
> create sequence s;
> autocommit off;
> select count(*) from sys.syssequences with rs;
> values next value for s;
> drop table t;
> rollback;
> -- same value as previous call
> values next value for s; 

This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message