RE: Grabbing sequence values blocks in consecutive order: Need a guaranteed method.
Date: Wed, 25 Mar 2009 22:56:53 -0400
Message-ID: <49caeef7.05ae660a.1990.3526_at_mx.google.com>
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.
-- 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-lReceived on Wed Mar 25 2009 - 21:56:53 CDT