db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-712) Support for sequences
Date Fri, 05 Feb 2010 00:36:28 GMT

     [ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Rick Hillegas updated DERBY-712:

    Attachment: derby-712-05-al-sequenceGenerator.diff

Attaching derby-712-05-al-sequenceGenerator.diff. This reworks the previous version of the
patch, replacing a deadlock situation with a potential race-condition.

The separation of functionality between SequenceUpdater and SequenceGenerator is now more

o SequenceUpdater handles handshaking with the catalog.

o SequenceGenerator is just a calculator which does no i/o.

This patch includes more extensive header comments for both classes,explaining how they are
used. The basic logic flow is documented in in SequenceUpdater.  Hopefully this is clear enough
that other people will be able to reason about its correctness and spot flaws. Here is that
header comment:


Here is the algorithm pursued when the caller asks for the next number in a sequence:

o We try to get the next number from a cache of pre-allocated numbers. The endpoint (last
number in the pre-allocated range) was previously recorded in the catalog row which describes
this sequence. If we are successful in getting the next number, we return it and all is well.

o Otherwise, we must allocate a new range by updating the catalog row. At this point we may
find ourselves racing another session, which also needs the next number in the sequence.

o When we try to update the catalog row, we check to see whether the current value there is
what we expect it to be. If it is, then all is well: we update the catalog row then return
to the first step to try to get the next number from the new cache of pre-allocated numbers.

o If, however, the value in the catalog row is not what we expect, then another session has
won the race to update the catalog. We accept this fact gracefully and do not touch the catalog.
Instead, we return to the first step and try to get the next number from the new cache of
numbers which the other session has just pre-allocated.

o We only allow ourselves to retry this loop a small number of times. If we still can't get
the next number in the sequence, we raise an exception complaining that there is too much
contention on the generator.

If applications start seeing exceptions complaining that there is too much contention on a
sequence generator, then we should improve this algorithm. Here are some options based on
the idea that contention should go down if we increase the number of pre-allocated numbers:

o We can let the user change the size of the pre-allocated range.

o Derby can increase the size of the pre-allocated range when Derby detects too much contention.


> Support for sequences
> ---------------------
>                 Key: DERBY-712
>                 URL: https://issues.apache.org/jira/browse/DERBY-712
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>         Environment: feature request 
>            Reporter: Tony Dahbura
>            Assignee: Suran Jayathilaka
>             Fix For:
>         Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, catalogs_c.patch,
catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, catalogs_f_2.patch, catalogs_g.diff,
catalogs_h.diff, create_drop_sequence_a.patch, create_drop_sequence_b.patch, create_drop_sequence_c.patch,
create_drop_sequence_d.patch, create_sequence_a.patch, createseq_args_bind_a.diff, createseq_args_bind_b.diff,
derby-712-02-aa-privilegeNodeCleanup.diff, derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff,
derby-712-04-aa-dblook.diff, derby-712-05-af-sequenceGenerator.diff, derby-712-05-al-sequenceGenerator.diff,
SequenceGenerator.html, sequences_next_value_a.patch
> Would like to see support added for sequences.  This would permit a select against the
sequence to always obtain a ever increasing/decreasing value.  The identity column works fine
but there are times for applications where the application needs to obtain the sequence number
and use it prior to the database write.  Subsequent calls to the table/column would result
in a new number on each call.
> SQL such as the following:
> SELECT NEXT VALUE FOR sequence_name FROM sometable ; would result in a next value.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message