I once used a scheme where I had a serialnumber table with one row for each
table in my db. I used a stored-proc to increment the serialnumber and return
it for a table. This is because DB2 didn't have a Sequence type in it like
Oracle.
Naturally, there are issues with row-level locking vs page level but the scheme
worked ok on low-volume applications.
What do you guys/gals think of this solution?
-Ron
--- Gabriel Lawrence <[EMAIL PROTECTED]> wrote:
> Santiago Gala wrote:
>
> > Calvin Varney wrote:
> >
> > > Performance will depend on how the dbms has been implemented but
> generally speaking a select max would be very efficient as the primary key
> should be indexed (possibly clustered) so the max value can be obtained
> directly from the index without even referring to the table.
> > >
> > > However, I'm still not convinced this is a safe thing to do. What happens
> if you have two eb's being created at the same time, won't they both select
> the same max value, add one leaving us with two identical primary keys?
> > >
> >
> > As there is a transaction covering the whole create call either the whole
> access (selection+insert) is serialized or the second one should fail.
> >
> > I have never experienced a problem with this scheme.
> >
> > The main issues are if you need to insert from outside the application and
> if you need that keys freed by objects removed are never reused.
> >
>
> Actually, this depends on the isolation level of the transaction. Unless you
> set the isolation level properly (and your database supports it... oracle
> doesnt seem to support all the isolation levels) you should also do a for
> update at the end of the sql to lock the table.
>
> As a side note, oracles sequence number generation allows you to do it in SQL
> in the create seperate from the commit of the ejb. This is kinda nice, as it
> allows you some control over sequence number assignment. The other solution
> we've used very successfully is to use an RMI
> service that assigns sequence numbers from a pool. This way we dont hit the
> database every time to create sequence numbers, but we can also assign them
> in the create....
>
> My mind is fuzzy, but I seem to remember a discussion on the ejb interest
> list suggesting that the post create method was the place to deal with the id
> stuff... You may want to do a search in the archive to see what the exact
> details of the answer was.
>
> -gabe
>
>
> --
> I'm hiring Java folks. See http://www.kovair.com/careers/default.html for
> info!
>
> Elvis needs boats!
>
>
>
> ----
> To unsubscribe, send email to [EMAIL PROTECTED] and
> include in the body of the message "unsubscribe jonas-users".
> For general help, send email to [EMAIL PROTECTED] and
> include in the body of the message "help".
__________________________________________________
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
http://mail.yahoo.com/
----
To unsubscribe, send email to [EMAIL PROTECTED] and
include in the body of the message "unsubscribe jonas-users".
For general help, send email to [EMAIL PROTECTED] and
include in the body of the message "help".
