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] [Commented] (DERBY-5151) Sequence without cache
Date Fri, 25 Mar 2011 13:03:05 GMT

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

Rick Hillegas commented on DERBY-5151:

Hi Mark. This is an interesting problem. Here's something to keep in mind: there will always
be gaps in the sequence because sequence-number-generating statements can be rolled back.
They can be rolled back because of Derby bugs or statement level errors like constraint violations,
or because the application discards the surrounding transaction for some reason. Once a sequence
number has been allocated, the counter is advanced and never backs up. If the statement which
allocated the number is rolled back, then there will be a hole in the sequence. Will your
use-case tolerate these holes?

I'm not keen on adding more SQL language to address this problem. Fortunately however, the
sequence generator was designed so that we could tune the cache size (SequenceGenerator.DEFAULT_PREALLOCATION_COUNT).
I would prefer to tune the cache size via Derby properties:

o You could introduce a master Derby property which controls the size of all sequence generator
caches, say derby.sequence.cache.size.

o You could introduce a space of per-sequence properties, say derby.sequence.cache.size.sequenceName.


> Sequence without cache
> ----------------------
>                 Key: DERBY-5151
>                 URL: https://issues.apache.org/jira/browse/DERBY-5151
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Mark Holster
>         Attachments: sequence-no-cache.patch
> Currently, a sequence in Derby always uses a pre allocating cache. I'm working on an
usecase which requires a number to increase with a fixed value. The usecase fails after a
restart because of the pre allocating cache.
> I've created a patch that adds the options CACHE | NO CACHE to the create sequence statement.
When no cache is provided, the pre allocating cache size will be set to 1, which results in
a "no cache sequence".
> I've followed the CYCLE | NO CYCLE code as much as possible. Tested it in my own app
and it seems to work fine.

This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

View raw message