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