[
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.
Thanks,
-Rick
> 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: 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2, 10.8.2.2,
> 10.9.0.0
> 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
