RE: Grabbing sequence values blocks in consecutive order: Need a guaranteed method.

Jack,
So am clear on what 'before' vs. 'behind' means in your reply.

The example 2.001 ...
- The '2' is incremented by a sequence.
- 001 incremented by application .

I confirmed per app specs that these id's must be whole numbers. 

So are you saying to start with...
- pick nextval of e.g. 9 then record ids will be 9001, 9002, ..., 9999
- sometimes later, nextval is e.g. 985 which will be 985001, 985002, ..., 985999

Am I understanding this correctly.

Thanks 

-----Original Message-----
From: Jack van Zanen <jack@xxxxxxxxxxxx>
Sent: Wednesday, March 25, 2009 11:20 PM
To: fmhabash@xxxxxxxxx
Cc: afatkulin@xxxxxxxxx; amar.padhi@xxxxxxxxx; ORACLE-L 
<oracle-l@xxxxxxxxxxxxx>; Fuad Habash <fdhabash@xxxxxxxxx>
Subject: Re: Grabbing sequence values blocks in consecutive order: Need a 
guaranteed method.

How about.

thread 1
1.001
1.002
1.003
etc
thread 2
2.001
2.002
2.003
etc

than it is fairly easy and sort of mentioned before

grab before the "."  from an ever increasing sequence number and add the
number behind the "." in the application as required.

this creates unique gapless blocks

Note there is no need for the "." it just makes it more clear to explain.
Limit is obviously 999 numbers in this case but change the format to suit
your need

Jack




2009/3/26 FmHabash <fmhabash@xxxxxxxxx>

> Am the one who originated this thread and I can not thank all enough for
> their professional and curteous contributions.
>
> This is a situation where we have 2 or more java threads originating from
> multiple hosts. Each thread needs to produce a physical file that will
> eventually be sent to a client. A file contains records with some unique
> id's. Historically, these files were shipped with non-consecutive, but
> ordered id's. Business has changed and clients are now asking that each file
> must contain an ordered and consecutive id's on a per file basis.
>
> So based on this, the requirements are:
> 1- Intra-file id blocks must be consecutive.
> 2- inter-file id blocks need not.
> 3- For simplicity, if a job has 3 threads working. thr1 obtains a new
> starting id for the block and starts incrementing consecutively for an upper
> value determined at run time. Once done, thr1 updates its upper value on a
> table. When this incrementing is in progress, thr2 and thr3 are waiting for
> thr1 to finish so one can pick up a new starting id for block2 from where
> thr1 has updated it upper value.
>
> We were hoping there is a way within oracle to enque these threads within
> oracle.
>
> Sequences are no good since they do not guarantee gapless blocks and we
> can't predict the size of a block before-hand as the basis for the sequence
> increments. At best, they can be used to pickup starting value for a block.
>
> The use of dbms-lock or select for update is not helpful either since it
> will not prevent thr2 from selecting on the last upper value used even if
>  if thr1 has the row locked.
>
> Am, by no stretch of wildest imagination, a developer. As an Infrastructure
> dba, I rarely, if ever, had any use for dbms_lock pkg. Having reviewed its
> documentation now, I think it is the answer to what I was looking for. My
> situation here is no different from the check printing example given in the
> reference manual.
>
> Again,
> I thank all for their valued contribution.
>
>
> -----Original Message-----
> From: Alex Fatkulin <afatkulin@xxxxxxxxx>
> Sent: Wednesday, March 25, 2009 4:17 PM
> To: amar.padhi@xxxxxxxxx
> Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
> Subject: Re: Grabbing sequence values blocks in consecutive order: Need a
>     guaranteed method.
>
> Amar, the method you described in your article can not be used for
> what you say it can be used.
>
> It has nothing to do with thinking differently. Look at the example
> which I gave  -- your code isn't safe during race conditions.
>
> On Wed, Mar 25, 2009 at 3:44 PM, Amar Kumar Padhi <amar.padhi@xxxxxxxxx>
> wrote:
> > Well not at all alex, I just like to think differently.
>
> --
> Alex Fatkulin,
> http://afatkulin.blogspot.com
> http://www.linkedin.com/in/alexfatkulin
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


-- 
Jack van Zanen

-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation

--
http://www.freelists.org/webpage/oracle-l


Other related posts: