Our project is large scale one based on J2EE, we use a ID Bean of type Stateless Session bean to generate the next ID (primary Key).Next ID is generated by reading the next ID from table and immediately updating that table with the new ID.Meaning One select and update statement in nextID() method.(Table has only one row with 2 columns - Sl_No and next_ID.)
Settings on this ID bean is TX_REQUIRES_NEW so that the transaction is commmited immediately and leavig way for the next client.
When ever a transaction needs to add a new row to the database, it will call the ID Bean by passing the table name and will get the next ID.
Please note that we have only one ID bean for the entire application. It is definitely will affect the response time because this ID bean is used by all the users. In a particular transaction, one user will insert 180 records to the table.
We do not go with any Database or EJB specific Primary Key Geneartion method.
I would like to know whether this ID generation could be improved in multi user environment or any other better / proven implemenation is availabe in the J2EE world?
u can use a singleton class with a static variable which is incremented with each call - this doesnt need any db update and hence the locking is very much reduced.
btw, i would like to know how do u ensure that only one instance of SLSB is there? Have u configured no of beans in pool to be 1?
I'd suggest that you *do* use the table, but allocate id's 100 (say) at a time. That way the DB only gets hit every 100 rows.
This works in a clustered situation, or one with multiple class loaders (i.e. if you can't get a true singleton).
It has the disadvantage that you may get gaps in your id sequence (which you can get anyway with your current scheme), and your IDs won't increase monotonically with time.
Check out the chapter on Primary key ID generation on this site. The method I liked, and implemented, was UUID generation by a SLSB that does not involve the database. Part of the key is from the IP address of the machine, part from System.currentTimeMillis(), part from the hashcode of the SLSB object, and part from a random number generated per call.
From my tests, I've seen sub-millisecond response times for UUID generation using this method, and it can scale linearly, since it's a SLSB and can be pooled and clustered safely.
You need not necessarily keep an primary key sequence table in database. what we do as a generic pattern we keep an in memory increment for the primary key sequence. Ok there is an helper class which actually is synchronized for next id method call. if the memory variable id==0 then it makes the call to the table once for a server start up and gets the max id from the table and stores it for that variable.
from then on any program looking for next id to create data objects to be stored will have the id generated in the memory.
trust this meets ur requirements