Generating sequences using Oracle

[Pho Tek]

Hi,

I would like to generate monotonically increasing sequence numbers
BUT I don't want to use Oracle SEQUENCES. I am using a MAX(id)+1 solution which is frowned upon by Tom Kyte from asktom.oracle fame. Firstly - and please don't laugh - scability is not an issue here. I am perfectly fine with having to serialized access to the sequence generator. But I want to use Oracle's SELECT FOR UPDATE to do this. Currently I have created a simple java method that looks like this.

  
0  Long generate(Long branch_id) {
1    set autocommit off;
2    exec "select max(btno) from BuyTransactions
           where bid=branch_id 
           for update WAIT 15"; 
3    Was any records returned in #2 ?
4        if (no) {
5            next_id = blah..blah; // ignore
7        } else {
8            next_id = value from #2 + 1;
9        }
10   commit; // release row locks
11   return next_id;
12 }

You will see that "btno" in the BuyTransactions table is the actual field
that will store the generated sequence number. However I have a requirement
in line #5 such that if there are no records yet for branch with branch_id;
then I return the starting value (blah..blah is just a placeholder for some other calculation to come up with the initial value).

The problem is that if there isn't any record yet for branch_id in BuyTransactions; then the SELECT FOR UPDATE does not block. i.e. no rows
are locked. Thus multiple threads can execute the rest of the method; so the
method will cause collisions. Any ideas on how to solve this ?

Thanks

Pho

[Shailesh Chandra]

Pho,

I hope I understood you problem correctly !!I have a suggestion !! think about it if it appeals you !

Do not provide access of the method which generate sequence to every thread, Rather create a singleton class which access same method and caches(a fixed number) generated sequnces in a hashmap.

let say hashmap contains 10 sequnces any time a thread need a sequnce it can get from the hashmap, and the moment you allocate a sequnce to any thread you remove same sequnce from hashMap and when your hashmap is empty, generate next 10 sequnces from oracle and cache them.

here you will achieve that single point for interacting with datbase for sequnces. and multiple thread will access you hashmap for sequences.

and one more thing for generating 10 sequnces (or parameterised number of sequnces) you need not to access database 10 times.


Shailesh