[
https://issues.apache.org/jira/browse/DERBY-5493?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13242571#comment-13242571
]
Mike Matrigali commented on DERBY-5493:
---------------------------------------
Long term I believe the goal should be to design a solution for sequences that
can also be used for generated keys. I agree with kathey
that we should look at a phased approach with sequence implementation first and
identity column in a subsequent release. So am going
to talk about that here, though the jira issue is about sequences.
I think a simple solution that does the following would address both
correctness and performance issues:
o all "next" operations, including ddl to create them always happen in a nested
transaction and they should at worst get 1 lock on 1 row
in a system catalog. I am leaning now that that catalog can not be
SYSCOLUMNS as this catalog is just to intertwined with other
ddl operations. I am hoping that it can be SYSSEQUENCES.
o these operations should be scalable as possible and be designed to allow good
performance for large number of thread across large
number of processors across a large number of sequences and generated keys.
Above I described what I hope works for sequences, basically just move all
current sequence operations into nested transaction - need to
verify a create sequence only adds a single row to a sequence catalog. Doing
all work for sequences in a nested transaction I think
eliminates the whole garbage collection issue. Never escalate, throw errors on
lock timeouts/deadlocks. By not escalating we eliminate
the correctness problems with escalating.
generated keys are not as easy as we can't just move all create tables into
nested transactions. My proposal here is that create
table does not do anything with SYSSEQUENCES, it delays the addition of a row
to sequence catalog until first insert. Again all
operation next operations are done same as above proposal for sequences.
note: I think drop table is one exception where we will get lock on sequence
row in user transaction, but that seems ok as no
one should be doing anything on table once we have the table exclusive lock on
drop table. and also note drop table needs to
handle the sequence row existing or not, both are valid.
note2: i think a serialized isolation level is needed to handle on demand
creation of the sequences row, to handle two threads trying
to create the row.
I hope current sequence catalog can either support this now, or can with an
upgrade. If we feel we need another catalog I would rather
see it designed as a replacement for the sequence catalog(s). At least then
new databases only have what is needed, or maybe we
can even get rid of old catalog on hard upgrade.
This proposal is not addressing feature of the ghost catalog that prevents
users from getting locks on the catalogs by scanning
them directly. I am ok with throwing errors on lock timeouts and calling out
the likely cause. Long term it would be better to somehow turn all system
catalogs into "ghosts" with respect at least to unintended lock interactions
from user direct access. maybe we could change
system to not get locks on system catalogs direct scans by users. Maybe we
should beef up the documentation on not supporting
direct access to system catalogs and call out locking issues.
> 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
