Re: Multiple update problem

Based on the information given, you are suggesting that there is a unique key associated with each record. And currently you are getting the value by looking for the the greatest value currently in the table (or by some other method.)

A better plan is to use a sequence number. The key field is the next value in the sequence " someSeq.nextval " when the record is being created but before it is commited. The system will, by definition, give both users a differenct unique number. When each user finally commits, the

value will be unique.

The compliment activity would be both users create a record (and use a sequence
number) and one or both decide not to commit or save the record. What happens? The sequence number is consumed and cannot be reused. In fact once the someSeq.nextval is processed, the number is consumed. If the database is small, lossing a few sequence numbers here and there is

probably of no consequence. If the table is very large -- one could develop
a scheme for recovering and reusing keys. This is generally very difficult if
the key field is referenced in other tables, as is typically done.

Mike Krolewski.

Mungo Henning wrote:

> Hi,
> [newbie, lurking here for a few weeks, please treat gently! :-) ]
>
> I guess this is a well-known scenario, but could some kind soul let me
> in on the secret please:
>
> Two users on an Oracle database, using (say) the DEPT table.
> Primary key on DEPTNO (uniqueness enforced).
> UserA performs an UPDATE on the record whose DEPTNO is ten, setting
> the DEPTNO to ninety.
> UserB inserts a new record whose DEPTNO is ninety.
>
> Obviously after both users commit we cannot have two records with the
> same DEPTNO, so when is the problem detected and (basically) what
> happens?
>
> I'm thinking of the following (separate) scenarios: the past is to the
> left
> of the line, the future to the right:
>
> UserA:update UserB:insert UserA:commit UserB:commit
> or
> UserB:insert UserA:update UserA:commit UserB:commit
>
> There may be other scenario's, but the above will do for a start! :-)
>
> Any help gratefully accepted.
>
> Regards
>
> Mungo Henning
Received on Sun May 24 1998 - 09:19:07 CEST