Win a copy of 97 Things Every Java Programmer Should Know this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
  • Piet Souris
  • salvin francis
  • fred rosenberger

Generating sequences using Oracle

Ranch Hand
Posts: 782
Python Chrome Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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


Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.

It's a pleasure to see superheros taking such an interest in science. And this tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
    Bookmark Topic Watch Topic
  • New Topic