Re: JDBC problem

On Fri, 25 Aug 2000, Cedar Cox wrote:

> 
> 
> On Thu, 24 Aug 2000, John McKown wrote:
> 
> > The syntax that I've seen is:
> > 
> > insert into KEY_ITEM (key_item_id,keyname) values
> > (select max(key_item_id)+1,'?' FROM KEY_ITEM);
> > 
> > Just out of curiousity, why not make key_item_id a SERIAL field? That way,
> > if you don't specify a value, it gets the next value from the associated
> > sequence.
> 
> I was going to ask you to explain SERIAL but the documentation makes it
> fairly clear.. basically just the next value, yes?  Simply setting the
> field type to SERIAL seems to be the same as the MS Access 'autonumber'
> field.  Is this correct?  

I'm not familiar with MS Access, but I'll bet that it is very similiar to
'autonumber'. However, a sequence can be shared by multple tables /
fields. This means that a single table/field could have "holes" in the
sequence number.

> 
> One detail that seems to have an advantage over the MAX+1 method is that
> in the case of adding a new record, deleting it, then adding another new
> record the ID will not be reused (correct?).

Correct. The sequence is independant of the table and the value only
increases.

> 
> Question:  What happens when transactions enter the picture?  Will you get
> duplicate values, or holes, or does it work just fine?

A good question to which I don't know the answer. 

However, after thinking about it a bit, I would guess that the sequence is
"locked" for the duration of the transaction. This would imply that all
other transactions which needed to access the sequence would be stalled
until this transaction ended. When the transaction ended, then sequence
would either have the highest number assigned so far, or would be rolled
back to the same value it had when the transaction started.

The above is just my guess. If the sequence is not "locked", then I would
guess that an aborted transaction would cause the sequence to end up with
holes. Actually, multiple tables and fields can share a sequence (why
would anybody do this?). This means that a single table could have
"holes" in the sequence.

Under no circumstances would I expect duplicates.

> 
> -Cedar
> 
>