2 Replies Latest reply on Sep 4, 2002 2:55 PM by sree

    "Committing immediately" for sequence generation

    Tim Fox Master

      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.