Re: Sequence number availability to others during transaction

Hello Paul,

the creation of a sequence happens in an autonomous transaction which means that it is done independently of any invoking transaction. This means that it cannot be rolled back, and you cannot get two equal numbers from a sequence in two different transactions provided you retrieve the value by seq.nextval.

Do you use seq.currval somewhere?

Martin

Paul Cloud wrote:
>
> Hello everybody,
>
> I have a stored procedure running on Oracle 7.3.4, that does one or more
> inserts into a table where the primary key is a sequence number. The
> procedure also does some other table updates, too. At the end of the
> procedure, I want to commit all transactions, or rollback if there was an
> error at some pont in the processing. If two or more users execute this
> procedure at or about the same time, the second user receives a -0001 (dup
> value on index) error, which I believe is due to the fact that the
> transactions for user 1 have not been commited at the time user 2 tries to
> do the same processing, and they are both trying to insert using the same
> sequence numbers. I don't think a savepoint after the inserts will
> "publish" the new sequence so that other users will use the "new, revised"
> sequence, but I could be wrong. Does anybody know how to solve this?
>
> Thanks,
> Paul
  Received on Sun May 07 2000 - 00:00:00 CDT