Re: Grabbing sequence values blocks in consecutive order: Need a guaranteed method.
Date: Thu, 26 Mar 2009 12:02:01 +0200
Message-id: <49CB5299.3020607_at_inter.net.il>
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_at_gmail.com>
> Sent: Wednesday, March 25, 2009 4:17 PM
> To: amar.padhi_at_gmail.com
> Cc: ORACLE-L <oracle-l_at_freelists.org>
> 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_at_gmail.com> wrote:
>
>> Well not at all alex, I just like to think differently.
>>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 26 2009 - 05:02:01 CDT