db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (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 17:32:29 GMT

    [ 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

        

Mime
View raw message