Re: find max id for forms

On Tue, 07 Aug 2001 18:56:14 +0100, Connor McDonald <connor_mcdonald_at_yahoo.com> wrote:

>This only works in a single-user system, since two or more people could
>get the same value. A sequence is a better solution,

A word of warning: we have had problems in the past with Sequences and Export. I don't remember details, but an export/import of the database managed to somehow mess up our sequences: restarted the count, or something.

> or if you must use
>the (x+1) method, then you need to:
>
>- lock appropriate rows in table
>- get max(blah)+1
>- release lock

The method recommended back in Oracle 5 (pre-sequences!) days involved the use of a separate table that stored the next free ID. This was selected from and locked for update in the pre-insert, then incremented and released in the post-insert.

Yes, I know, storing the max value is de-normalising. But it's a lot faster than the MAX function. And it gives the option of holding the next *free* slot in the pseudo-sequence, not just the highest+1. Received on Thu Aug 09 2001 - 00:14:20 CEST