Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Generating sequences using Oracle

 
Pho Tek
Ranch Hand
Posts: 782
Chrome Python Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic