Re: Grabbing sequence values blocks in consecutive order: Need a guaranteed method.

fmhabash

  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@xxxxxxxxx> 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.

>
>
> -----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?
> ...
>

Other related posts: