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

  • From: Yechiel Adar <adar666@xxxxxxxxxxxx>
  • Date: Thu, 26 Mar 2009 12:02:01 +0200

Can you provide some more information:
1) Can each file start with 1 and go on.
2) Does the threads create files for the same client or to different clients. I mean: for client A, is there only one thread that create a file or two threads that run at the same time creating two files for the client and you need the second one to start with the next number after the end of the first file. 3) Do you need the number to continue on the next file creation or the next file can start again from 1.
I think you can create a table with client id as a PK and one field with type number. At each run, the thread lock the record that contain the client id and increment the number in memory. 
At the end it just update the number in the record.
This way, runs for different clients do not interfere with each other and runs for the same client will be executed serially. 
If the thread fails, then you do rollback and no numbers where skipped over.

Adar Yechiel
Rechovot, Israel



FmHabash wrote:

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.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2009