Re: oracle concurrent

In article <0oTqEFAus7DxEwQB_at_tomcooke.demon.co.uk> Tom Cooke <tom_at_tomcooke.demon.co.uk> writes:
>Path: nntp.teleport.com!news.reed.edu!news.orst.edu!news.uoregon.edu!news.dacom.co.kr!usenet.seri.re.kr!news.cais.net!xara.net!peer-news.britain.eu.net!demon!tomcooke.demon.co.uk!tom
>From: Tom Cooke <tom_at_tomcooke.demon.co.uk>
>Newsgroups: comp.databases.oracle
>Subject: Re: oracle concurrent
>Date: Wed, 31 Jan 1996 18:55:42 +0000
>Organization: North Staffordshire Hospital NHS Trust
>Lines: 21
>Distribution: world
>Message-ID: <0oTqEFAus7DxEwQB_at_tomcooke.demon.co.uk>
>References: <4e1jpi$b6a_at_cnct.com> <4e1kbv$cb7_at_cnct.com>
> <310FAE65.1232_at_us.oracle.com>
>NNTP-Posting-Host: tomcooke.demon.co.uk
>X-NNTP-Posting-Host: tomcooke.demon.co.uk
>MIME-Version: 1.0
>X-Newsreader: Turnpike Version 1.11 <91JPjwBJDWIUxzmtiYJRSjsG0G>

>In article <310FAE65.1232_at_us.oracle.com>, "Robert C. Nix"
><rnix_at_us.oracle.com> writes
>>> lee (lee_at_cnct.com) wrote:
>>> problem:
>>> how to avoid two user SELECT same data from database,
>>> and one's UPDATE + COMMIT overwrite another one's UPDATE +
>>> COMMIT ?
>>> we don't want lock the row after SELECT, but want lock
>>> the row after UPDATE, and before COMMIT, how to do
>>> this ?
>>> Thank you.
>>
>>one option is to add a date_modified column to the table.
>> stuff deleted
 

>Sounds like bad business process/application design - which user has the
>"correct" data i.e. accurately represents some aspect of the real world,
>which is what databases are for, no?
>--
>Tom Cooke

Let us say that you have a person table with the following fields:

ssn,name, address,sex where ssn is the primary key

Let us say that the data is:
111-11-1111,Bob,123 Main Street,M

User 1 and user 2 selects bob's info.
User 1 changes Bob's address to 123 Maple Street. So user one issue the follwoing update statement

update person set address='123 Maple Street' where ssn='111-11-1111' and name='Bob' and address='123 Main Street' and sex='M'; commit;

In the mean time user 2 changes the address also. User two issues the following update statement
update person set address='123 Oak Street' where ssn='111-11-1111' and name='Bob' and address='123 Main Street' and sex='M';

User 2's update statement will return with 0 rows processed because the data was changed underneath him. Thus the system can alert user 2 to what occured and ask him what to do.

Jim Kennedy Received on Sun Feb 04 1996 - 00:00:00 CET