I'm using 3.0.2.
I have a class that provides primary key values for my entity beans.
The class has a method getNextId(String sequenceName) which returns the next id for the named sequence.
Basically the class caches a set of ids for each name, in memory so a db call isn't incurred each time.
I have a counter table which has the following structure
sequence_name varchar2(10),
nextval number(10),
chunksize number(10)
The chunksize determines how many ids the class fetches in one go.
When the class fetches a batch of ids from the database, it updates the nextval value to be the next free value.
The idea is:
I do a select for update... to get the value
and then do an update counter set nextval=?
to set the value
Problems I have is this:
1. The select and the update need to be done together, and quickly in order to prevent locking ocurring.
Ie I need to commit the transaction that performs the update immediately after the update.
Basically I want it to to be in it's own transaction.
2. If somewhere else in my business code, an exception occurrs I don't want my update on the nextval value to be rolled back, even though I want the rest of the stuff to roll back.
Can anyone think of a good way of doing this?
I've thought of wrapping it in a slsb and setting transaction attribute to "requires new" but not sure if that will work.
Also I am using local, not XA transactions for what it's worth.
Thanks.
