RE: Grabbing sequence values blocks in consecutive order: Need a guaranteed method.
- From: FmHabash <fmhabash@xxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>, Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Mar 2009 14:45:00 -0400
Thanks Mark.
But how would you do it if the range is unpredictable?
Also ...
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.
-----Original Message-----
From: Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>
Sent: Tuesday, March 24, 2009 1:30 PM
To: fmhabash@xxxxxxxxx <fmhabash@xxxxxxxxx>; Oracle-L Group
<oracle-l@xxxxxxxxxxxxx>
Subject: RE: Grabbing sequence values blocks in consecutive order: Need a
guaranteed method.
So, your app needs to grab a block of sequence values....? How large a block?
One possible solution would be to use a sequence w/ increment by <your sequence
block size>. So, if you need a block of 10, create a sequence that is
"increment by 10", and then each session will select as follows:
Session 1:
Select seq.nextval will get 1
Session 2:
Select seq.nextval will get 11
Session 3:
Select seq.nextval will get 21
Now, in the application logic, it will do 10 inserts, starting with the
sequence it fetched. So, session 1 will insert 1-10, 2 will insert 11-20,
three will insert 21-30, etc.
You'll have to account how to deal with rollbacks, application crashes, other
errors, etc. But, in principle, I think it will work, and still be scalable.
Hope that helps,
-Mark
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of FmHabash
Sent: Tuesday, March 24, 2009 1:21 PM
To: Oracle-L Group
Subject: Grabbing sequence values blocks in consecutive order: Need a
guaranteed method.
Hi all,
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?
Thanks
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
