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 ?
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.
Gravitation cannot be held responsible for people falling in love ~ Albert Einstein