Forums Register Login

Generating sequences using Oracle

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
These are the worst of times and these are the best of times. And this is the best tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 1137 times.
Similar Threads
Table-level locking, how?
RETREIVING MAX (DATE) FROM DATABASE
what Collection to use?
quadruple
JPilot,, Java ranch exam question ID: 21
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 19:16:35.