Re: Grabbing sequence values blocks in consecutive order: Need a guaranteed method.
Date: Tue, 24 Mar 2009 14:15:51 -0500
If you *really* want quaranteed method, then you will need to use UL locks.
Session #1 acquires UL in X mode (sequence_name as id1).
Increments sequence values
commit/rollback to release UL lock
Session #2 will wait for UL lock if tries at the same time. If available, will proceed to increment sequences.
Essentially, serializing on UL locks. This can have detrimental performance issues depending upon frequency and duration of these lock requests. Oracle applications has some part of critical code protected by UL type locks.
With combination of ORDERED sequences and UL locking in *every* part of the code that accesses this sequence, you will have a guaranteed method: No gaps and strict ordering at a block of values.
-- Cheers Riyaj Shamsudeen Principal DBA, Ora!nternals - http://www.orainternals.com Specialists in Performance, Recovery and EBS11i Blog: http://orainternals.wordpress.com On Tue, Mar 24, 2009 at 1:45 PM, FmHabash <fmhabash_at_gmail.com> wrote: 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.Received on Tue Mar 24 2009 - 14:15:51 CDT
> -----Original Message-----
> From: Bobak, Mark <Mark.Bobak_at_proquest.com>
> Sent: Tuesday, March 24, 2009 1:30 PM
> To: fmhabash_at_gmail.com <fmhabash_at_gmail.com>; Oracle-L Group <
> 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