Multiple update problem

From: Mungo Henning <mungoh_at_itacs.strath.ac.uk>
Date: Wed, 20 May 1998 16:05:49 +0100
Message-ID: <3562F14D.67D9ACC_at_itacs.strath.ac.uk>

Hi,
[Quoted] [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?

[Quoted] [Quoted] I'm thinking of the following (separate) scenarios: the past is to the left
[Quoted] of the line, the future to the right:

        UserA:update UserB:insert UserA:commit UserB:commit or

[Quoted]         UserB:insert UserA:update UserA:commit UserB:commit

[Quoted] There may be other scenario's, but the above will do for a start! :-)

Any help gratefully accepted.

Regards

Mungo Henning Received on Wed May 20 1998 - 17:05:49 CEST