Re: Grabbing sequence values blocks in consecutive order: Need a guaranteed method.
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: greg@xxxxxxxxxxxxxxxxxx
- Date: Wed, 25 Mar 2009 21:39:58 +0000
Hi Greg
I think the OP sort of answered this, though not what entities we have here,
with his follow up
It will vary so I can not even predict a range. It may be 2 or 50 values.
It looks though, my options 2e...
1- rely on sequences to get the starting value.
2- have java code increment up to the range determined and insert
accordingly .
3- One remaining issue is that for this transaction, I may have multiple
java threads doing the work. So I want thread 2 to start incrementing from
where thread 1 ended. Am looking for some way I can have thses threads work
in sync picking up their starting values.
so multiple threads accessing unknown and varying blocks of sequence
numbers with a gapless requirement. I can only think of two potential
solutions
1) serialize on a counter value (I'm not sure a sequence would work here,
can't see how it would deal with lost updates) in the db.
2) serialize on a variable in the app code (probably worse).
fm
for gapless, you have to consider what you want to happen when a thread
grabs a block of numbers and doesn't use them all (dies,bug, server restart
etc etc). you also *have* to serialize access to the current value of the
counter. gapless sequences will kill scalability (or actually not be
gapless). As Jared suggested it's well worth investigating what the business
requirement actually is, and if it can be met in some other way. Usually
it's a misstated audit requirement.
Nial
On Wed, Mar 25, 2009 at 7:10 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:
> Taking a big step back from all the suggestions, I would like to ask
> what is the specific level of granularity required for the ordered/gap
> less sequence. For example, is this for "things" withing a given
> application transaction? Is it across multiple "things" for
> application transactions? I'm also curious what the requirement
> (business and/or technical) for gap less sequences is?
>
> Order implies a level of serialization and gap less order implies
> exclusive serialization and serialization is the bane of scalability.
> There was a comment made about having 2 threads, but if they serialize
> for the same resource, what benefit is there to that?
>
>
> On Tue, Mar 24, 2009 at 10:20 AM, FmHabash <fmhabash@xxxxxxxxx> wrote:
> > I have a need for a java app to be able in some guaranteed manner to grab
> a block of sequence values that are consecutive in order.
> > Given the fact that there can potentially be multiple sessions making
> such request, no session should end up interrupting another's while this
> sequence values block is being granted.
> > I know this can be done on java side, but thought may be it can also be
> done on db as well.
> > My initial review shows that sequences will never guarantee gapless
> values. If this is the case, can this still be done at least guaranteeing an
> ordered (not necessarily consecutive ) values?
>
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
