RE: Grabbing sequence values blocks in consecutive order: Need a guaranteed method.
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <fmhabash@xxxxxxxxx>, "'Bobak, Mark'" <Mark.Bobak@xxxxxxxxxxxx>, "'Oracle-L Group'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Mar 2009 15:09:14 -0400
(the other Mark replying)...
Mr. Bobak's method would be slick and fast, and if you really know the limit
is 50 (or even 100) then that is your stride. (Sorry, Oracle decided to call
stride increment). You'll burn some numbers, but they will be guaranteed in
order. If you're willing to burn some numbers, you can deal with the
rollbacks and everything else by grabbing your range as a discrete
transaction with a commit before you do your "real" transaction. If your
transaction breaks and gets rolled back, you either re-use the range
(presuming your session was not lost and you still have the range in hand),
or if the transaction is restarted with a new session, just grab a new
range.
If you cannot stride at the maximum 'line count' you might ever have,
perhaps due to a concern that your transaction line ids might wrap too soon,
then you might try an actual relational design where there is a single
transaction id and each subcomponent is relationally identified with that
transaction id plus a unique identifier for the row in question.
I'm not sure how you are throwing work at the proposed multiple java
threads, or what signals the end of a transaction to a java thread.
Designing a method to process input streams in parallel is best done with an
idea of whether you are processing one or more input files or input from
interactive users. Either way, a single transaction id that goes only up
shouldn't be a big problem with a two or more table approach.
If you need it to be really continuous and only going up, then you need one
row in a table with select for update that you do not release until commit
of the whole transaction. That is not a great way to scale the throughput of
parallel threads unless the gate on throughput is the processing that must
be done on the input for each transaction as opposed to inserting and
committing the results. I'm not aware of any other point in time recoverable
method mechanism that is more scalable, though, if that is what you really
need.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of FmHabash
Sent: Tuesday, March 24, 2009 2:45 PM
To: Bobak, Mark; Oracle-L Group
Subject: RE: Grabbing sequence values blocks in consecutive order: Need a
guaranteed method.
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
Other related posts:
