Hi...
I've read an IBM EJB article about using select max(id) from tableXXX + 1 for generating id. I think this solution has a problem cause since it's possible that two or more bean can get the same id. For example....

1. bean1 retrieves max id
2. bean2 retrieves max id
3. bean1 increments maxid and store to db
4. bean2 increments maxid and store to db

I think this situation can only be prevented if the isolation level is set to serializable. Am I right? If yes, isn't it setting isolation level to serialable is discourage since it degrades the system performance.

Please comment on using this mechanism of generating id. Note: I've read pattern section in this site about generating id.

Jerson

Hi Jerson,
   THe basic concept is that initially u use the 'select max(id)' query to pick up the last record in the table.After that u initialise a stateful session bean and store the retrieved value in the bean.Store the bean in JNDI .There is just one copy of the bean and all future requests for 'id generation' will be handled by this stateful bean .It will increment the last value stored in it and give it to the next user.

The sample code doesn't use a stateful bean. It's just calls a private method getNextID() in the ejbCreate.

something like this...
private int getNextID() {
return select max(id) from tablexxx + 1
}

Am I right that this will only work if the isolation level is set to serializabl? Please comment.

Jerson

Id simply use an update statement to lock the record so only one person can read it


UPDATE RecordGeneratorTable
SET nextid = (SELECT MAX(nextid) + 1 from RecordGeneratorTable)

SELECT MAX(nextid) FROM RecordGeneratorTable

Now you can serialize access to the record since the UPDATE locks the record and you then read it. Like any other pre-lock/post-read styled operation.

Dave Wolf
Internet Applications Division
Sybase

Hi Dave...
It's not clear to me. Please elaborate... If possible can you post a code snippet?

Jerson

Hi Dave...
Now I understand what you're actually suggesting. It's actually like a portable sequence.
I think it's a good idea.
But this is not what I'm referring to.
Here's the pseudo code to make my point clear.

public class CustomerEntityBean {
public void ejbCreate(String name, int type) {
key = getNextKey();
...
}


protected long getNextKey() {
     long nextKey = 0;
    
Customer newestCustomer = ((CustomerHome)
entityContext.getEJBHome()).findNewest();
// the implementation of findNewest is not shown,
// i guess the findNewest returns the bean with the highest id/primary key
// in my point of view this is like select max(id) from customer + 1
nextKey = ((CustomerKey)newestCustomer.getPrimaryKey()).key + 1;
return nextKey;
}
}

I think it's possible that one or more entity can get the same nextKey. Please comment on
this design. What's your opinion?

Jerson

If all you are after is a unique primary key, without the order inherent in the above method, maybe you could consider using a GUID. Since guid code for java is few and far between, you could use a 'unique' id generated on the server using the rmi package.

The cost is a larger key (32 bytes) but cpu resources and storage is cheap IMHO. Using this technique, you don't have to worry about distributed singleton patterns or dirty reads etc

regards Rob

You might also look at the Pattern thread of this site which has a detailed discussion on the topic of Entity Bean Primary Key Generators.
    http://theserverside.com/patterns/thread.jsp?thread_id=220

Jim

you can get around this problem with weak isolation levels by just catching the SQLException. Assuming you try to insert rows with new Pkeys simultaneously and you have a primary key constraint in the DB, one of the inserts will fail. Just retry it. You could also have an upper limit on retries before forcing a short-lived lock on the page.


  This way you don't need an increment table, you can just gen the pkey within the insert statement:

insert into MyTable values((select max(wow) is null ? 0 : max(wow) + 1), value1, value2, value3, ...)

Stu