Re: User Sequence Numbers

Hermann Angstl wrote:
> Hi,
>
> let's create the following table:
>
> create table foo (k number(5)); -- dont add primary key on k --
> insert into foo values (1);
> insert into foo values (2);
> insert into foo values (3);
> commit;
>
> Is it possible, with the following statement, to create unique numbers ?
> Even with multiple sessions ?
>
> select k from foo where k in (select max(k) from foo) for update of k;
> k++;
> insert into foo values (new_k);
> commit;
>

no.
you don't want to update, so you don't need to lock a row

> ... is there another solution ?
>

lock table foo in exclusive mode;
insert into foo select nvl(max(k) + 1, 1) from foo; commit;

this solution is not so good (suppose you are not the only one wanting to do that, you have to lock the whole table)

Oracle noticed that disadvantage and invented sequences.

have a look at them !!

matthias

> cu,
> hal
>
Received on Fri Jan 31 2003 - 03:52:14 CST