[
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.
Thanks,
-Rick
> 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
