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

From: Riyaj Shamsudeen <>
Date: Tue, 24 Mar 2009 14:15:51 -0500
Message-ID: <>


  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.


Riyaj Shamsudeen
Principal DBA,
Ora!nternals -
Specialists in Performance, Recovery and EBS11i

On Tue, Mar 24, 2009 at 1:45 PM, FmHabash <> 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

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 <>
> Sent: Tuesday, March 24, 2009 1:30 PM
> To: <>; 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
> block?
> ...
Received on Tue Mar 24 2009 - 14:15:51 CDT

Original text of this message