Re: Multiple update problem
Date: Wed, 20 May 1998 17:36:27 GMT
Message-ID: <3563142b.12014626_at_192.86.155.100>
A copy of this was sent to Mungo Henning <mungoh_at_itacs.strath.ac.uk> (if that email address didn't require changing) On Wed, 20 May 1998 16:05:49 +0100, you 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?
>
only one will be successful, the other will get an error.
>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
If userA updates 10 to 90, UserB's insert will BLOCK on the unique index entry. When UserA commits, UserB will get an error stating duplicate index entry.
>or
> UserB:insert UserA:update UserA:commit UserB:commit
>
If userB inserts 90, userA's update will block. UserA will not get to the commit statement yet (userA is blocked), UserB will commit and userA will get an error message about duplicate index entries.
>There may be other scenario's, but the above will do for a start! :-)
>
>Any help gratefully accepted.
>
>Regards
>
>Mungo Henning
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed May 20 1998 - 19:36:27 CEST